Manage Learn to apply best practices and optimize your operations.

ADO.NET Typed DataSet objects

Learn the differences between DataSets and disconnected Recordsets, then get methods for generating ADO.NET objects with VS 2005 and SQL Server 2000 or 2005.

The following tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.

The DataSet object is unique to ADO.NET and typed DataSets are the preferred method for retrieving and updating relational tables, although DataSets aren't limited to processing relational data. You create typed DataSets, which are defined by an XML schema and implemented by a very large amount of auto-generated VB 2005 code, with VS 2005 designers. Untyped DataSets are runtime objects that you create with code. DataSets have no corresponding ADODB object, but both classes of DataSets behave similarly to disconnected Recordsets in the following ways:

  • They open a connection, retrieve and cache the data to edit, and then close the connection.
  • They bind to simple and complex Windows form controls for editing.
  • They permit editing locally cached data while the connection is closed.
  • They can be saved to local files and reopened for editing.
  • They let you reopen the connection and apply updates to base tables in batches.
  • They implement optimistic concurrency for base table updates. You must write code to handle concurrency violations gracefully.

Use the following table of contents to navigate to the most important differences between DataSets and disconnected Recordsets, then get methods for generating ADO.NET objects with VS 2005 and SQL Server 2000 or 2005.

DataSets and disconnected Recordsets

  • A DataSet consists of cached copies of one or more sets of records -- called DataTable objects -- selected from one or more individual base tables. A Recordset is a single set of records that can represent a view of one or two or more related tables.
  • Persisting a DataSet serializes the DataTables' records to a hierarchical, element-centric XML Infoset document and saves it to the local file system. Disconnected Recordsets store data locally as a flat, attribute-centric XML file.
  • DataTables usually are -- but need not be -- related by primary-key/foreign-key relationships.
  • Primary-key and foreign-key constraints, and table relationships, must be manually defined, unless you create the DataSet automatically with VS 2005's Data Source Configuration Wizard.
  • You can create DataTables from base tables of any accessible database server instance.
  • You can create DataTables from structured (tabular) XML Infoset documents.
  • TableAdapters fill and update DataTables through a managed connection. TableAdapters are wrappers over DataAdapter objects.
  • The Data Source Configuration Wizard lets you choose an existing data connection that's defined in the Server Explorer, or create a new connection object. The wizard then generates parameterized SQL queries or stored procedures for performing UPDATE, INSERT, and DELETE operations. These queries are based on the SELECT query or stored procedure that you specify for filling each DataTable.
  • DataSets cache copies of original and modified table data in XML format. Thus, DataSets that have a large number of rows consume much more client RAM resources than Recordsets that have the same number of rows.
  • You can write code to create runtime data connections, DataAdapters, and basic DataSets, but it's much easier to take advantage of VS 2005 automated processes for generating the code to create typed DataSets, which are defined by an XML schema.
  • DataSet updates occur row-by-row if you don't specify a value greater than 1 for the new DataAdapter.BatchSize property, which sets the maximum number of updated rows per batch.

Figure 1-5 compares the objects required by updatable ADODB Recordsets and ADO.NET 1.x and 2.0 typed DataSets. Components that are new in ADO.NET 2.0 are shaded. Parameters are optional for ADODB commands, but not for updatable TableAdapters, which have four standard commands— SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. Use of the new ADO.NET 2.0 BindingNavigator components is optional. The section "Add a DataGridView and DataNavigator Controls," later in this chapter, describes how the BindingSource fits into ADO.NET 2.0's data access architecture.

The following sections show you alternative methods for generating Figure 1-5's ADO.NET objects with VS 2005 and SQL Server 2000 or 2005.

VS 2005 materializes TableAdapters, DataSets, BindingSources, and BindingNavigators as named objects in the form design tray. TableAdapters and DataSets also appear in the Toolbox's ProjectName Components section; the Data section has DataSet, BindingSource, and BindingNavigator controls. During the early part of VS 2005's long gestation period, these design-time objects collectively were called Data Components, BindingSource was called a DataConnector, and BindingNavigator was DataNavigator. This book uses the term data component to refer to named design-time data objects that reside in the form design tray.

Add a Typed DataSet from an SQL Server Data Source

