ADO.NET

Now that we have a database, let’s see how we can get data from that database into our application, or from our application into our database. An obvious choice nowadays would be an Object Relational Mapper (ORM) that automatically creates classes from our tables and knows how to read and store data with a simple, single command. It can be that easy. However, a lot is still happening that you do not know about. It is my belief that you can’t truly understand an ORM if you haven’t done the same thing without an ORM. In fact, I’ve met developers who have never used anything other than an ORM, and were absolutely clueless when it didn’t support some feature they needed. Or who were amazed when their ORM spewed out huge queries at 100 times a second! If you don’t really understand your ORM, you’re going to have a hard time (and a slow application). So let’s work without an ORM first, and see what happens at a (somewhat) lower level.

Open up Visual Studio and create a new Console Application in C# (or Visual Basic if you like, but my examples will be in C#). The .NET Framework has a set of components for working with data sources (such as databases or spreadsheets), collectively called ADO.NET. Microsoft first introduced ADO, which is short for ActiveX Data Objects, long before the .NET Framework. Most of ADO.NET is in the System.Data namespace, which is automatically available when you create a new project.

Let’s say we want to retrieve a person from the database and use it in our software. The first thing we’d need is a connection to the database. For now, we’re assuming we’ll always use a SQL Server database. In this case, we’re going to make extensive use of the System.Data.SqlClient namespace.

Establishing a connection

The first thing we’ll need is an actual connection. Without a connection, we’ll never be able to do anything with our database.

The connection string

In order to set up a connection, we’ll need a connection string: a text value specifying various options necessary to set up a connection, such as the server on which our instance is running, the database to connect to, login credentials, a name for the connection, timeout, language, and more.

You can connect to any type of database using a connection string, and every database vendor has their own format. Luckily, we have websites such as connectionstrings.com to help us out. The typical connection string looks as follows:

Server=server\instance; Database=database; User Id=username; Password=password.

Or, in case you choose integrated security, instead of User Id and Password you can put in Trusted_Connection=True; or Integrated Security=True; or even Integrated Security=SSPI (they’re all the same).

Likewise, Data Source=server\instance is a much-used alternative to Server=server\instance, and Integrated Security=database is a much-used alternative to Database=database.

So let’s fill in the blanks and create a connection. My connection string looks as follows: Server=Laptop23\SQLEXPRESS; Database=SuccinctlyExamples; Integrated Security=SSPI.

Creating a connection

Now let’s create a connection in code and open it. A short note on the code: I’m using $”…” syntax for strings, which is a new feature in C# 6.0. It’s basically a shorthand for string.Format.

Code Listing 4: Creating a SqlConnection

The first line of code creates a new SqlConnection instance (found in System.Data.SqlClient) and passes in the connection string. A lot of objects we will use need to be properly disposed to release resources (in this case the database connection), so we will properly wrap the SqlConnection in a using block. The connection is automatically closed when the Close() or Dispose() method is called (by the using block). The connection.Open() opens the connection.

A lot can go wrong in these few lines of code, hence the try-catch block. Try making a typo in the connection string (for example, SServer), use a non-existing database (any random value), supply invalid credentials, or try to open the connection twice (without closing in between). All of those will result in an Exception being thrown.

Configuring your connection string

Having the connection string hard-coded in C# may be convenient during development, but is not very practical (or secure) in a production environment. You will need to change it, rebuild, and redeploy every time your database or environment (development, test, production) changes.

Connection strings are often stored in config files. The configuration file for your application is usually different for each environment. The big advantage to storing various settings in a configuration file is that you can change the behavior of your application without actually changing and redeploying your application, allowing different settings in multiple environments. Storing configuration strings in configuration files is so common that the .NET config file actually has a connectionStrings section. I’ve added the SuccinctlyDB connection string to the app.config file.

Code Listing 5: connectionStrings section in config file

