In this article, we’ll cover the most important aspects of running queries using ADO .NET. This continues from Part 1, in which we discussed ADO .NET and saw how to connect to a database.
A Stellar Example
To demonstrate the capabilities of ADO .NET, we’ll need a database with at least one table. We’ll focus on SQL Server for the time being, and talk about how to connect to different database engines in Part 3.
Therefore, in SQL Server Management Studio, create a database called Cosmos and run the following query to create a table called Star and populate it with a few rows:
create table Star(
Id int not null primary key identity(1, 1),
Name nvarchar(50) not null
);
insert into Star (Name) values
('Aldebaran'),
('Sirius'),
('Betelgeuse');
Running A Query
In ADO .NET, there are three things we need to do to execute a query against a database:
- Open a connection
- Create an SQL command (i.e. the query)
- Execute that command and get the results
We’ve actually already done the first step in Part 1, so we can start with the same code, just with an updated connection string to specify that we will actually be connecting to the Cosmos database that we created in the previous section:
const string connStr = @"Server=.\SQLEXPRESS;
Initial Catalog=Cosmos; Integrated Security=True";
using (var conn = new SqlConnection(connStr))
{
conn.Open();
Console.WriteLine("Connected!");
}
After opening the connection, we create our query. Here’s one way to do it:
using (var conn = new SqlConnection(connStr))
{
conn.Open();
const string query = "select * from Star";
using (var command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = query;
command.CommandType = System.Data.CommandType.Text;
// TODO execute query
}
}
We specify the connection that the command will use to execute the query. We also provide the SQL query in the CommandText property, given that CommandType is Text (which applies for regular SQL queries). If you wanted to execute a stored procedure (SP), you would set CommandType to StoredProcedure and put the name of the SP in the CommandText property instead.
But since Text is actually the default value of CommandType, and we can provide the CommandText and Connection in SqlCommand‘s constructor, then we can write this much more concisely as follows:
using (var conn = new SqlConnection(connStr))
{
conn.Open();
const string query = "select * from Star";
using (var command = new SqlCommand(query, conn))
{
// TODO execute query
}
}
There is also a third way to create the SqlCommand which we’ll talk about in Part 3.
At this point, we can execute the query and get the results. For this particular query, this means calling ExecuteReader() and iterating over the results as follows:
using (var conn = new SqlConnection(connStr))
{
conn.Open();
const string query = "select * from Star";
using (var command = new SqlCommand(query, conn))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = (int) reader["Id"];
string name = (string) reader["Name"];
Console.WriteLine($"{id}: {name}");
}
}
}
Each call to SqlDataReader.Read() will move to the next row, and return false when there are no more, breaking the loop. With each iteration, you can extract column values in a few different ways, including:
- By column name, as shown above. I favour this method because I find it to be more readable, although there is a little more conversion work involved.
- By column number, via
reader[columnNo], which returns anobjectand therefore also requires conversion. - Using helper methods such as
reader.GetInt32(columnNo). These handle data conversion for you, but they only take the column number as input, making them less readable.
In Part 4, we’ll see how to avoid this nonsense altogether by using Dapper.
The output of the code we have so far would be:
1: Aldebaran
2: Sirius
3: Betelgeuse
Asynchronous Queries
Queries against a database are a type of Input/Output (I/O) operation (like reading a file, making an HTTP request, etc), and as such they should be executed asynchronously. Fortunately, asynchronous versions of ADO .NET methods are available, and we only need to update the code in three places (other than Main() itself, of course):
static async Task Main(string[] args)
{
const string connStr = @"Server=.\SQLEXPRESS;
Initial Catalog=Cosmos; Integrated Security=True";
using (var conn = new SqlConnection(connStr))
{
await conn.OpenAsync();
const string query = "select * from Star";
using (var command = new SqlCommand(query, conn))
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
int id = (int) reader["Id"];
string name = (string) reader["Name"];
Console.WriteLine($"{id}: {name}");
}
}
}
}
Scalar Queries
Not all queries require you to iterate over multiple rows. For instance, the following query simply gives us back the value ‘Sirius‘:
select top 1 Name
from Star
order by Name desc;
The execution of such queries is simpler, and uses ExecuteScalarAsync() instead of ExecuteReaderAsync(). Here’s an example:
using (var conn = new SqlConnection(connStr))
{
await conn.OpenAsync();
const string query = @"select top 1 Name
from Star
order by Name desc; ";
using (var command = new SqlCommand(query, conn))
{
string name = (string) await command.ExecuteScalarAsync();
Console.WriteLine(name);
}
}
We essentially threw away the third using block because it’s not necessary for a query that returns a single scalar value. However, ExecuteScalarAsync() returns Task<object>, which means that a cast is necessary (especially if the data returned is a numeric type — for a string you could get away with just calling ToString()).
Non Queries
There is another category of queries that return nothing at all: inserts, updates and deletes. Technically, although we tend to use the term “query” for any SQL statement, these aren’t really queries at all, because they aren’t giving us back any data. In fact, we execute them in ADO .NET by calling ExecuteNonQuery(). Take a look:
using (var conn = new SqlConnection(connStr))
{
await conn.OpenAsync();
const string query = @"insert into Star (Name) values ('Vega');";
using (var command = new SqlCommand(query, conn))
{
int rows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"{rows} row(s) affected.");
}
}
Calling ExecuteNonQueryAsync() gives us back a Task<int>, representing the number of rows inserted, updated or deleted. This can be useful to determine whether the query was effective, but is not strictly necessary, so you could also ignore the return value if you don’t need it.
Summary and Next Steps
After setting up a very simple example of a database table, we saw how to execute different types of queries: those giving us rows of data, those giving us back a single scalar value, and those that manipulate the table data and do not give us any data back. We’ve also seen how to run these queries asynchronously.
However, we haven’t yet talked about parameterised queries, transactions, or how to connect to databases that aren’t SQL Server. In fact, we’re about to cover these in Part 3.
Also, it’s worth mentioning that while we haven’t seen any exception handling in the code samples for the sake of brevity, it needs to be present in your production code.
