Reading a Column Value Using Strongly Typed DataSet Classes

Reading a Column Value Using Strongly Typed DataSet Classes

A strongly typed DataSet object allows you read a column value using a property with the same name as the column. For example, to read the CustomerID of a column, you can use myDataRow.CustomerID rather than myDataRow["CustomerID"]. This is a nice feature because the compiler can then catch any errors in column spellings at compile time rather than runtime. For example, if you incorrectly spelled CustomerID as CustimerID, then the mistake would be caught by the compiler.

Another feature of a strongly typed DataSet is that when you work with it in VS .NET, IntelliSense automatically pops up the properties and methods of the DataSet when you are typing. You can then pick the property or method from the list, rather than have to type it all in.

The downside to using a strongly typed DataSet is that you must do some initial work to generate it before you can use it. If the columns in your database tables don't change very often, then you should consider using strongly typed DataSet objects. On the other hand, if your database tables change a lot, you should probably avoid them because you'll need to regenerate the strongly typed DataSet to keep it synchronized with the definition of the database table.

Note 

You'll find a completed VS .NET example project for this section in the StronglyTypedDataSet directory. You can open this project in VS .NET by selecting File ➣ Open ➣ Project and opening the WindowsApplication4.csproj file. You'll need to change the ConnectionString property of the sqlConnection1 object to connect to your SQL Server Northwind database. You can also follow along with the instructions in this section by copying the DataReader directory to another directory and using that project as your starting point.

Creating a Strongly Typed DataSet Class

In this section, you'll create a strongly typed DataSet class that is used to access the Customers table. If you're following along with these instructions, open the DataReader project in VS .NET and double-click Form1.cs in the Solution Explorer window. You open the Solution Explorer window by selecting View ➣ Solution Explorer.

Next, select File ➣ Add New Item. Select Data Set from the Templates area and enter MyDataSet.xsd, as shown in Figure 10.3.

Figure 10.3: Adding a new Data Set

Click the Open button to continue.

VS .NET will add MyDataSet.xsd to your project, as shown in Figure 10.4.

Figure 10.4: MyDataSet.xsd

At the bottom left of Figure 10.4, you'll notice two tabs: DataSet and XML. The DataSet tab is displayed by default and you use it to see the visual view of your DataSet. The XML tab allows you to see the XML file of your DataSet.

Next, make sure you've opened the Server Explorer window; to open the window, select View ➣ Server Explorer. Open the Data Connections node and either use an existing connection to your Northwind database or create a new one by right-clicking on the Data Connections node and selecting Add Connection from the pop-up menu.

Double-click your connection and drill down to the table, view, or stored procedure you want use, and then drag it onto your form. Go ahead and drag the Customers table onto your form. Figure 10.5 shows the form once the Customers table has been added.

Figure 10.5: Customers table added to form

Note 

You can add multiple tables to your form and define relations between them.

Next, save your work by selecting File ➣ Save All or press Ctrl+S on your keyboard.

Your project now contains a XSD file named MyDataSet.xsd, as shown in Listing 10.14. You can view this file by clicking the XML tab at the bottom of the XML Designer window.

Listing 10.14: MYDATASET.XSD

 <?xml version="1.0" encoding="utf-8" ?> <xs:schema   targetNamespace="http://tempuri.org/MyDataSet.xsd" elementFormDefault="qualified"  attributeFormDefault="qualified"  xmlns="http://tempuri.org/MyDataSet.xsd"  xmlns:mstns="http://tempuri.org/MyDataSet.xsd"  xmlns:xs="http://www.w3.org/2001/XMLSchema"  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="MyDataSet" msdata:IsDataSet="true">     <xs:complexType>       <xs:choice maxOccurs="unbounded">         <xs:element name="Customers">           <xs:complexType>             <xs:sequence>               <xs:element name="CustomerID" type="xs:string" />               <xs:element name="CompanyName" type="xs:string" />               <xs:element name="ContactName" type="xs:string" minOccurs="0" />               <xs:element name="ContactTitle" type="xs:string" minOccurs="0" />               <xs:element name="Address" type="xs:string" minOccurs="0" />               <xs:element name="City" type="xs:string" minOccurs="0" />               <xs:element name="Region" type="xs:string" minOccurs="0" />               <xs:element name="PostalCode" type="xs:string" minOccurs="0" />               <xs:element name="Country" type="xs:string" minOccurs="0" />               <xs:element name="Phone" type="xs:string" minOccurs="0" />               <xs:element name="Fax" type="xs:string" minOccurs="0" />             </xs:sequence>           </xs:complexType>         </xs:element>       </xs:choice>     </xs:complexType>     <xs:unique name="MyDataSetKey1" msdata:PrimaryKey="true">       <xs:selector xpath=".//mstns:Customers" />       <xs:field xpath="mstns:CustomerID" />     </xs:unique>   </xs:element> </xs:schema> 

