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

ado| Data Create a dataset

Suppose you have to use a flexible data object that allows you to include multiple tables, set relationships between tables, and read data forward or backward.

Ado. NET, the object that provides these features--or even more--is the dataset. A DataSet is a disconnected (disconnected) object, which means that once the data is populated, you do not have to keep the database connection. A network connection to the database server will not affect your client application using the data in the database even if it is disconnected. One of the advantages of this technique is that you release the database to other users and your application is no longer dependent on the connection. However, this also means that the data in the database may change after you populate the dataset, and you should be aware of the possible differences.

The data access layer can create multiple DataSet objects. For example, you might use a dataset to hold static data to populate a list box on the user interface, or you might want to make that data accessible to multiple users. Another object may contain the updated data for a user, and the code to upload changes back to the database is as simple and necessary as possible. Now, you can use an object to complete all of these requirements.

Figure 1 shows the structure of each table in the Northwind database, which contains customer and order information. You can create a dataset as a structure image of the database, or change it completely to suit your application requirements.



Figure 1: Structure of each table used in the Northwind Customers DataSet

The following code defines a dataset in the GetDataSet method that contains all the columns of the user's order data. It comes with a Bsaveschema parameter that indicates whether the process needs to save copies of the DataSet object's XML and XML schemas as disk files, which will be used in other parts of the application.

The GetDataSet code begins with instantiating a new empty DataSet object, which is named Northwindcustomerorders. If you do not provide a name for this dataset, the dataset will take the default named NewDataSet. This is not a problem in most cases, but when you generate and use XML from this dataset, the name is sometimes used as part of the XML structure.

Public Shared Function GetDataSet (ByVal Bsaveschema as Boolean) as DataSet

' Instantiate the DataSet, giving it a custom name

' (the default name is NewDataSet)

Dim DS as New DataSet ("Northwindcustomerorders")

The next step is to establish a database connection-reading the database connection string from the application configuration file.

' Set up the connection to the ' database, reading from the app.config

Dim sqlcnn as New SqlConnection

Sqlcnn.connectionstring = _

ConfigurationSettings.AppSettings ("Connectdb")

Sqlcnn.open ()

There are several ways to create a DataTable object in a dataset to hold relational data. One way to do this is to use an SQL statement (as a string variable). This is easy to do, but usually you prefer to use stored procedures to access data. The simple SELECT statement in the following code requires no arguments-it returns the complete collection of records in the table.

Most ado.net classes, like many other classes in the. NET framework, have multiple constructors, each with different parameters, so that you can choose how to instantiate the object. You can instantiate a SqlCommand object by passing a string of SQL statements and a constructor for a connection object to the SqlCommand object.