Database Factory Implementation In C#

Many developers have confusion about how to write database code in an application. I will discuss several approaches here and try to provide the explanation for each approach.

Many developers have confusion about how to write database code in an application. I will discuss several approaches here and try to provide the explanation for each approach.

Approach 1

This is the easiest and very straightforward approach that will work absolutely fine.  I am assuming you already have an understanding of design principles. I am just exploring the different approaches to achieve the database connectivity.

using System.Collections.Generic;
using System.Data.SqlClient;
namespace DatabaseFactory {
    class Program {
        static void Main(string[] args) {
            var employees = SomeDabOperation();
        }
        private static List < Employee > SomeDabOperation() {
            using(SqlConnection myConnection = new SqlConnection("connectionString")) {
                using(SqlCommand myCommand = new SqlCommand("Select * From Employee", myConnection)) {
                    using(SqlDataReader reader = myCommand.ExecuteReader()) {
                        //Some Code.......
                    }
                }
            }
            return new List < Employee > ();
        }
    }
    public class Employee {}

Approach 2

How about the below code? Just a single line! Isn't it more clean than the earlier approach?

Where did the rest of the code get moved? Of course, we can't remove the code and we have just moved it to a separate class.

namespace DatabaseFactory {
    class Program {
        static void Main(string[] args) {
            var employees = DatabaseManager.SomeDabOperation();
        }
    }
}

We can see the benefit of moving database logic to different layer - we are not bothered if the database will get changed in the future, it's much more readable than before etc.

using System.Collections.Generic;
using System.Data.SqlClient;
namespace DatabaseFactory {
    internal class DatabaseManager {
        internal static object SomeDabOperation() {
            using(SqlConnection myConnection = new SqlConnection("connectionString")) {
                using(SqlCommand myCommand = new SqlCommand("Select * From Employee", myConnection)) {
                    using(SqlDataReader reader = myCommand.ExecuteReader()) {
                        //Some Code.......
                    }
                }
            }
            return new List < Employee > ();
        }
    }
    public class Employee {}
}

Approach 3

Before writing the cleaner method of Database Factory, let's explore the use of web.cofig/app.config in the implementation.

App.config 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <section name="DbFactoryConfiguration" type="GuruBook.Store.DbFactorySectionHandler, GuruBook.Store" /> </configSections>
    <connectionStrings>
        <clear/>
        <add name="sqlConnectionString" providerName="System.Data.SqlClient" connectionString="DataSource=W10FMV4P72\SQLEXPRESS;Initial Catalog=FitBook;Integrated Security=True;" /> </connectionStrings>
    <DbFactoryConfiguration Name="GuruBook.Store.DataStores.SqlDataBase" ConnectionStringName="sqlConnectionString" />
</configuration>

Create a class to read custom section

public class DbFactorySectionHandler: ConfigurationSection {
        public string Name {
            get {
                return (string) base["Name"];
            }
        }
        public string ConnectionStringName {
            get {
                return (string) base["CnnectionStringName"];
            }
        }
        public string ConnectionString {
            get {
                try {
                    return ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
                } catch (Exception excep) {
                    throw new Exception("Connection string " + ConnectionStringName + " was not found in web.config. " + excep.Message);
                }
            }
        }
    }
    -- -- -- -- -- -- - Create Database Abstract Class-- -- -- -- -- -- -- -- -- -- --
    public abstract class DataBase {
        public string connectionString;#
        region Abstract Functions
        public abstract IDbConnection CreateConnection();
        public abstract IDbCommand CreateCommand();
        public abstract IDbConnection CreateOpenConnection();
        public abstract IDbCommand CreateCommand(string commandText, IDbConnection connection);
        public abstract IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection);
        public abstract IDataParameter CreateParameter(string parameterName, object parameterValue);#
        endregion
    }
    -- -- -- - Create a Concrete Database class-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    public class SqlDataBase: DataBase {
        public override IDbCommand CreateCommand() {
            return new SqlCommand();
        }
        public override IDbCommand CreateCommand(string commandText, IDbConnection connection) {
            SqlCommand command = (SqlCommand) CreateCommand();
            command.CommandText = commandText;
            command.Connection = (SqlConnection) connection;
            command.CommandType = CommandType.Text;
            return command;
        }
        public override IDbConnection CreateConnection() {
            return new SqlConnection(connectionString);
        }
        public override IDbConnection CreateOpenConnection() {
            SqlConnection connection = (SqlConnection) CreateConnection();
            connection.Open();
            return connection;
        }
        public override IDataParameter CreateParameter(string parameterName, object parameterValue) {
            return new SqlParameter(parameterName, parameterValue);
        }
        public override IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection) {
            SqlCommand command = (SqlCommand) CreateCommand();
            command.CommandText = procName;
            command.Connection = (SqlConnection) connection;
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }
    }
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --Factory Implementation-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - public sealed class DataBaseFactory {
        public static DbFactorySectionHandler sectionHandler = (DbFactorySectionHandler) ConfigurationManager.GetSection("DbFactoryConfiguration");
        private DataBaseFactory() {}
        public static DataBase CreateDataBase() {
            if (sectionHandler ? .Name ? .Length == 0) throw new Exception("Database name not defined in DbFactoryConfiguration section of config file");
            try {
                Type database = Type.GetType(sectionHandler.Name);
                ConstructorInfo constructorInfo = database.GetConstructor(new Type[] {});
                DataBase databaseObj = (DataBase) constructorInfo.Invoke(null);
                databaseObj.connectionString = sectionHandler.ConnectionString;
                return databaseObj;
            } catch (Exception excep) {
                throw new Exception("Error instantiating database " + sectionHandler.Name + ". " + excep.Message);
            }
        }
    }
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - public class DbWorker {
        public static DataBase _database = null;
        static DbWorker() {
            _database = DataSoreFactory.DataBaseFactory.CreateDataBase();
        }
        public DataBase dataBase {
            get {
                return _database;
            }
        }
    }
Use: SomeClass: DbWorker {
    dataBase.CreateOpenConnection()
}