Using ADO.NET with SQL Server

Introduction

Using ADO.NET with SQL Server and Access

As a developer, I find that an incredible amount of my time is spent dealing with databases in some form or another - be it displaying, manipulating or analyzing data; creating client or server applications. The advent of the .NET Framework has brought with it some major changes, and database access is certainly no exception. Although ADO has been around since 1996, with the arrival of ASP, its latest incarnation present in the .NET Framework - ADO.NET - really does represent its coming-of-age. This new architecture is so extensive, that I can only hope to give you a brief introduction. Although the article is using the SQL server data access components, much applies equally as well to using Microsoft Access. We'll be covering connecting to a SQL Server database, executing queries, calling stored procedures, filtering data, and reflecting changes in a database - and point you in the direction of further resources for more in-depth discussions on particular topics. I've provided code in both VB and C# if there are any major syntatical differences, and the rest in C#.

If you've got any comments, or if I've made any stupid mistakes, then feel free to drop me a line!
Connecting to the database

In order to use ADO.NET, we'll need to import two namespaces:

[VB]
Imports System.Data
Imports System.Data.SqlClient

[C#]
using System.Data;
using System.Data.SqlClient;

In order to establish a connection with SQL Server, we use the SqlConnection object. There are also generic OleDbConnection and OdbcConnection objects present in the System.Data.OleDb and System.Data.Odbc namespaces for use with other data sources such as Access (NB: support for ODBC was added in .NET 1.1). This would work in exactly the same way as illustrated here, but as Microsoft were kind enough to provide a data access class specifically optimized for SQL server, we might as well take advantage of it! If you're using Access, then you can literally replace every occurence of SqlSomething with OleDbSomething.

Opening a connection is very simple:

[VB]
' create a new SqlConnection object with the appropriate connection string
Dim sqlConn As New SqlConnection(connectionString) 'OleDbConnection i
' open the connection
sqlConn.Open()
// do some operations here...
// close the connection
sqlConn.Close()

[C#]

// create a new SqlConnection object with the appropriate connection string
SqlConnection sqlConn = new SqlConnection(connectionString)
// open the connection
sqlConn.Open();
// do some operations ...
// close the connection
sqlConn.Close();


with the connection string usually taking this form:
server=serverAddress;uid=username;pwd=password;database=database;

It is probably worth noting at this stage something about connection pooling. The idea behind connection pooling is simple - instead of incurring a large amount of overhead each time a connection to database server is established and closed, once a connection has been opened, it remains open for the lifetime of the process, available to be used again if needed. Pooling database connections can significantly enhance the performance and scalability of your application. The .NET data providers automatically pool connections for you. However, to take maximum advantage of these, you should take note of the following:
Connections are only pooled if they have the same connection string; so ensure this is always constant.
When finished with a SqlConnection object, call Dispose() or Close() to release the connection back to the pool.
In order to keep the maximum number of connections available, you should keep connections open for a short as period as possible - remembering that thanks to connection pooling, re-opening a database connection will incur little overhead.

For more information on connection pooling, take a look at this MSDN page.

Once open, you can't do much with a SqlConnection object on its own - other than close the connection again, and query its connection status using the ConnectionState property, so we'll move on to how we go about querying the database.

Executing a SQL query and SqlDataReader

In order to execute a SQL query, or a stored procedure (covered later) on the server, we use the SqlCommand object. The constructor that we'll use here accepts the query to execute, and the connection that it should use:

[VB]
' create the command object
Dim sqlComm As New SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn)

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn);

From here, we have a number of methods to choose from in order to execute the query.

      Item                                                                           Description
ExecuteReader            ---->     Executes the query, and returns a SqlDataReader object.
ExecuteNonQuery      ---->     Executes the query, and does not collect any results.
                                                      Generally used for queries such as UPDATE and DELETE.
ExecuteScalar              ---->     Executes the query, and returns a single value
                                                     (from the first column of the first row).

If we're not interested in the result of a query (other than whether it executed successfully or not), then use ExecuteNonQuery(). For example,

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("DELETE FROM users WHERE userid=1", sqlConn);
sqlComm.ExecuteNonQuery();

If you're performing a query, and are only interested in the first column of the first row returned, then use ExecuteScalar(), and cast the result to the appropriate data type. For example,

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("SELECT COUNT(*) FROM users", sqlConn);
int userCount = (int)sqlComm.ExecuteScalar();
Using the DataReader

The most common case, however, is going to be when we're retrieving multiple rows and columns of data. In this case, we use the ExecuteReader method, which returns an instance of a SqlDataReader object. Like its cousin OleDbDataReader, this provides read-only forward access to rows returned by the SqlCommand object.

So, now we've got this SqlDataReader object, what can we do with it? If you're interested in whether the query returned any results at all, or how many rows were affected by the query (usually due to DELETE or UPDATE statements) then check the HasRows and RecordsAffected properties.

First, call the Read() method, which advances the reader to the first row (and then to the next row on subsequent calls), and returns a boolean value indicating whether there was actually a row to read. Now we're free to access any columns that we see fit - we've got three different ways to do this - and then remember to call Close() on the DataReader (and the database connection, if need be).

