Improvements to data access layer

Question

 public class EmployeeDBHandler
    {
        public List<Employee> GetEmployees()
        {
            List<Employee> empList = new List<Employee>();
            string sqlConnstr = Utils.GetDBConnection();
            SqlConnection sqlConn = new SqlConnection(sqlConnstr);
            SqlCommand sqlCmd = new SqlCommand("GetAllEmployee", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlConn.Open();
            using (SqlDataReader reader = sqlCmd.ExecuteReader())
            { 
                while (reader.Read())
                {
                    var emp = new Employee()
                    {
                        EId = (int)(reader["EmpID"]),
                        FirstName = Convert.ToString(reader["FirstName"]),
                        LastName = Convert.ToString(reader["LastName"])                        
                    };
                    empList.Add(emp);
                }
            }
            return empList;
        }
        public List<Employee> FetchEmployee(int empid)
        {
            List<Employee> empList = new List<Employee>();
            string sqlConnstr = Utils.GetDBConnection();
            SqlConnection sqlConn = new SqlConnection(sqlConnstr);
            SqlCommand sqlCmd = new SqlCommand("usp_FetchEmployeeDetails", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@EmpID", empid);
            sqlConn.Open();
            using (SqlDataReader reader = sqlCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var emp = new Employee()
                    {
                        EId = (int)(reader["EmpID"]),
                        FirstName = (reader["FirstName"].ToString()),
                        LastName = (reader["LastName"].ToString())
                    };
                    empList.Add(emp);
                }
            }
            return empList;
        }
        public void AddEmployeeInfo(string firstname, string lastname)
        {
            string sqlConnstr = Utils.GetDBConnection();
            SqlConnection sqlConn = new SqlConnection(sqlConnstr);
            SqlCommand sqlCmd = new SqlCommand("AddEmployee", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@FirstName", firstname);
            sqlCmd.Parameters.AddWithValue("@LastName", lastname);
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlConn.Close();
        }
        public void UpdateEmployee(int empid,string firstname, string lastname)
        {
            string sqlConnstr = Utils.GetDBConnection();
            SqlConnection sqlConn = new SqlConnection(sqlConnstr);
            SqlCommand sqlCmd = new SqlCommand("usp_UpdateEmployeeDetails", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@EmpID", empid);
            sqlCmd.Parameters.AddWithValue("@FirstName", firstname);
            sqlCmd.Parameters.AddWithValue("@LastName", lastname);
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlConn.Close();
        }
        public void DeleteEmployee(int empid)
        {
            string sqlConnstr = Utils.GetDBConnection();
            SqlConnection sqlConn = new SqlConnection(sqlConnstr);
            SqlCommand sqlCmd = new SqlCommand("usp_DeleteEmployeeDetails", sqlConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.Parameters.AddWithValue("@EmpID",empid);
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlConn.Close();
        }

    }

I'm try to build a 3-tier arch. but I feel these code can be improvised so please review my above Data Access Layer code and give suggestions and corrections, if any, to improvise my code.

Answer 1

something along these lines

public static class DTExtensions
{
    public static List<T> ToList<T>(this System.Data.DataTable dt) where T : new()
    {
        var obj = dt.Rows.OfType<System.Data.DataRow>().Select(dr => dr.ToObject<T>()).ToList();
        return obj;
    }
    public static T ToObject<T>(this System.Data.DataRow dataRow) where T : new()
    {
        T item = new T();
        var itemType = item.GetType();
        foreach (System.Data.DataColumn column in dataRow.Table.Columns)
        {
            System.Reflection.PropertyInfo property = itemType.GetProperty(column.ColumnName);

            if (property != null && dataRow[column] != DBNull.Value)
            {
                var result = Convert.ChangeType(dataRow[column], property.PropertyType);
                property.SetValue(item, result, null);
            }
        }

        return item;
    }
}


public class DataHandler
{
    private readonly string _ConnectionInfo = String.Empty;
    public DataHandler(string connectioninfo)
    {
        _ConnectionInfo = connectioninfo;
    }

    public async Task<System.Data.DataTable> DataAsync( string commandSQL, IEnumerable<System.Data.SqlClient.SqlParameter> listParameter=null)
    {
        System.Data.DataTable dt = new System.Data.DataTable("data");

        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(_ConnectionInfo))
        {
            try
            {
                await connection.OpenAsync().ConfigureAwait(false);
            }
            catch (InvalidOperationException ex)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                return null;
            }
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(commandSQL, connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                if (listParameter != null && listParameter.Any())
                {
                    command.Parameters.AddRange(listParameter.Where(p => p != null).ToArray());
                }
                using (var r = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess).ConfigureAwait(false))
                {
                    dt.Load(r);
                }
                command.Parameters.Clear();
            }
            connection.Close();
        }

        return dt;
    }
}

public sealed class Employee
{
    public string EmpID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class EmployeeDBHandler
{
    private readonly DataHandler _DataHandler;
    public EmployeeDBHandler(DataHandler datahandler)
    {
        _DataHandler = datahandler ?? throw new System.ArgumentNullException("datahandler");
    }

    public async Task<List<Employee>> GetEmployees()
    {
        System.Data.DataTable dt = await _DataHandler.DataAsync("GetAllEmployee").ConfigureAwait(false);
        if (dt != null)
        {
            //OR look into using yeild
            //yield return employee
            //OR
            var list = dt.ToList<Employee>();
            return list;
        }
        return null;
    }

    public async Task<List<Employee>> FetchEmployee(int empid)
    {
        List<Employee> empList = new List<Employee>();
        var parameters = new List<System.Data.SqlClient.SqlParameter> { new SqlParameter("@EmpID", empid) };
        System.Data.DataTable dt = await _DataHandler.DataAsync("usp_FetchEmployeeDetails", parameters).ConfigureAwait(false);
        if (dt != null && dt.Rows.Count>0)
        {
            var list = dt.ToList<Employee>();
            return list;
        }
        return null;
    }
}

//  main program:
public class MainProgram
{
    public void DoSomeWork()
    {
        string sqlConnstr = @"Data Source=<...>; Initial Catalog=<...>; User ID=username; Password=password"; //Utils.GetDBConnection();
        var dbHandler = new DataHandler(sqlConnstr);
        var employeeHandler = new EmployeeDBHandler(dbHandler);

        var employees = employeeHandler.GetEmployees();
        if (employees != null)
        {
            //.....
        }
    }
}