LINQ to DataSet Code Review

Use LINQ to DataSet

Code Download

  • Download Description:linq to dataset download
  • .NET Framework:3.5
  • .NET Language:C#
  • Date Published:2009-07-01
  • Download Size:21 KB

Code Walkthrough

Define the database connection string and declare a DataSet object.

class Program { static String connString = @"Server=SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI"; static DataSet dsOrders = null;

Fill the DataSet object with Sales Order data retrieved from the database.

static void FillOrdersDataSet() { String commandText = String.Empty;
commandText = "SELECT SalesOrderID, OrderDate, OnlineOrderFlag, SalesOrderNumber, TotalDue " + "FROM Sales.SalesOrderHeader " + "WHERE DATEPART(YEAR, OrderDate) = @year; " + "SELECT d.SalesOrderID, d.SalesOrderDetailID, d.OrderQty, d.ProductID, d.UnitPrice " + "FROM Sales.SalesOrderDetail d " + "INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID " + "WHERE DATEPART(YEAR, OrderDate) = @year";
using (SqlDataAdapter da = new SqlDataAdapter(commandText, connString)) { da.SelectCommand.Parameters.AddWithValue("@year", 2002); da.TableMappings.Add("Table", "SalesOrderHeader"); da.TableMappings.Add("Table1", "SalesOrderDetail"); da.Fill(dsOrders); } }

Call the RetrieveAllOnlineOrders() method to read all online orders for August month from the Sales Order data retrieved from the database.

static void RetrieveAllOnlineOrders() { DataTable dtOrders = dsOrders.Tables["SalesOrderHeader"];
var onlineOrders = from order in dtOrders.AsEnumerable() where order.Field<Boolean>("OnlineOrderFlag") == true && order.Field<DateTime>("OrderDate").Month == 8 orderby order.Field<Decimal>("TotalDue") select new { SalesOrderID = order.Field<Int32>("SalesOrderID"), OrderDate = order.Field<DateTime>("OrderDate").ToString("yyyy-MM-dd"), TotalDue = order.Field<Decimal>("TotalDue") };
foreach (var order in onlineOrders) { Console.WriteLine("Sales Order ID: {0}, Order Date: {1}, Total Due: {2}", order.SalesOrderID, order.OrderDate , order.TotalDue); } Console.WriteLine(); }

Use the AsEnumerable() extension method to convert the orders data table to a type that implements IEnumerable. This is done to allow LINQ queries to be executed against the data.

The generic Field extension method provides strongly-typed access to each of the column values in the data collection.

Call the RetrieveAllOnlineOrdersAndDetails() method to read all online orders for August month from the Sales Order header and detail data retrieved from the database.

static void RetrieveAllOnlineOrdersAndDetails() { DataTable dtOrdersHdr = dsOrders.Tables["SalesOrderHeader"]; DataTable dtOrdersDet = dsOrders.Tables["SalesOrderDetail"];
var onlineOrders = from order in dtOrdersHdr.AsEnumerable() join detail in dtOrdersDet.AsEnumerable() on order.Field<Int32>("SalesOrderID") equals detail.Field<Int32>("SalesOrderID") where order.Field<Boolean>("OnlineOrderFlag") == true && order.Field<DateTime>("OrderDate").Month == 8 orderby order.Field<Decimal>("TotalDue") select new { SalesOrderID = order.Field<Int32>("SalesOrderID"), SalesOrderDetailID = detail.Field<Int32>("SalesOrderDetailID"), OrderQty = detail.Field<Int16>("OrderQty"), ProductID = detail.Field<Int32>("ProductID") };
foreach (var order in onlineOrders) { Console.WriteLine("Sales Order ID: {0}, Order Detail ID: {1}, Order Qty: {2}, Product ID: {3}", order.SalesOrderID, order.SalesOrderDetailID, order.OrderQty, order.ProductID); } Console.WriteLine(); }

Use the AsEnumerable() extension method to convert the orders data table to a type that implements IEnumerable. This is done to allow LINQ queries to be executed against the data.

Use the "join" keyword to join the header and details order data based on Sales Order ID column.

The generic Field extension method provides strongly-typed access to each of the column values in the data collection.

The static Main() method defines various LINQ functions to be applied against the database.

static void Main(string[] args) { dsOrders = new DataSet();
FillOrdersDataSet();
RetrieveAllOnlineOrders(); RetrieveAllOnlineOrdersAndDetails();
dsOrders.Dispose(); dsOrders = null;
Console.Read(); } }