Using ADO.NET with SQL Server

Although the DataReader is very fast and simple, in many situations, we're going to need more than just forward-only access to the results of our queries - this is where the DataSet and SqlDataAdapter classes come in. The DataSet is essentially an in-memory database, complete with multiple tables, constraints, running queries and sorting, plus the ability to persist its state to an XML file. You can use SqlDataAdapter (and its cousins OleDbDataAdapter and OdbcDataAdapter) to populate the DataSet with rows from a SQL server query. Once populated, you can make changes to the DataSet, add rows, perform sorting etc, and then use the SqlDataAdapter again to reflect these changes in the original database using appropriate UPDATE and DELETE sql statements. Depending on the available resources on your database server and the web server, relocating these operations to a disconnected model can be greatly beneficial.

Lets first take a look at how we can fill a dataset:

[C#]
// create the data adapter
SqlDataAdapter dataAdapter = new SqlDataAdapter ("SELECT userId,username FROM users ORDER BY username", sqlConn);
// create the DataSet
DataSet dataSet = new DataSet();
// fill the DataSet using our DataAdapter
dataAdapter.Fill (dataSet);

Here the SQL query is actually executed when we call the Fill method of the SqlDataAdapter - and as soon as the query is completed, the connection for the database query is closed (so the DataSet acts as "disconnected" data store). Now that we've populated the DataSet, its Tables property will be populated with DataTable objects for each table in our query. So, the following code would have the same result as our small DataReader example earlier - except we're no longer reading the results straight from the database, and can enumerate the table rows as often as we like, in any order we like.

[C#]
foreach(DataRow dataRow in dataSet.Tables["users"].Rows) {
    Debug.WriteLine(dataRow["username"] + "(" + dataRow["userid"] + ")");
}

Sorting and Filtering

How about if we want to filter or sort the data further once we've retrieved it from the database? The simplest way (if we're not looking to DataBind to a control), is to use the Select method of the DataTable, which accepts two parameters - a filter expression and a sort expression - and returns an array of DataRow objects.

[C#]
DataRow[] matchingRows = dataSet.Tables["users"].Select("username like 'Bob%'","dateJoined DESC");

Another method is to use the DataView object, which is specifically designed for sorting and filtering rows, and can be used as a DataSource in its own right - so we could bind a DataGrid control to this customised "view". We can get an instance of a DataView object from the DefaultView property of our DataTable. Then, we can sets its Sort and RowFilter properties:

[C#]
DataView dataView = dataSet.Tables["users"].DefaultView;
dataView.RowFilter = "username like 'Bob%'";
dataView.Sort = "dateJoined DESC";

myDataGrid.DataSource = dataView;
//Call to DataBind needed in ASP.NET
//myDataGrid.DataBind();

Adding, Deleting & Updating Rows

Making modifications to the DataSet, and then updating the database to reflect this changes is simple, especially if we're just performing queries on one table. For the moment, lets assume that we're using a DataGrid to display our data - which means it will automatically add new rows to the DataSet for us, and allow rows to be edited or deleted without having to write any code whatsoever. All we'll need to do is "sync" the database with the modifications that take place, using the SqlDataAdapter/OleDbDataAdapter.

For this purpose, the data adapter exposes four properties - SelectCommand (which we have already set indirectly when we construct the SqlDataAdapter object), UpdateCommand, DeleteCommand and InsertCommand, and a method called Update. All we need to do is provide SqlCommand/OleDbCommand objects for these properties, and call Update - then the DataAdapter will use the appropriate commands to update the database with the changes made in the DataSet. In fact, our lives are made even easier by the existence of the SqlCommandBuilder class - which will mean we only have to write the one SELECT statement, and it will do the rest. Here's a demonstration:

[C#]
// create the data adapter
SqlDataAdapter dataAdapter = new SqlDataAdapter ("SELECT userId,username FROM users ORDER BY username", sqlConn);
// create an SqlCommandBuilder - this will automatically generate the
// commands, and set the appropriate properties in the dataAdapter
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
// create the DataSet
DataSet dataSet = new DataSet();
// fill the DataSet using our DataAdapter into a table called users
dataAdapter.Fill (dataSet,"users");
// set the DataGrid source to the one table in our dataset
myDataGrid.DataSource = dataSet.Tables[0];

Then, when we've finished making our changes to the DataSet via the DataGrid, we call

dataAdapter.Update(dataSet);

and the database will now contain the changes we have made.