ADO.NET Programming : Microsoft SQL Server (part 4) - Working with Typed Data Sets

5/22/2013 9:52:03 PM

7. Working with Typed Data Sets

All the data sets we have worked with thus far have been undefined until runtime. That is, not only are their instances not created until runtime, which is true of any class, but their very structure—what tables constitute the data set, what columns constitute the tables—is not specified until runtime. Yet in several cases that structure was known at design time. The multi SELECT example that we just saw is such a case. The five tables and their selected columns could be determined by examining the underlying database. In fact, if the stored procedure’s SELECT statements had been specific in their column lists, the resulting data set structure would be even more apparent.

Typed data sets allow us to provide this structure specification at design time; enabling us to define a class that inherits from DataSet and that specifies the structure of our specific data set. Defining the class is quite easy to do. Instead of our writing the entire class, Visual Studio lets us connect to a source database; specify the desired tables, columns, and relationships; and have the class code generated for us. We get the benefits of a strongly typed data set, such as IntelliSense help and structure-specific routines. For instance,




And in coding the second line, the moment we enter “OurDataSet.”, the Products table, being a property of the typed data set, shows in the IntelliSense drop-down list.

Typed data sets, like all data sets, call hold data that is retrieved from a data source or data that is created programmatically; but they are most commonly used for holding data that has been retrieved from a database, especially since the underlying database’s structure propagates into the application’s data structure. This common association between databases and typed data sets is the reason why we waited until we covered the System.Data data provider classes before beginning our discussion of typed data sets.

To illustrate the benefits of typed data sets we’ll write a simple program, one that retrieves sales data from five different tables, all located in Microsoft’s Northwind sample database.

We start by creating a new C# Smart Device application within Visual Studio. We then go to the Product menu and select Add New Item, giving us the Add New Item dialog shown in Figure 8. We specify the name for our typed data set class, SalesData, and choose Add.

Figure 8. Adding a New Typed Data Set

Note that if we had added the data set by using the Data tab of the Toolbox, we would have created an untyped data set, which is not what we want. We must use the dialog shown in Figure 8 to have Visual Studio generate a typed data set class for us.

Visual Studio will add an .xsd file and some .cs files to our project. When we view the .xsd file in the Designer, we are given some choices. We choose to right-click and then pick Add | Table Adapter, and are presented with the first of a series of dialogs that will lead us through the definition of one data table and its associated data adapter, connection, and command objects. Since our data set will contain five data tables, we will pass through this series of dialogs five times.

The first of these dialogs is shown in Figure 9 and lets us specify the underlying database. The next few let us write the command that will retrieve the database data into the data table. In our case, with the Designer’s help, we write the command shown in Figure 6.24. We then accept all defaults for the remaining dialogs and arrive at the situation shown in Figure 11: a data set that contains one table, a five-column Employee table. Note that Visual Studio has detected the presence of a primary key constraint in the database table and has included that constraint in the definition of the data table.

Figure 9. Specifying the Data Connection

Figure 10. Coding the Query

Figure 11. The Resulting DataTable and Its TableAdapter

We’ll define four more data tables but show the dialogs for only one of them, as the one aspect left to be illustrated is relationships. When we complete the definition of the first of our four remaining data tables, the Orders data table, Visual Studio detects a foreign key relationship between Employees and Orders and adds it to the typed data set, as shown in Figure 12.

Figure 12. Two DataTables and Their DataRelation

We add three more table adapters, generating three more data tables and their respective data adapters, and arrive at the typed data set shown in Figure 13.

Figure 13. The Five Tables of Application Data

The code for our typed data set class, written by Visual Studio, is in the SalesData.Designer.cs file, which appears in the Solution Explorer when you expand the SalesData.xsd file icon. It contains the code that exposes the data tables as properties of the data set; routines for navigating relationships such as ourEmployeeRow.GetOrdersRows(); routines for finding a row given its primary key value, such as ourEmployeeTable.FindByEmployeeId(); and others.

What neither the class itself, nor any other class in the project, contains is code to create an instance of our data set and code to fill that instance with data from the database. That code we must write ourselves. Fortunately, there is sufficient code already in the typed data set class to make our share relatively small and easy to write.

For the moment, we will put all the necessary code required to create and load the data set in the LoadDataset menu item’s Click event handler, as shown in Listing 9.

Listing 9. Code to Create and Load a Typed Data Set
SalesData dsetSales = new SalesData();

EmployeesTableAdapter daptEmployee = new EmployeesTableAdapter();

CustomersTableAdapter daptCustomer = new CustomersTableAdapter();

ProductsTableAdapter daptProduct = new ProductsTableAdapter();

OrdersTableAdapter daptOrder = new OrdersTableAdapter();

Order_DetailsTableAdapter daptDetail =
   New Order_DetailsTableAdapter();

dgrdOrders.DataSource =

