Using Ado.net Datasets in Microsoft Office System (III)

ado| data to this point, your dataset should be completely populated with data from the database, so you can close the database connection object. Closing the connection as early as possible is always a good programming habit, so you can release it to other applications. On the other hand, if the current program is going to use the connection immediately--perhaps to create another dataset--you can keep it open to avoid the extra overhead of rebuilding the connection.

Note: If you reuse the exact same connection string for each connection in the application-including nested spaces, by default, ADO. NET will share the same connection. So in most cases, closing and reopening a connection can only result in very small overhead (negligible).

We can say that your dataset is now populated and ready for use and disconnected from the database. In many application programs, these are the things you need, but you may want to create multiple relationships to establish the hierarchical nature of the data. Creating these relationships requires the use of the DataRelation class.

Focus even if you define the relevant integrity relationships in your database, you have no way to automatically recreate them in a dataset--in other words, you have to create these relationships by hand.



Creating a data relationship requires referencing more than two columns of existing tables in the dataset. The following code uses the parent and child DataColumn objects to add a relationship between a primary key and a foreign key in two tables. Once you instantiate these objects (parent and child objects), you can instantiate the DataRelation object and pass it to a name that identifies the relationship as parent and child objects.

Code also sets the nested (nested) property of the relationship to True. When you read the XML representation of the dataset, the child element is not automatically nested into its parent object. Instead, all the parent elements are listed first, and all the child elements are followed and grouped hierarchically. Although a very useful structure is rare, the nested property is nested with data.

In the final step, add a new DataRelation object to the Relations collection of the DataSet object. Notice that the name of the table is used as a reference table in the code. You can replace them with the name of your own table.

' Add the relationships to the DataSet

' Customers-> Orders

Dim Parent as DataColumn = _

Ds. Tables ("Customers"). Columns ("CustomerID")

Dim child as DataColumn = ds. Tables ("Orders"). Columns ("CustomerID")

Dim Sqlrel as New DataRelation ("Customer Orders", parent, child)

sqlrel.nested = True

Ds. Relations.Add (Sqlrel)

The final step of the process saves the XML schema and data for the DataSet object to your hard disk. This step is not necessary when using a dataset, but in the "Import dataset as XML into the Excel 2003 Worksheet" section, you will see how to use them.

Most. NET Frameword objects support serialization into one-dimensional or multidimensional, but the DataSet object makes serialization simpler--by using the WriteXml and WriteXmlSchema methods. When you call both methods so that you need to pass the name of the corresponding disk file to each method, the data is saved to the appropriate file. You can provide the full path, but this code saves the file to the application's Bin folder. Here it is useful to table names as reference flags.

' Optional steps: Saving XML data and schemas as disk files

If Bsaveschema Then

' Places ' The file in this app ' s bin directory

Ds. WriteXmlSchema ("Customers.xsd")

Ds. WriteXml ("Customers.xml")

End If

This procedure returns the complete DataSet object to the procedure that invoked it.

Return DS

End Function

Create a DataSet for a single entity

When you create a dataset in a client application, ADO. NET passes all data from the database over the network to the client and, to the maximum extent possible, populates the dataset with the necessary data.

If your watch has millions of records, what will happen? You want to populate the dataset with as little data as possible over the network, for example, you might just want to get information from a single customer. In the Northwind database, the class method Getcustomerdataset of the data access layer implements this functionality by selecting a particular customer using the CustomerID as a parameter from the Drop-down list box on the form.

The code you need is very similar to the code in GetDataSet, but there are some important differences. First, you need to filter the data now. In other words, you have to filter the data in the table, to return only the required records and perform these actions on any related tables, but multiple tables typically return multiple records, such as filtering out one of the orders that the customer has placed (one customer may have 0 or more orders).

You can filter and sort the data that is contained in the dataset, but this means that the entire dataset will traverse the network. A good way to do this is to perform filtering and sorting on the database side and return only the required records.

You can perform a filter through the SqlParameter object. These parameters apply to each dynamic SQL statement in your code, and note that executing such a method is equivalent to invoking the corresponding stored procedure.

The following code populates the Customers table in the dataset. Notice that the SQL statement now has a WHERE clause without an ORDER BY clause--it is not necessary to sort a single record. The SqlCommand object is instantiated in the same way as before, but now it needs to set the @custid parameter. The SqlParameter object is instantiated with the parameter name-@CustID, and the argument type--nchar (5). It then sets the Value property of the parameter to the values passed to the procedure. The final step is to add this new parameter to the Parameters collection of the Command object, and then call the data adapter's fill (the previous step). You can add as many parameter objects as the SQL statement requires.

Dim SQL as String = "Select CustomerID, CompanyName, ContactName," _

ContactTitle, address, City, Region, PostalCode, Country, "_

& "Phone, Fax from Customers WHERE CustomerID = @CustID"

Dim SQLCMD as New SqlCommand (SQL, SQLCNN)

Dim Sqlparam as New SqlParameter ("@CustID", SqlDbType.NChar, 5)

Sqlparam.value = Scustid

SQLCMD.PARAMETERS.ADD (Sqlparam)

Dim SqlDA as New SqlDataAdapter (SQLCMD)

Sqlda.fill (ds, "Customers")

Other tables are populated in almost identical ways, except that some of the filtered tables need to be merged into the Orders table so that CustomerID can be accessed.

Each child table needs to reuse SqlParameter objects because they all need to filter the CustomerID, not just the Products table. You want to return all the products in the database, regardless of whether the product is ordered or not. Therefore, this part of the code calls the clear method of the Parameters collection.