LINQ To DataTable

Sorry for not choosing bulk insert / update again in this post. I select this topic LINQ to DataTable because I think this is a very interesting topic to post. In one of the project  in my company, I saw many DataTable operations using the old style – dt.Select() method to filter DataRows. There are lots of chances of errors at run time because all conditions are simple strings written in “.Select()” method.
If you have LINQ support in your project then LINQ to DataTable is the best way to filtering datarows from DataTable. This will also reduce run time errors and I also found that LINQ takes less time to execute then Select() method if amount of rows in DataTable is more.
So, let’s start by creating one temporary DataTable. We will use this DataTable to understand LINQ to DataTable.

Create new DataTable
// Create new DataTable
DataTable dtOrders = new DataTable();

// Add columns you need
dtOrders.Columns.Add("OrderID", typeof(int));
dtOrders.Columns.Add("OrderDate", typeof(DateTime));
dtOrders.Columns.Add("ShippedDate", typeof(DateTime));
dtOrders.Columns.Add("ShipName", typeof(string));
dtOrders.Columns.Add("ShipAddress", typeof(string));
dtOrders.Columns.Add("ShipCity", typeof(string));
dtOrders.Columns.Add("ShipRegion", typeof(string));
dtOrders.Columns.Add("ShipPostalCode", typeof(string));
dtOrders.Columns.Add("ShipCountry", typeof(string));



Fill sample data
// Create new DataRow
DataRow drOrder = null;

// Loop on Customers table in database to add required values in new DataTable
foreach (Orders o in Orders.ToArray()) // Here Orders is table from Northwind database
{
// Initialize DataRow variable
drOrder = dtOrders.NewRow();

// Set required values in DataRow
drOrder["OrderID"] = o.OrderID;
drOrder["OrderDate"] = o.OrderDate;
drOrder["ShippedDate"] = o.ShippedDate == null ? DateTime.MinValue : o.ShippedDate;
drOrder["ShipName"] = o.ShipName;
drOrder["ShipAddress"] = o.ShipAddress;
drOrder["ShipCity"] = o.ShipCity;
drOrder["ShipRegion"] = o.ShipRegion;
drOrder["ShipPostalCode"] = o.ShipPostalCode;
drOrder["ShipCountry"] = o.ShipCountry;

// Add DataRow to DataTable
dtOrders.Rows.Add(drOrder);
}



Fetch rows from DataTable using LINQ
var drAll = from orders in dtOrders.AsEnumerable()
where orders.Field<DateTime>("ShippedDate") == new DateTime(1996, 7, 16)
select orders;

// Write below statement to assign result of above LINQ to new DataTable 
// DataTable dt = drAll.CopyToDataTable<DataRow>();



To understand more about “.CopyToDataTable()” method please visit http://msdn.microsoft.com/en-us/library/bb386921.aspx

Display result data
// Display result data
foreach (var order in drAll)
{
Console.WriteLine(String.Format("{0} | {1}", order["OrderID"], order["ShippedDate"]));
}



Sorting in DataTable using LINQ
// Order by ascending 
var drAll1 = from orders in dtOrders.AsEnumerable()
orderby orders.Field<DateTime>("ShippedDate") ascending
select orders;

// Order by descending
var drAll2 = from orders in dtOrders.AsEnumerable()
orderby orders.Field<DateTime>("ShippedDate") descending
select orders;



Group by in DataTable using LINQ
var drAll = from orders in dtOrders.AsEnumerable()
group orders by orders.Field<string>("ShipRegion") into g
select new { ShipRegion = g.Key, Group = g };



Instead of simple DataTable, you can also use typed dataset in your project. This will convert all your columns into property so in this case, instead of using “.Field<DateTime>(“ShippedDate”)” you can simply access “ShippedDate” property of your typed dataset. ex. where orders.ShippedDate == new DateTime(1996, 7, 16) where “.ShippedDate” is a columns of your typed dataset with data type DateTime.


Comments

Post a Comment

Popular posts from this blog

Lambda Expressions In Entity Framework

From the two options I mentioned in my last post, I chose lambda expression for this post for those programmers who are not much comfortable with SQL like LINQ query. Microsoft gives us very good option called “Lambda Expression”. In this post I am going to demonstrate below operations: Simple select query using LINQ & Simple select query using lambda expression Select query with where condition using LINQ & select query with where condition using lambda expression Inner Join using LINQ & Inner Join using lambda expression Left Join using LINQ & Left Join using lambda expression Group by using LINQ & Group by using lambda expression Sorting using LINQ & Sorting using lambda expression Simple select: // Using LINQ Employee [] allEmployees1 = ( from E in NorthwindEntity.Employees select E).ToArray(); // Using lambda expression Employee [] allEmployees2 = NorthwindEntity.Employees.ToArray(); // Where condition using LINQ Employee [] allEmployees1 = ( from ...
Read more

Select Columns From Multiple Tables Using LINQ

From Northwind database, to show all available products it needs to select values from multiple tables because in Products table there are two columns – CategoryID and SupplierID which are referenced from Category and Suppliers tables. There are more then one options to do this but I use below way to select from multiple tables and bind to grid. First of all create a class and create properties, ProductID, ProductName, CompanyName, CategoryName, UnitPrice. These are the properties which I want to show in grid. public class AvailableProducts { public int ProductID { get ; set ; } public string ProductName { get ; set ; } public string CompanyName { get ; set ; } public string CategoryName { get ; set ; } public decimal UnitPrice { get ; set ; } } Now, write below function in data access layer: public List < AvailableProducts > GetAllAvailableProducts() { var linqAllProducts = from p in NorthwindEntity.Products from c in NorthwindEntity.Categories from s in NorthwindEntity.Supp...
Read more

LINQ To Entity Framework Tutorial

As a startup, I choose this topic for those beginners who wants to use Entity Framework. The ADO.NET Entity Framework helps developers to access database very easily. All you need is to create a data model from actual database and programming against generated data model instead of programming directly against the actual database by either creating stored procedure or writing select/insert/update/delete query in data access layer itself. Using this way of programming during the development phase of the project helps to reduce amount of code to access database and also reduce maintenance work in future. Please follow below steps in order to create data access application using ADO.NET Entity Framework. In this tutorial I am using “Northwind” database in backend. Create new “ASP.Net Empty Web Application” using Visual Studio 2010. Create new folder under web application project. Name it “DataAccess” Right click on “DataAccess” folder and click on Add –> New Item… Select “Dat...
Read more