We can now easily retrieve the connection string from the config file. First we will need to add a project reference to System.Configuration. Now we can easily replace the first line of code to use the value from the config file.

Code Listing 6: Use connection string from the config file

Notice that when the connection string is not present in the config file, connectionString will be empty and an Exception will be raised when trying to open the connection (“The ConnectionString property was not initialized”). Without the ?. null-conditional operator, we would get a NullReferenceException when trying to get the ConnectionString property instead.

Building a connection string

In some use cases, you might want to edit the connection string or create it from scratch (for example, when writing the SSMS login form). For this use case, we can use a SqlConnectionStringBuilder. The SqlConnectionStringBuilder does pretty much what its name implies; it creates connection strings using various input parameters. Let’s look at a simple example.

Code Listing 7: The SqlConnectionStringBuilder

Notice that the SqlConnectionStringBuilder actually uses the terms DataSource and InitialCatalog instead of Server and Database. Also, IntegratedSecurity is set to true instead of SSPI (or Trusted_Connection).

We can also use the SqlConnectionStringBuilder to edit connection strings. The constructor is overloaded, so you can insert a connection string to be used as a base.

Code Listing 8: Editing a connection string

In this example we’re setting UserID and Password in favor of Integrated Security. Of course, trying to open the connection will now fail, as these credentials are clearly invalid.

Querying your database

The next step is using our connection to retrieve some data. This requires a SqlCommand to hold and execute our queries. Let’s select our Person table using the query SELECT Id, FirstName, LastName, DateOfBirth, GenderId FROM Person. To do this, we’ll need to create the SqlCommand, pass it our query and a SqlConnection, and have it execute the query.

Tip: Never use SELECT * in your production code or SQL queries. SELECT * retrieves all columns in a table, which is not always what you want. By using SELECT * you’re losing control over your code and what it retrieves. SELECT * may yield unintended results. For example, when used in a view, SELECT * will select all fields that were present at the time the view was created, not all fields that are present when the view is queried (so removing a column will still break your view). Overall, it’s best to avoid SELECT * except for some simple, ad-hoc queries.

Reading data

When using the ExecuteReader function on a SqlCommand instance, its select query is executed and we get a SqlDataReader that holds the retrieved data. A SqlDataReader, unfortunately, is not very easy to work with. I don’t know why, but it requires quite a bit of typing. The SqlDataReader reads the result set row by row (forward only), and you can access columns by index or name. Obviously, selecting by index is not very readable, especially in big queries (who knows what column index 43 is?), but getting the index by name returns the value as object and requires the programmer to make the proper conversions. You can get the index of a column by name as well, and then use the SqlDataReader methods to get the correct values. Then, if no value is present (some say the value is NULL), you will have to make a conversion to a default value or null yourself. Fun fact: NULL from your database is a whole different something than null in C#. A database NULL is represented in C# by the static DBNull.Value. Tedious, to say the least. We’ll probably want to store the results in some custom objects too, so we’ll need to create a Person class. Let’s see what this looks like.

First, let’s create a simple Person class.

Code Listing 9: A Person class

And now for the code to get all people from the database:

Code Listing 10: Usage of the SqlDataReader

First of all, notice that I’m declaring my List<Person> at the top so I can use it outside of the using blocks. It’s generally best practice to open a connection, get your data right away, and immediately close your connection. Your database connection remains open until it is explicitly closed, and when using a SqlDataReader, you can’t close it until everything is read. So just read, close, and use your data later!

Creating the SqlCommand is pretty straightforward. And as promised, the SqlDataReader is a little less straightforward. The SqlDataReader works with a table, of which a single row is accessible at a time. The first row (if any) will become accessible when SqlDataReader.Read is called. Any subsequent call on Read will move to the next row, if any. If no (next) row is available, Read will return false. To check if any rows are available, you can use the SqlDataReader.HasRows property. The GetOrdinal function returns the index of the column name (nameof only works here because I’ve named my properties the same as my database columns; if one of them changes, this code will break, but if both change, all your code will keep working).