If you want to access them by name - probably the most common and readable option - you can use the reader's indexer:

[VB]
Dim sqlComm As New SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn)
Dim r As SqlDataReader = sqlComm.ExecuteReader()
While r.Read()
Dim username As String = CStr(r("username"))
Dim userID As Integer = CInt(r("userid"))
Debug.WriteLine((username + "(" + userID + ")"))
End While
r.Close()

[C#]
SqlCommand sqlComm = new SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn);
SqlDataReader r = sqlComm.ExecuteReader();
while ( r.Read() ) {
string username = (string)r["username"];
int userID = (int)r["userid"];
Debug.WriteLine(username + "(" + userID + ")");
}
r.Close();

Note that, as with the ExecuteScalar method, we have to cast the value to the type we need - and if the column username wasn't a string in the code above, we'd get an InvalidCastException. For strings, we could equally well have used
r["username"].ToString()
instead of the formal cast. If you're trying to squeeze every ounce of performance from your application, then its certainly worth noting that the DataReader performs a case-sensitive search for the column name before resorting to a case-insensitive one - so it does help to be consistent in the way you case the field names.

A more efficient method (note: we're still talking small efficiency gains here!) is to use the column's index (rather than its name) - but this requires you to be very careful when writing your SQL statements (and in later modifications) that you don't change this:

int integerColumnValue = (int)r[i];


And finally, you could use one of the many Get methods that the SqlDataReader offers:

int integerColumnValue = r.GetInt(i);


These include GetInt, GetString, GetDouble etc, all of which accept an integer parameter for the column index.

One thing to bear in mind. If your query has the possibility of returning columns that are NULL, then you'll need to be careful before casting or calling the Get methods - the indexers will not return null, but DBNull.Value, and the Get methods will throw an exception, so for those you will need to check using sqlDataReader.IsDbNull(columnIndex) first.
Data Binding

Alternatively, if you simply want to bind a control like the DataGrid or DataRepeater objects to the rows, you can use the SqlDataReader as a DataSource:

[VB]
Dim sqlComm As New SqlCommand("SELECT userId,username FROM users ORDER BY username", sqlConn)
Dim r As SqlDataReader = sqlComm.ExecuteReader(CommandBehavior.CloseConnection)
myDataGrid.DataSource = r
' Call to DataBind needed in ASP.NET
'myDataGrid.DataBind()
r.Close()

[C#]
SqlCommand sqlComm = new SqlCommand("SELECT userId,username FROM users ORDER BY username", sqlConn);
SqlDataReader r = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);
myDataGrid.DataSource = r;
// Call to DataBind needed in ASP.NET
//myDataGrid.DataBind();
r.Close();

Note that in the above code we provide the ExecuteReader command with a parameter
CommandBehavior.CloseConnection
- this tells the data reader to call close on the database connection when we call its own Close method. If we hadn't used this, then we would need to call sqlConn.Close() at some point in our code!

Stored Procedures and other queries with parameter

Writing code that generates correct SQL code for calling stored procedures, or with UPDATE and INSERT statements can often be a real pain - especially when worrying about escaping strings, or getting date formats correct. Using the SqlCommand/OleDbCommand object allows us to provide a SQL statement with variables waiting to be filled - and we can then set the value of these variables seperately in our code, and let ADO.NET worry about sending it to the database correctly! First, we create the SqlCommand object as usual. If we are calling a stored procedure, then we just need to provide its name. If we are using a normal SQL statement, then we'll prefix the parameter names with @ (as they are in Stored Procedures).


[C#]
// stored procedure
SqlCommand com = new SqlCommand("UpdateUser",sqlConn);
// tell the command that this is a stored procedure!
com.CommandType = CommandType.StoredProcedure;
/*

we'll assume the Stored Procedure takes the following form at the server:

ALTER PROCEDURE UpdateUser(@id AS INTEGER, @username AS VARCHAR(30)) AS
UPDATE users SET username=@username WHERE userId=@id

*/

or

// normal SQL statement
OleDbCommand com = new OleDbCommand("UPDATE users SET username=@username WHERE userId=@id",sqlConn);


We specify the parameters by adding SqlParameter/OleDbParameter objects to the Command object's Parameters property. The constructor we'll use here accepts a string for the parameters name, and an OleDbType or SqlDbType for the data type. We then add this new Parameter object to the collection, and set its Value property:

[VB]
' add a parameter @username to the command, and set its value to the string "James"
com.Parameters.Add(New SqlParameter("@username", OleDbType.VarChar)).Value = "James"
' add a parameter @id to the command, and set its value to 1
com.Parameters.Add(New SqlParameter("@id", OleDbType.Integer)).Value = 1
' we can now execute the command...
com.ExecuteNonQuery()

[C#]
// add a parameter @username to the command, and set its value to the string "James"
com.Parameters.Add(new SqlParameter("@username",OleDbType.VarChar)).Value = "James";
// add a parameter @id to the command, and set its value to 1
com.Parameters.Add(new SqlParameter("@id",OleDbType.Integer)).Value = 1;
// we can now execute the command...
com.ExecuteNonQuery();


Stored Procedure Return Values

If your stored procedure uses the RETURN statement, or has parameters marked as out , then you can also use the Parameters collection to retrieve these values after a query has been executed.

To get the value of a RETURN statement, you need to do the following:



[C#]
// add a new parameter, with any name we want - its for our own use only
SqlParameter sqlParam = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.ReturnValue;

Then, after the stored procedure has been executed,

int returnValue = (int)com.Parameters["@ReturnValue"].Value

will retrieve the value that was set.

Now suppose you had a stored procedure that has an output parameter - maybe something like the following:

CREATE PROCEDURE AddUser @UserName VarChar(30), @Identity int OUT AS ...

then again, we can use

[C#]
// add a new parameter, we need to get the name right this time!
SqlParameter sqlParam = com.Parameters.Add("@Identity", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.Output;

and after the execution of the query, com.Parameters["@Identity"].Value will contain the value that was set in the stored procedure. If you need any further info, see Input and Output Parameters, and Return Values on MSDN.

Using the DataSet

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.


Doing without CommandBuilder & Wrapping Up

Although the CommandBuilder does a great job on simple tables, when we start using more complex SELECT statements - or simply want more control over what's going on - then its time to specify our own commands for each of the Command properties in the DataAdapter. First, we can specify the SelectCommand as usual, perhaps adding parameters for a Stored Procedure or WHERE clause.

[C#]
// create the data adapter - we'll specify the connection through our SqlCommand object
SqlDataAdapter dataAdapter = new SqlDataAdapter();
SqlCommand sqlSelectCommand = new SqlCommand("SELECT userid,username FROM users",sqlConn);
// assign to the SelectCommand property
dataAdapter.SelectCommand = sqlSelectCommand;

Now, for the INSERT, DELETE and UPDATE statements we write a generalized query with parameters - and then take advantage of another constructor for the SqlParameter object that lets us effectively bind a parameter to a column name (as obviously we won't actually know its value ourselves);

public SqlParameter (System.String parameterName, System.Data.SqlDbType dbType, System.Int32 size, System.String sourceColumn )

So, we can do the following:

[C#]
// create a new Command for our UPDATE statement
SqlCommand sqlUpdateCommand = new SqlCommand("UPDATE users SET username=@username WHERE userid=@id",sqlConn);
// add parameter to this command for @username, and bind it to the column "username"
sqlUpdateCommand.Parameters.Add("@username",SqlDbType.VarChar, 30, "username");
// create another parameter
SqlParameter sqlParam = new SqlParameter("@id",SqlDbType.Integer, 8, "userid");
sqlParam.SourceVersion = DataRowVersion.Original;
// add parameter
sqlUpdateCommand.Parameters.Add(sqlParam);
// assign to UpdateCommand
dataAdapter.UpdateCommand = sqlUpdateCommand;


Now, when the DataAdapter needs to perform an update, it will run the above command - setting @username to the value of the new username in the row that was modified. @userid, however, will be set to the original version of its value, as we set the SourceVersion property of this parameter to DataRowVersion.Original. This is generally a good idea - otherwise if we had for some reason modified the user id then we'd lose our "handle" on the row assuming userid was a primary key, and not update the correct row. If we wanted to access the new version, we would set SourceVersion to DataRowVersion.Current (its default value).

The DeleteCommand and InsertCommand properties of the DataAdapter can be set in a similar manner. Weaning Developers from the CommandBuilder on MSDN provides a good insight into further alternatives to the CommandBuilder.
Adding, Updating & Deleting Rows in a DataSet/DataTable

When we started examining the DataSet object, we skipped over the issue of actually modifying the data in our DataTable object - instead allowing the DataGrid to do the work for us. For sake of completeness, I shall give a brief summary as to how to do it "manually" here.

To add a new row to a DataTable, use the NewRow() method:

[C#]
// get a new row
DataRow newRow = myDataSet.Tables["users"].NewRow();
// set the appropriate fields here... for example
newRow["username"] = "myNewUsername";
// add to the DataTable:
myDataSet.Tables["users"].Rows.Add(newRow);


To update a row in the DataTable, you can simply modify a columns value:

myDataSet.Tables["users"].Rows[rowIndex]["realName"] = "James Crowley";

And finally, to delete one, use the Remove or RemoveAt method:
myDataSet.Tables["users"].Rows.RemoveAt(rowIndex)

or
myDataSet.Tables["users"].Rows.Remove(dataRowObject)

For more information on these, I suggest you take a look at the MSDN article Manipulating Data in a Data Table.
Conclusion

Well, that's pretty much it folks, so I'll just give a very quick overview!
Establishing a Connection - Use the SqlConnection and OleDbConnection objects
Running queries - Use the SqlCommand and OleDbCommand objects - using theirExecuteNonQuery, ExecuteScalar and ExecuteReader methods
Stored procedures - Just the same as above, but add appropriate SqlParameter objects to its Parameters collection
Fast forward-only data access - Use SqlDataReader and OleDbDataReader
Disconnected data access - Use a DataAdapter's Fill method to populate a DataSets/DataTable object, and to then reflect changes in the database by calling Update