When learning to talk to a database, there are two fundamental things that every programmer must do: close the connections and parameterize the queries. These items are separate from the actual process of running an sql statement and receiving the results, but they are still absolutely essential. For some reason, most tutorials available on the internet just gloss over them or even get them just plain wrong, perhaps because it's so second nature to anyone advanced enough to write the tutorial. My goal here is to show you how to do build the entire process, including these additional fundamentals, in a way that makes it easier to get this right, and get it right every time.
The first thing to do is realize that hiding data access code away in one method is not enough: we actually want to build a separate class (or even class library) for this. By creating a separate class, we can make our actual connection method private inside that class, so that only other methods in the class can connect to the database. This way, we set up a gatekeeper that forces all database code in the program to run through an approved channel. Get the gatekeeper code right with regards to the two issues I talked about above, and your whole program will consistently get it right, too. So here's our start:
public class DataLayer
{
private DbConnection GetConnection()
{
//This could also be a connection for OleDb, ODBC, Oracle, MySQL,
// or whatever kind of database you have.
//We could also use this place (or the constructor) to load the
// connection string from an external source, like a
// (possibly-encrypted) config file
return new SqlConnection("connection string here");
}
}
To this point we haven't really addressed either fundamental issue from the introduction. All we've done so far is set ourselves up to write code that will allow us to enforce good practices later. So let's get started. First up, we'll worry about how to enforce closing your connections. We do this by adding a method that runs a query, returns the results, and makes sure the connection is closed when we're done:
private DataTable Query(string sql)
{
var result = new DataTable();
using (var connection = GetConnection())
using (var command = new SqlCommand(sql, connection)
{
connection.Open();
result.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
}
return result;
}
You could add additional similar methods for returning scalar data or that don't return data at all (for updates/inserts/deletes). Don't get too attached to this code just yet, because it's still broken. I'll explain why in a minute. For now, let me point out that this method is still private. We're not done yet, and so we don't want this code to be available to other parts of your program.
The other thing I want to highlight is the using keyword. This keyword is a powerful way to declare a variable in .Net and C#. The using keyword creates a scope block underneath the variable declaration. At the end of the scope block, your variable is disposed. Note that there are three important parts to this. The first is that this really only applies to unmanaged resources like database connections; memory is still collected in the usual way. The second is that the variable is disposed even if an exception is thrown. This makes the keyword suitable for use with time-sensitive or tightly-constrained resources like database connections, without the need for a separate try/catch block nearby. The final piece is that the keywords make use of the IDisposable pattern in .Net. You don't need to know all about IDisposable right now: just know that database connections implement (think: inherit) the IDisposable interface, and so will work with a using block.
You don't have to use the using keyword in your code. But if you don't, the correct way to handle a connection looks like this:
SqlConnection connection;
try
{
connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand("sql query here", connetion);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
//do something with the data reader here
}
finally
{
connection.Close();
}
Even that is still the simple version. You also need an additional check in the finally block to make sure your connection variable is valid. The using keyword is a much more concise way to express this, and it makes sure you get the pattern right each time. What I want to show here is that if you just call connection.Close(), with no protection to make sure the program actually reaches that line, you've failed. If an exception is thrown by your sql code without the protection of try/finally or using, you'll never reach the .Close() call and thus potentially leave the connection open. Do this often enough, and you can lock yourself out of your database!
Now let's build something public: something you can actually use from other code. As I hinted at earlier, each sql query you write for an app will go in it's own method. Here's an example method for a simple query to get all the records from your Employee table:
public DataTable GetEmployeeData()
{
return Query("SELECT * FROM Employees");
}
Wow, that was easy... a single line function call, and we've got data coming back from the database. We're really getting somewhere. Unfortunately, we're still missing one piece of the puzzle: you see, it's pretty rare to want to return an entire table. Typically, you'll want to filter that table in some way, and maybe join it with another table. Let's alter this query to return all the data for a fictional employee named "Fred":
public DataTable GetFredsEmployeeData()
{
return Query("SELECT * FROM Employees WHERE Firstname='Fred'");
}
Still pretty easy, but that misses the spirit of what we're trying to accomplish. You don't want to build another method for every possible employee name. You want something more like this:
public DataTable GetEmployeeData(string FirstName)
{
return Query("SELECT * FROM Employees WHERE FirstName='" + FirstName + "'");
}
Uh oh. Now we have a problem. There's that pesky string concatenation, just waiting for someone to come along and enter the text ';Drop table employees;-- (or worse) into the FirstName field in your app. The correct way to handle this is using query parameters, but this is where it gets tricky, because several paragraphs back we built a query method that only accepts a finished sql string.
A lot of people want to write a method just like that Query method. I think just about every database programmer is tempted by that pattern at a certain point in their career, and unfortunately it's just plain wrong until you add a way to accept sql parameter data. Fortunately, there are number of different way to address this. The most common is to add a parameter to the method that allows us to pass in the sql data to use. To accomplish this, we could pass an array of SqlParameter objects, a collection of key/value pairs, or even just an array of objects. Any of those would be sufficient, but I think we can do better.
I've spent a lot of time working through the different options, and I've narrowed down what I think is the simplest, most effective, and (more importantly) most accurate and maintainable option for C#. Unfortunately, it does require that you understand the syntax for one more advanced language feature in C#: anonymous methods/lambdas (really: delegates, but I'll show a lambda soon enough). What this feature allows you to do is define a function within another function, hold on to it with a variable, pass it to other functions, and call it at your leisure. It's a useful capability that I'll try to demonstrate. Here's how we'll modify the original Query() function to take advantage of this ability:
private DataTable Query(string sql, Action<SqlParameterCollection> addParameters)
{
var result = new DataTable();
using (var connection = GetConnection())
using (var command = new SqlCommand(sql, connection)
{
//addParameters is a function we can call that was as an argument
addParameters(command.Parameters);
connection.Open();
result.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
}
return result;
}
Note the new Action<SqlParameterCollection> parameter. Don't mind the < > part. If you're not familiar with generics, you can just pretend it's part of the class name for now. What's important is that this special Action type allows us to pass one function (in this case, one that takes an SqlParameterCollection as an argument) to another function. Here's how this looks when used from our GetEmployeeData() function:
public DataTable GetEmployeeData(string firstName)
{
return Query("SELECT * FROM Employees WHERE FirstName= @Firstname",
p =>
{
p.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstName;
});
}
The key to all this is that the Query() function now has a way to connect the firstName argument passed to it's parent GetEmployeeData() function to the @FirstName expression in the sql string. This is done using features built into ADO.Net and your sql database engine. Most importantly, it happens in a way that prevents any possibility for sql injection attacks. Again, this strange syntax isn't the only valid way to send parameter data. You might be a lot more comfortable just sending a collection that you iterate. But I do think this code does a good job of keeping parameter code near the query code while also avoiding extra working building and then later iterating (rebuilding) parameter data.
I'll finish (finally!) with two short items. The first is the syntax for calling your new query method with no parameters:
public DataTable GetAllEmployees()
{
return Query("SELECT * FROM Employees", p => {});
}
While we could also provide this as an overload of the original Query() function, in my own code I prefer not to do that, as I want to communicate to other developers that they should be looking to parameterize their code, and not sneak around with string concatenation.
Secondly, the code outlined in this answer is still unfinished. There are some important weaknesses yet to address. An example is that using a datatable rather than a datareader forces you to load the entire result set from every query into memory all at once. There are things we can do to avoid that. We also haven't discussed inserts, updates, deletes, or alters, and we haven't addressed how to combine complex parameter situations, where we might want to, say, add code to also filter on the last name, but only if data for a last name filter was actually available from the user. While this can be easily adapted for all of those scenarios, I think at this point I have completed the original objective, and so I'll leave that to the reader.
In conclusion, remember the two things you must do: close your connections via finally block, and parameterize your queries. Hopefully this post will set you on course to do that well.
