Many developers has confusion on how to write database code in application. I will discuss several approaches here and try to provide explanation on each approach.
Approach 1 : Easiest approach which is very straight forward which will work absolutely fine. The issues we will get to manage the project in terms of software principles. Like if you going to change Database provider, you would need to change using statements and other issue like mixing Database code with business logic etc. I will not go in detail here, I am assuming you already have an understanding of design principles. I am just exploring 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 more clean than earlier approach ?
Where the rest of the code got moved ? Ofcource, we can't remove the code and wew have just moved to separate class.
namespace DatabaseFactory
{
class Program
{
static void Main(string[] args)
{
var employees = DatabaseManager.SomeDabOperation();
}
}
}
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
We can see the benefit by moving database logic to different layer, we are not bothered if Database will get changed in future, much 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 more cleaner way 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>
Approach 1 : Easiest approach which is very straight forward which will work absolutely fine. The issues we will get to manage the project in terms of software principles. Like if you going to change Database provider, you would need to change using statements and other issue like mixing Database code with business logic etc. I will not go in detail here, I am assuming you already have an understanding of design principles. I am just exploring 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 more clean than earlier approach ?
Where the rest of the code got moved ? Ofcource, we can't remove the code and wew have just moved to separate class.
namespace DatabaseFactory
{
class Program
{
static void Main(string[] args)
{
var employees = DatabaseManager.SomeDabOperation();
}
}
}
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
We can see the benefit by moving database logic to different layer, we are not bothered if Database will get changed in future, much 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 more cleaner way 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 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()
}
Comments
Post a Comment