Introduction
With the arrival of LINQ, sometimes it is more useful to have all the database values loaded in a generic list instead of being in DataTables. This is because the use of a list is more friendly in LINQ, in my opinion. This article shows how to load any DataTable in any generic list in a dynamic way. We are going to create two classes, fill two DataTables with a query, and load all the values within the DataTables in two different generic lists. At the end, we will see a dummy example of LINQ "navigation" through the list.
Background
In order to use the code, it's necessary to have two important things in mind:
- You must define a class with properties names as the same as the query fields.
- The data types of the properties must be the same as that of the data fields.
Using the code
First of all, we are going to create two tables and populate them. In this example, they are created in a SQL Server database, but feel free to use any kind of database:
USE [Test]
GO
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
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]
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')
Once the tables are created and populated, we have to declare two classes. These classes will be a representation of the row of the table we want to load in the list, or at least a representation of the fields requested in the query:
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; }
}
As you can see, the names of the properties and their types are identical to the fields declared in the tables.
Now, we are going to check the "core" class. The class DataFiller
is a Generic class. The type of this class will be the same as the "register like" class. It will receive a DataTable
, and navigate thought the DataRow
s and the fields. When the loop reaches each field, it will locate the corresponding member of the class and will assign the correct value inside the property.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
namespace FromDataTableToGenericListExample
{
public class DataFiller<T>
{
public List<T> FromDataTableToList(DataTable dataTable)
{
List<T> genericList = new List<T>();
Type t = typeof(T);
PropertyInfo[] pi = t.GetProperties();
foreach (DataRow row in dataTable.Rows)
{
object defaultInstance = Activator.CreateInstance(t);
foreach (PropertyInfo prop in pi)
{
try
{
object columnvalue = row[prop.Name];
if (columnvalue!= DBNull.Value)
{
prop.SetValue(defaultInstance, columnvalue, null);
}
}
catch (Exception ex)
{
Console.WriteLine(prop.Name + ": " + ex.ToString());
return null;
}
}
T myclass = (T)defaultInstance;
genericList.Add(myclass);
}
return genericList;
}
}
}
Now, we can run the test example:
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();
if (SqlDataBase.Connect(servername, dabasename, user, password) == true)
{
string sqlEmployees = "SELECT NAME,DEPARTMENT,SALARY FROM EMPLOYEES";
dEmployees = SqlDataBase.ReturnDataTable(sqlEmployees);
DataFiller<Employee> dtfEmployee = new DataFiller<Employee>();
Employees= dtfEmployee.FromDataTableToList(dEmployees);
string sqlClients = "SELECT ID,NAME,COUNTRY,ADDRESS FROM CLIENTS";
dClients = SqlDataBase.ReturnDataTable(sqlClients);
DataFiller<Client> dtfClient = new DataFiller<Client>();
Clients = dtfClient.FromDataTableToList(dClients);
SqlDataBase.Disconnect();
}
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());
}
}
}
You can download a test project in order to check how it works. Feel free to leave any comments or suggestions. Hope it helps.