In this example we see GetInt32, GetString, and GetDateTime. There are more Get[Type] methods, like GetInt16, GetInt64, GetBoolean, GetChar, and more. They convert the SQL type to the appropriate CLR type. If a value is missing, the Get[Type] methods will throw an Exception. So for nullable fields, we’ll need to check for NULL using IsDBNull. Alternatively, there’s a GetValue function that will just return an object. You can use it if you don’t need the type of a value. To check for NULL, use DBNULL.Value.

Code Listing 11: SqlDataReader.GetValue(string)

Now here’s another interesting one. What if we wanted to return multiple result sets? Say we change the select query and retrieve all genders. The SqlDataReader can retrieve multiple result sets and access them in the same manner it can access rows. The reader is on the first result set by default, but if you have any subsequent result sets, you can call NextResult and the reader will move to the next result set. You can call Read again to move to the first row (if any).

Code Listing 12: SqlDataReader with multiple result sets

So does it have to be this difficult to read data from a database? There are some alternatives, which we’ll look at later in this book. Using a SqlDataReader is, however, the best-performing way (when done correctly) to read data from a database.

By the way, if your query returns a single value (a table of one row and one column), you can use ExecuteScalar instead. This will return an object that can be cast to the correct type.

Code Listing 13: Usage of ExecuteScalar

Parameterization

Before we continue, I’d like to address a very, VERY important topic: parameterization. I can’t tell you enough how important it is to use parameters. This is the “practice safe sex” of programming. Unfortunately, just as people have unsafe sex, programmers still don’t always use parameters. The result is slow queries, and even more important, security breaches and leaked data. The worst part is that it happens to really big IT companies like Sony, Symantec, and SAP. There is this fun SQL Injection Hall of Shame that you really don’t want to be in. Really—use parameters.

Now that you know parameterization is really important, let’s look at what it is exactly, and how to implement it. Usually, when working with a database, you’re not selecting entire tables. Most of the time you’re going to use some filter, like WHERE Id = x, where x is a variable. With the knowledge I’ve shared so far, you might be tempted to implement a GetPeopleByName function as follows.

Code Listing 14: Non-parameterized query

Sure, it gets you a list of people with the given first name. It also generates the exact SQL statement that you would use in SSMS to get people with the given first name. So what’s the problem? The firstName variable is probably a value the user entered in some text field, either on the web or in a desktop application. If the user enters a name like “D’artagnan,” this will break your code because the apostrophe ends the string in SQL Server (pretty annoying). Don’t go around and escape names yourself (replace ‘ with ‘’ or some such); that’s just a lot of hassle.

Far more dangerous is if a user (or more likely, a malicious hacker) enters the first name “John’; USE master; DROP DATABASE SuccinctlyExamples; GO --”? Try it out and bam! There goes your database (yes, it’s really gone). This is called SQL Injection. A user is now able to alter SQL statements directly, and by doing this, can get access to sensitive data and destroy that data. There is actually a legendary xkcd comic about SQL Injection that I think every developer should have hanging in the office.

This is really just database basics, yet many people get it wrong—and not just beginners. SAP, Yahoo, LinkedIn, and even the FBI and NASA have fallen victim to this simple yet so dangerous exploit. All in all, tens of thousands of applications (and programmers) have been left unsecure because people don’t parameterize their queries. Unbelievable, right?

Let’s see how we can parameterize in .NET. Luckily, this is very easy (making it even more amazing that so many people don’t do it). We can simply place a parameter in the SQL query and add its value using the SqlCommands (Sql)Parameters collection.

Code Listing 15: Parameterized query

