Tips for LINQ: Fill any generic List from a DataTable

• Download sample - 20.3 KB

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:

1. You must define a class with properties names as the same as the query fields.
2. 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
-- 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')

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 DataRows 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;

//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;
    }
}
}

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();

            //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());
        }
    }
}

You can download a test project in order to check how it works. Feel free to leave any comments or suggestions. Hope it helps.