The aim of this article was to help users to avoid writing the same code for Data access again and again for different projects.
It is much helpful to users who actually write applications but are not sure about the type of database that would be existing on the Live-Server or imagine a situation when the client decides to migrate his existing database from SQL to Oracle or maybe Microsoft Access.
These are some of the problems that I have faced in my life as a programmer and through this article, I would hope to help people who face a similar problem.
Here is the Connection Class:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
namespace HPR.DAL
{
public class ConnectionClass
{
/// <summary>
/// Cannot Instantiate this class
/// </summary>
private ConnectionClass()
{
}
/// <summary>
/// Returns a closed DbConnection object. User has to open/close it whenever required.
/// </summary>
/// <returns></returns>
public DbConnection GetConnection()
{
if (InstanceCounter < 10)
{
//Dont let the user to create any new connections.
try
{
string ConnectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["HPRConnectionString"]);
DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory();
DbConnection conn = Dbfactory.CreateConnection();
conn.ConnectionString = ConnectionString;
return conn;
}
catch (DbException)
{
throw new Exception("An exception has occured while creating the connection. Please check Connection String settings in the web.config file.");
}
}
else
{
throw new Exception("The number of active connections has reached the maximum limit. (i.e.) " + InstanceCounter.ToString() + ".");
}
}
/// <summary>
/// Provides with a DbProviderFactory object with the provider name from the config file.
/// </summary>
/// <returns>DbProviderFactory object</returns>
internal static DbProviderFactory GetDbFactory()
{
try
{
string ProviderName = ConfigurationManager.AppSettings["ProviderName"];
DbProviderFactory Dbfactory = DbProviderFactories.GetFactory(ProviderName);
return Dbfactory;
}
catch(DbException)
{
throw new Exception("An exception has occured while creating the database provider factory. Please check the ProviderName specified in the web.config file.");
}
}
/// <summary>
/// Provides with a DbProviderFactory object with the supplied Provider name.
/// </summary>
/// <param name="ProviderName">Data Provider Name (e.g.) Oledb, Odbc, SqlClient</param>
/// <returns>DbProviderFactory object</returns>
internal static DbProviderFactory GetDbFactory(string ProviderName)
{
DataTable dtProviders = DbProviderFactories.GetFactoryClasses();
if (dtProviders.Rows.Count == 0)
{
throw new Exception("No Data Providers are installed in the .Net FrameWork that implement the abstract DbProviderFactory Classes. ");
}
bool errorFlag = false;
foreach (DataRow dr in dtProviders.Rows)
{
if (dr[2] != null)
{
string ExistingProviderName = dr[2].ToString();
if (ProviderName.ToLower() == ExistingProviderName.Trim().ToLower())
{
errorFlag = false;
break;
}
else
{
errorFlag = true;
}
}
}
if (errorFlag)
{
throw new Exception("The ProviderName string supplied is not a valid Provider Name<BR>or it does not implement the abstract DbProviderFactory Classes. <BR>The string ProviderName is case-sensitive. Also please check it for proper spelling. ");
}
DbProviderFactory Dbfactory = DbProviderFactories.GetFactory(ProviderName);
return Dbfactory;
}
}
}
It is much helpful to users who actually write applications but are not sure about the type of database that would be existing on the Live-Server or imagine a situation when the client decides to migrate his existing database from SQL to Oracle or maybe Microsoft Access.
These are some of the problems that I have faced in my life as a programmer and through this article, I would hope to help people who face a similar problem.
Here is the Connection Class:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
namespace HPR.DAL
{
public class ConnectionClass
{
/// <summary>
/// Cannot Instantiate this class
/// </summary>
private ConnectionClass()
{
}
/// <summary>
/// Returns a closed DbConnection object. User has to open/close it whenever required.
/// </summary>
/// <returns></returns>
public DbConnection GetConnection()
{
if (InstanceCounter < 10)
{
//Dont let the user to create any new connections.
try
{
string ConnectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["HPRConnectionString"]);
DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory();
DbConnection conn = Dbfactory.CreateConnection();
conn.ConnectionString = ConnectionString;
return conn;
}
catch (DbException)
{
throw new Exception("An exception has occured while creating the connection. Please check Connection String settings in the web.config file.");
}
}
else
{
throw new Exception("The number of active connections has reached the maximum limit. (i.e.) " + InstanceCounter.ToString() + ".");
}
}
/// <summary>
/// Provides with a DbProviderFactory object with the provider name from the config file.
/// </summary>
/// <returns>DbProviderFactory object</returns>
internal static DbProviderFactory GetDbFactory()
{
try
{
string ProviderName = ConfigurationManager.AppSettings["ProviderName"];
DbProviderFactory Dbfactory = DbProviderFactories.GetFactory(ProviderName);
return Dbfactory;
}
catch(DbException)
{
throw new Exception("An exception has occured while creating the database provider factory. Please check the ProviderName specified in the web.config file.");
}
}
/// <summary>
/// Provides with a DbProviderFactory object with the supplied Provider name.
/// </summary>
/// <param name="ProviderName">Data Provider Name (e.g.) Oledb, Odbc, SqlClient</param>
/// <returns>DbProviderFactory object</returns>
internal static DbProviderFactory GetDbFactory(string ProviderName)
{
DataTable dtProviders = DbProviderFactories.GetFactoryClasses();
if (dtProviders.Rows.Count == 0)
{
throw new Exception("No Data Providers are installed in the .Net FrameWork that implement the abstract DbProviderFactory Classes. ");
}
bool errorFlag = false;
foreach (DataRow dr in dtProviders.Rows)
{
if (dr[2] != null)
{
string ExistingProviderName = dr[2].ToString();
if (ProviderName.ToLower() == ExistingProviderName.Trim().ToLower())
{
errorFlag = false;
break;
}
else
{
errorFlag = true;
}
}
}
if (errorFlag)
{
throw new Exception("The ProviderName string supplied is not a valid Provider Name<BR>or it does not implement the abstract DbProviderFactory Classes. <BR>The string ProviderName is case-sensitive. Also please check it for proper spelling. ");
}
DbProviderFactory Dbfactory = DbProviderFactories.GetFactory(ProviderName);
return Dbfactory;
}
}
}
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Here is the DBClass:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common ;
using System.Configuration;
namespace HPR.DAL
{
/// <summary>
/// This Class contains all the Utility methods to interact with any database.
/// </summary>
public class DBClass
{
/// <summary>
/// Cannot Instantiate this class.
/// </summary>
private DBClass()
{
}
#region Utility Methods
/// <summary>
/// Returns a DataSet filled with data from the execution of the given Command.
/// </summary>
/// <param name="command">Command Object filled with necessary parameters.</param>
/// <param name="ErrorMessage">Output parameter for getting the error message if any.</param>
/// <returns>Returns a DataSet filled with data from the execution of the given Command.</returns>
public static DataSet GetDataSet(ref DbCommand command , out string ErrorMessage)
{
ErrorMessage = String.Empty;
DataSet ds = new DataSet();
DbDataAdapter dbDap = null ;
if (command == null)
{
ErrorMessage = "Please initilise the command object.";
return null;
}
try
{
DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory() ;
dbDap = Dbfactory.CreateDataAdapter();
dbDap.SelectCommand = command;
dbDap.Fill(ds);
}
catch(DbException exp)
{
ErrorMessage = "An exception has occured while executing the database transactions. <BR>";
ErrorMessage = ErrorMessage + exp.Message;
}
finally
{
if (command != null)
{
command.Dispose();
}
if (dbDap != null)
{
dbDap.Dispose();
}
}
return ds;
}
/// <summary>
/// Executes the command object returning an int value.
/// </summary>
/// <param name="command">Command object filled with the necessary parameters.</param>
/// <param name="ErrorMessage">Error Message if any.</param>
/// <returns>Int32 value indicating the error.</returns>
public static int ExecuteNonQuery(ref DbCommand command, out string ErrorMessage)
{
ErrorMessage = String.Empty;
DbTransaction tran = null;
DbConnection conn = null;
int result = 0;
if (command == null)
{
ErrorMessage = "Please initilise the command object.";
return result = -1;
}
try
{
conn = ConnectionClass.GetConnection();
command.Connection = conn ;
conn.Open();
tran = conn.BeginTransaction();
result = command.ExecuteNonQuery();
tran.Commit();
}
catch(DbException exp)
{
ErrorMessage = "An exception has occured while executing the database transactions. <BR>";
ErrorMessage = ErrorMessage + exp.Message;
if (tran != null)
{
tran.Rollback();
}
}
finally
{
if (command != null)
{
command.Dispose();
}
if (tran != null)
{
tran.Dispose();
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn != null)
{
conn.Dispose();
}
}
return result;
}
/// <summary>
/// Executes the command object returning the first column of the first row.
/// </summary>
/// <param name="command">Command object filled with the necessary parameters.</param>
/// <param name="ErrorMessage">Error Message if any.</param>
/// <returns>An object containing the first column of the first row.</returns>
public static object ExecuteScalar(ref DbCommand command, out string ErrorMessage)
{
ErrorMessage = String.Empty;
DbTransaction tran = null;
DbConnection conn = null;
object result = null ;
if (command == null)
{
ErrorMessage = "Please initilise the command object.";
return null;
}
try
{
conn = ConnectionClass.GetConnection();
command.Connection = conn;
conn.Open();
tran = conn.BeginTransaction();
result = command.ExecuteScalar();
if (tran != null)
{
tran.Commit();
}
}
catch (DbException exp)
{
ErrorMessage = "An exception has occured while executing the database transactions. <BR>";
ErrorMessage = ErrorMessage + exp.Message;
if (tran != null)
{
tran.Rollback();
}
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn != null)
{
conn.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (tran != null)
{
tran.Dispose();
}
}
return result;
}
/// <summary>
/// Returns a DbCommand object.
/// </summary>
/// <returns>Returns a DbCommand object.</returns>
public static DbCommand GetCommand()
{
DbCommand comm = ConnectionClass.GetDbFactory().CreateCommand();
return comm;
}
/// <summary>
/// Returns an array of DbParameter objects.
/// </summary>
/// <param name="count">Denotes the size of the required parameter array. Should be greater than 0.</param>
/// <returns>Returns an array of DbParameter objects.</returns>
public static DbParameter[] GetParameters(ushort count)
{
DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory();
DbParameter[] parameters = new DbParameter[count];
try
{
for (int i = 0; i < count; i++)
{
parameters[i] = Dbfactory.CreateParameter();
}
}
catch (System.IndexOutOfRangeException)
{
throw new Exception("Count has to be greater than zero and less than 100", new IndexOutOfRangeException() );
}
return parameters;
}
/// <summary>
/// Returns a single DbParameter object
/// </summary>
/// <returns>Returns a single DbParameter object </returns>
public static DbParameter GetParameter()
{
DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory();
DbParameter parameter = Dbfactory.CreateParameter();
return parameter;
}
/// <summary>
/// Uses a fast datareader to load and return a datatable.
/// </summary>
/// <param name="command">Command object filled with necessary parameters.</param>
/// <param name="ErrorMessage">Output parameter for getting the error message if any.</param>
/// <returns>Returns a loaded DataTable from the execution of the given Command.</returns>
public static DataTable GetTable(ref DbCommand command, out string ErrorMessage)
{
ErrorMessage = String.Empty;
DbTransaction tran = null;
DbConnection conn = null;
DbDataReader reader = null;
DataTable dtable = new DataTable();
if (command == null)
{
ErrorMessage = "Please initilise the command object.";
return dtable ;
}
try
{
conn = ConnectionClass.GetConnection();
command.Connection = conn;
conn.Open();
tran = conn.BeginTransaction();
reader = command.ExecuteReader();
if (reader.HasRows)
{
dtable.Load(reader);
}
else
{
ErrorMessage = "No records found. <BR>";
return null;
}
}
catch (DbException exp)
{
ErrorMessage = "An exception has occured while executing the database transactions. <BR>";
ErrorMessage = ErrorMessage + exp.Message;
if (tran != null)
{
tran.Rollback();
}
}
finally
{
command.Dispose();
if (tran != null)
{
tran.Dispose();
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (conn != null)
{
conn.Dispose();
}
}
return dtable ;
}
#endregion
}
}
