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

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using TDS;

namespace DAT
{
	/// <summary>
	///  This class deals with  order details
	/// </summary>
	public class DAOrder:DABasis
	{
		public DAOrder()
		{

		}
		
		/// <summary>
		/// retrieves  order details  for an Order  
		/// </summary>
		/// <param name="nOrderID">OrderID</param>
		/// <returns>SqlDataAdapter</returns>
		public SqlDataReader GetOrderDetail(int nOrderID)
		{
			SqlDataReader oReader = null;
			try
			{
				// lock the intrinsic DataAcces utilities 
				Monitor.Enter(this);
				// intialize DataAcces utilities for the local SqlServer
				Prepair_L("SPSelOrderDetail");
				// Add parameters to the command
				SqlParameter pmOrderID = pmFactory_In.GetPMInt4("@OrderID");
				pmOrderID.Value = nOrderID;
				AddParameter_L(pmOrderID);

				// Open the connection to the local server and  excequtethe command
				this.Open_L();
				oReader = dbCommand_L.ExecuteReader(CommandBehavior.CloseConnection);
                
			}
			catch(Exception oException)
			{
				this.Close_L();
				Monitor.Exit(this);
				string strError;
				strError = "An Error Occured in DAOrder:GetOrderDetailOrders(DR)";
				this.ErrorLog(strError,oException);
			}
			return oReader;
		
		}

		/// <summary>
		/// Retrieves  order details  for an OrderID   
		/// </summary>
		/// <param name="nOrderID">OrderID</param>
		/// <param name="dsOrderDetail">typed DataSet</param>
		public void GetOrderDetail(int nOrderID,out DSOrderDetail dsOrderDetail)
		{
			// Create the typed Dataset for output
			dsOrderDetail = new DSOrderDetail();
			try
			{
				// lock the intrinsic DataAcces utilities 
				Monitor.Enter(this);
				// intialize DataAcces utilities for the local SqlServer
				Prepair_L("SPSelOrderDetail");
				dbAdapter_L = new SqlDataAdapter();
				dbAdapter_L.SelectCommand = dbCommand_L;
				// Add query parameters to the command
				SqlParameter pmOrderID = pmFactory_In.GetPMInt4("@OrderID");
				pmOrderID.Value = nOrderID;
				AddParameter_L(pmOrderID);
				string strTable = dsOrderDetail.OrderDetails.TableName;
				// Retrive the typed DataSet
				GetDataSet_L(dsOrderDetail,strTable);
			
			}
			catch(Exception oException)
			{
				string strError;
				strError = "An Error Occured in DAOrder:GetOrderDetailOrders";
				this.ErrorLog(strError,oException);

			}
			finally
			{
				this.Close_L();
				Monitor.Exit(this);

			}

		}

		/// <summary>
		/// Updates  quantity of a product in an order deatail
		/// </summary>
		/// <param name="nOrderID">OrderID</param>
		/// <param name="nProductID">Product</param>
		/// <param name="nQuantity">Quantity</param>
		/// <returns> affected Rows</returns>
		public int UpdateOrderDetail(int nOrderID,int nProductID,int nQuantity)
		{
			int nAffected = 0;
			try
			{
				// lock the intrinsic DataAcces utilities 
				Monitor.Enter(this);
				// intialize DataAcces utilities for the local SqlServer
				Prepair_L("SPUpOrderDetail");
			  
				// Add parameters to the command
				SqlParameter pmOrderID = pmFactory_In.GetPMInt4("@OrderID");
				pmOrderID.Value = nOrderID;
				AddParameter_L(pmOrderID);

				SqlParameter pmProductID = pmFactory_In.GetPMInt4("@ProductID");
				pmProductID.Value = nProductID;
				AddParameter_L(pmProductID);

				SqlParameter pmQuantity = pmFactory_In.GetPMInt4("@Quantity");
				pmQuantity.Value = nQuantity;
				AddParameter_L(pmQuantity);

				// Open the Connection and exequte
				this.Open_L();
				nAffected = this.ExecuteNonQuery_L();

			}
			catch(Exception oException)
			{
				string strError;
				strError = "An Error Occured in DAOrder:UpdateOrderDetail";
				this.ErrorLog(strError,oException);
			}
			finally
			{
				this.Close_L();
				Monitor.Exit(this);
			}
			return nAffected;
		}

		/// <summary>
		///  Deletes an order and its cooresponding entriesinorder details .
		/// Its is used to demonstrate transaction 
		/// </summary>
		/// <param name="nOrderID"></param>
		public  bool DeleteOrder(int nOrderID)
		{   
			// At first, we will delete Order Details with the 
			// foreign key value nOrderID and after we 
			// will delete the corresponding Order. If this transaction fails,
			// then we will roll back first transaction
			bool bSuccess=false;
			try
			{
				Monitor.Enter(this);

				// Delete Order Detials with the OrderID
				Prepair_L("SPDelODDemo");
				// Add the parametrs
				SqlParameter pmOrderID = pmFactory_In.GetPMInt4("@OrderID");
				pmOrderID.Value = nOrderID;
				AddParameter_L(pmOrderID);
				// open the connection to the local server
				this.Open_L();
				this.BeginTransaction_L(System.Data.IsolationLevel.Serializable);
				int nOD = ExecuteNonQuery_L();
				// write on the WaringLog
				StringBuilder strBuilder = new StringBuilder();
				strBuilder.Append(nOD);
				strBuilder.Append(" Rows are deleted in [Order Details] with the OrderID ");
				strBuilder.Append(nOrderID);
				this.WarningLog(strBuilder.ToString());
			
				// delete Orders with the OrderID= nOrderID
				ReuseCommand_L("SPDelOrdersDemo");
				// Add the parametrs
				AddParameter_L(pmOrderID);
				nOD = ExecuteNonQuery_L();
				// write on the WaringLog
				strBuilder = new StringBuilder();
				strBuilder.Append("an order was deleted with ID ");
				strBuilder.Append(nOrderID);
				WarningLog(strBuilder.ToString());
                
				// commit the transaction
			    this.Commit_L();
				bSuccess=true;
                
			}
			catch(Exception oException)
			{
				// Rollback the transaction
				bSuccess=false;
				this.Rollback_L();
				string strMessage = "An error occured in DAOrderDetail:DeleteOrder ";
				ErrorLog(strMessage,oException);

			}
			finally
			{
				Close_L();
			    Monitor.Exit(this);
			}
			return bSuccess;
		}


	}
	 
	
}