Connection String

<connectionStrings>
    <add name="db" connectionString="Data Source=.;Initial Catalog=hr;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Class Department

class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Class DepartmentRepository

class DepartmentRepository
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
}

Method GetDepartments

public static List<Department> GetDepartments()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("GetDepartments", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            List<Department> list = new List<Department>();
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(new Department
                    {
                        Id = (int)reader["DepartmentId"],
                        Name = (string)reader["DepartmentName"]
                    });
                }
                
            }
            return list;
        }
    }
}

Method GetDepartmentById

public static Department GetDepartmentById(int id)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("GetDepartmentById", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Value = id;
            connection.Open();
            using (SqlDataReader reader  = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new Department
                    {
                         Id = (int)reader["DepartmentId"],
                        Name = (string)reader["DepartmentName"]
                    };
                }
                return null;
            }
        }
    }
}

Method GetDepartmentById Extention

Class Employee
class Employee
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public bool Gender { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string Address { get; set; }
    public int Salary { get; set; }
    public int DepartmentId { get; set; }
}
Update class Department
class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Employee> Employees { get; set; }
}
Method GetDepartmentById
public static Department GetDepartmentById(int id)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("GetDepartmentById", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Value = id;
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    Department obj = new Department
                    {
                        Id = (int)reader["DepartmentId"],
                        Name = (string)reader["DepartmentName"]
                    };
                    if (reader.NextResult())
                    {
                        List<Employee> list = new List<Employee>();
                        while (reader.Read())
                        {
                            list.Add(new Employee
                            {
                                Id = (int)reader["EmployeeId"],
                                FullName = (string)reader["FullName"],
                                Address = (string)reader["Address"],
                                DateOfBirth = (DateTime)reader["DateOfBirth"],
                                DepartmentId = (int)reader["DepartmentId"],
                                Gender = (bool)reader["Gender"],
                                Salary = (int)reader["Salary"]
                            });
                        }
                        obj.Employees = list;
                    }
                    return obj;
                }
                return null;
            }
        }
    }
}

Method Add Department

public static int Add(Department obj)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("AddDepartment", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@name", SqlDbType.NVarChar, 128).Value = obj.Name;
            connection.Open();
            return command.ExecuteNonQuery();
        }
    }
}

Method Add Deparment and Ouput parementer

public static int Add(Department obj)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("AddDepartment", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@name", SqlDbType.NVarChar, 128).Value = obj.Name;
            connection.Open();
            int ret = command.ExecuteNonQuery();
            if (ret > 0)
            {
                obj.Id = (int)command.Parameters["@id"].Value;
                return ret;
            }
            return 0;
        }
    }
}

Class Member

class Member
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
}

Class MemberRepository

class MemberRepository
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
}

Method Add Member

public static int Add(Member obj)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("AddMember", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@username", SqlDbType.VarChar, 32).Value = obj.Username;
            command.Parameters.Add("@password", SqlDbType.VarChar, 16).Value = obj.Password;
            command.Parameters.Add("@ret", SqlDbType.TinyInt).Direction = ParameterDirection.ReturnValue;
            connection.Open();
            int rs = command.ExecuteNonQuery();
            if (rs > 0)
            {
                obj.Id = (int)command.Parameters["@id"].Value;
            }
            return (int)command.Parameters["@ret"].Value;
        }
    }
}

Method Sign In

public static int SignIn(Member obj)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("SignIn", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@username", SqlDbType.VarChar, 64).Value = obj.Username;
            command.Parameters.Add("@password", SqlDbType.VarChar, 16).Value = obj.Password;
            command.Parameters.Add("@ret", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            connection.Open();
            command.ExecuteNonQuery();
            int ret = (int)command.Parameters["@ret"].Value;
            if (ret == 1)
                obj.Id = (int)command.Parameters["@id"].Value;
            return ret;
        }
    }
}