DataObjectBase.txt

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace {0}
{
	[Flags]
	public enum TransactionMode
	{
		None = 0,
		RequireTransaction = 1
	} 
	public struct ValidateRecord
	{
		public string PropertyName;
		public string Message;
		public string ValidationError;
	}

	public class ValidationException : Exception
	{
		public List<ValidateRecord> RequiredProperties { get; set; }
		public ValidationException(string msg, List<ValidateRecord> RequiredProp)
			: base(msg)
		{
			RequiredProperties = RequiredProp;
		}
	}
	internal class Transaction
	{
		public string TransactionName;
		public SqlTransaction sqltransaction = null;
		public DataBaseObject owner;
		public Transaction(DataBaseObject Owner)
		{
			TransactionName = Guid.NewGuid().ToString();
			owner = Owner;
		}
		public Transaction(SqlTransaction trans, DataBaseObject Owner)
		{
			TransactionName = Guid.NewGuid().ToString();
			sqltransaction = trans;
			owner = Owner;
		}
	}
	internal class TransactionManager
	{
		private static TransactionManager _sTransactionManager;
		private Stack<Transaction> Trans = new Stack<Transaction>();
		private TransactionManager()
		{ }
		public static TransactionManager ManagerInstance
		{
			get
			{
				if (_sTransactionManager == null)
					_sTransactionManager = new TransactionManager();
				return _sTransactionManager;
			}
		}
		public bool TransactionAvailable { get { return (Trans.Count > 0); } }
		public void StartNewTransaction(DataBaseObject owner)
		{
			Trans.Push(new Transaction(owner));
		}
		public DataBaseObject ActiveTransactionOwner
		{
			get { return Trans.Peek().owner; }
		}
		public Transaction ActiveTransaction
		{
			get { return Trans.Peek(); }
		}

		public void CommitActive()
		{
			Trans.Peek().sqltransaction.Commit();
			Trans.Pop();
		}
		public void Roleback()
		{
			Trans.Peek().sqltransaction.Rollback();
			Trans.Pop();
		}
	}

	[Serializable]
	public class DataBaseObject : IDisposable
	{
		[NonSerialized]
		public string ConnectionString =ConfigurationManager.AppSettings.Get("ConnectionString"); // "Server={1};database={2};UID={3};PWD={4}";

		[NonSerialized]
		internal string TableName;
		private bool isDisposing = false;

		#region abstract Methods
		/// <summary>
		/// this is called to validate the required columns (Not nullable) before executeing the database command , the validation would throw an exception of type ValidationException if it fails
		/// </summary>
		protected virtual void OnValidate() { throw new NotImplementedException("OnValidate is not Implemented yet"); }

		/// <summary>
		/// it is called before the Insert event take place , this is the right place to have your business validation logic
		/// </summary>
		/// <param name="Cancel">set this to false in order to cancel the insert command</param>
		protected virtual void OnBeforeInsert(ref bool Cancel) { Cancel = false; }
		/// <summary>
		/// it is called before the Update event take place , this is the right place to have your business validation logic
		/// </summary>
		/// <param name="Cancel">set this to false in order to cancel the Update command</param>
		protected virtual void OnBeforeUpdate(ref bool Cancel) { Cancel = false; }
		/// <summary>
		/// it is called before the Delete event take place , this is the right place to have your business validation logic or other none DB clean up code.
		/// </summary>
		/// <param name="Cancel">set this to false in order to cancel the Delete command</param>
		protected virtual void OnBeforeDelete(ref bool Cancel) { Cancel = false; }

		/// <summary>
		/// this is where the actual code for the record insertion goes , do not override / change this unless you know what your doing
		/// </summary>        
		protected virtual bool OnInsert() { throw new NotImplementedException("OnInsert is not Implemented yet"); }
		/// <summary>
		/// this is where the actual code for the record update goes , do not override / change this unless you know what your doing
		/// </summary>        
		protected virtual bool OnUpdate() { throw new NotImplementedException("OnUpdate is not Implemented yet"); }
		/// <summary>
		/// this is where the actual code for the record deletion goes , do not override / change this unless you know what your doing
		/// </summary>        
		protected virtual bool OnDelete() { throw new NotImplementedException("OnDelete is not Implemented yet"); }

		/// <summary>
		/// this procedure is called if the insert has taken place and it is successful
		/// </summary>
		protected virtual void OnAfterInsert() { }
		/// <summary>
		/// this procedure is called if the Update has taken place and it is successful
		/// </summary>
		protected virtual void OnAfterUpdate() { }
		/// <summary>
		/// this procedure is called if the Delete has taken place and it is successful
		/// </summary>
		protected virtual void OnAfterDelete() { }

		public bool Insert()
		{
			bool res = false;
			bool isCanceled = false;
			OnValidate();
			OnBeforeInsert(ref isCanceled);
			if (!isCanceled)
			{
				res = OnInsert();
				if (res)
					OnAfterInsert();
			}
			return res;
		}
		public bool Update()
		{
			bool res = false;
			bool isCanceled = false;
			OnValidate();
			OnBeforeUpdate(ref isCanceled);
			if (!isCanceled)
				res = OnUpdate();
			if (res)
				OnAfterUpdate();
			return res;
		}
		public bool Delete()
		{
			bool res = false;
			bool isCanceled = false;
			OnBeforeDelete(ref isCanceled);
			if (!isCanceled)
				res = OnDelete();
			if (res)
				OnAfterDelete();
			return res;
		}
		#endregion

		#region IDisposable Members

		public void Dispose()
		{
			if (!isDisposing)
			{
				isDisposing = true;
				this.Close();
			}
		}



		#endregion

		[NonSerialized]
		protected SqlConnection oCon;
		private TransactionMode _TransactionType = TransactionMode.None;

		public TransactionMode TransactionType
		{
			get { return _TransactionType; }
			set { _TransactionType = value; }
		}
		//protected SqlTransaction oCurTransaction = null;
		protected bool IsLocalConnection = false;


		#region Connection Management

		protected void Open()
		{
			if (TransactionManager.ManagerInstance.TransactionAvailable)
				oCon = TransactionManager.ManagerInstance.ActiveTransactionOwner.oCon;
			if (oCon == null)
			{
				oCon = new SqlConnection(ConnectionString);
				oCon.Open();
				if (TransactionManager.ManagerInstance.TransactionAvailable && this == TransactionManager.ManagerInstance.ActiveTransactionOwner)
				{
					//oCurTransaction = oCon.BeginTransaction();
					TransactionManager.ManagerInstance.ActiveTransaction.sqltransaction = oCon.BeginTransaction();
				}
				IsLocalConnection = true;
			}
			else if (TransactionManager.ManagerInstance.TransactionAvailable && this != TransactionManager.ManagerInstance.ActiveTransactionOwner && IsLocalConnection == true)
			{
				TransactionManager.ManagerInstance.ActiveTransaction.sqltransaction = oCon.BeginTransaction();
				//oCurTransaction = oCon.BeginTransaction();
			}
			else
			{
				if (oCon.State == ConnectionState.Closed | oCon.State == ConnectionState.Broken)
					throw new Exception("DataBase Connection Broken");
				IsLocalConnection = false;
			}
		}

		protected void Close()
		{
			if (IsLocalConnection && !TransactionManager.ManagerInstance.TransactionAvailable)
				if (oCon != null && oCon.State != ConnectionState.Broken && oCon.State != ConnectionState.Closed)
				{
					oCon.Close();
					oCon.Dispose();
					oCon = null;
				}
		}

		#endregion

		#region Parameters Creation

		protected SqlParameter MakeParameter(string ParameterName, object Value)
		{
			SqlParameter Param = new SqlParameter(ParameterName, Value);
			Param.Direction = ParameterDirection.Input;
			return Param;
		}
		protected SqlParameter MakeImageParameter(string ParameterName, object Value)
		{
			SqlParameter Param = new SqlParameter(ParameterName, Value);
			Param.DbType = DbType.Binary;
			Param.Direction = ParameterDirection.Input;
			return Param;
		}
		protected SqlParameter MakeParameter(string ParameterName, object Value, ParameterDirection Direction)
		{
			SqlParameter Param = new SqlParameter(ParameterName, Value);
			Param.Direction = Direction;
			return Param;
		}

		protected SqlParameter MakeImageParameter(string ParameterName, object Value, ParameterDirection Direction)
		{
			SqlParameter Param = new SqlParameter(ParameterName, Value);
			Param.DbType = DbType.Binary;
			Param.Direction = Direction;
			return Param;
		}
		#endregion

		#region Sql Command Creation

		protected SqlCommand CreateCommand(string StoredProcedureName, SqlParameter[] Parameters)
		{
			SqlCommand cmd;

			Open();
			cmd = new SqlCommand(StoredProcedureName, oCon);
			cmd.CommandType = CommandType.StoredProcedure;
			if (Parameters != null)
				cmd.Parameters.AddRange(Parameters);

			//if (oCurTransaction != null)
			//    cmd.Transaction = oCurTransaction;
			if (TransactionManager.ManagerInstance.TransactionAvailable)
				cmd.Transaction = TransactionManager.ManagerInstance.ActiveTransaction.sqltransaction;

			return cmd;
		}

		protected SqlCommand CreateCommand(string CmdString)
		{
			SqlCommand cmd;
			Open();
			cmd = new SqlCommand(CmdString, oCon);

			//if (oCurTransaction != null)
			//    cmd.Transaction = oCurTransaction;
			if (TransactionManager.ManagerInstance.TransactionAvailable)
				cmd.Transaction = TransactionManager.ManagerInstance.ActiveTransaction.sqltransaction;
			return cmd;
		}

		#endregion

		#region Command Excution

		protected Int64 RunProcedure(string StoredProcedureName)
		{
			SqlCommand cmd = null;
			Int64 result;
			try
			{
				cmd = CreateCommand(StoredProcedureName, null);
				result = (Int64)cmd.ExecuteScalar();
			}
			catch (Exception ex)
			{
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			cmd.Dispose();
			cmd = null;
			return result;
		}

		protected object RunProcedure(string StoredProcedureName, SqlParameter[] SqlParameters)
		{
			SqlCommand cmd = null;
			object result;
			try
			{
				cmd = CreateCommand(StoredProcedureName, SqlParameters);
				result = cmd.ExecuteScalar();
			}
			catch (Exception ex)
			{
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			cmd.Dispose();
			cmd = null;
			return result;
		}

		protected void RunProcedure(string StoredProcedureName, ref SqlDataReader DataReader)
		{
			SqlCommand cmd = null;
			try
			{
				cmd = CreateCommand(StoredProcedureName, null);
				DataReader = cmd.ExecuteReader();
			}
			catch (Exception ex)
			{
				DataReader = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			cmd.Dispose();
			cmd = null;
		}

		protected void RunProcedure(string StoredProcedureName, SqlParameter[] Parameters, ref SqlDataReader Datareader)
		{
			SqlCommand cmd = null;
			try
			{
				cmd = CreateCommand(StoredProcedureName, Parameters);
				Datareader = cmd.ExecuteReader();
			}
			catch (Exception ex)
			{
				Datareader = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			cmd.Dispose();
			cmd = null;
		}

		protected void RunProcedure(string StoredProcedureName, SqlParameter[] Parameters, DataSet SqlDataSet)
		{
			SqlCommand cmd = null;
			SqlDataAdapter sqladb = null;
			try
			{
				cmd = CreateCommand(StoredProcedureName, Parameters);
				sqladb = new SqlDataAdapter(cmd);
				sqladb.Fill(SqlDataSet);
			}
			catch (Exception ex)
			{
				if (sqladb != null)
					sqladb.Dispose();
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			cmd.Dispose();
			sqladb.Dispose();
			sqladb = null;
			cmd = null;
		}

		protected void RunProcedure(string StoredProcedureName, DataSet SqlDataSet)
		{
			SqlCommand cmd = null;
			SqlDataAdapter sqladb = null;
			try
			{
				cmd = CreateCommand(StoredProcedureName, null);
				sqladb = new SqlDataAdapter(cmd);
				sqladb.Fill(SqlDataSet);
			}
			catch (Exception ex)
			{
				if (sqladb != null)
					sqladb.Dispose();
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			cmd.Dispose();
			sqladb.Dispose();
			sqladb = null;
			cmd = null;
		}

		#endregion

		#region Transaction Functions

		public void StartTransaction()
		{
			TransactionType = TransactionMode.RequireTransaction;
			TransactionManager.ManagerInstance.StartNewTransaction(this);
		}

		//public void EnlistInTransaction(DataBaseObject Obj)
		//{
		//    oCon = Obj.oCon;
		//    //oCurTransaction = Obj.oCurTransaction;
		//}

		public void Commit()
		{
			if (TransactionManager.ManagerInstance.TransactionAvailable)
			{
				TransactionManager.ManagerInstance.CommitActive();
				//oCurTransaction.Commit();
				//oCurTransaction.Dispose();
				//oCurTransaction = null;
				oCon.Close();
				oCon.Dispose();
			}
			else
			{
				this.Close();
				throw new Exception("No Transaction to Commit");
			}
		}

		public void RollBack()
		{
			if (TransactionManager.ManagerInstance.TransactionAvailable)
			{
				TransactionManager.ManagerInstance.Roleback();
				//oCurTransaction.Rollback();
				//oCurTransaction.Dispose();
				//oCurTransaction = null;
				oCon.Close();
				oCon.Dispose();
			}
			else
			{
				this.Close();
				throw new Exception("No Transaction to rollback");
			}
		}

		#endregion


		#region Public Methods
		public void RunSqlCommand(string SqlCmd, ref SqlDataReader DataReader)
		{
			SqlCommand cmd = null;
			try
			{
				cmd = CreateCommand(SqlCmd);
				DataReader = cmd.ExecuteReader();
			}
			catch (Exception ex)
			{
				DataReader = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			finally
			{
				if (cmd != null)
					cmd.Dispose();
				cmd = null;
			}

		}
		public void RunSqlCommand(string SqlCmd, SqlParameter[] Params, ref SqlDataReader DataReader)
		{
			SqlCommand cmd = null;
			try
			{
				cmd = CreateCommand(SqlCmd, Params);
				DataReader = cmd.ExecuteReader();
			}
			catch (Exception ex)
			{
				DataReader = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			finally
			{
				if (cmd != null)
					cmd.Dispose();
				cmd = null;
			}

		}
		public void RunSqlCommand(string SqlCmd, DataSet SqlDataSet)
		{
			SqlCommand cmd = null;
			SqlDataAdapter Sqladp;

			try
			{
				cmd = CreateCommand(SqlCmd);
				Sqladp = new SqlDataAdapter(cmd);
				Sqladp.Fill(SqlDataSet);
			}
			catch (Exception ex)
			{
				SqlDataSet = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			finally
			{
				if (cmd != null)
					cmd.Dispose();
				cmd = null;
				Sqladp = null;
			}
		}


		public void RunSqlCommand(string SqlCmd, SqlParameter[] Params, DataSet SqlDataSet)
		{
			SqlCommand cmd = null;
			SqlDataAdapter Sqladp;

			try
			{
				cmd = CreateCommand(SqlCmd, Params);
				Sqladp = new SqlDataAdapter(cmd);
				Sqladp.Fill(SqlDataSet);
			}
			catch (Exception ex)
			{
				SqlDataSet = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			finally
			{
				if (cmd != null)
					cmd.Dispose();
				cmd = null;
				Sqladp = null;
			}
		}

		public void RunSqlCommand(string SqlCmd, DataSet SqlDataSet, string TableName)
		{
			SqlCommand cmd = null;
			SqlDataAdapter Sqladp;

			try
			{
				cmd = CreateCommand(SqlCmd);
				Sqladp = new SqlDataAdapter(cmd);
				Sqladp.Fill(SqlDataSet, TableName);
			}
			catch (Exception ex)
			{
				SqlDataSet = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			finally
			{
				if (cmd != null)
					cmd.Dispose();
				cmd = null;
				Sqladp = null;
			}
		}

		public static void RunStaticSqlCommand(string SqlCmd, DataSet SqlDataSet, string TableName)
		{
			SqlCommand cmd = null;
			SqlDataAdapter Sqladp;
			DataBaseObject dbobj = new DataBaseObject();
			dbobj.TableName = TableName;
			try
			{
				cmd = dbobj.CreateCommand(SqlCmd);
				Sqladp = new SqlDataAdapter(cmd);
				Sqladp.Fill(SqlDataSet, TableName.Replace("[", "").Replace("]", ""));
			}
			catch (Exception ex)
			{
				SqlDataSet = null;
				if (cmd != null)
					cmd.Dispose();
				throw ex;
			}
			finally
			{
				if (cmd != null)
					cmd.Dispose();
				cmd = null;
				Sqladp = null;
			}
		}

		public DataSet ListAll()
		{
			DataSet ds = new DataSet();
			RunSqlCommand("Select * from " + TableName, ds, TableName);
			return ds;
		}
		public DataSet ListTop(int count)
		{
			DataSet ds = new DataSet();
			RunSqlCommand("Select top("+count.ToString()+") * from " + TableName, ds, TableName);
			return ds;
		}

		public DataSet ListWhere(string FilterBy)
		{
			DataSet ds = new DataSet();
			RunSqlCommand("Select * from " + TableName + " where " + FilterBy, ds, TableName);
			return ds;
		}

		public DataSet ListTopWhere(string FilterBy,int count)
		{
			DataSet ds = new DataSet();
			RunSqlCommand("Select  top(" + count.ToString() + ") * from " + TableName + " where " + FilterBy, ds, TableName);
			return ds;
		}
		public SqlDataReader ListAllDataReader()
		{
			SqlDataReader Res = null;
			RunSqlCommand("Select * from " + TableName, ref Res);
			return Res;
		}
		public SqlDataReader ListTopDataReader(int count)
		{
			SqlDataReader Res = null;
			RunSqlCommand("Select  top(" + count.ToString() + ")  * from " + TableName, ref Res);
			return Res;
		}
		public SqlDataReader ListWhereDataReader(string FilterBy)
		{
			SqlDataReader Res = null;
			RunSqlCommand("Select * from " + TableName + " where " + FilterBy, ref Res);
			return Res;
		}
		public SqlDataReader ListTopWhereDataReader(string FilterBy,int count)
		{
			SqlDataReader Res = null;
			RunSqlCommand("Select  top(" + count.ToString() + ") * from " + TableName + " where " + FilterBy, ref Res);
			return Res;
		}

		#endregion

	}
}