Friday, July 22, 2011

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

Way every 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)

        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);
              values = null;

      return results;