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?

4 Comments:

Anonymous Norman said...

connection = SqlConnection, transaction = SqlTransaction, cursors = SqlDataReader, pity that you have to always use the methods on the connection to do anything.

Using GetInt/GetString is fast, but unreadable. I far prefer to use csr["card_number"], csr["surname"], csr["name"]. It's slower, but if you change your sql, then it doesn't require any changes, It also makes it easier for someone else to understand and support the code.

9:31 PM  
Anonymous Anonymous said...

Good design!
[url=http://vpxgosix.com/nmlw/uuqj.html]My homepage[/url] | [url=http://bzgelmgs.com/jvcl/gyqr.html]Cool site[/url]

2:57 AM  
Anonymous Anonymous said...

Well done!
My homepage | Please visit

2:57 AM  
Anonymous Anonymous said...

Great work!
http://vpxgosix.com/nmlw/uuqj.html | http://njbzewhi.com/regg/ybgg.html

2:58 AM  

Post a Comment

<< Home