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 |
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!
Comments