Strongly Typed DataSet s

Strongly Typed DataSets

Until now, we have been discussing DataSets in their untyped form. However, with ADO.NET and Visual Studio, we can also generate typed DataSets. A typed DataSet is derived from the untyped DataSet class but adds objects, methods, properties, and events that are specific to our database schema. The schema is defined in an XML Schema file (.xsd file), and a design-time tool is provided to generate a set of classes based on this schema.

Because the typed DataSet is derived from the DataSet, it inherits all its functionality, properties, methods, and events, and it can be used anywhere an untyped DataSet can be used. However, as its elements are strongly typed, it offers additional features that provide increased development speed and reliability.

• DataSets, DataTables, and DataRows are objects specific to the schema being handled.

• DataColumns and DataRelations are exposed as specific named properties, rather than generic collection elements.

• Compile-time-type checking is made possible.

• IntelliSense statement completion is provided in the Visual Studio code editor.

• Code is more concise and readable overall.

For example, using our Customers table, setting a column value for a row would look like

 dsCustomers.Tables("Customers").Rows(row)("FirstName") = NewValue  

where row is the index into the Rows collection of the Customers table, FirstName is the name of the column being accessed, and NewValue is the new value being assigned. Several points of potential errors will only be reported at run-time rather than at design time. Is the variable NewValue of the correct type to match the type of the column being assigned? Does the table Customers exist? Does the column FirstName exist? Was the table or column name accidentally misspelled? The same questions apply to reading a column value.

However, if we generate a typed dsCustomers DataSet, the DataSet has properties specific to our schema and can already do all the required type checking at design time. The corresponding code for a typed DataSet containing the Customers table would be

 dsCustomers.Customers(row).FirstName = NewValue  

Note how the Customers table is a specific property of the typed DataSet and the FirstName column is a specific property of the typed DataTable Customers. We also show shortly how the IntelliSense in the code editor utilizes these properties.

Let's return to the Departments table to see how this approach works. First, we do the following.

1. Open the DataSetCode project in Visual Studio.

2. On the frmDataSets form, add a button beneath the Data Views button.

3. Name the new button btnTypedDataSet and set its Text property to Typed DataSet.

We now need to add the desired schema to our project. If we already had an .xsd file, we could use that. As we don't have one, we'll create one. We can do so easily by using the components from the Data tab of the toolbox, as follows.

1. Add a SqlDataAdapter component to the form. For the Configuration Wizard, use the connection to the Novelty (SQL Server) database, use "select * from tblDepartment" for the selection string, and use the default settings for the other options. Doing so adds a new DataAdapter and a new Connection to the form. Change the name of the DataAdapter to daDepartments.

2. Display the Generate DataSet dialog box, by either selecting it from the main Data menu r by selecting it from the pop-up menu displayed by right-clicking on the form. Choose a New dataset, name it DepartmentsDS, leave the Add this dataset to the designer checkbox checked, and click on the OK button.

3. Change the name of the DataSet component just added to dsDepartments.

The Dataset Generator also added the XML schema file, DepartmentsDS.xsd, to the project in the Solution Explorer. In addition, the file that will implement the custom typed DataSet classes, DepartmentsDS.vb, was added to the project beneath the schema file. The DepartmentsDS.vb file isn't visible unless we click on the Show All Files button on the top of the Solution Explorer.

Note

The DataSet Generator nd Schema Editor handle schemas with related tables as easily as they handle single tables.

1. Double-click on the DepartmentsDS.xsd file in the Solution Explorer to display the XML Schema editor. We are going to make a simple name change to the element in the displayed schema.

2. Change the element name from tblDepartment to Departments, by editing the uppermost left cell in the element. The results are shown in Figure 7.7.

   Figure 7.7. The DepartmentsDS schema in the Schema Editor

   

3. Right-click on the Schema Editor design surface to verify that the Generate DataSet option is selected. This is the default setting, but if for some reason it isn't selected, select it now.

4. Save and close the Schema Editor. The code in the DepartmentsDS.vb file is automatically generated.

Note

If you're curious, go ahead and open the DepartmentsDS.vb file in the code editor to see what the generated code looks like. The important thing to remember is that for a given table name (which we changed from tblDepartment to Departments) the DataSet Generator generates three object classes,

 DepartmentsDataTable  DepartmentsDataRow DepartmentsRowChangeEvent 

in addition to the type DataSet class itself, which we specified as DepartmentsDS.

We can now go ahead and add the code to use the typed DepartmentsDS DataSet, as shown in Listing 7.8.

Listing 7.8 Code to display the contents of the typed DataSet DepartmentsDS

 Private Sub btnTypedDataSet_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnTypedDataSet.Click   daDepartments.Fill(dsDepartments, "Departments")   DisplayDepartments(dsDepartments) End Sub Private Sub DisplayDepartments(ByVal ds As DepartmentsDS)   Me.lstOutput.Items.Clear()   Me.lstOutput.Items.Add("DISPLAY TYPED DATASET")   Me.lstOutput.Items.Add("=====================")   'Each Column is now a property of the DepartmentsDS DataSet   Dim row As DepartmentsDS.DepartmentsRow   For Each row In ds.Departments.Rows     Me.lstOutput.Items.Add( _           ds.Departments.IDColumn.ColumnName _           & " : " & row.ID.ToString)        Me.lstOutput.Items.Add( _           ds.Departments.DepartmentNameColumn.ColumnName _           & " : " & row.DepartmentName)      Next End Sub 

In the Click handler for btnTypedDataSet, we call the Fill method of the daDepartments DataAdapter to load the Department table with data. We then call the DisplayDepartments routine to display the contents of the table. To display the table contents, we simply loop across ll the rows in the table and display the column name and value for each column. This approach isn't very different from what we've done before, but note the following.

• The variable row is declared as a specific type of row, DepartmentsDS.DepartmentsRow, rather than a generic DataRow.

• The Departments table is accessed as a property of the DataSet, ds.Departments, rather than as an item in the Tables collection that is, ds.Tables("Departments").

• The columns of the table are accessed as properties of the table, ds.Departments.IDColumn and ds.Departments.DepartmentNameColumn, rather than as items in the Columns collection that is, ds.Tables("Departments").Columns("ID") and ds.Tables("Departments").Columns("DepartmentName").

• The column values are accessed as properties of the row (DepartmentsRow), row.ID and row.DepartmentName, rather than as items in Row's Items collection that is, row("ID") and row("DepartmentName").

Figure 7.8 shows how typed properties (for example, DepartmentName) appear in the IntelliSense pop-up menu, which aids and accelerates the coding process.

Figure 7.8. IntelliSense menu, showing properties of a typed DataSet