// Program.cs
using Microsoft.Data.SqlClient;
using System.Data;

namespace MyConsoleApp // Add a namespace, optional but good practice
{
    internal class Program // Define a class
    {
        private const string _connectionString = @"Server=Judah;Database=SampleDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static void Main(string[] args) 
        {
            UseOutputParameters(_connectionString);
            GetAReturnValue(_connectionString);
            GetASingleValue(_connectionString);
            GetMultipleValues(_connectionString);
            GetMultipleValuesWithADatatable(_connectionString);
            UseIOParameters(_connectionString);

            Console.ReadKey();
        }

        private static void UseIOParameters(string connectionString)
        {
            Console.WriteLine();
            Console.WriteLine("UseIOParameters()");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("GetUserNameById", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // Input parameter
                cmd.Parameters.AddWithValue("@UserID", 1);

                // Output parameter
                SqlParameter outputParam = new SqlParameter("@UserName", SqlDbType.NVarChar, 100)
                {
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(outputParam);

                conn.Open();
                cmd.ExecuteNonQuery();

                string userName = outputParam.Value.ToString();
                Console.WriteLine($"User Name: {userName}");
            }

        }

        private static void GetMultipleValuesWithADatatable(string connectionString)
        {
            Console.WriteLine();
            Console.WriteLine("GetMultipleValuesWithADatatable()");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("GetUsers", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(row["UserName"]);
                }
            }

        }

        private static void GetMultipleValues(string connectionString)
        {
            Console.WriteLine();
            Console.WriteLine("GetMultipleValues()");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("GetUsers", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["UserName"]);
                    }
                }
            }
        }

        private static void GetASingleValue(string connectionString)
        {
            Console.WriteLine();
            Console.WriteLine("GetASingleValue()");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Users", conn);
                conn.Open();

                int userCount = (int)cmd.ExecuteScalar();

                Console.WriteLine($"User count from GetASingleValue(): {userCount}");
            }
        }

        private static void GetAReturnValue(string connectionString)
        {
            Console.WriteLine();
            Console.WriteLine("GetAReturnValue()");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("GetRecordStatus", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter returnParam = new SqlParameter
                {
                    Direction = ParameterDirection.ReturnValue
                };
                cmd.Parameters.Add(returnParam);

                conn.Open();
                cmd.ExecuteNonQuery();

                int statusCode = (int)returnParam.Value;

                Console.WriteLine($"Status Code: {statusCode}");
            }
        }

        static void UseOutputParameters(string connectionString)
        {
            Console.WriteLine();
            Console.WriteLine("UseOutputParameters()");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("GetUserCount", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter outputParam = new SqlParameter("@UserCount", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(outputParam);

                conn.Open();
                cmd.ExecuteNonQuery();

                int userCount = (int)outputParam.Value;
                Console.WriteLine($"User count from UseOutputParameters() {userCount}");
            }

        }
    }
}