ADO.Net Interface Example

I recently wrote about using interfaces when working with ADO.Net. I received a comment requesting an example. So, today I am going to do something a little different and provide a little simple application to demonstrate how one could declare interfaces, and instantiate objects.

There are a few ways you could do the instantiation. Injection is a popular method, allowing you to swap out the implementing objects through configuration. In my example, I used a very simple factory class. When creating the necessary objects for my commands it checks a configuration setting to instantiate the SQL Server client, System.Data.SqlClient, or OleDb client for everything else. You can make it more specific by implementing an Oracle client, etc. using ADO.Net drivers specific to your desired database engine.

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ADOExample
{
    public static class ADOFactory
    {
        private const string DB_TYPE_SQL_SERVER = "SqlServer";
        private const string DB_TYPE_ORACLE = "Oracle";
        private const string DB_TYPE_DB2 = "Db2";

        private static string DB_TYPE { set; get; }
        private static string CONNECTION_STRING { set; get; }
        
        static ADOFactory()
        {
            DB_TYPE = ConfigurationManager.AppSettings["DbTypeSetting"].ToString();
            CONNECTION_STRING = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;
        }

        public static IDbConnection GetConnection()
        {
            IDbConnection conn;
            switch(DB_TYPE)
            {
                case DB_TYPE_SQL_SERVER:
                    conn = new System.Data.SqlClient.SqlConnection(CONNECTION_STRING);
                    break;
                default:
                    conn = new OleDbConnection(CONNECTION_STRING);
                    break;
            }
            return conn;
        }

        public static IDbCommand GetCommand(string sql, IDbConnection conn)
        {
            IDbCommand cmd;
            switch (DB_TYPE)
            {
                case DB_TYPE_SQL_SERVER:
                    cmd = new System.Data.SqlClient.SqlCommand(sql, (SqlConnection)conn);
                    break;
                default:
                    cmd = new OleDbCommand(sql, (OleDbConnection)conn);
                    break;
            }
            return cmd;
        }

        public static IDbDataAdapter GetDataAdapter(IDbCommand cmd)
        {
            IDbDataAdapter da;
            switch (DB_TYPE)
            {
                case DB_TYPE_SQL_SERVER:
                    da = new System.Data.SqlClient.SqlDataAdapter((SqlCommand)cmd);
                    break;
                default:
                    da = new OleDbDataAdapter((OleDbCommand)cmd);
                    break;
            }
            return da;
        }
    }
}

My configuration has an AppSetting which determines the database type, and a database connection string.

Connection Strings Section:
add name="DbConnection" connectionstring="data source=.MSSQLSERVER2014;initial catalog=Master;integrated security=True;persist security info=False;MultipleActiveResultSets=True;" providername="System.Data.SqlClient"

AppSettings Section:
add key="DbTypeSetting" value="SqlServer"

To execute SQL Commands I have implemented a class I call SqlManager. This class has two methods GetDataSet, and GetDataTable. Each method returns the System.Data.DataSet or System.Data.DataTable object respectively. Since the DataSet and DataTable classes are not engine specific, the SQL Manager has become ubiquitous. It calls the AdoFactory to instantiate the necessary engine specific objects to perform work. It doesn’t know what driver the factory used; it simply knows the interface returned, and the methods implemented. As a result, it can return a DataSet. To return a DataTable, I get a DataSet and return the first table from the DataSet.

using System.Data;

namespace ADOExample
{
    public static class SqlManager
    {
        public static DataSet GetDataSet(string sql)
        {
            var ds = new DataSet();
            using (IDbConnection conn = ADOFactory.GetConnection())
            using (IDbCommand cmd = ADOFactory.GetCommand(sql, conn))
            {
                IDataAdapter da = ADOFactory.GetDataAdapter(cmd);
                conn.Open();
                da.Fill(ds);
                conn.Close();
            }
            return ds;
        }

        public static DataTable GetDataTable(string sql)
        {
            var ds = GetDataSet(sql);
            return ds.Tables[0];
        }
    }
}

;

I demonstrate the use of this infrastructure in the Program class. Main calls a method that prints to the screen the name of every table in the Master.INFORMATION_SCHEMA.TABLES view.

using System;
using System.Data;

namespace ADOExample
{
    public class Program
    {
        static void Main(string[] args)
        {
            ShowTables();
            Console.ReadKey();
        }

        private static void ShowTables()
        {
            var sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
            var dt = SqlManager.GetDataTable(sql);
            DataTableReader dr = new DataTableReader(dt);
            while (dr.Read())
            {
                Console.WriteLine(dr["TABLE_NAME"].ToString());
            }
        }
    }
}

As you can see, there is no error handling, so this simple app is not ready for any real application. However, it does demonstrate how you can use interfaces to write your code, and when the SQL is cross database compatible, change the connection string and the type of database in the configuration file, and it will work without any other modifications.

I hope this simplistic example communicates better the concepts we have been reviewing, and the power of using interfaces along with objects implementing the interfaces.

Cheers,

Ben