Convert LINQ Query Result to Datatable

Vithal Wadje
10y
77.4k
0
5

Article

Background

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:

"Start" - "All Programs" - "Microsoft Visual Studio 2010".
"File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
Provide the Project name such as "ConvertLinqResultToDataTable" or another as you wish and specify the location.
Then right-click on Solution Explorer and select "Add New Item" then select Default.aspx page.
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

IEnumerable Interface in C#

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.

Note

Download the Zip file from the attachment for the full source code of the application.

Summary

I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.

Up Next

Ebook Download

View all

ASP.NET MVC 5 Recipes: A Practical Solution

Read by 9.3k people

Download Now!

Learn

View all