Книга: C# 2008 Programmer

Typed DataSet

Typed DataSet

So far you've used the Field() extension method to access the field of a DataTable object. For example, the following program uses LINQ to DataSet to query all the customers living in the USA. The result is then reshaped using an anonymous type:

SqlConnection conn;
SqlCommand comm;
SqlDataAdapter adapter;
DataSet ds = new DataSet();
conn = new SqlConnection(@"Data Source=.SQLEXPRESS;" +
 "Initial Catalog=Northwind;Integrated Security=True");
comm = new SqlCommand("SELECT * FROM Customers", conn);
adapter = new SqlDataAdapter(comm);
adapter.Fill(ds, "Customers");
var query1 =
 (from customer in ds.Tables[0].AsEnumerable()
 where customer.Field<string>("Country") == "USA"
 select new {
  CustomerID = customer.Field<string>("CustomerID"),
  CompanyName = customer.Field<string>("CompanyName"),
  ContactName = customer.Field<string>("ContactName"),
  ContactTitle = customer.Field<string>("ContactTitle")
 }).ToList();
dataGridView1.DataSource = query1;

As your query gets more complex, the use of the Field() extension method makes the query unwieldy. A good way to resolve this is to use the typed DataSet feature in ADO.NET. A typed DataSet provides strongly typed methods, events, and properties and so this means you can access tables and columns by name, instead of using collection-based methods.

To add a typed DataSet to your project, first add a DataSet item to your project in Visual Studio 2008 (see Figure 14-10). Name it TypedCustomersDataset.xsd.

Figure 14-10

In the Server Explorer window, open a connection to the database you want to use (in this case it is the Northwind database) and drag and drop the Customers table onto the design surface of TypedCustomersDataSet.xsd (see Figure 14-11). Save the TypedCustomersDataSet.xsd file.

Figure 14-11

With the typed DataSet created, rewrite the query as follows:

SqlConnection conn;
SqlCommand comm;
SqlDataAdapter adapter;
TypedCustomersDataSet ds = new TypedCustomersDataSet();
conn = new SqlConnection(@"Data Source=.SQLEXPRESS;" +
 "Initial Catalog=Northwind;Integrated Security=True");
comm = new SqlCommand("SELECT * FROM Customers", conn);
adapter = new SqlDataAdapter(comm);
adapter.Fill(ds, "Customers");
var query1 =
 (from customer in ds.Customers
 where customer.Country == "USA"
 select new {
  customer.CustomerID,
  customer.CompanyName,
  customer.ContactName,
  customer.ContactTitle
 }).ToList();
dataGridView1.DataSource = query1;

Notice that the query is now much clearer because there is no need to use the Field() extension method. Figure 14-12 shows the output.

Figure 14-12

Оглавление книги