ADO.NET uses the term data source as a synonym for a typed DataSet with a predefined, persistent database connection. The process of creating an ADO.NET data source is similar to using VB6's Data Environment Designer to specify an OLE DB data provider from one or more tables. Unlike the Data Environment Designer, multi-table DataSets don't have the hierarchical structure that the OLE DB Shape provider creates for display in VB6's Hierarchical FlexGrid control.

Web services and object instances also can act as ADO.NET data sources, as you'll see in later chapters.

Figure 1-5

Here's how to add a new SQL Server Northwind data source for a new Windows form project and automatically generate a typed DataSet and its components from the Customers table: 

1. Choose Data➪Show Data Sources to open the Data Sources window, if necessary, and click Add New Data Source to start the Data Source Configuration Wizard.

2. On the Choose a Data Source Type page, accept the default Database type, and click Next to open the Choose Your Database Connection page, which displays existing data connections, if any, in a dropdown list.

3. Click the New Connection button to open a simplified Add Connection dialog, which usually defaults to Microsoft SQL Server Database File. This option requires attaching a copy of northwnd.mdb to your SQL Server or SQLX instance, so click the Change button to open the Change Data Source dialog, select Microsoft SQL Server in the Data Source list, and click Continue to open the full version of the Add Connection dialog.

4. Type localhost or .SQLEXPRESS in the Select or Enter a Server Name combo box. Alternatively, select a local or networked SQL Server or MSDE instance that has a Northwind or NorthwindCS database.

5. Accept the default Use Windows NT Integrated Security option, and open the Select or Enter a Database Name list and select Northwind. Click Test Connection to verify the SqlConnection object, as shown in Figure 1-6.

Figure 1-6

6. Click OK to close the dialog and return to the Choose Your Data Connection page, which displays ServerName.Northwind.dbo as the new connection name, System.Data.SqlClient as the Provider, and Data Source=localhost;Integrated Security=True;Database=Northwind as the Connection String.

7. Click Next to display the Save the Connection String to the Application Configuration File page. Mark the Yes, Save the Connection As checkbox and accept the default NorthwindConnectionString as the connection string name.

8. Click Next to open the Choose Your Database Objects page, which displays treeview Tables, Views, Stored Procedures, and table-returning Functions. Expand the Tables node and mark the Customers table. Accept NorthwindDataSet as the DataSet Name, as shown in Figure 1-7.

Figure 1-7

Selecting a table automatically generates the SelectCommand that retrieves all table rows, and an UpdateCommand, InsertCommand, and DeleteCommand for base table updates.

9. Click Finish to generate the NorthwindDataSet typed DataSet and display it in the Data Sources window. Expand the Customers node to display the Customers table's columns, as shown in

Figure 1-8

The new SqlConnection object you created in preceding Steps 3 through 5 appears under Server Explorer's DataConnections node as ServerName.Northwind.dbo. You can rename the node in Server Explorer to a simpler name, such as localhost.Northwind; doing this doesn't affect dependent objects in your project.

Adding a typed DataSet generates an XSD schema, NorthwindDataSet.xsd for this example, and adds 1,197 lines of VB 2005 code to the NorthwindDataSet.Designer.vb partial class file, which weighs in at 73KB. Partial classes are a new VB 2005 and C# feature that enable extending a class, such as NorthwindDataSet, with additional class files. VB 2005 uses the Public Partial Class className statement to identify a partial class file. You must choose Project➪Show All Files to see NorthwindDataSet.Designer.vb and two empty NorthwindDataSet.xsc and NorthwindDataSet.xss files.

Double-click the NorthwindDataSet.xsd node in Project Explorer to display the Customers DataTable and its associated Customers TableAdapter, as shown in Figure 1-9, in the Schema Designer window. The VB 2005 code in DataSetName.Designer.vb provides IntelliSense for DataSet objects and lets you early-bind DataTable and DataSet objects. The code also provides direct access to named classes, methods, and events for the DataSet and its TableAdapter(s) -- Customers TableAdapter for this example -- in the NorthwindDataSet.Designer.vb code window's Classes and Methods lists.

Figure 1-10 shows Internet Explorer displaying the first few lines of the 352-line schema .

If you've worked with typed DataSets in VS 2003, you'll notice that the schema for ADO 2.0 DataSets is much more verbose than the ADO 1.x version, which has only 30 lines that define the Customers DataSet. ADO.NET 2.0 prefixes the design-time schema with 258 lines of <xs:annotation> information, which provide a full definition of the DataSet and its connection string, commands and their parameters, and column mapping data. The part of the schema that defines the elements for the table fields grows from 30 to 94 lines because element definitions now contain maxLength attribute values and use restrictionBase attributes to specify XSD data types.