Notice that this file contains the details of the columns in the Customers table.

Your project also contains a new class file named MyDataSet.cs, which contains your strongly typed DataSet class. You can view the contents of this file using the Solution Explorer window. You open the Solution Explorer window by selecting View ➣ Solution Explorer.

Note 

To view the MyDataSet.cs file, click the Show All Files button in the Solution Explorer window.

Next, expand the node beneath MyDataSet.xsd. You'll see MyDataSet.cs, as shown in Figure 10.6, and a file named MyDataSet.xsx, which contains layout information for the visual view of your DataSet.

Figure 10.6: Viewing all the files using the Solution Explorer window

Go ahead and open MyDataSet.cs by double-clicking it in the Solution Explorer window. View the code for this form by selecting View ➣ Code. One of the classes declared in that file is MyDataSet. This class is derived from the DataSet class. You'll use it in the next section to create a strongly typed DataSet object to access the Customers table.

Using a Strongly Typed DataSet Class

Once you have your strongly typed MyDataSet class, you can create an object of that class using the following code:

 MyDataSet myDataSet = new MyDataSet(); 

You can also create a strongly typed DataTable table object using the MyDataSet.CustomersDataTable class and populate it with rows from the Customers table. For example, you can set the Form1_Load() method of your form to retrieve the CustomerID, CompanyName, and Address column values from the Customers table and add them to a ListView control named listView1. To do this, double-click Form1.cs in the Solution Explorer windows, view the code, and set the Form1_Load() method as follows:

 private void Form1_Load(object sender, System.EventArgs e) {   System.Data.SqlClient.SqlCommand mySqlCommand =     sqlConnection1.CreateCommand();   mySqlCommand.CommandText =     "SELECT CustomerID, CompanyName, Address " +     "FROM Customers " +     "WHERE CustomerID = 'ALFKI'";   System.Data.SqlClient.SqlDataAdapter mySqlDataAdapter =     new System.Data.SqlClient.SqlDataAdapter();   mySqlDataAdapter.SelectCommand = mySqlCommand;   MyDataSet myDataSet = new MyDataSet();   sqlConnection1.Open();   mySqlDataAdapter.Fill(myDataSet, "Customers");   sqlConnection1.Close();   MyDataSet.CustomersDataTable myDataTable = myDataSet.Customers;   foreach (MyDataSet.CustomersRow myDataRow in myDataTable.Rows)   {     listView1.Items.Add(myDataRow.CustomerID);     listView1.Items.Add(myDataRow.CompanyName);     listView1.Items.Add(myDataRow.Address);   } } 

The myDataRow.CustomerID property returns the value for the CustomerID column, and so on. Compile and run your form in one step by selecting Debug ➣ Start Without Debugging. Figure 10.7 shows the running form.

Figure 10.7: The running form

Note 

The MyDataSet class contains a number of methods that allow you to modify the rows stored in a MyDataSet object. These methods include NewCustomersRow(), AddCustomersRow(), FindByCustomerID(), and RemoveCustomersRow(). You can also check if a column value contains a null value using methods such as IsContactNameNull(), and you can set a column to null using methods such as SetContactNameNull(). You'll learn how to use these methods in Chapter 11.