c# - 将 IDataRecord 单个记录分组到集合中

Question

我在数据库中有如下所列的数据。

我有以下适用于简单场景的数据访问层代码。但是对于上述场景，我需要基于 employeeID 分组 的结果。一个员工的所有角色都应该属于一个员工目的。

我们如何通过使用 C# 的通用委托(delegate)功能修改以下数据访问代码来实现这一点？

注意:我正在寻找一种不使用 DataTable 的解决方案(因为 DataTable 预先加载所有数据并且比 IDataRecord 方法慢)。

引用文献

1. An Elegant C# Data Access Layer using the Template Pattern and Generics
2. Using C# generics and factory classes to map IDataReader to POCO

数据传输对象

public class Role
{
    public int RoleID { get; set; }
    public string RoleName { get; set; }
}


public class Employee
{
    public int EmployeeID { get; set; }
    public string EmployeeName { get; set; }
    public List<Role> Roles { get; set; }

    //IDataRecord Provides access to the column values within each row for a DataReader
    //IDataRecord is implemented by .NET Framework data providers that access relational databases.

    //Factory Method
    public static Employee EmployeeFactory(IDataRecord record)
    {
        return new Employee
        {
            EmployeeID = (int)record[0],
            EmployeeName = (string)record[1]
        };
    }
}

通用 DAL

public class MyCommonDAL
{
    public static IEnumerable<T> ExecuteQueryGenericApproach<T>(string commandText, List<SqlParameter> commandParameters, Func<IDataRecord, T> factoryMethod)
    {
        string connectionString = @"Server=TRVMVSDDVXXXX;Database=AS400_Source;User Id=XXXXXXXX;Password=XXXXXXX";

        //Action, Func and Predicate are pre-defined Generic delegates.
        //So as delegate they can point to functions with specified signature.

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.CommandTimeout = 0;
                command.Parameters.AddRange(commandParameters.ToArray());

                connection.Open();
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        yield return factoryMethod(rdr);
                    }
                    rdr.Close();
                }
            }
        }
    }
}

特定 DAL

public class MyEmployeeDAL
{
    public List<Employee> GetEmployees(string excludedEmployee)
    {


        List<SqlParameter> commandParameters = new List<SqlParameter>()
                                                {
                                                    new SqlParameter {ParameterName = "@ExcludedEmployee", 
                                                                      Value = excludedEmployee, 
                                                                      SqlDbType = SqlDbType.VarChar}
                                                };


        string commandText = @"SELECT E.EmployeeID,E.EmployeeName,R.RoleID,R.RoleName FROM dbo.EmployeeRole ER
                                INNER JOIN dbo.Employee E  ON E.EmployeeID= ER.EmployeeID
                                INNER JOIN dbo.[Role] R ON R.RoleID= Er.RoleID 
                                WHERE EmployeeName <> @ExcludedEmployee";

        IEnumerable<Employee> employees = MyCommonDAL.ExecuteQueryGenericApproach<Employee>(commandText, commandParameters, Employee.EmployeeFactory);
        return employees.ToList();
    }
}

客户端

    static void Main(string[] args)
    {
        MyEmployeeDAL logDAL = new MyEmployeeDAL();
        List<Employee> logSeverities = logDAL.GetEmployees("test");
    }

Answer 1

你应该添加新的平面类

public class RoleAndEmployee
{
    public int RoleID { get; set; }
    public string RoleName { get; set; }
    public int EmployeeID { get; set; }
    public string EmployeeName { get; set; }

    public static Employee EmployeeFactory(IDataRecord record)
    {
        return new RoleAndEmployee
        {
            EmployeeID = (int)record[0],
            EmployeeName = (string)record[1],
            RoleID = (int)record[2],
            RoleName = (string)record[3]
        };
    }
}

然后调用(我希望，我在没有 IDE 的情况下写得正确):

IEnumerable<Employee> employees = MyCommonDAL.ExecuteQueryGenericApproach<RoleAndEmployee>(commandText, commandParameters, RoleAndEmployee.EmployeeFactory)
    .GroupBy(c=>new {c.EmployeeId, c.EmployeeName}, c=>new{c.RoleId, c.RoleName})
    .Select(k=>new Employee{EmployeeId=k.Key.EmployeeId, EmployeeName= k.Key.EmployeeName, Roles = k.ToList()});

更新:如果你不想引入平面类，你可以使用下一个方法:

public static Employee EmployeeFactory(IDataRecord record)
{
    var employee = new Employee
    {
        EmployeeID = (int)record[0],
        EmployeeName = (string)record[1],
        Roles = new List<Role>()
    };
    employee.Roles.Add(new Role{RoleID = (int)record[2], roleName=(string)record[3]});
    return employee;
}

IEnumerable<Employee> employees = MyCommonDAL.ExecuteQueryGenericApproach
    <Employee>(commandText, commandParameters, Employee.EmployeeFactory)
        .GroupBy(
            x => new { x.EmployeeID, x.EmployeeName},
            (key, group) => 
                new Employee
                    {
                        EmployeeId=key.EmployeeID, 
                        EmployeeName=key.EmployeeName,
                        Roles = group.SelectMany(v => v.Roles).ToList()
                    }).ToList();