SqlDataAdapter and the DataSet Class

for RuBoard

The DataSet class is a memory-resident, lightweight relational database class. It has properties that reflect the tables ( Tables ) and relationships between tables ( Relations ) within the data set. You can control whether corresponding constraints are enforced with the EnforceConstraints property. You can name the data set with the DataSetName property. You can also set the name of the data set in the DataSet constructor.

The SqlDataAdapter class is used to get data from the database into the DataSet . The constructor of the HotelBroker class shows how to use a data adapter class to populate a data set. The code is found in the Hotel subdirectory of the HotelBrokerAdmin directory of the case study for this chapter.

 conn = new SqlConnection(connString);  citiesAdapter =  new SqlDataAdapter();  citiesAdapter.SelectCommand = new SqlCommand(                  "select distinct City from Hotels", conn);  citiesDataset = new DataSet();  citiesAdapter.Fill(citiesDataset, "Cities"); 

The SqlDataAdapter class has properties associated with it for selecting, inserting, updating, and deleting data from a data source. Here the SqlCommand instance is associated with the SelectCommand property of the SqlDataAdapter instead of being executed independently through one of its own execute methods .

The Fill method of the SqlDataAdapter is then used to execute the select command and fill the DataSet with information to be put in a table whose name is supplied as an argument. If the database connection was closed when the Fill method was executed, it will be opened. When finished, the Fill method will leave the connection in the same state as it was when it was first called.

At this point the connection to the database could be closed. You now can work with the DataSet and its contained data independently of the connection to the database.

SqlDataAdapter is implemented with the SqlDataReader class, so you can expect better performance with the latter. The SqlDataReader might also be more memory efficient depending on how your application is structured. If you do not need the features of the DataSet , there is no point incurring the overhead. If you are doing expensive processing you can free up the database connection by using a DataSet . You may get better scalability by loading the data into the DataSet , freeing the associated database resources, and doing the processing against the DataSet .

Disconnected Mode

This scenario of working with a database is referred to as disconnected. Connected mode represents a tightly coupled , connected environment where state and connections can be maintained. Client-server environments are examples where this is true. ADO and OLEDB were designed for this world. In a connected-mode environment data readers can be used. If necessary, ADO can be used through the COM interop facility. In fact ADO was not rewritten for .NET so that absolute backward compatibility could be maintained , bugs and all.

Connections, however, are expensive to maintain in environments where you want to be able to scale to a large number of users. In this environment there is often no need to hold locks on database tables. This aids scalability because it reduces contention on database tables. The DataTable objects in the DataSet's Tables collection with their associated constraints can mimic the tables and relationships in the original database. For applications that are implemented completely with .NET, DataSet instances can be passed around or remoted to the various parts of an application. For applications that can make optimistic assumptions about concurrency this can produce large gains in scalability and performance. This is true of many types of Internet- or intranet-based applications.

In the disconnected mode, a connection is made in the same way as with the connected mode of operation. Data is retrieved using the data provider's data adapter class. The SelectCommand property specifies the SQL statement used to place data into the data set. Unlike the data reader, which is related to a particular database connection, the data set has no relationship to any database, including the one from which the data originally came.

for RuBoard