Figure 1-9

Figure 1-10

Using the DataSet.WriteXml and DataSet.WriteXmlSchema methods to persist DataSets to local files shows that the Customers DataSet schema, which differs greatly from the design-time version, is 9.31KB and the XML data document is 37.3KB. The section "Create a Complete Data Entry Form in One Step," later in this chapter, includes code to save the schema for the Northwind Customers DataSet. You can't open the saved schema in the project's Schema Designer.

Add a DataGridView and BindingNavigator Controls

Opening Form1 and the Data Sources window changes the appearance of the DataSource nodes. By default, the Customers DataTable icon now represents a DataGridView control. Dragging the Customers table node from the Data Sources window to your project's default Form1 autogenerates four components in the tray below the form designer and adds DataGridView and DataNavigator controls to a dramatically expanded form, as shown in Figure 1-11.

Figure 1-11

"Surfacing" is a common term for adding data and other components to the tray.

Here are descriptions of the four tray components shown in Figure 1-11:

  • NorthwindDataSet is the form's reference to the data source for the form, NorthwindDataSource.xsd.
  • CustomersTableAdapter is the form's wrapper for an SqlDataAdapter object, which fills the NorthwindDataSet's Customers DataTable by invoking the CustomersTableAdapter.Fill method. Update, Insert, and Delete methods send DataSet changes to the database server. The CustomersTableAdapter.Adapter property lets you access the underlying SqlDataAdapter.
  • CustomersBindingSource is a form-based BindingSource object that unifies control data binding and row data navigation for the Customers DataTable by providing direct access to the BindingManager object. To make it easier for VB6 developers to adapt to ADO.NET 2.0, BindingSources have properties and methods that emulate ADODB.Recordset objects. Examples are AllowEdit, AllowAddNew, and AllowRemove (delete) properties, and corresponding AddNew, CancelNew, EndNew, Edit, CancelEdit, and EndEdit methods. Familiar MoveFirst, MoveLast, MoveNext, and MovePrevious methods handle row navigation. Enabling navigation requires binding a DataGridView or adding other controls to manipulate the BindingSource.
  • CustomersBindingNavigator is a custom ToolStrip control that emulates the VCR and other buttons of an ADODB.DataControl. Binding the CustomersBindingNavigator to the CustomersBindingSource enables the buttons to invoke the Move..., AddNew, and Cancel... methods. By default, BindingNavigators dock to the top of the form. When you run the form, you can drag a BindingNavigator to a more natural position at the bottom of the form; alternatively, you can set a DataNavigator's Dock property value to Bottom in the designer.

DataComponents, DataConnectors, and DataNavigators are new ADO.NET 2.0 components and controls that replace ADO.NET 1.x's form-based DataConnections and DataAdapters. VS 2005 data sources automatically create DataSet Relationships between tables, which previously required manual intervention. DataConnectors simplify code for navigating data tables. The DataSet.vb file contains the classes, interfaces, and event handlers for the data components.

The final step in the VS 2005 data form autogeneration process is adding the CustomersComponent .Fill method to the Form1_Load event handler, and code to save DataSet changes isn't added to the bindingNavigatorSaveItem_Click handler automatically, because of code complexity when the DataSet contains multiple DataTables. Saving multiple changes to parent and child tables requires sequencing inserts, updates, and deletions to maintain referential integrity.

     Private Sub Form1_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles MyBase.Load
         'TODO: This line of code loads data into the 'NorthwindDataSet.Customers' table.
         'You can move, or remove it, as needed.
     End Sub

     Private Sub dataNavigatorSaveItem_Click(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles dataNavigatorSaveItem.Click
     End Sub

Figure 1-12 shows the final form after reducing the form's size, expanding the DataGridView control to fill the available space, and pressing F5 to build, debug, and run the project.

Figure 1-12

The CustomersDataGridView is bound to the Northwind Customers table, and editing is enabled by default. Changes you make to the DataGridView don't propagate to the table until you click the Save Data button. To make editing easier, you can automate increasing the column widths to match the content by setting the DataGridView's AutoSizeColumnsMode property value to AllCells or DisplayedCells, which adds a horizontal scrollbar to the control.

Persist and Reopen the DataSet

The project's frmDataGridView_Load event handler includes the following code to save the NorthwindDataSet's XML data document -- with and without an embedded schema -- and the schema only. You can add similar code after the last DataComponent.Fill or DataAdapter.Fill invocation of any data project to persist its DataSet.

     Private Sub frmDataGridView_Load(ByVal sender As System.Object, _
              ByVal e As System.EventArgs) Handles MyBase.Load
          Dim strPath As String = Application.StartupPath
          With Me.NorthwindDataSet
                .WriteXml(strPath + "CustsNoSchema.xml", XmlWriteMode.IgnoreSchema)
                .WriteXml(strPath + "CustsWithSchema.xml", XmlWriteMode.WriteSchema)
                .WriteXmlSchema(strPath + "CustsSchema.xsd")
          End With
    End Sub

Persisting the DataSet as an XML document without the embedded schema lets you support disconnected users by reloading the DataSet from the file. You can substitute the following statement for Me .CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers) when the user is disconnected:

