Simple class for data access using ADO.NET

Many data access layers we see today are meant for medium to big projects, and building the required models, and loading the required dll’s in a small project sometimes feels like trying to kill a fly with a mortar. So, what do we do most times? Write some plain ADO.NET access code to retrieve the data and that’s it, we don’t want to mess our code with NHibernate, Entity Framework or DataSets only for retrieving data from a single (or two) tables. If we change providers, all we would do is just change the class names.

This approach is simple, but with a little tweaks we can make it reusable, ergo we don’t have to rewrite the code is it would be a fairly plain database access. To accomplish this, we will make a small mix of singletons and dictionaries, to be able to talk to different databases at the same time. I’ve found this feature useful, since I use SQLite for caching my request and MySQL to handle big stuff that I require saving on DB.

So, our singleton code would be like this one:


private static Dictionary _instances;

public static Database Get(string flavour)
{
    if ( _instances == null )
        _instances = new Dictionary();

    if (_instances.ContainsKey(flavour))
        return _instances[flavour];

    var dbProvider = DbProviderFactories.GetFactory(flavour);
    var connectionStrings = ConfigurationManager.ConnectionStrings;
    var cs = connectionStrings[flavour].ConnectionString
                              .Replace("|HomeDir|", Environment.CurrentDirectory)
                              .Replace("'", "\"");
    
    _instances.Add(flavour, new Database(dbProvider, cs));
    return _instances[flavour];
}

private DbConnection _connection = null;
private DbProviderFactory _factory;

private Database(DbProviderFactory factory, string connectionString)
{
    _factory = factory;
    _connection = _factory.CreateConnection();
    _connection.ConnectionString = connectionString;
}

As you can see, we use the DbProviderFactories class to load the required factory classes for our project. A sample app.settings file would be like this one:




    
        
        
    

    
        
            
            
            
            
            
        
    



The basic idea is to be able to load the factory required for our flavor from an XML file and forget about instantiation of classes. This way, we can truly reuse our code. Now we need to mimic the 3 important methods of any ADO.NET command:

  • ExecuteScalar
  • ExecuteNonQuery
  • ExecuteReader

But, we also need to support parameters, and to do so, we will just pass parameters as a list of KeyValuePair objects. With that in mind, let’s create an utility method to create the command for a given query:


private DbCommand CreateCommand(string query, 
                                params KeyValuePair[] args)
{
    var cmd = _factory.CreateCommand();
    cmd.Connection = _connection;
    cmd.CommandText = query;

    foreach (var argument in args)
    {
        var param = _factory.CreateParameter();
        param.ParameterName = argument.Key;
        param.Value = argument.Value;

        cmd.Parameters.Add(param);
    }

    return cmd;
}

With this utility method, creating one of the proxies for ADO.NET is quite simple. The ExecuteScalar proxy would be like this one:


public object ExecuteScalar(string query, params KeyValuePair[] args)
{
    var cmd = CreateCommand(query, args);

    try
    {
        _connection.Open();
        object scalar = cmd.ExecuteScalar();

        _connection.Close();
        return scalar;
    }
    catch (Exception)
    { return null; }
}

This one is quite easy uh? Now, we wont return a DbDataReader in the ExecuteReader, because it would be simple to read the values and return a List of dictionaries, where each Dictionary object would represent a given row.
Since I don’t return a DbDataReader, I thought that ExecuteReader wouldn’t be OK, so I changed the name to ExecuteList. The basic layout of the method is like the past one, but once we have the DbDataReader, we would fill the rows, like this:


while (reader.Read())
{
    var row = new Dictionary();
    int fcount = reader.FieldCount;

    for (int i = 0; i < fcount; i++)
    {
        string fName = reader.GetName(i);
        object val = reader.GetValue(i);

        if (reader.IsDBNull(i))
            val = null;

        row.Add(fName, val);
    }

    result.Add(row);
}

And we return the result, which is of type List<Dictionary<string, object>>. Cute uh?

With these three methods, we have a jumpstart for a nice (a very simple) data access class, but what would be of .NET without types, so we would expect to have a typed retrieval method, something like this:

public List ExecuteList(string query, params KeyValuePair[] args)

Where T is (hopefully) a POCO class, and we just want to fill it. This is a lame attempt of doing some ORM, but as we said earlier, the idea is to create a small reusable class for data access in small apps, most likely for small jobs or personal projects. Back to the problem, we would need a method to receive a dictionary and return an object of type T, and using the wondrous Reflection, we can do this:

private T ParseClass(Dictionary hash)
{
    Type t = typeof (T);
    var properties = t.GetProperties();
    var instance = Activator.CreateInstance(t);

    foreach (var property in properties)
    {
       if ( property.CanWrite && hash.ContainsKey(property.Name) )
        property.SetValue(instance, hash[property.Name], null);
    }

    return (T)instance;
}

I’m making a few assumptions here.

  1. First, I use classes to access data, not DataTables, since I find Types much more convenient at development time. I think we can agree that this:

        var name = st.Name;
    

    Is much friendlier than this:

        var name = (string)dt.Rows[“Name”];
    

  2. Second, this magic method happens only for simple classes, which work as POCOs so we don’t have to worry about read only properties, or strange fields or highly complex structures, just some small properties, keep in mind, that for medium projects, using a small class like this could (most times IS) fatal to your application/design.

With that in mind, the last method for typed data retrieval would look like this:

public List ExecuteList(string query, params KeyValuePair[] args)
{
    var dictionary = ExecuteList(query, args);
    return dictionary.Select(ParseClass).ToList();
}

As I’ve said many times in this post, this class would be only for small projects, but it does help to have a something like that, because we might find a few personal projects with small lines of code using the same data access code and retyping it again and again is not going to make it better. Remember that any snippet of code that you type over and over in projects, no matter how small it is, can be abstracted into a bit more complex (and useful) library.

I hope that this code can help someone out there, feel free to comment!

No comments:

Post a Comment

Commenting is allowed!