using System;
using System.Data;
using System.Data.Common;
using Framework.Lib;
namespace Framework.DAL
{
/// <summary>
/// Facade Design Pattern
/// This DBase class is being used in outside applications to get
/// access to default database defined in setting file through our
/// Connection Factory Object
///
/// Note:
/// Do not create and use DB objects here, always use
/// DBObjectFactory to have a Database object
///
/// Also we can use DBObjectFactory to get MilDB objects
/// outside of this lib but it is recomended to use this
/// facade object just for simplicity
/// </summary>
#region TransactionEventArgs
public class TransactionEventArgs: EventArgs
{
public enum TransactionTypeName { Commited, RolledBack };
public IDbTransaction iDbTransaction ;
public TransactionTypeName TransactionType;
public object ApplicationID;
public string TableHoldingApplicationID;
public string PropagateTo;
public string CalledFrom;
public string PropagateMethodName;
public object[] PropagateMethodParams;
public TransactionEventArgs( TransactionTypeName status, IDbTransaction theDbTransaction)
{
this.PropagateMethodName = null;
this.PropagateMethodParams = null;
this.TableHoldingApplicationID = null;
this.ApplicationID = null;
this.iDbTransaction = theDbTransaction;
this.TransactionType = status;
this.PropagateTo = null;
this.CalledFrom = null;
}
public TransactionEventArgs( TransactionTypeName status, object applicationID, string tableHoldingApplicationID, IDbTransaction theDbTransaction)
{
this.PropagateMethodName = null;
this.PropagateMethodParams = null;
this.TableHoldingApplicationID = tableHoldingApplicationID;
this.ApplicationID = applicationID;
this.iDbTransaction = theDbTransaction;
this.TransactionType = status;
this.PropagateTo = null;
this.CalledFrom = null;
}
public TransactionEventArgs( TransactionTypeName status, object applicationID, string tableHoldingApplicationID, string propagateTo, string CalledFrom, string propagateMethodName, object[] propagateMethodParams, IDbTransaction theDbTransaction)
{
this.PropagateMethodName = propagateMethodName;
this.PropagateMethodParams = propagateMethodParams;
this.TableHoldingApplicationID = tableHoldingApplicationID;
this.ApplicationID = applicationID;
this.iDbTransaction = theDbTransaction;
this.TransactionType = status;
this.PropagateTo = propagateTo;
this.CalledFrom = CalledFrom;
}
} //end of class TransactionEventArgs
#endregion
public class DBase
{
private DbConnection conn;
#region Database Transactions
private IDbTransaction iDbTransaction = null;
#region Event definitions
// Definition of the Event that would be fired when the current transaction is being finnished
public delegate void TransactionEventHandler(object source, TransactionEventArgs rea);
public event TransactionEventHandler TransactionEvent;
private void OnTransactionEvent(object source, TransactionEventArgs rea)
{
try
{
if ((TransactionEvent != null) && (iDbTransaction != null))
{
this.TransactionEvent(source, rea);
}
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
public IDbTransaction BeginTransaction()
{
try
{
iDbTransaction = conn.BeginTransaction();
return iDbTransaction;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
public void CommitTransaction()
{
try
{
OnTransactionEvent(this, new TransactionEventArgs(TransactionEventArgs.TransactionTypeName.Commited, iDbTransaction));
iDbTransaction.Commit();
iDbTransaction = null;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
public void CommitTransaction(object ID, string Table4ID)
{
try
{
OnTransactionEvent(this, new TransactionEventArgs(TransactionEventArgs.TransactionTypeName.Commited, ID, Table4ID, iDbTransaction));
iDbTransaction.Commit();
iDbTransaction = null;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
public void CommitTransaction(object ID, string Table4ID, string PropagateTo, string CalledFrom, string propagateMethodName, object[] propagateMethodParams)
{
try
{
OnTransactionEvent(this, new TransactionEventArgs(TransactionEventArgs.TransactionTypeName.Commited, ID, Table4ID, PropagateTo, CalledFrom, propagateMethodName, propagateMethodParams, iDbTransaction));
iDbTransaction.Commit();
iDbTransaction = null;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
public void RollbackTransaction()
{
try
{
OnTransactionEvent(this, new TransactionEventArgs(TransactionEventArgs.TransactionTypeName.RolledBack, iDbTransaction));
iDbTransaction.Rollback();
iDbTransaction = null;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region Fill a DataSet from a Table
/// <summary>
/// Open database, query a table
/// and fill the related dataset
/// </summary>
/// <param name="tableName"> The table that is in both DataSet and Database </param>
/// <param name="ds">The dataset to be filled</param>
/// <returns>The number of rows added</returns>
public int FillDataSet(DataTable dataTable, string filter)
{
int i = -1;
try
{
DbDataAdapter adapter = DBObjectFactory.CreateDBDataAdapter();
string query;
if (filter != null && filter.Trim() != "")
query = "Select * from " + dataTable.TableName + " where " + filter;
else
query = "Select * from " + dataTable.TableName;
adapter.SelectCommand = DBObjectFactory.CreateDBCommand(query, conn);
adapter.TableMappings.Add("Table", dataTable.TableName);
i = adapter.Fill(dataTable);
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
return i;
}
#endregion
#region Fill a DataSet from a Table
/// <summary>
/// Open database, query a table
/// and fill the related dataset
/// </summary>
/// <param name="tableName"> The table that is in both DataSet and Database </param>
/// <param name="ds">The dataset to be filled</param>
/// <returns>The number of rows added</returns>
public int FillDataSet(DataTable dataTable)
{
int i = -1;
try
{
DbDataAdapter adapter = DBObjectFactory.CreateDBDataAdapter();
string query = "Select * from " + dataTable.TableName;
adapter.SelectCommand = DBObjectFactory.CreateDBCommand(query, conn);
//adapter.TableMappings.Add("Table", tableName);
i = adapter.Fill(dataTable);
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
return i;
}
#endregion
#region Fill a DataSet from a Table
/// <summary>
/// Open database, query a table
/// and fill the related dataset
/// </summary>
/// <param name="tableName"> The table that is in both DataSet and Database </param>
/// <param name="ds">The dataset to be filled</param>
/// <returns>The number of rows added</returns>
public int FillDataSet(DataSet ds, string tableName)
{
int i = -1;
try
{
DbDataAdapter adapter = DBObjectFactory.CreateDBDataAdapter();
string query = "Select * from " + tableName;
adapter.SelectCommand = DBObjectFactory.CreateDBCommand(query, conn);
//adapter.TableMappings.Add("Table", tableName);
i = adapter.Fill(ds,tableName);
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
return i;
}
#endregion
#region Fill a DataSet from a Table
/// <summary>
/// Open database, query a table
/// and fill the related dataset
/// </summary>
/// <param name="tableName"> The table that is in both DataSet and Database </param>
/// <param name="ds">The dataset to be filled</param>
/// <returns>The number of rows added</returns>
public int FillDataSet(DataSet ds, string tableName, string filter)
{
int i = -1;
try
{
DbDataAdapter adapter = DBObjectFactory.CreateDBDataAdapter();
string query;
if (filter != null && filter.Trim() != "")
query = "Select * from " + tableName + " where " + filter;
else
query = "Select * from " + tableName;
adapter.SelectCommand = DBObjectFactory.CreateDBCommand(query, conn);
adapter.TableMappings.Add("Table", tableName);
i = adapter.Fill(ds);
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
return i;
}
#endregion
#region Open and return a table
public DataTable openTable (string tableName)
{
DbDataAdapter adapter = DBObjectFactory.CreateDBDataAdapter();
DataTable dtable = null;
string query = "Select * from " + tableName;
adapter.SelectCommand = DBObjectFactory.CreateDBCommand(query, conn);
DataSet dset = new DataSet ("mydata");
try {
//this.open();
adapter.Fill (dset);
dtable = dset.Tables [0];
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
return dtable;
}
#endregion
#region get a generic DataAdapter
public DbDataAdapter CreateAdapter()
{
try
{
return DBObjectFactory.CreateDBDataAdapter();
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region get a generic Command
public DbCommand CreateCommand()
{
try
{
return DBObjectFactory.CreateDBCommand();
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region get a generic Parameter
public object CreateParameter()
{
try
{
return DBObjectFactory.CreateDBParameter();
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
public object CreateParameter(string parameterName,
MilDbType dbType,
int size,
ParameterDirection direction,
byte precision,
byte scale,
string sourceColumn,
DataRowVersion sourceVersion,
bool sourceColumnNullMapping,
Object value,
string xmlSchemaCollectionDatabase,
string xmlSchemaCollectionOwningSchema,
string xmlSchemaCollectionName)
{
try
{
return DBObjectFactory.CreateDBParameter(parameterName,
dbType,
size,
direction,
precision,
scale,
sourceColumn,
sourceVersion,
sourceColumnNullMapping,
value,
xmlSchemaCollectionDatabase,
xmlSchemaCollectionOwningSchema,
xmlSchemaCollectionName);
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region Return a table from a SQL query
public DataTable openQuery(string query)
{
DbDataAdapter dsCmd = DBObjectFactory.CreateDBDataAdapter();
DataSet dset = new DataSet (); //create a dataset
DataTable dtable = null; //declare a data table
try {
//create the command
dsCmd.SelectCommand = DBObjectFactory.CreateDBCommand(query, conn);
//this.open(); //open the connection
//fill the dataset
dsCmd.Fill(dset);
//get the table
dtable = dset.Tables[0];
//this.close(); //always close it
return dtable; //and return it
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region Execute a DML SQL query
public bool ExecuteQuery(string query)
{
try
{
DbCommand dsCmd = DBObjectFactory.CreateDBCommand(query,conn);
if(iDbTransaction!=null)
{
dsCmd.Transaction = (System.Data.Common.DbTransaction)iDbTransaction;
}
if( dsCmd.ExecuteNonQuery()>0)
return true;
else
return false;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region Execute a DML SQL query and return first row
public object ExecuteScalar(string query)
{
try
{
DbCommand dsCmd = DBObjectFactory.CreateDBCommand(query,conn);
return dsCmd.ExecuteScalar();
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region Return a DataReader from a SQL query
public DbDataReader openDataReader(string query)
{
try
{
DbCommand theCommand = DBObjectFactory.CreateDBCommand(query, conn);
return (DbDataReader)theCommand.ExecuteReader();
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region open
// Open a DBConnection to work with
public void open()
{
try
{
if (conn == null)
{
conn = DBObjectFactory.CreateDBConnection(); // Get a Generic Database connection
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region close
public void close()
{
try
{
if ((conn != null) && (conn.State == ConnectionState.Open))
{
iDbTransaction = null;
conn.Close();
conn = null;
}
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
#region Connection
public DbConnection Connection
{
get
{
try
{
return DBObjectFactory.CreateDBConnection(); // Get a Generic Database connection
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
}
#endregion
#region DatabaseName
public string DatabaseName
{
get
{
try
{
return DBObjectFactory.DatabaseName;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
}
#endregion
#region DBHostName
public string DBHostName
{
get
{
try
{
return DBObjectFactory.DBHostName;
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
}
#endregion
// Application specific methods
#region Check if a Value exist in column of a table
public bool IsValueExistsInTable( string TblName, string ColName, object Val )
{
try
{
DbCommand theCommand = DBObjectFactory.CreateDBCommand();
theCommand.Connection = conn;
return false;// theCommand.ExistsInTable(TblName, ColName, Val);
}
catch (Exception baExp)
{
throw new baDALException(baExp, System.Reflection.MethodBase.GetCurrentMethod());
}
}
#endregion
}
}
Submit an article or tip