Hi,
This article is basically intended for the beginners or intermediate level of C# developers who loves 3 tier Architecture in their app. This is a wrapper class called DbHelper which can interact with any database , supported by ADO.Net framework.
Name spaces that are used by the various classes in this DbHelper class.
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Data.Common;
This is mine custom NameSpace. You can replace it with your own. The name space has a class DbHelper which is implementing IDiposible interface , as I prefer using{ } block in my code. Why using, because the open parentheses and closing parentheses includes try - catch and calls dispose automatically. Some private members like DbConnection and DbProviderFactory are declared. Here we are using DbProviderFactory as Data providers with registered factory classes in the .NET Framework include System.Data.Odbc, System.Data.OleDb, System.Data.SqlClient, System.Data.SqlServerCe, and System.Data.OracleClient. For more details on DbProviderFactory have MSDN . Below is the entire code. I know one thing i am missing here is exception handling. Yes its true, as i generally catches exception in business layer or presentation and there I use to log it. Instead of placing try catch in each level, I use a single try-catch at presentation layer , so that I can have entire stack trace.
namespace ARSoftSys.DataAccessWrapper
{
public sealed class DbHelper : IDisposable
{
#region PRIVATE MEMBER VARIABLES
private static DbConnection _connection;
private static bool _dispose;
private static DbProviderFactory _provider;
#endregion
///
namespace ARSoftSys.DataAccessWrapper
{
public sealed class DbHelper : IDisposable
{
#region PRIVATE MEMBER VARIABLES
private static DbConnection _connection;
private static bool _dispose;
private static DbProviderFactory _provider;
#endregion
///
/// contructor, call method to open connection
///
public DbHelper()
{
_connection = CreateConnection();
}
///
/// retrieves connection string from config
///
private static string ConnectionString
{
get { return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; }
}
///
/// retrieves provider from connection string
///
private static string ProviderName
{
get { return ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; }
}
#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
///
/// Destructor of the class. Calls method close connection that will automatically
/// closes connection and calls class , dispose method
///
~DbHelper()
{
CloseConnection();
Dispose();
}
// Given a provider name and connection string,
// create the DbProviderFactory and DbConnection. Returns a DbConnection on success; null on failure.
private static DbConnection CreateConnection()
{
// Assume failure.
DbConnection connection = null;
// Create the DbProviderFactory and DbConnection.
if (ConnectionString != null)
{
try
{
_provider = DbProviderFactories.GetFactory(ProviderName);
connection = _provider.CreateConnection();
connection.ConnectionString = ConnectionString;
connection.Open();
}
catch (Exception ex)
{
// Set the connection to null if it was created.
if (connection != null)
{
connection = null;
}
Common.LogError(ex);
}
}
else
{
throw new NullReferenceException(
"Invalid or missing connection string . Check if it exists in configuration file.");
}
// Return the connection.
return connection;
}
private static void CloseConnection()
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
_connection.Dispose();
_provider = null;
}
}
///
///
///
///
///
public DbDataReader ExecuteSelect(string commandText)
{
return ExecuteSelect(commandText, CommandType.Text, null);
}
///
///
///
///
///
///
public DbDataReader ExecuteSelect(string commandText, CommandType cmdType)
{
return ExecuteSelect(commandText, cmdType, null);
}
///
/// Accepts command text , command type and database parameters
/// as parameters and retrieves the table
///
/// Select statement
/// Type of command
/// Database parameters
///
public DbDataReader ExecuteSelect(string commandText, CommandType commandType, object parameters)
{
DbDataReader reader = null;
// Check for valid DbConnection.
if (_connection != null)
{
using (_connection)
{
try
{
// Create the command.
var command = _connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
if (parameters != null)
command.Parameters.Add(parameters);
// Open the connection.
_connection.Open();
// Retrieve the data.
reader = command.ExecuteReader();
command.Parameters.Clear();
}
catch (DbException ex)
{
Common.LogError(ex);
throw;
}
}
}
return reader;
}
///
///
///
///
///
public DataSet ExecuteSelectDataSet(string commandText)
{
return ExecuteSelectDataSet(commandText, CommandType.Text, null);
}
///
///
///
///
///
///
public DataSet ExecuteSelectDataSet(string commandText, CommandType cmdType)
{
return ExecuteSelectDataSet(commandText, cmdType, null);
}
///
/// Accepts command text , command type and database parameters
/// as parameters and retrieves the table
///
/// Select statement
/// Type of command
/// Database parameters
///
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType, object parameters)
{
DataSet ds = null;
// Check for valid DbConnection.
if (_connection != null)
{
using (_connection)
{
try
{
// Create the command.
var command = _connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
if (parameters != null)
command.Parameters.Add(parameters);
// Open the connection.
_connection.Open();
using (DbDataAdapter adapter = _provider.CreateDataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(ds);
}
command.Parameters.Clear();
}
catch (DbException ex)
{
Common.LogError(ex);
throw;
}
}
}
return ds;
}
///
///
///
///
///
public object ExecuteScalar(string commandText)
{
return ExecuteScalar(commandText, CommandType.Text, null);
}
///
///
///
///
///
///
public object ExecuteScalar(string commandText, CommandType cmdType)
{
return ExecuteScalar(commandText, cmdType, null);
}
///
/// Accepts command text , command type and database parameters
/// as parameters and retrieves the table
///
/// Select statement
/// Type of command
/// Database parameters
///
public object ExecuteScalar(string commandText, CommandType commandType, object parameters)
{
object obj = null;
// Check for valid DbConnection.
if (_connection != null)
{
using (_connection)
{
try
{
// Create the command.
DbCommand command = _connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
if (parameters != null)
command.Parameters.Add(parameters);
// Open the connection.
_connection.Open();
obj = command.ExecuteScalar();
command.Parameters.Clear();
}
catch (DbException ex)
{
Common.LogError(ex);
throw;
}
}
}
return obj;
}
///
///
///
///
///
public object ExecuteNonQuery(string commandText)
{
return ExecuteNonQuery(commandText, CommandType.Text, null);
}
///
///
///
///
///
///
public object ExecuteNonQuery(string commandText, CommandType cmdType)
{
return ExecuteNonQuery(commandText, cmdType, null);
}
///
/// Accepts command text , command type and database parameters
/// as parameters and retrieves the table
///
/// Select statement
/// Type of command
/// Database parameters
///
public int ExecuteNonQuery(string commandText, CommandType commandType, object[] parameters)
{
int rowsAffected = 0;
try
{
// Create the command.
DbCommand command = _connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
if (parameters != null)
command.Parameters.AddRange(parameters);
rowsAffected = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch (DbException ex)
{
Common.LogError(ex);
throw;
}
return rowsAffected;
}
private static void Dispose(bool disposing)
{
if (_dispose) return;
if (disposing)
{
if (_connection != null)
{
_provider = null;
_connection.Dispose();
}
}
_dispose = true;
}
}
}
Finally, please provide your valuable suggestions to make it more useful and more flexible.
