Objects and Datasets

The DataSet tool utilizes the extension methods of the DataRowExtensions and DataTableExtensions classes. It does not replace ADO.NET; furthermore, it uses its architecture. All ADO.NET code within applications using LINQ to DataSet functions normally, with no conflicts.

It features tight integration with GUI controls, solid caching for preservation of the relational shape of data, fast and simple query navigation, and easy management of data subsets aggregated (or not aggregated) from multiple sources. It allows for in-memory manipulation of data while retaining relational shape.

Though it offers many features and uses, its query capability proves limited. It uses Select for filtering and sorting, and GetChildRows and GetParentRow methods for hierarchy navigation. Any tasks beyond the scope of those tools requires a custom query, which may result in an application difficult to maintain and plagued by poor performance.

Queries in LINQ to DataSet use the programming language itself rather than a query language. Begin by populating the DataSet. The DataAdapter class and LINQ to SQL provide two of many ways to perform this task. Review an example below:

try
{
	// Spawn an adapter and feed it a query to gather sales order, contact,
	// address, and product info for 2002 sales. Direct connection
	// information to the “PartyTime” configuration setting.

	string connectionString = "Data Source=localhost;Initial Catalog=PartyTime;"
		+ "Integrated Security=true;";

	SqlDataAdapter salesDA = new SqlDataAdapter(
		"SELECT SalesOrderID, ContactID, OrderDate, OnlineOrderFlag, " +
		"TotalDue, SalesOrderNumber, Status, ShipToAddressID, BillToAddressID " +
		"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; " +

		"SELECT p.ProductID, p.Name, p.ProductNumber, p.MakeFlag, " +
		"p.Color, p.ListPrice, p.Size, p.Class, p.Style, p.Weight " +
		"FROM Production.Product p; " +

		"SELECT DISTINCT a.AddressID, a.AddressLine1, a.AddressLine2, " +
		"a.City, a.StateProvinceID, a.PostalCode " +
		"FROM Person.Address a " +
		"INNER JOIN Sales.SalesOrderHeader h " +
		"ON a.AddressID = h.ShipToAddressID OR a.AddressID = h.BillToAddressID " +
		"WHERE DATEPART(YEAR, OrderDate) = @year; " +

		"SELECT DISTINCT c.ContactID, c.Title, c.FirstName, " +
		"c.LastName, c.EmailAddress, c.Phone " +
		"FROM Person.Contact c " +
		"INNER JOIN Sales.SalesOrderHeader h " +
		"ON c.ContactID = h.ContactID " +
		"WHERE DATEPART(YEAR, OrderDate) = @year;",
		connectionString);

	// Include table mappings
	salesDA.SelectCommand.Parameters.AddWithValue("@year", 2002);
	salesDA.TableMappings.Add("Table", "SalesOrderHeader");
	salesDA.TableMappings.Add("TableOne", "SalesOrderDetail");
	salesDA.TableMappings.Add("TableTwo", "Product");
	salesDA.TableMappings.Add("TableThree", "Address");
	salesDA.TableMappings.Add("TableFour", "Contact");

	// Populate the DataSet.
	salesDA.Fill(salesDS);

	// Include data relations.
	DataTable orderHeader = salesDS.Tables["SalesOrderHeader"];
	DataTable orderDetail = salesDS.Tables["SalesOrderDetail"];
	DataRelation order = new DataRelation("SalesOrderHeaderDetail",
										  orderHeader.Columns["SalesOrderID"],
										  orderDetail.Columns["SalesOrderID"], true);
	salesDS.Relations.Add(order);

	DataTable contact = salesDS.Tables["Contact"];
	DataTable orderHeader2 = salesDS.Tables["SalesOrderHeader"];
	DataRelation orderContact = new DataRelation("SalesOrderContact",
											  contact.Columns["ContactID"],
											  orderHeader2.Columns["ContactID"], true);
	salesDS.Relations.Add(orderContact);
}
catch (SqlException ex)
{
	Console.WriteLine("An SQL exception appeared: " + ex.Message);
}

After population, execute queries. Queries resemble most LINQ queries. Query variables only store commands when a query must return a value sequence, however, if the query does not contain a method forcing immediate execution, the query remains deferred until iteration by a foreach loop. Review an example of a LINQ to DataSet query below:

// Populate the DataSet.
DataSet DSforStuff = new DataSet();
DSforStuff.Locale = CulturalData.LocalCult;
FillDataSet(DSforStuff);

DataTable items = DSforStuff.Tables["Item"];

IEnumerable<DataRow> query =
	from item in items.AsEnumerable()
	select item;

Console.WriteLine("Item Nomenclatures:");
foreach (DataRow y in query)
{
	Console.WriteLine(y.Field<string>("Nomenclature"));
}

Another approach for queries utilizes methods. These queries employ a sequence of direct method calls and pass Lambda expressions as parameters. Review an example below:

// Populate the DataSet.
DataSet dat = new DataSet();
dat.Locale = CulturalData.LocalCult;
FillDataSet(dat);

DataTable items = dat.Tables["Item"];

var query = items.AsEnumerable().
	Select(item => new
	{
		ItemName = item.Field<string>("Name"),
		ItemNumber = item.Field<string>("ID"),
		Price = item.Field<decimal>("Price")
	});

Console.WriteLine("Item information:");
foreach (var itemInfo in query)
{
	Console.WriteLine("Item name: {0} Item number: {1} Price: ${2} ",
		itemInfo.ItemName, itemInfo.ItemNumber, itemInfo.Price);
}

LINQ to Objects refers specifically to the direct (no provider) use of queries with IEnumberable or IEnumerable<T> collections. These collections result from APIs or user-defined sources. This tool replaces the old method of using complex foreach looping to retrieve from a collection. It uses declarative code describing the target of the retrieval operation. Review an example below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LINQtoObjects
{
	class Example
	{
		static void Main(string[] args)
		{
			string[] pwrT = { "Drill", "Nailgun", "CircularSaw", "Pump", "Airgun"};
			var list = from p in pwrT
					   select p;

			StringBuilder strB = new StringBuilder();

			foreach (string x in list)
			{
				strB.Append(x + Environment.NewLine);
			}
			Console.WriteLine(strB.ToString(), "Power Tools");
			Console.ReadLine();
		}
	}
}