public abstract class DAL: IDAL, IDisposable
{
#region Private and Public Fields and Properties public DataProvider ProviderType { get; set; } public string ConnectionString { get; set; } public IDbConnection Connection { get; set; } public IDbTransaction Transaction { get; set; } public IDataReader DataReader { get; set; } public IDbCommand Command { get; set; } public IDbDataParameter[] Parameters { get; set; }
#endregion Private and Public Fields and Properties
#region Class Constructors public DAL() { } ///---------------------------------------------------------------------------
/// <summary>
/// Method Name: DAL
/// Description: Overload constructor for DAL.
/// </summary>
///
/// <param name="providerType">data provider</param>
///---------------------------------------------------------------------------
public DAL(DataProvider providerType) {
this.ProviderType = providerType; }
public DAL(DataProvider providerType, string connectionString) {
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: DAL
/// Description: Overload constructor for DAL.
/// </summary>
///
/// <param name="providerType">data provider</param>
/// <param name="connectionString">connection string text</param>
///---------------------------------------------------------------------------
this.ProviderType = providerType; this.ConnectionString = connectionString; }
#endregion Class Constructors
#region Instance Methods ///---------------------------------------------------------------------------
/// <summary>
/// Method Name: Open
/// Description: Creates an open connection.
/// </summary>
///---------------------------------------------------------------------------
public void Open() {
Connection = DALFactory.GetConnection(this.ProviderType); Connection.ConnectionString = this.ConnectionString; if (Connection.State != ConnectionState.Open) Connection.Open();
this.Command = DALFactory.GetCommand(this.ProviderType); }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: Close
/// Description: Closes the connection.
/// </summary>
///---------------------------------------------------------------------------
public void Close() {
if (Connection.State != ConnectionState.Closed) Connection.Close();
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: Dispose
/// Description: Clean objects from memory.
/// </summary>
///---------------------------------------------------------------------------
public void Dispose() {
GC.SuppressFinalize(this); this.Close(); this.Command = null; this.Transaction = null; this.Connection = null; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: CreateParameters
/// Description: Creates parameters for use.
/// </summary>
///
/// <param name="paramsCount">count of parameter</param>
///---------------------------------------------------------------------------
public void CreateParameters(int paramsCount) {
Parameters = new IDbDataParameter[paramsCount]; Parameters = DALFactory.GetParameters(this.ProviderType, paramsCount);
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: AddParameters
/// Description: Adds values to the parameters.
/// </summary>
///
/// <param name="index">index of parameter</param>
/// <param name="paramName">name of parameter</param>
/// <param name="objValue">value of object</param>
///---------------------------------------------------------------------------
public void AddParameters(int index, string paramName, object objValue) {
if (index < Parameters.Length) {
Parameters[index].ParameterName = paramName;
Parameters[index].Value = objValue;
}
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: AddParameters
/// Description: Adds values to the parameters.
/// </summary>
///
/// <param name="index">index of parameter</param>
/// <param name="paramName">name of parameter</param>
/// <param name="objValue">value of object</param>
///---------------------------------------------------------------------------
public void AddParameters(int index, string paramName, object objValue, int dir, int length) {
if (index < Parameters.Length) {
Parameters[index].ParameterName = paramName;
Parameters[index].Value = objValue;
if (dir == 0) {
Parameters[index].Direction = ParameterDirection.Input; }
else if (dir == 1) {
Parameters[index].Direction = ParameterDirection.Output; }
Parameters[index].Size = length;
}
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: BeginTransaction
/// Description: Starts the transaction.
/// </summary>
///---------------------------------------------------------------------------
public void BeginTransaction() {
if (this.Transaction == null) Transaction = DALFactory.GetTransaction(this.ProviderType); this.Command.Transaction = Transaction; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: CommitTransaction
/// Description: Commits the transaction.
/// </summary>
///---------------------------------------------------------------------------
public void CommitTransaction() {
if (this.Transaction != null) this.Transaction.Commit(); Transaction = null; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: ExecuteReader
/// Description: executes a datareader.
/// </summary>
///
/// <param name="commandType">command type</param>
/// <param name="commandText">command text</param>
/// <returns>datareader</returns>///
///---------------------------------------------------------------------------
public IDataReader ExecuteReader(CommandType commandType, string
commandText)
{
this.Command = DALFactory.GetCommand(this.ProviderType); Command.Connection = this.Connection; PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters); this.DataReader = Command.ExecuteReader(); Command.Parameters.Clear();
return this.DataReader; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: CloseReader
/// Description: Closes the reader.
/// </summary>
///---------------------------------------------------------------------------
public void CloseReader() {
if (this.DataReader != null) this.DataReader.Close(); }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: AttachParameters
/// Description: Sets parameters to DBNull.
/// </summary>
///
/// <param name="command">object of type IDbCommand</param>
/// <param name="commandParameters">object of type IDbDataParameter text</param>
///---------------------------------------------------------------------------
private void AttachParameters(IDbCommand command, IDbDataParameter[]commandParameters) {
foreach (IDbDataParameter idbParameter in commandParameters) {
if ((idbParameter.Direction == ParameterDirection.InputOutput) && (idbParameter.Value == null)) {
idbParameter.Value = DBNull.Value; }
command.Parameters.Add(idbParameter);
}
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: AttachParameters
/// Description: Sets parameters to DBNull.
/// </summary>
///
/// <param name="command">object of type IDbCommand</param>
/// <param name="connection">object of type IDbConnection</param>
/// <param name="transaction">object of type IDbTransaction</param>
/// <param name="commandType">object of type CommandType</param>
/// <param name="commandText">command text</param>
/// <param name="commandParameters">object of type IDbDataParameter text</param>
///---------------------------------------------------------------------------
private void PrepareCommand(IDbCommand command, IDbConnection
connection,
IDbTransaction transaction, CommandType commandType, string
commandText,
IDbDataParameter[]commandParameters) {
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null) {
command.Transaction = transaction;
}
if (commandParameters != null) {
AttachParameters(command, commandParameters);
}
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: ExecuteNonQuery
/// Description: executes and returns a value.
/// </summary>
///
/// <param name="commandType">command type</param>
/// <param name="commandText">command text</param>
/// <returns>integer</returns>///
///---------------------------------------------------------------------------
public int ExecuteNonQuery(CommandType commandType, string
commandText)
{
this.Command = DALFactory.GetCommand(this.ProviderType); PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText,this.Parameters); int returnValue = Command.ExecuteNonQuery(); Command.Parameters.Clear();
return returnValue; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: ExecuteScalar
/// Description: executes and returns a single value.
/// </summary>
///
/// <param name="commandType">command type</param>
/// <param name="commandText">command text</param>
/// <returns>object</returns>///
///---------------------------------------------------------------------------
public object ExecuteScalar(CommandType commandType, string
commandText)
{
this.Command = DALFactory.GetCommand(this.ProviderType); PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters); object returnValue = Command.ExecuteScalar(); Command.Parameters.Clear();
return returnValue; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: ExecuteDataSet
/// Description: executes and returns a dataset object.
/// </summary>
///
/// <param name="commandType">command type</param>
/// <param name="commandText">command text</param>
/// <returns>DataSet</returns>///
///---------------------------------------------------------------------------
public DataSet ExecuteDataSet(CommandType commandType, string commandText) {
this.Command = DALFactory.GetCommand(this.ProviderType); PrepareCommand(Command, this.Connection, this.Transaction, commandType,
commandText, this.Parameters); IDbDataAdapter dataAdapter =DALFactory.GetDataAdapter (this.ProviderType); dataAdapter.SelectCommand = Command;
DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet);
Command.Parameters.Clear();
return dataSet; }
#endregion Instance Methods }
}
The DAL Factory Class:
namespace AppBase {
#region Class Using References using System; using System.Data; using System.Data.Odbc; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.OracleClient;
#endregion Class Using References ///---------------------------------------------------------------------------
/// Namespace: AppBase
/// Derived Class:
/// Filename: DALFactory.cs
/// Date: 04/28/2008
/// Author: OUR Team
/// Updated:
/// <summary>
/// Purpose: The DALFactory class contains the methods for data transaction called by DAL class.
/// </summary>
///---------------------------------------------------------------------------
public sealed class DALFactory
{
#region Private and Public Fields and Properties
#endregion Private and Public Fields and Properties
#region Class Constructors private DALFactory() {
//
// TODO: Add constructor logic here
//
}
#endregion Class Constructors
#region Static Methods ///---------------------------------------------------------------------------
/// <summary>
/// Method Name: GetConnection
/// Description: Retrieves connection based on provider type.
/// </summary>
///
/// <param name="providerType">object of type DataProvider</param>
/// <returns>connection object</returns>///
///---------------------------------------------------------------------------
public static IDbConnection GetConnection(DataProvider providerType) {
IDbConnection iDbConnection = null; switch (providerType) {
case DataProvider.SqlServer: iDbConnection = new SqlConnection(); break; case DataProvider.OleDb: iDbConnection = new OleDbConnection(); break; case DataProvider.Odbc: iDbConnection = new OdbcConnection(); break; case DataProvider.Oracle: iDbConnection = new OracleConnection(); break; default: return null; }
return iDbConnection; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: GetCommand
/// Description: Retrieves command based on provider type.
/// </summary>
///
/// <param name="providerType">object of type DataProvider</param>
/// <returns>command object</returns>///
///---------------------------------------------------------------------------
public static IDbCommand GetCommand(DataProvider providerType) {
switch (providerType) {
case DataProvider.SqlServer: return new SqlCommand(); case DataProvider.OleDb: return new OleDbCommand(); case DataProvider.Odbc: return new OdbcCommand(); case DataProvider.Oracle: return new OracleCommand(); default: return null; }
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: GetDataAdapter
/// Description: Retrieves data adapter based on provider type.
/// </summary>
///
/// <param name="providerType">object of type DataProvider</param>
/// <returns>IDbDataAdapter object</returns>///
///---------------------------------------------------------------------------
public static IDbDataAdapter GetDataAdapter(DataProvider providerType) {
switch (providerType) {
case DataProvider.SqlServer: return new SqlDataAdapter(); case DataProvider.OleDb: return new OleDbDataAdapter(); case DataProvider.Odbc: return new OdbcDataAdapter(); case DataProvider.Oracle: return new OracleDataAdapter(); default: return null; }
}
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: GetTransaction
/// Description: Gets transaction based on provider type.
/// </summary>
///
/// <param name="providerType">object of type DataProvider</param>
/// <returns>IDbTransaction object</returns>///
///---------------------------------------------------------------------------
public static IDbTransaction GetTransaction(DataProvider providerType) {
IDbConnection iDbConnection =GetConnection(providerType); IDbTransaction iDbTransaction =iDbConnection.BeginTransaction(); return iDbTransaction; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: GetParameter
/// Description: Retrieves parameters based on provider type.
/// </summary>
///
/// <param name="providerType">object of type DataProvider</param>
/// <returns>IDataParameter object</returns>///
///---------------------------------------------------------------------------
public static IDataParameter GetParameter(DataProvider providerType) {
IDataParameter iDataParameter = null; switch (providerType) {
case DataProvider.SqlServer: iDataParameter = new SqlParameter(); break; case DataProvider.OleDb: iDataParameter = new OleDbParameter(); break; case DataProvider.Odbc: iDataParameter = new OdbcParameter(); break; case DataProvider.Oracle: iDataParameter = new OracleParameter(); break; }
return iDataParameter; }
///---------------------------------------------------------------------------
/// <summary>
/// Method Name: GetParameter
/// Description: Overloaded method that returns an array of parameters based on provider type.
/// </summary>
///
/// <param name="providerType">object of type DataProvider</param>
/// <param name="paramsCount">count of parameters</param>
/// <returns>IDataParameter object</returns>///
///---------------------------------------------------------------------------
public static IDbDataParameter[]GetParameters(DataProvider providerType, int paramsCount) {
IDbDataParameter[]idbParams = new IDbDataParameter[paramsCount]; switch (providerType) {
case DataProvider.SqlServer: for (int i = 0; i < paramsCount;++i) {
idbParams[i] = new SqlParameter(); }
break; case DataProvider.OleDb: for (int i = 0; i < paramsCount;++i) {
idbParams[i] = new OleDbParameter(); }
break; case DataProvider.Odbc: for (int i = 0; i < paramsCount;++i) {
idbParams[i] = new OdbcParameter(); }
break; case DataProvider.Oracle: for (int i = 0; i < paramsCount; ++i) {
idbParams[i] = new OracleParameter(); }
break; default: idbParams = null; break; }
return idbParams; }
#endregion Static Methods }
}
namespace AppBase {
#region Class Using References using System; using System.Data; using System.Data.Odbc; using System.Data.SqlClient; using System.Data.OleDb; //using System.Data.OracleClient;
#endregion Class Using References ///---------------------------------------------------------------------------
/// Namespace: AppBase
/// Derived Class:
/// Filename: IDAL.cs
/// Date: 04/28/2008
/// Author: OUR Team
/// Updated:
/// <summary>
/// Purpose: Contains the Interface which the DAL class is derived.
/// </summary>
///---------------------------------------------------------------------------
#region Enum Section public enum DataProvider
{
Oracle,SqlServer,OleDb,Odbc
}
#endregion Class Using References public interface IDAL
{
#region Private and Public Fields and Properties DataProvider ProviderType {get; set;} string ConnectionString {get; set;} IDbConnection Connection {get;} IDbTransaction Transaction {get;} IDataReader DataReader {get;} IDbCommand Command {get;} IDbDataParameter[]Parameters {get;}
#endregion Private and Public Fields and Properties
#region Methods Required in classes that implements this interface void Open(); void BeginTransaction(); void CommitTransaction(); void CreateParameters(int paramsCount); void AddParameters(int index, string paramName, object objValue); IDataReader ExecuteReader(CommandType commandType, string
commandText);
DataSet ExecuteDataSet(CommandType commandType, string
commandText);
object ExecuteScalar(CommandType commandType, string commandText); int ExecuteNonQuery(CommandType commandType,string commandText); void CloseReader(); void Close(); void Dispose();
#endregion Methods Required in classes that implements this interface }
}