Me.NorthwindDataSet.ReadXml(strPath + "CustsNoSchema.xml", XmlReadMode.Auto)

The real-world scenario for persisting and reloading DataSets is more complex than that shown here. Later chapters describe how to save and reload pending DataSet changes that haven't been committed to the base tables. The XmlReadMode.Auto argument is the default, so including it is optional.

The sample project at this point is GeneratedDataGridView.sln in your VB2005DBChapter01 GeneratedDataGridView folder.

Change from a DataViewGrid to a Details Form

The default combination of DataViewGrid and DataNavigator controls speeds the creation of a usable form. However, a DataNavigator is much more useful for a details form that displays column values in text boxes or other bound controls, such as date pickers for DateTime and checkboxes for Boolean values. The Data Sources window makes it easy to change a DataGridView to a details form. Delete the DataGridView control, display the Data Sources window, open the dropdown list for the DataTable, and select Details, as shown in Figure 1-13.

Figure 1-13

Drag the DataTable icon to the form to automatically add a column of labels with associated data-bound Controls -- text boxes for this example -- to the form. Figure 1-14, which is a modified version of the GeneratedDataGridView project, shows the labels and text boxes rearranged to reduce form height.

Figure 1-14

The completed GeneratedDetailView.sln project is in the VB2005DBChapter01GeneratedDetailView folder.

Add a Related DataBound Control

You can add a related table to the Data Sources window and then add a control, such as a DataGridView, that you bind to the related BindingAdapter. To add a related OrdersDataGridView control to a copy of the GeneratedDetailView.sln project, do the following:

1. Copy and paste the GeneratedDetailView folder, and rename the new folder OrdersDetailView. Don't rename the solution or project.

2. Press F5 to build and compile the project. Correct any object name errors that the debugger reports.

3. Open the Data Source window, and click the Configure DataSet with Wizard button to open the Choose Your Database Objects page.

4. Expand the Tables node, mark the Orders table checkbox, and click Finish, which adds in the Data Sources window a related Orders node to the Customers table and a standalone Orders node (see Figure 1-15).

Figure 1-15

5. With DataGridView selected in the dropdown list, drag the related Orders node below the bound text boxes of the form to autogenerate an OrdersDataGridView control.

6. Adjust the size and location of the controls, and set the OrdersDataGridView .AutoSizeRowsMode property value to DisplayedCells. Optionally, alter the form's Text property to reflect the design change.

7. Press F5 to build and run the project. The form appears as shown in Figure 1-16.

Figure 1-16

Dragging the related Orders table node to the form adds an OrdersTableAdapter and OrdersBindingSource to the tray and the OrdersDataGridView control to the form. The OrdersDataGridView control's DataSource property value is the OrdersBindingSource. The OrdersBindingSource's DataSource property value is CustomersBindingSource and the DataMember property value is FK_Orders_Customers, which is the foreign-key relationship on the CustomerID field between the Customers and Orders tables. To verify the properties of FK_Orders _Customers, open NorthwindDataSet.xsd in the DataSet Designer, right-click the relation line between the Orders and Customers tables, and choose Edit Relation to open the Relation dialog (see Figure 1-17).

Figure 1-17

Relations you define by adding related tables to the Data Sources window don't enforce referential integrity by default. You must change the default Relation Only property value to one of the other options to maintain referential integrity. You also can specify cascade or other options for Update, Delete, and Accept/Reject Rules.

The above tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.

Dig Deeper on SQL Server Database Modeling and Design