Wednesday, 12 February 2014

.Net Tips : Convert DataTable object to list of class object using LINQ with C#.Net and VB.Net example

You can fill List of object from DataTable using LINQ. There are many situations where you need to get data from DB into data table and after that from data table to proper class object. We can do this easily with LINQ.

Here is example for this.
In this example we take one DataTable object which contains two columns 'product_id' and 'product_name'. This DataTable has many rows. Now we want to convert DataTable object into list of class object. We have one Class 'clsProduct' which has two property 'product_id' and 'product_name'. Now we transform DataTable object data in to list of clsProduct class object using LINQ.

C#. Net Example :
    private DataTable GetProducts()
        DataTable dt = new DataTable();
        DataColumn dc;
        DataRow dr;

        dt.TableName = "product";

        dc = new DataColumn("product_id", int.MaxValue.GetType());

        dc = new DataColumn("product_name");

        dr = dt.NewRow();
        dr["product_id"] = 1;
        dr["product_name"] = "Monitor";

        dr = dt.NewRow();
        dr["product_id"] = 2;
        dr["product_name"] = "Mouse";

        dr = dt.NewRow();
        dr["product_id"] = 3;
        dr["product_name"] = "KeyBoard";

        dr = dt.NewRow();
        dr["product_id"] = 4;
        dr["product_name"] = "LCD";

        return dt;
    public class clsProduct
        public int product_id;
        public string product_name;
    protected void Page_Load(object sender, EventArgs e)

        DataTable dt = GetProducts();
        List<clsProduct> lstProduct;
        lstProduct = (
                        from m in dt.Rows.Cast<DataRow>() 
                        select new clsProduct { 
                            product_id = m.Field<Int32>("product_id"), 
                            product_name = m.Field<string>("product_name") }

VB.Net Examples :
    Private Function GetProducts() As DataTable
        Dim dt As New DataTable()
        Dim dc As DataColumn
        Dim dr As DataRow

        dt.TableName = "product"

        dc = New DataColumn("product_id", Integer.MaxValue.[GetType]())

        dc = New DataColumn("product_name")

        dr = dt.NewRow()
        dr("product_id") = 1
        dr("product_name") = "Monitor"

        dr = dt.NewRow()
        dr("product_id") = 2
        dr("product_name") = "Mouse"

        dr = dt.NewRow()
        dr("product_id") = 3
        dr("product_name") = "KeyBoard"

        dr = dt.NewRow()
        dr("product_id") = 4
        dr("product_name") = "LCD"

        Return dt
    End Function
    Public Class clsProduct
        Public product_id As Integer
        Public product_name As String
    End Class
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dt As DataTable = GetProducts()
        Dim lstProduct As List(Of clsProduct)
        lstProduct = (
                        From m In dt.Rows.Cast(Of DataRow)()
                        Select New clsProduct With { _
                            .product_id = m.Field(Of Int32)("product_id"), _
                            .product_name = m.Field(Of String)("product_name") _
    End Sub

Output : 
Conversion from DataTable object to Class Object

Below are the books that you would like :


  1. It is really a great work and the way in which u r sharing the knowledge is excellent.
    Thanks for helping me to understand basic concepts. As a beginner in Dot Net programming your post help me a lot.Thanks for your informative article.. dot net training and placement in chennai | best dot net training in chennai

  2. Given so much information in it. its very useful .perfect explanation about Dot net framework.Thanks for your valuable information. dot net training in chennai velachery | dot net training institute in velachery

  3. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
    Software Testing Training in Chennai