Using DataSet and DataAdapter
The SqlCommand object allows for interaction with a data source while in a connected mode.
DataReader provides a way to read data quickly and drop the connection. The DataSet and SqlDataAdapter objects provide a means of working with data that straddles the characteristics of the command and data reader objects.
A DataSet stores data in memory, with the ability to hold many tables. DataSets merely hold data, and do not interact with a data source. DataAdapter provides the data source connection and the ability to perform disconnected operations. DataAdapter opens connections only when necessary and closes them immediately after completing a task; for example, when populating a DataSet, it opens the connection, retrieves data to put into the DataSet, and then closes the connection. This efficiency improves the scalability of an application. In web applications, this proves critical as database accesses will grow with the user base.
DataSet instantiation occurs as with any other C# object. Review an example below:
DataSet dsProducts = new DataSet();
SqlDataAdapter contains SQL commands and a connection object. Its initialization requires a select statement and connection object:
SqlDataAdapter daProducts = new SqlDataAdapter( "select ProductID, OrganizationName from Products", conn);
SqlDataAdapter contains all necessary commands, and the example above reveals only a select operation. Two ways exist for adding insert, update, and delete commands: SqlDataAdapter properties and SqlCommandBuilder. This guide examines the first option, which allows broader capability, later. The most simple way involves SqlCommandBuilder:
SqlCommandBuilder cmdBL = new SqlCommandBuilder(daProducts);
The statement above instructs CommandBuilder to use the specified DataAdapter. It then reads associated statements; infers insert, update, and delete commands; and assigns new commands to insert, update, and delete properties of the DataAdapter. It suffers from certain limitations; for example, it fails in join operations.
POPULATING THE DATASET
After instantiating DataSet and DataAdapter, populate the DataSet. The Fill method performs this task:
The example above utilizes two parameters, a dataset and a table name. The DataSet must be instantiated prior to a fill, and the statement spawns the table within the DataSet.
DataSets allow for use with multiple DataAdapters. Each data adapter can fill one or more DataTable objects and manage updates in the appropriate source. DataRelation and Constraint objects added to the DataSet allow relation of data from different sources such as an SQL Server database, MySQL, and an IBM DB2 database. One or more DataAdapters manage the required communication.
DataSets bind with Windows forms DataGrids and ASP.NET. Review an example of assigning the DataSet to a DataGrid:
dgProducts.DataSource = dsProducts; dgProducts.DataMember = "Products";
After making modifications to data, the data source of course requires an update. The example below demonstrates how to use DataAdapter's Update method to push modifications back to the data source: