Objectives to Learn

string sSQL = "SELECT * FROM Products"; string sConnString = "Server=(local);Database=Northwind;Integrated Security=SSPI;"; using (SqlConnection oCn = new SqlConnection(sConnString)) { SqlCommand oSelCmd = new SqlCommand(sSQL, oCn); oSelCmd.CommandType = CommandType.Text; oCn.Open(); SqlDataReader oDr = oSelCmd.ExecuteReader(); DataGrid1.DataSource = oDr; DataGrid1.DataBind(); }

The Disconnected Side

The DataSet is the main data storage tool in the ADO.NET disconnected architecture. Unlike the DataReader, the DataSet is not connected directly to a database through a Connection object when you populate it. Instead, to fill a DataSet from a database you first create a DataAdapter object (such as a SqlDataAdapter) for the provider and associate it with a SqlConnection object. Then the SqlDataAdapter can broker the data retrieval for the DataSet by issuing a SqlCommand against the database through the SqlConnection, retrieving the data, and filling the DataSet.

You can think of the SqlDataAdapter as a bridge between the connected and disconnected objects. One of its purposes is to serve as the route for a rowset to get from the database to the DataSet. For example, when the SqlDataAdapter's Fill method is executed it opens its associated SqlConnection object (if not already open) and issues its associated SqlCommand object against the SqlConnection. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet. Once all of the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.

The following code shows how a DataSet can be filled from the Products table of the Northwind database. Notice that there is no explicit SqlDataReader object in this code sample:

Copy Code

string sSQL = "SELECT * FROM Products";
string sConnString = 
    "Server=(local);Database=Northwind;Integrated Security=SSPI;";
SqlDataAdapter oDa = new SqlDataAdapter();
DataSet oDs = new DataSet();
using(SqlConnection oCn = new SqlConnection(sConnString))
{
    SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);
    oSelCmd.CommandType = CommandType.Text;
    oDa.SelectCommand = oSelCmd;
    oDa.Fill(oDs, "Products");
}

The DataSet can read and load itself from an XML document as well as export its rowset to an XML document. Because the DataSet can be represented in XML, it can be easily transported across processes, a network, or even the Internet via HTTP. Unlike the DataReader, the DataSet is not read-only. A DataSet can be modified, and rows can be added or deleted. Changes to a DataSet can be sent to the database via a managed provider's objects. Another key difference between the DataSet and the DataReader is that the DataSet is fully navigable. Its rows can be traversed forward or backward. The DataReader can be traversed forward only. In addition, a DataSet is highly flexible in that its DataTable objects can be filtered vertically or horizontally and they can be sorted or even searched. The DataSet is independent of any one data provider as it relies on a DataAdapter specific to each provider to broker the data between the DataSet and the database.

Not only can the DataSet be loaded from XML, it can also be loaded manually. Notice in Figure 2 how a DataTable is created and its columns added manually. A primary key constraint is established as well as an auto-incrementing value for the key field. Then the DataTable is added to an empty DataSet (though is doesn't have to be empty) and the rows are added one by one to the DataTable, all without ever connecting to a data source.

Figure 2 Creating a DataSet Manually

Copy Code

//-- Create the table
DataTable oDt = new DataTable("Employees");
DataRow oRow;
oDt.Columns.Add("EmployeeID", System.Type.GetType("System.Int32"));
oDt.Columns.Add("FirstName", System.Type.GetType("System.String"));
oDt.Columns.Add("LastName", System.Type.GetType("System.String"));
oDt.Constraints.Add("PK_Employees", oDt.Columns["EmployeeID"], true);
oDt.Columns["EmployeeID"].AutoIncrement = true;
oDt.Columns["EmployeeID"].AutoIncrementSeed = -1000;
oDt.Columns["EmployeeID"].AutoIncrementStep = -1;
oDs.Tables.Add(oDt);

//-- Add the rows
oRow = oDs.Tables["Employees"].NewRow();
oRow["FirstName"] = "Haley";
oRow["LastName"] = "Smith";
oDs.Tables["Employees"].Rows.Add(oRow);
oRow = oDs.Tables["Employees"].NewRow();
oRow["FirstName"] = "Madelyn";
oRow["LastName"] = "Jones";
oDs.Tables["Employees"].Rows.Add(oRow);

//-- Bind it to a DataGrid
grdEmployees.DataSource = oDs.Tables["Employees"];

Because the DataSet is disconnected, its use can reduce the demand on database servers. It does, however, increase the memory footprint in the tier where it is stored, so be sure to account for that when designing around a DataSet as your data store. Scaling up on the middle tier using parallel servers and load balancing is a common way to handle the increased load so that session-based information can be stored in objects such as the DataSet.

Choosing a DataReader (Connected ) or a DataSet (Disconnected)