MENU

Convert LINQ Query Result to Datatable

Filed Under: , , on
I have read many forum post regarding how to convert LINQ var Query Result to Data table and also there is always need to Convert LINQ Query Result To Data table so by considering above requirement I have decided to write this article, so let us learn step by step how to Convert LINQ Query Result To Data table
 What is LINQ ?
LINQ stands for Language-Integrated Query is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic, using LINQ you can manipulate the data as similar SQL queries .
Let us learn it practically how to convert LINQ query result to Datatable by creating one simple application as
Now create the project as:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
  3. Provide the Project name such as "ConvertLinqResultToDataTable" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer and select "Add New Item" then select Default.aspx page.
  5. Drag and drop One Button and Grid view to bind the records after creating the data table from LINQ query.
Now the Default.aspx source code will be as follows:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ConvertLinqResultToDataTable.Default" %>  
  
<!DOCTYPE html>  
  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title></title>  
</head>  
<body style="background-color: Blue">  
    <h4 style="color: White">Article by Vithal Wadje</h4>  
    <form id="form1" runat="server">  
        <table style="margin-top: 60px; color: White">  
  
            <tr>  
                <td></td>  
                <td>  
                    <asp:Button ID="btngetresult" runat="server" Text="Get Result"  
                         />  
                </td>  
            </tr>  
  
            <tr>  
                <td>  
                    <asp:GridView ID="GridView1" runat="server"></asp:GridView>  
                </td>  
            </tr>  
        </table>  
  
    </form>  
</body>  
</html>
Now open the Default.aspx.cs class file and create class named Customer with properties as
public class Customer   
    {  
        private string _Name, _City, _Address;  
  
        public string Name  
        {  
            get { return _Name; }  
            set { _Name = value; }  
        }  
  
        public string City  
        {  
            get { return _City; }  
            set { _City = value; }  
        }  
  
        public string Address  
        {  
            get { return _Address; }  
            set { _Address = value; }  
        }  
                    
    }
Now create the Array of type customer class and assign the values to the each property as
Customer[] cust = new Customer[]  
        {  
          
        new Customer{Name="Vithal Wadje",City="Mumbai",Address="Boriwali"},  
        new Customer{Name="Sudhir Wadje",City="Latur",Address="Kabansangvi"},  
        
          
        };
Now we have records to fire the LINQ query, you can also take the records from database but for ease of understanding I have not used database records.
Now create function to named LINQResultToDataTable which convert LINQ Result To DataTable as
public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)  
       {  
           DataTable dt = new DataTable();  
  
            
           PropertyInfo[] columns = null;  
  
           if (Linqlist == null) return dt;  
  
           foreach (T Record in Linqlist)  
           {  
                 
               if (columns == null)  
               {  
                   columns = ((Type)Record.GetType()).GetProperties();  
                   foreach (PropertyInfo GetProperty in columns)  
                   {  
                       Type colType = GetProperty.PropertyType;  
  
                       if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()  
                       == typeof(Nullable<>)))  
                       {  
                           colType = colType.GetGenericArguments()[0];  
                       }  
  
                       dt.Columns.Add(new DataColumn(GetProperty.Name, colType));  
                   }  
               }  
  
               DataRow dr = dt.NewRow();  
  
               foreach (PropertyInfo pinfo in columns)  
               {  
                   dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ? DBNull.Value : pinfo.GetValue  
                   (Record, null);  
               }  
  
               dt.Rows.Add(dr);  
           }  
           return dt;  
       }
The above function takes the LINQ query result and convert it into the Data Table, if you wants to learn more about IEnumerable and  above function how it work, refer my following article
Now Double click on Get Result Button and write the following code on Click event of button as
protected void btngetResult_Click(object sender, EventArgs e)  
      {  
          //linq Query  
          var query = from Customer s in cust  
                      select s;  
          
          //stored result into datatable  
          DataTable dt = LINQResultToDataTable(query);  
          //bind gridview  
          GridView1.DataSource = dt;  
          GridView1.DataBind();  
      }
After creating the functions to convert LINQ query result to Data Table ,then the entire Default.aspx.cs code will be look like as follows
using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Linq;  
using System.Reflection;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
  
namespace ConvertLinqResultToDataTable  
{  
    public partial class Default : System.Web.UI.Page  
    {  
  
        protected void Page_Load(object sender, EventArgs e)  
        {  
  
        }  
  
  
        Customer[] cust = new Customer[]  
        {  
          
        new Customer{Name="Vithal Wadje",City="Mumbai",Address="Boriwali"},  
        new Customer{Name="Sudhir Wadje",City="Latur",Address="Kabansangvi"},  
        
          
        };  
  
        
        protected void btngetResult_Click(object sender, EventArgs e)  
        {  
            //linq Query  
            var query = from Customer s in cust  
                        select s;  
            
            //stored result into datatable  
            DataTable dt = LINQResultToDataTable(query);  
            //bind gridview  
            GridView1.DataSource = dt;  
            GridView1.DataBind();  
        }  
  
  
  
        public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)  
        {  
            DataTable dt = new DataTable();  
  
             
            PropertyInfo[] columns = null;  
  
            if (Linqlist == null) return dt;  
  
            foreach (T Record in Linqlist)  
            {  
                  
                if (columns == null)  
                {  
                    columns = ((Type)Record.GetType()).GetProperties();  
                    foreach (PropertyInfo GetProperty in columns)  
                    {  
                        Type colType = GetProperty.PropertyType;  
  
                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()  
                        == typeof(Nullable<>)))  
                        {  
                            colType = colType.GetGenericArguments()[0];  
                        }  
  
                        dt.Columns.Add(new DataColumn(GetProperty.Name, colType));  
                    }  
                }  
  
                DataRow dr = dt.NewRow();  
  
                foreach (PropertyInfo pinfo in columns)  
                {  
                    dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ? DBNull.Value : pinfo.GetValue  
                    (Record, null);  
                }  
  
                dt.Rows.Add(dr);  
            }  
            return dt;  
        }  
  
    }  
  
    public class Customer   
    {  
        private string _Name, _City, _Address;  
  
        public string Name  
        {  
            get { return _Name; }  
            set { _Name = value; }  
        }  
  
        public string City  
        {  
            get { return _City; }  
            set { _City = value; }  
        }  
  
        public string Address  
        {  
            get { return _Address; }  
            set { _Address = value; }  
        }  
      
    }  
}
Now we have written all logic to convert LINQ query result to Data Table, now run the application the initial screen will be look like as follows
 
Click on above Get Result button, then LINQ Result will convert into Data Table and it will displayed into the Grid view are as follows
 
From above example its clear that we can convert LINQ query Result to DataTable. 
Summary
I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also. 

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode