Wednesday, January 07, 2009

Easy SQL

Accessing databases on Windows has been a constantly moving target over the years. First there was ODBC, a pretty simple C based API. When COM came along, MS decided that they needed to have an object based data access library so out came DAO, followed pretty swiftly by RDO, OLEDB and ADO. When .NET came out we got ADO.NET and then recently we've had LINQ to SQL and now Entity Framework. LINQ to SQL lasted such a short time that I didn't even have time to look at it properly before it died. And there's probably others I've forgotten about.

Some may not think this is a problem. After all, the older technologies are still available so there's nothing stopping you continuing to use them, but the chances are you'll have to maintain a project at some point that uses another data access technology so you'll need to have a passing knowledge of all of them.

So I have an idea, Microsoft. How about adding a common API to all your data access technologies? Here's my suggested interface.

public interface IEasySQL
{
  void Connect(string connectionString);

  void ExecuteSQL(string sql);

  object[][] SelectSQL(string sql);
}

I think it's fairly obvious how this interface works, so I won't bore you with the details. Now some of you are probably thinking it's missing a few features. Things like transactions, parameterised queries, that kind of thing. Fair point, but for me at least, this would be adequate 90% of the time. If I need the more complicated stuff I can always go to the underlying API, but for most of the time I can just throw a query at the database, because that's all I need to do. Yeh, it's not scalable, but generally my queries only return a few results, maybe a thousand, and this will work fine. So how about it, MS? And if anybody is interested, I can knock together an implementation using ADO.NET.

No comments: