Designing and implementing a versatile data access tier for an ASP.NET application

using System;
using System.Threading;
using System.Configuration;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;


namespace DAT
{
	/// <summary>
	/// This is  the super class of all data access  objects
	/// </summary>
	public class DABasis
	{
		//  connection string for the local Sqlserver
		protected static string strLocalServer; 

		// EventLog for Data Access Tier
		protected static EventLog daEventLog;
		// strings to connect EventLog
		protected static string strLog;
		protected static string strSource;
		protected static string strMachine;

        //OleDbparameter Factory
        protected  PMFactory_In pmFactory_In;
		protected  PMFactory_Out pmFactory_Out;

		// Objects for the local database access   
		protected SqlConnection dbConnection_L;
		protected SqlCommand dbCommand_L;
		protected SqlDataAdapter dbAdapter_L;
		protected SqlTransaction dbTransaction_L;
	  
		public DABasis()
		{
	
		 // Initialize parameter factory 
		  pmFactory_In = new PMFactory_In();
		  pmFactory_Out = new PMFactory_Out();
          
		}

		/// <summary>
		/// retrives values from web.config
		/// </summary>
		static   DABasis()
		{
		  // string to connect the local Sqlserver
          strLocalServer = ConfigurationSettings.AppSettings["LocalConnection"];
		  // strings to connect EventLog
		  strLog = ConfigurationSettings.AppSettings["daLog"];
		  strSource = ConfigurationSettings.AppSettings["daSource"];
          strMachine = ConfigurationSettings.AppSettings["daMachine"];
		
			// Create an instance from EventLog
			if(EventLog.SourceExists(strLog,strMachine))
			{
				daEventLog= new EventLog(strLog,strMachine,strSource);
			}
		}

		/// <summary>
		/// Writes error messages on the EventLog
		/// </summary>
		/// <param name="strMessage"></param>
		 protected virtual void ErrorLog(string strMessage)
		{ 
			// write in Windows EventLog
			if(daEventLog!=null)
			{
				daEventLog.WriteEntry(strMessage,EventLogEntryType.Error);
			}
			// throw an error to the buisness access tier
			Exception oException= new Exception("An error occured in  DataAccess Tier");
			throw oException;
		}


		/// <summary>
		/// Writes the error message on the EventLog
		/// </summary>
		/// <param name="strMessage"></param>
		/// <param name="oException"></param>
		 protected virtual void ErrorLog(string strMessage,Exception oException)
		{
			// write in Windows EventLog
			if(daEventLog!=null)
			{
				string oMessage = oException.Message + " " + strMessage;
				daEventLog.WriteEntry(oMessage,EventLogEntryType.Error);
			}
			// throw an error to the buisness access tier
			Exception oExcept= new Exception("An error occured in  DataAccess Tier");
			throw oExcept;
		}
       
		/// <summary>
		/// Writes warning messages on the EventLog
		/// </summary>
		/// <param name="strMessage"></param>
	    protected virtual void WarningLog(string strMessage)
		{
			// write in Windows EventLog
			if(daEventLog!=null)
			{
				daEventLog.WriteEntry(strMessage,EventLogEntryType.Warning);
			}
		}

		/// <summary>
		/// This method initialize data access utilities for the local Sqlserver
		/// this method must be called always at first
		/// </summary>
		/// <param name="strSP">name of the stored procedure</param>
		protected virtual void Prepair_L(string strSP)
		{
			try
			{
				dbConnection_L = new SqlConnection(strLocalServer);
				dbCommand_L = new SqlCommand(strSP,dbConnection_L);
				dbCommand_L.CommandType = CommandType.StoredProcedure;

			}
			catch(Exception oException)
			{
				string strMessage = "Occurred in Prepail_L() ";
				ErrorLog(strMessage,oException);
			}
				
		}

		/// <summary>
		/// this method will reintialize existing dbCommand_L
		/// with the 
		/// </summary>
		/// <param name="strSP"></param>
		protected virtual void ReuseCommand_L(string strSP)
		{
			try
			{
				// clear the parameter list
				this.dbCommand_L.Parameters.Clear();
				// intialize dbCommand_L with the store procedure SP
				dbCommand_L.CommandText=strSP;
				dbCommand_L.CommandType=CommandType.StoredProcedure;

			}
			catch(Exception oException)
			{
				string strMessage="Occurred in ReuseCommand_L ";
				ErrorLog(strMessage,oException);
			}
				
		}
		/// <summary>
		/// opens the connection to the local Sqlserver
		/// </summary>
		protected virtual void Open_L()
		{
			try
			{
				if(dbConnection_L.State!=ConnectionState.Open)
				{
					dbConnection_L.Open();
				}
			}
			catch(Exception oException)
			{
              string strMessage="Occurred in Open_L() ";
			  ErrorLog(strMessage,oException);

			}
		}

		/// <summary>
		/// closes the connection to the local Sqlserver
		/// </summary>
		protected virtual void Close_L()
		{
			try
			{
				if(dbConnection_L.State==ConnectionState.Open)
				{
					dbConnection_L.Close();
				}
			}
			catch(Exception oException)
			{
				string strMessage = "Occurred in Close()_L() ";
				ErrorLog(strMessage,oException);

			}
		}
		
		/// <summary>
		/// fills the Dataset.must be called after calling Prepair_L
		/// </summary>
		/// <param name="dsOut">Dataset which is to be filled</param>
		/// <param name="nStartRecord">The zero-based record number to start with. 
		///</param>
		/// <param name="nMaxRecord">Maximum number of reocords to retrieve</param>
		/// <param name="strTable">Name of the Table</param>
		protected virtual void GetDataSet_L(DataSet dsOut,int nStartRecord,int nMaxRecord,string strTable)
		{
			try
			{
				dbAdapter_L.Fill(dsOut,nStartRecord,nMaxRecord,strTable);
			}
			catch(Exception oException)
			{
				string strMessage = "Occured in GetDataSet_L ";
				strMessage += strTable+ oException.Message;
				ErrorLog(strMessage);    
			}
		}

		/// <summary>
		/// fills the Dataset.must be called after calling Prepair_L
		/// and Open_L();
		/// </summary>
		/// <param name="dsOut">Dataset which is to be filled</param>
		/// <param name="strTable">name of the DataTable</param>
		protected virtual void GetDataSet_L(DataSet dsOut,string strTable)
		{
			try
			{
              dbAdapter_L.Fill(dsOut,strTable);
			}
			catch(Exception oException)
			{
			 string strMessage = "Occured in GetDataSet_L ";
             ErrorLog(strMessage,oException);  
			}
		}


		/// <summary>
		/// Starts a transaction to the local server
		/// with isolationlevel
		/// </summary>
		protected void BeginTransaction_L(IsolationLevel iLevel)
		{
			try
			{
				// assign the OleDbtransaction to the SqlCommand
				this.dbTransaction_L = dbConnection_L.BeginTransaction(iLevel);
				this.dbCommand_L.Transaction = this.dbTransaction_L;

			}
			catch(Exception oException)
			{
				string strMessage = "Occured in BeginTransaction_L()";
				ErrorLog(strMessage,oException);  

			}
		}

        /// <summary>
        /// Starts a transaction to the local server
        /// </summary>
		protected void BeginTransaction_L()
		{
			try
			{
				// Excequte the command
				this.dbTransaction_L = dbConnection_L.BeginTransaction();
				this.dbCommand_L.Transaction = this.dbTransaction_L;

			}
			catch(Exception oException)
			{
				string strMessage = "Occured in BeginTransaction_L()";
				ErrorLog(strMessage,oException);  

			}
		}

		/// <summary>
		/// Rollbacks the transaction to the local server
		/// </summary>
		protected void Rollback_L()
		{
			try
			{
			   this.dbTransaction_L.Rollback();
			}
			catch(Exception oException)
			{
				string strMessage = "Occured in RollBack_L()";
				ErrorLog(strMessage,oException);

			}
		}

		/// <summary>
		/// Commits the local server
		/// </summary>
		protected void Commit_L()
		{
			try
			{
				this.dbTransaction_L.Commit();
			}
			catch(Exception oException)
			{
				string strMessage = "Occured in Commit_L()";
				ErrorLog(strMessage,oException);
			}

		}

		

		/// <summary>
		/// this method wraps the method dbCommand_L.ExecuteNonQuery();
		/// and must be called after the method Prepair_L
		/// </summary>
		/// <returns>affected rows</returns>
		protected virtual int ExecuteNonQuery_L()
		{
			int nAffected = 0;
			try
			{
				nAffected = dbCommand_L.ExecuteNonQuery();
			}
			catch(Exception oException)
			{
				string strMessage = "Occured in GetDataSet_L ";
				strMessage += oException.Message;
				ErrorLog(strMessage);    
			}
            return nAffected;
		}


		
		/// <summary>
		/// this method wraps the method dbCommand_L.ExecuteScalar();
		/// and must be called after the method Prepair_L
		/// </summary>
		/// <returns>affected rows</returns>
		protected virtual object ExcecuteScalar_L()
		{
			object oScalar=null;
			try
			{
				oScalar = dbCommand_L.ExecuteScalar();
			}
			catch(Exception oException)
			{
				string strMessage = "Occured in DABasis:ExcecuteScalar() ";
				ErrorLog(strMessage,oException);    
			}
			return oScalar;
		}


		/// <summary>
		/// this method adds SqlParameters to the dbCommand_L
		/// </summary>
		/// <param name="oParameter"></param>
		protected virtual void AddParameter_L(object oParameter)
		{
			try
			{
              this.dbCommand_L.Parameters.Add(oParameter);
       
			}
			catch(Exception oException)
			{
              string strMessage = "An error occured in the method:AddParameter";
			  strMessage += oException.Message;
			  ErrorLog(strMessage);  
			}


		}
	}
}