LINQ的提示:从一个DataTable填充任何泛型List
简介
随着LINQ的到来，有时是更为有用的加载通用的清单，而不是在数据表中的所有数据库中的值。这是因为使用列表更友好，在我看来，在LINQ。本文演示了如何加载的DataTable在任何泛型列表中的任何一个动态的方式。我们将创建两个类，填写查询两个数据表，在两个不同的泛型列表的所有值和负载范围内的数据表。最后，我们将看到一个虚拟的例子; navigationquot LINQ的quot;通过名单。背景
为了使用的代码，它必须有两个重要的事情记:与查询字段相同的属性的名称，您必须定义一个类。属性的数据类型必须是相同的数据字段。使用代码
首先，我们要创建两个表，并填充它们。在这个例子中，创建一个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')
一旦创建和填充表，我们必须声明两个类。这些类将代表我们要加载列表中的表中的行，或者至少是在查询请求的领域的代表性:{C}
正如你可以看到，属性和它们的类型的名称是相同的的表中宣布的领域。
现在，我们要检查的quot; corequot;类。类DataFiller是一个泛型类。这个类的类型将作为quot;登记likequot;类。它会收到一个DataTable，导航思想的DataRow和领域。当循环到达各个领域，它会找到对应的类成员，将分配在属性的正确值。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;
}
}
}
现在，我们可以运行测试的例子:
你可以下载一个测试项目，以检查它是如何工作的。可以自由离开任何意见或建议。希望有所帮助。