This code creates a data set containing five empty tables, creates five table adapters and uses them to fill the five tables, and then selects all orders for the employee whose Employee ID is 1 and binds those orders to a data grid, resulting in the display shown in Figure 14.

Figure 14. A Display of One Employee’s Orders

When Visual Studio wrote the code for the data set class, it defined a class for each table adapter, which it placed inside a namespace named SalesDataTableAdapters; that is, the name of the data set suffixed by TableAdapters. That namespace was placed inside the project’s default namespace, in our case, TypedDataset. Thus, the code shown in Listing 6.19 requires the following line at the start of the code file:

   using TypedDataset.SalesDataTableAdapters;

Now let us consider a small enhancement that will make our data set class more functional and more flexible. The added functionality will be a Load method in the data set class that will pull the data in from the database, allowing us to remove all knowledge of, and dependence upon, the table adapters from the form class’s code. The added flexibility will be the option to specify the source database at runtime rather than being bound to the database that was specified at design time. This database designation will be made when the Load method is executed; that is, the connection string value will be a parameter of the Load method.

Thus, we move the code for loading the data set from the form’s menu event handler into the data set’s newly defined Load method. The best location for the Load method is in SalesData.cs, not SalesData.Designer.cs. When the Designer wrote the data set class, it spread the code over the two files mentioned in the preceding sentence. SalesData.cs contains a skeleton partial class and is the place for our code. The other file is where the Designer writes its share of the class; we should not modify that file.

The contents of SalesData.cs are shown in Listing 10, and the revised menu handler is shown in Listing 11.

Listing 10. Partial Class Code for the Typed Data Set
using System;
using System.Data;
using System.Data.SqlClient;

using TypedDataset.SalesDataTableAdapters;

namespace TypedDataset
   public partial class SalesData
      public void Load(string connectionString)
         EmployeesTableAdapter daptEmployee =
            new EmployeesTableAdapter();
         CustomersTableAdapter daptCustomer =
            new CustomersTableAdapter();
         ProductsTableAdapter daptProduct =
            new ProductsTableAdapter();
         OrdersTableAdapter daptOrder =
            new OrdersTableAdapter();
         Order_DetailsTableAdapter daptDetail =
            new Order_DetailsTableAdapter();

         daptEmployee.Connection.ConnectionString =
         daptCustomer.Connection.ConnectionString =
         daptProduct.Connection.ConnectionString =
         daptOrder.Connection.ConnectionString =
         daptDetail.Connection.ConnectionString =



Listing 11. Revised Menu Selection Handler
private void mitemLoadDataset_Click(object sender,
                                    EventArgs e)
   string connectionString =
                    "Data Source=RIMROCK",
                    "Initial Catalog=Northwind",
                    "Integrated Security=SSPI");

   SalesData dsetSales = new SalesData();


   dgrdOrders.DataSource =

So, we now have a data set containing five data tables and five data adapters that support those tables. Thus, we could update the data in the data tables and propagate the changes back to the data source by using the same methods that were available with the untyped data source.

All of this raises the question of combining the last two subjects that we just discussed. Can we have a typed data set that uses a multi SELECT stored procedure to retrieve multiple tables of information for display only? The answer is yes, but we must do a few steps slightly differently.

Since we are dealing with multiple result sets of read-only data from a single command, a data adapter is of no benefit. In design mode, when we right-click on the typed data set’s design surface, we should choose new table rather than new table adapter. The bad news is, the Designer will give us less help designing a table than designing a table adapter; the good news is, we will need to write less code.

We can write less code because the Designer always writes a Load method for typed data sets; not one that we could use before but one that we can use now. This Load method takes two parameters: one is the data reader that will pull the result sets of the multi SELECT stored procedure, and another is an array referencing the data tables to be filled. 


Programming with ADO.NET for Microsoft SQL Server is similar to programming for Microsoft SQL Server CE. The namespaces are different and the class names are different, but their PMEs are mostly the same. The major differences are caused by the different capabilities of the two engines, not by the classes themselves. Connectivity, security, stored procedures, advanced data integrity, concurrency, and differences in schema definition combine to make programming for SQL Server different from programming for SQL Server CE.

  •  SQL Server 2008 R2 : Database Maintenance - Executing a Maintenance Plan
  •  SQL Server 2008 R2 : Database Maintenance - Managing Maintenance Plans Without the Wizard
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 2)
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 1)
  •  SQL Server 2008 : Common performance problems (part 2)
  •  SQL Server 2008 : Common performance problems (part 1) - Procedure cache bloating
  •  SQL Server 2008 : SQLOS schedulers, Wait analysis
  •  MySQL for Python : Creating Users and Granting Access - Removing privileges in MySQL, Using REVOKE in Python
  •  MySQL for Python : Creating Users and Granting Access - GRANT access in MySQL
  •  MySQL for Python : Creating Users and Granting Access - Removing users in MySQL, DROPping users in Python, Granting access in Python
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone