Using ADO.NET with SQL Server

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.

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!