Friday, July 22, 2011

C# IDbConnection && IDbCommand && IDataReader: where is my abstraction?

Way every ado.net example I spot on internet is still coded against concrete implementation of Connection && Command && DataReader instead of Interfaces?

It is so simple to use provider factory here follows a code example:
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;

namespace Utils
{
  public static class DBUtils
  {
    public static IList ExecuteQuery(string query, string connectionString, string providerName)
    {
      if (string.IsNullOrEmpty(query))
        throw new ArgumentNullException("Query text is null or empty!");

      if (string.IsNullOrEmpty(connectionString))
        throw new ArgumentNullException("ConnectionString is null or empty!");

      if (string.IsNullOrEmpty(providerName))
        throw new ArgumentNullException("ProviderName is null or empty!");

      IList results = null;

      DbProviderFactory providerFactory = DbProviderFactories.GetFactory(providerName);
      if (providerFactory == null)
        throw new Exception("Failed to get DbProviderFactory instnce!");

      using (IDbConnection con = providerFactory.CreateConnection())
      {
        con.ConnectionString = connectionString;

        if (con.State != ConnectionState.Open)
          con.Open();

        using (IDbCommand cmd = providerFactory.CreateCommand())
        {
          cmd.CommandText = query;
          cmd.Connection = con;

          using (IDataReader reader = cmd.ExecuteReader())
          {
            results = new List();
            while (reader.Read())
            {
              object[] values = new object[reader.FieldCount];
              int count = reader.GetValues(values);
              results.Add(values);
              values = null;
            }
          }
        }
      }

      return results;
    }
  }
}