After putting it off for a long time I stated working on a new Data Layer. I wanted to know what DataLayer will be better against a SQL 2005. I decided to create an abstract class call DatabaseManager:

public abstract class MasterDatabaseManager : IDatabaseManager

    {

        public abstract void Close();

        public abstract int GetNonQuery(string sQuery);

        public abstract System.Data.SqlClient.SqlDataReader GetQuery(string sQuery);

        public abstract DataTable GetQueryPaging(string sQuery);

        public abstract DataView GetQueryPagingDataView(string sQuery);

        public abstract void Dispose();

    }

Then I added 2 classes with this base class. MicrosoftDatabaseManager and OldDatabaseManager.

Microsoft Database Manager uses Microsoft.Practices.EnterpriseLibrary.Data.Sql library, mine just uses the old System.Data and System.Data.SqlClient;

Comparing a function to clarify:

Microsoft:

public override System.Data.SqlClient.SqlDataReader GetQuery(string sQuery)

        {

            SqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);

            DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);

            return ((SqlDataReader)dbSvc.ExecuteReader(dbCommand));

        }

Mine:

public override SqlDataReader GetQuery(string sQuery)

            {

                  SqlConnection myConnection = new SqlConnection(m_sConnectionString);

                  SqlCommand myCommand = new SqlCommand(sQuery, myConnection);

                  myCommand.CommandTimeout = 120;

                  myConnection.Open();

                  SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                  m_myConnection = myConnection; // The class will close the connection

                  return result;

            }

So I created a class to test it with:

public class DatabaseManager : IDatabaseManager

    {

        enum DataLayer

        {

            OldSql = 1,

            MsSql = 2

        }

        MasterDatabaseManager m_oMan = null;

        DataLayer m_oLayer = DataLayer.MsSql;

        Stopwatch m_oWatch = new Stopwatch();

        public DatabaseManager()

        {

            BuildDataLayer();

            if (m_oLayer == DataLayer.MsSql)

                m_oMan = new MSDatabaseManager();

            else

                m_oMan = new OldDatabaseManager();

        }

        public DatabaseManager(string sConnectionString)

        {

            BuildDataLayer();

            if (m_oLayer == DataLayer.MsSql)

                m_oMan = new MSDatabaseManager(sConnectionString);

            else

                m_oMan = new OldDatabaseManager(sConnectionString);

        }

        private void BuildDataLayer()

        {

            if (ConfigurationSettings.AppSettings["DataLayer"] != null)

            {

                int iLayer = Int32.Parse(ConfigurationSettings.AppSettings["DataLayer"].ToString());

                m_oLayer = (DataLayer)iLayer;

            }

        }

        #region IDatabaseManager Members

        public void Close()

        {

            m_oMan.Close();

        }

        public System.Data.SqlClient.SqlDataReader GetQuery(string sQuery)

        {

            m_oWatch.Start();

            System.Data.SqlClient.SqlDataReader oTemp = m_oMan.GetQuery(sQuery);

            m_oWatch.Stop();

            Debugger.Log(1, "DataLayer", "DatabaseManager: " + (m_oWatch.ElapsedMilliseconds) + sQuery + "\r\n");

            m_oWatch.Reset();

            return (oTemp);

        }

This way just changing the member m_oLayer I could run some numbers. I added them in an excel file after every run. And after more than 10 runs I gave up as the numbers were very different running always the same queries.

The numbers are very similar between Data Layers, some times MS was the winner by a few milliseconds, sometime I was the winner for a few milliseconds. I believe that I would never know witch one is faster as SQL 2005 is taking care of making the bentchmarks a little difficult.

What I got set up know is a abstract class where I can add as many layers as I can, so if you can use my Iterface, send me your DataLayers and I’ll run them in the test. I’ll be also writing a new one to improve UPDATES in batches, but this is another story ….

Cheers

Al