Using ADO.NET with SQL Server

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.

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!

You might also like...

Comments

About the author

James Crowley

James Crowley United Kingdom

James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audien...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.”