LINQ技巧：从DataTable填充任何泛型列表
4.26/5 (10投票s)
本文展示了如何将DataTable填充到任何泛型列表。
引言
随着LINQ的出现，有时将所有数据库值加载到泛型列表中，而不是保留在DataTables中，会更有用。我认为使用列表在LINQ中更友好。本文展示了如何以动态方式将任何DataTable加载到任何泛型列表中。我们将创建两个类，使用查询填充两个DataTables，并将DataTables中的所有值加载到两个不同的泛型列表中。最后，我们将看到一个LINQ“导航”通过列表的示例。
背景
为了使用这段代码，需要牢记两点：
- 您必须定义一个类，其属性名称与查询字段相同。
- 属性的数据类型必须与数据字段的数据类型相同。
使用代码
首先，我们将创建两个表并填充它们。在本例中，它们是在SQL Server数据库中创建的，但您可以随意使用任何类型的数据库。
USE [Test]
GO
-- Table [dbo].[EMPLOYEES]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EMPLOYEES](
[Name] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Department] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Salary] [float] NOT NULL,
CONSTRAINT [PK_EMPLOYEES] PRIMARY KEY CLUSTERED
(
[Name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [Test]
GO
--Table [dbo].[CLIENTS]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CLIENTS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Address] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
--Data
INSERT INTO [Test].[dbo].[EMPLOYEES] VALUES ('Peter','Sales',1000)
INSERT INTO [Test].[dbo].[EMPLOYEES] VALUES ('John','Sales',1500)
INSERT INTO [Test].[dbo].[EMPLOYEES] VALUES ('Mary','Orders',1500)
INSERT INTO [Test].[dbo].[EMPLOYEES] VALUES ('Henry',null,3000)
INSERT INTO [Test].[dbo].[CLIENTS] VALUES ('TechEmp','Canada','Unknown')
INSERT INTO [Test].[dbo].[CLIENTS] VALUES ('Ars Inc','Canada','Unknown')
INSERT INTO [Test].[dbo].[CLIENTS] VALUES ('Aslter','Polony','Unknown')
INSERT INTO [Test].[dbo].[CLIENTS] VALUES ('Malper','France','Unknown')
创建并填充表后，我们需要声明两个类。这些类将是对我们想要加载到列表中的表的行的表示，或者至少是对查询中请求的字段的表示。
public class Employee
{
public string Name { get; set; }
public string Department { get; set; }
public double Salary { get; set; }
}
public class Client
{
public int Id { get; set; }
public string Name { get; set; }
public string Country { get; set; }
public string Address { get; set; }
}
如您所见，属性的名称和类型与表中声明的字段完全相同。
现在，我们将检查“核心”类。DataFiller类是一个泛型类。该类的类型将与“类似记录”的类相同。它将接收一个DataTable，并遍历DataRows和字段。当循环到达每个字段时，它将定位类中相应的成员，并将正确的值分配到属性中。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
//This is neccesary in order to "navigate"
//throught the class members
using System.Reflection;
namespace FromDataTableToGenericListExample
{
//In order to return any generic container (in this example is a list)
//,the class must be generic
public class DataFiller<T>
{
public List<T> FromDataTableToList(DataTable dataTable)
{
//This create a new list with the same type of the generic class
List<T> genericList = new List<T>();
//Obtains the type of the generic class
Type t = typeof(T);
//Obtains the properties definition of the generic class.
//With this, we are going to know the property names of the class
PropertyInfo[] pi = t.GetProperties();
//For each row in the datatable
foreach (DataRow row in dataTable.Rows)
{
//Create a new instance of the generic class
object defaultInstance = Activator.CreateInstance(t);
//For each property in the properties of the class
foreach (PropertyInfo prop in pi)
{
try
{
//Get the value of the row according to the field name
//Remember that the classïs members and the tableïs field names
//must be identical
object columnvalue = row[prop.Name];
//Know check if the value is null.
//If not, it will be added to the instance
if (columnvalue!= DBNull.Value)
{
//Set the value dinamically. Now you need to pass as an argument
//an instance class of the generic class. This instance has been
//created with Activator.CreateInstance(t)
prop.SetValue(defaultInstance, columnvalue, null);
}
}
catch (Exception ex)
{
Console.WriteLine(prop.Name + ": " + ex.ToString());
return null;
}
}
//Now, create a class of the same type of the generic class.
//Then a conversion itïs done to set the value
T myclass = (T)defaultInstance;
//Add the generic instance to the generic list
genericList.Add(myclass);
}
//At this moment, the generic list contains all de datatable values
return genericList;
}
}
}
现在，我们可以运行测试示例了。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace FromDataTableToGenericListExample
{
public class TestExample
{
public void StartExample()
{
string servername = "MYPC\\SQLDES";
string dabasename = "test";
string user = "myuser";
string password = "mypass";
List<Employee> Employees = null;
List<Client> Clients = null;
DataTable dEmployees = new DataTable();
DataTable dClients = new DataTable();
//Connect to the database
if (SqlDataBase.Connect(servername, dabasename, user, password) == true)
{
//Get the first DataTable
string sqlEmployees = "SELECT NAME,DEPARTMENT,SALARY FROM EMPLOYEES";
dEmployees = SqlDataBase.ReturnDataTable(sqlEmployees);
//Fill the first generic List with the DataTable values
DataFiller<Employee> dtfEmployee = new DataFiller<Employee>();
Employees= dtfEmployee.FromDataTableToList(dEmployees);
//Get another DataTable
string sqlClients = "SELECT ID,NAME,COUNTRY,ADDRESS FROM CLIENTS";
dClients = SqlDataBase.ReturnDataTable(sqlClients);
//Fill another generic List with the DataTable values
DataFiller<Client> dtfClient = new DataFiller<Client>();
Clients = dtfClient.FromDataTableToList(dClients);
//Disconnect
SqlDataBase.Disconnect();
} //Linq Dummy Example
Employee employee = (from e in Employees
where e.Name == "Mary"
select e).First();
Console.WriteLine("The salary is: " + employee.Salary.ToString());
Client client = (from c in Clients
where c.Country == "Polony"
select c).First();
Console.WriteLine("The name is: " + client.Name.ToString());
}
}
}
您可以下载一个测试项目来检查它的工作方式。欢迎留下任何评论或建议。希望对您有所帮助。