Notice that the query now has a placeholder, @FirstName (parameters in SQL Server start with @), where the name is supposed to be. As an added bonus, we don’t have to remember to put quotes around it (since it’s a string) because SQL Server will do that for us. The actual value for @FirstName is pushed to the SqlCommand using command.Parameters.AddWithValue(“FirstName”, firstName); (the @ in the parameter name is optional in this context). Not often do I see a more self-explanatory piece of code. If you push in a value like D’artagnan now, everything will be fine because SQL Server knows this is a single value, and will escape it for you. The same goes for “John’; USE master; DROP DATABASE SuccinctlyExamples; GO --”.

Yet this is still not completely right. It’s safe, but not optimal. The thing is, and I’ll get back to it, SQL Server creates an execution plan for each query. This plan basically contains instructions for the SQL engine to get the queried data in a manner that’s probably pretty fast. The creation of an execution plan takes some time, but luckily SQL Server caches the plan and will reuse it in the future if the same query comes along. This is a double-edged sword, but I’ll get back to that. For now, we want to reuse query plans as much as possible.

So for a query “SELECT FirstName, LastName FROM dbo.Person” this is straightforward. If the same query comes along, the cached plan will be used. But what if the plan contains parameters such as “SELECT FirstName, LastName FROM dbo.Person WHERE Id = @Id”? The value for @Id will probably be different the next time we call this query. Still, as long as @Id has the same type, but not necessarily the same value, the query plan will be reused. And there’s the crux: we never specified a type for our parameter. The AddWithValue method infers the type, but does so from the data passed in to it—not the type of column in the database. The problem here is that the value “Sander” gets inferred to an nvarchar(6) while the value “Bill” becomes an nvarchar(4).

Another problem, and one possibly more devastating to performance, is if a type gets inferred incorrectly. For example, the FirstName database field is a varchar(256), but your parameter has the value “Sander”. This will be converted to nvarchar(6) by .NET. Those are different types, and in order to compare values of these types, SQL Server will have to implicitly cast every value in the FirstName column to nvarchar(6) before comparing. Although SQL Server can optimize some of these differences, there comes a time when this is going to bite you in the backside.

So in order to fix this issue, we should call Parameters.Add instead. Unfortunately, Microsoft didn’t leave us with an easy overload where we can specify type, size, precision, and scale along with the value. In varchar(5), varchar is the type and 5 the size. In decimal(10, 5), decimal is the type, 10 the precision, and 5 the scale. Parameters.Add has a few overloads; most will create the SqlParameter for you, and one simply takes a SqlParameter as input. All of them return the added SqlParameter.

Code Listing 16: A SqlParameter with the correct type and size

So you see, parameterizing your queries is not only secure, robust, and performing, but also not very hard.

Create, update, delete

Inserting, updating, and deleting data goes pretty much the same way. Instead of ExecuteReader, we use ExecuteNonQuery, which only returns the number of rows affected.

Code Listing 17: Insert statement

Again, the whole null to DBNull.Value conversion is pretty annoying. Here are two nifty (extension) methods that can make this kind of code a lot shorter. Unfortunately, they work on ALL types, whether they’re database-compatible or not, so wrong usage will result in an Exception.

Code Listing 18: Some parameter utils

The usage now looks as follows.

Code Listing 19: Usage of the utils

This looks a lot nicer.

The UPDATE method looks pretty much the same; the biggest difference is that we now also need an Id parameter for the WHERE clause.

Code Listing 20: Update statement

And finally, the DELETE method.

Code Listing 21: Delete statement

Stored procedures

Executing stored procedures is really not that different from all the previous examples. Whether you need to call ExecuteReader, ExecuteScalar, or ExecuteNonQuery depends on the nature of the procedure. You can pass in the name of the procedure to the command and change the CommandType to StoredProcedure. Suppose we created a stored procedure, GetFirstName, that returns the first name of a person based on ID. The call would look as follows.

Code Listing 22: Calling a Stored Procedure

Stored procedures can make use of output parameters, which are also easy to implement. Suppose we used an output parameter for the first name. Simply create a parameter, set its Direction to Output, and read the value after execution.

