Friday, May 13, 2005

Using databases in C#

Having used the excellent pqxx library to access PostgreSQL databases via C++, I was somewhat less than overwhelmed when confronted with the .NET framework's data access libraries. The latter are really just a trivial evolution of ADO, of which I have few fond memories. But, I remembered, any system lacking sufficiently elegant APIs needs must beget patterns. So here is a handy one to tuck into your copy-paste-coding repository. In C#:


// using System.Data.SqlClient;
SqlConnection dbCon = new SqlConnection("server=localhost;database=example;");
dbCon.Open();

lock (dbCon) using (SqlCommand cmd = dbCon.CreateCommand())
{
cmd.CommandText = "select card_number, surname, name from employees;";
using(SqlDataReader csr = cmd.ExecuteReader()) while (csr.Read())
{
employeeMap.Add(
csr.GetInt32(0),
new Employee(csr.GetInt32(0), csr.GetString(1), csr.GetString(2)));
}
}


This is just about the most readable way to process a query on a long-running connection, while maintaining the necessary API contracts. First, observe that the connection object is locked for the duration of the query, thus ensuring that no more than one SqlCommand is created at a time. Furthermore, both the command and the reader are declared in using blocks, ensuring the correct disposal of both objects. And the whole thing manages to only indent twice. Not too bad.

Of course, when you start using transactions, things get more complicated, and I still haven't found a wholly satisfactory way of laying out transactional code so that it is easy to read, purposeful and not cluttered with adjunct requirements.

I wish the designers of the System.Data APIs had read Python's DB-API spec, flawed though it is. Ideally, we want a connection entity, from which we can spawn many transaction entities, which in turn allow us to maintain multiple cursors. Is that too much to ask?