Code Listing 23: An output parameter

Data sets

Retrieving data and manually mapping to your own C# classes is quite a bit of tedious work. An easier method of getting the data from your database directly into memory is by using the SqlDataAdapter. The SqlDataAdapter is pretty much obsolete technology, but I want to briefly discuss it for completeness, and because you might still find code that uses it. It’s also a little introduction to the next chapter, Entity Framework.

The SqlDataAdapter maps the data in your database to a DataSet or DataTable. A DataTable is like your database table in C# memory. A DataSet is a collection of DataTables with references, constraints, etc. What’s awesome is that CREATE, UPDATE, and DELETE statements can be generated automatically. Using DataTables, you can also pass table parameters to SQL Server.

To populate a DataTable with people from the database, we simply call the Fill method on the SqlDataAdapter and give it a DataTable.

Code Listing 24: Populating a DataTable

The SqlDataAdapter takes a SqlCommand in its constructor. The SqlCommand is the command that’s used to select the data. There are a few overloads, one taking the SQL SELECT statement and a connection string. The adapter manages the connection, so there is no need to open and close it explicitly.

Now this looks wonderful. The code is a lot shorter than what we had previously, but how can we read the data from a DataTable? Unfortunately, the DataTable is a collection of rows, which in turn is a collection of columns. So to read everything, we have to loop through the rows and then loop through the columns. The columns can be accessed by index or name. The values are all objects—so there is still (un)boxing, and we still need to cast—and even DBNull is still DBNull. So basically, mapping to custom C# objects is just as hard as with the SqlDataReader.

The strength of DataSets becomes apparent when you use them directly in your code. This makes sense, for example, in WinForms environments with binding. You can update your DataSet directly and the DataSet keeps track of changes.

Code Listing 25: Update, insert, and delete with the adapter

First, I’ve added the SqlCommandBuilder and passed it the SqlDataAdapter. The SqlCommandBuilder builds CREATE, UPDATE, and DELETE SqlCommands based on the SELECT query. It doesn’t do this particularly well, by the way, so you may want to do this manually (the commands are properties of the adapter). After that, we must fill the schema using FillSchema, of the DataSet or DataTable. This will fetch the table’s schema from the database and provide the DataTable with information on columns, types, primary keys, etc. The Fill method gets the data. After that we can update, delete, and insert rows to the DataTable. Each DataRow will keep track of its own state and changes. Last, but not least, the SqlDataAdapter is able to insert, update, and delete the data (in that order) from the DataTable.

As I mentioned, you can use DataTables to pass in table-valued parameters to stored procedures. You can create the following type and procedure in your database to try this one out.

Code Listing 26: Create type and stored procedure

Now, to execute this stored procedure from code, we can pass in a DataTable.

Code Listing 27: DataTable as parameter

There’s a lot more you can do with DataTables and DataSets, but I wouldn’t recommend using this technology for database access unless you absolutely have to.

ADO.NET abstractions

As you’ve seen, I’ve used SqlConnections, SqlCommand, and SqlDataReaders in the previous examples. They work well with SQL Server, but what if you’re using Oracle, MySQL, or another relational database?

One thing you should know about ADO.NET is that there are a lot of abstractions. The SqlConnection, for example, inherits from DbConnection, which implements the IDbConnection interface. If you’re working with Oracle, you’re probably going to make use of an OracleConnection, which inherits from DbConnection. Likewise, we have an OleDbConnection and an OdbcConnection. They work together with OracleCommand, OleDbCommand, and OdbcCommand, which all inherit from DbCommand. Other vendors have implemented their own versions of these classes.

So you see, if you know how to use one, you pretty much know how to use them all. You can use one of these base classes throughout your software and get the correct type using Dependency Injection instead. The DbProviderFactory class is a base factory for constructing specific types of connections, commands, parameters, etc.