public void OpenDataSetThroughAdapter(String strSquery, ref System.Data.DataSet dsSetRef, CommandType cmdType,
Boolean blnRequiredTransaction, Boolean blnJoinedQuery, String strActualQuery, String strConName)
{
System.Data.IDbDataAdapter IDBAdpater;
System.Data.IDbCommand IMainCommand;
dsSetRef = new DataSet();
Boolean blnBeginTrans = false;
try
{
if ((strSquery.Trim()).Length == 0)
throw new Exception("Query is blank");
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = new SqlCommand();
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = new OleDbCommand();
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = new OdbcCommand();
}
else
{
IMainCommand = null;
}
IMainCommand.CommandTimeout = CommandTimeOutValue;
if (blnRequiredTransaction == false)
{
if (strConName.Length > 0)
{
OpenConnection(strConName);
}
else
{
OpenConnection(String.Empty);
}
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
if (mblnTransactionStart)
{
blnBeginTrans = true;
}
}
if (disconnection.IsNotNull() && disconnection.State == ConnectionState.Open)
{
IMainCommand.CommandText = strSquery;
IMainCommand.CommandType = cmdType;
IMainCommand.Connection = disconnection;
if (blnBeginTrans)
{
IMainCommand.Transaction = transaction;
}
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IDBAdpater = new SqlDataAdapter((SqlCommand)IMainCommand);
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IDBAdpater = new OleDbDataAdapter((OleDbCommand)IMainCommand);
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IDBAdpater = new OdbcDataAdapter((OdbcCommand)IMainCommand);
}
else
{
IDBAdpater = null;
}
//IDBAdpater.FillSchema(dsSetRef,SchemaType.Mapped);
IDBAdpater.Fill(dsSetRef);
foreach (DataTable dt in dsSetRef.Tables)
{
dt.ExtendedProperties.Add(Query, strSquery);
dt.ExtendedProperties.Add(UpdateQuery, strActualQuery);
dt.ExtendedProperties.Add(JoinedQuery, blnJoinedQuery);
}
}
if (blnRequiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
}
return;
}
catch (System.Data.OleDb.OleDbException OleDbEx)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
}
throw (OleDbEx);
}
catch (System.Exception ex)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
}
throw ex;
}
finally
{
if (blnRequiredTransaction.IsFalse() && disconnection.IsNotNull())
{
disconnection.Close();
disconnection.Dispose();
disconnection = null;
mblnTransactionStart = false;
}
IMainCommand = null;
IDBAdpater = null;
}
}
public void OpenDataReader(String query, out IDataReader readerRef,
CommandBehavior behaviour, Boolean requiredTransaction, String procedureName,
IDbDataParameter[] oleDbParameterArray, String conName)
{
IDbCommand mainCommand;
try
{
if ((procedureName.Trim()).Length.IsZero())
{
if ((query.Trim()).Length.IsZero())
throw new Exception("Query is blank");
}
switch (ProviderType)
{
case Util.ConnectionLibrary.SQlClient:
mainCommand = new SqlCommand();
break;
case Util.ConnectionLibrary.Oledb:
mainCommand = new OleDbCommand();
break;
case Util.ConnectionLibrary.ODBC:
mainCommand = new OdbcCommand();
break;
default:
mainCommand = null;
break;
}
if (mainCommand.IsNull())
{
readerRef = null;
return;
}
if (requiredTransaction.IsFalse())
{
if (conName.Length > 0)
{
OpenConnection(conName);
}
else
{
OpenConnection(String.Empty);
}
}
else
{
mainCommand.Transaction = transaction;
}
if ((procedureName.Trim()).Length.IsZero())
{
mainCommand.CommandType = CommandType.Text;
mainCommand.CommandText = query;
}
else if (oleDbParameterArray.IsNull() && (procedureName.Trim()).Length > 0)
{
mainCommand.CommandType = CommandType.StoredProcedure;
mainCommand.CommandText = procedureName;
}
else if (oleDbParameterArray.IsNotNull() && (procedureName.Trim()).Length > 0)
{
mainCommand.CommandType = CommandType.StoredProcedure;
mainCommand.CommandText = procedureName;
for (Int32 i = 0; i < oleDbParameterArray.Length; i++)
{
if (oleDbParameterArray[i].IsNotNull())
{
mainCommand.Parameters.Add(oleDbParameterArray[i]);
}
}
}
else
{
throw new Exception("Could Not Create the Command Object");
}
mainCommand.CommandTimeout = CommandTimeOutValue;
mainCommand.Connection = disconnection;
readerRef = mainCommand.ExecuteReader(behaviour);
}
catch (Exception ex)
{
CloseConnection();
throw (ex);
}
}
public ConnectionManager(String strConn, Boolean isConnectionString, Util.ConnectionLibrary eType)
{
String connectionString = String.Empty;
if (isConnectionString.IsFalse())
{
ProvideConnectionInfo(strConn, ref connectionString, ref eType);
}
else
{
connectionString = strConn;
mblnIsConnectionString = true;
}
if (connectionString.Trim().Length > 0)
{
Initailize(connectionString, eType);
}
else
{
throw new Exception("DAL is not configured properly\n.There is no link between Application Server and Database Server. Please configure the Application Server properly or ask your MIS Officer or technical person to do so.");
}
}
public Object ExecuteScalarWrapper(Boolean requiredTransaction, String conName, String spName, params Object[] parameterValues)
{
Boolean blnBeginTrans = false;
try
{
if ((spName.Trim()).Length.IsZero())
throw new Exception("spName is blank");
Object objRtValue = null;
if (requiredTransaction.IsFalse())
{
if (conName.Length > 0)
{
OpenConnection(conName);
}
else
{
OpenConnection(String.Empty);
}
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
blnBeginTrans = true;
}
objRtValue = DataAccessHelper.ExecuteScalar(transaction, spName, parameterValues);
if (requiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
}
return objRtValue;
}
catch (Exception ex)
{
if (requiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Rollback();
}
}
throw (ex);
}
finally
{
if (requiredTransaction.IsFalse())
{
CloseConnection();
}
}
}
// As OpenDataSetThroughAdapter and ExecuteNonQueryWrapper, this mothod also modified
// so that it can able to hold a transaction for store procedure containing multiple
// action query. So that it is now not required to make explicite transaction for DAL
// from the business layer. (DATE: Sat, 18 June, 2005)
public Object ExecuteScalarWrapper(String query, Boolean requiredTransaction, String conName)
{
IDbCommand mainCommand;
Boolean blnBeginTrans = false;
try
{
if ((query.Trim()).Length.IsZero())
throw new Exception("Query is blank");
switch (ProviderType)
{
case Util.ConnectionLibrary.SQlClient:
mainCommand = new SqlCommand();
break;
case Util.ConnectionLibrary.Oledb:
mainCommand = new OleDbCommand();
break;
case Util.ConnectionLibrary.ODBC:
mainCommand = new OdbcCommand();
break;
default:
mainCommand = null;
break;
}
object objRtValue = null;
if (mainCommand.IsNotNull())
{
mainCommand.CommandTimeout = CommandTimeOutValue;
if (requiredTransaction.IsFalse())
{
if (conName.Length > 0)
{
OpenConnection(conName);
}
else
{
OpenConnection(String.Empty);
}
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
mainCommand.Transaction = transaction;
}
else
{
mainCommand.Transaction = transaction;
blnBeginTrans = true;
}
mainCommand.Connection = disconnection;
mainCommand.CommandText = query;
objRtValue = mainCommand.ExecuteScalar();
}
if (requiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
}
return objRtValue;
}
catch (Exception ex)
{
if (requiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Rollback();
}
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (ex);
}
finally
{
if (requiredTransaction.IsFalse())
{
CloseConnection();
}
}
}
public void SaveDataSetThroughAdapter(System.Data.DataSet dsSetRef,
Boolean blnRequiredTransaction, String ExcludeTableName, String strConName)
{
Boolean blnBeginTrans = false;
OleDbDataAdapter objOleDBAdpater;
OdbcDataAdapter objOdbcDBAdpater;
SqlDataAdapter objSqlDBAdpater;
OdbcCommandBuilder objOdbcDBCommandBuilder;
OleDbCommandBuilder objOleDBCommandBuilder;
SqlCommandBuilder objSqlDBCommandBuilder;
IDbCommand IMainCommand;
DataTable dtInsert;
DataTable dtUpdate;
DataTable dtDelete;
Boolean TableExist;
String strQuery;
try
{
if (dsSetRef == null)
{
throw new Exception("DataSet not Initialized");
}
String[] TableName;
char[] delimeter;
String seperator;
seperator = ",";
delimeter = seperator.ToCharArray();
TableName = ExcludeTableName.Split(delimeter);
if (blnRequiredTransaction.IsFalse())
{
if (strConName.Length > 0)
{
OpenConnection(strConName);
}
else
{
OpenConnection(String.Empty);
}
}
if (disconnection.IsNotNull())
{
if (blnRequiredTransaction.IsFalse())
{
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
if (transaction == null)
{
throw new Exception("Transaction is not initialized");
}
else
{
blnBeginTrans = true;
}
}
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = new SqlCommand();
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = new OleDbCommand();
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = new OdbcCommand();
}
else
{
IMainCommand = null;
}
IMainCommand.Connection = disconnection;
IMainCommand.Transaction = transaction;
}
else
{
throw new Exception("Connection is not initialized");
}
IMainCommand.CommandTimeout = CommandTimeOutValue;
foreach (DataTable dtRef in dsSetRef.Tables)
{
TableExist = false;
foreach (String tablename in TableName)
{
if (dtRef.TableName.ToUpper() == tablename.ToUpper())
{
TableExist = true;
break;
}
}
if (TableExist) continue;
if ((Boolean)dtRef.ExtendedProperties[JoinedQuery])
{
strQuery = dtRef.ExtendedProperties[UpdateQuery].ToString();
}
else
{
strQuery = dtRef.ExtendedProperties[Query].ToString();
}
if ((strQuery.Trim()).Length == 0)
{
throw new Exception("Query is blank");
}
IMainCommand.CommandText = strQuery;
dtInsert = dtRef.GetChanges(DataRowState.Added);
dtUpdate = dtRef.GetChanges(DataRowState.Modified);
dtDelete = dtRef.GetChanges(DataRowState.Deleted);
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
objSqlDBAdpater = new SqlDataAdapter((SqlCommand)IMainCommand);
objSqlDBCommandBuilder = new SqlCommandBuilder(objSqlDBAdpater);
if (dtDelete.IsNotNull())
{
objSqlDBCommandBuilder.GetDeleteCommand();
objSqlDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
if (dtInsert.IsNotNull())
{
objSqlDBCommandBuilder.GetInsertCommand();
objSqlDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objSqlDBCommandBuilder.GetUpdateCommand();
objSqlDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
objOleDBAdpater = new OleDbDataAdapter((OleDbCommand)IMainCommand);
objOleDBCommandBuilder = new OleDbCommandBuilder(objOleDBAdpater);
if (dtInsert.IsNotNull())
{
objOleDBCommandBuilder.GetInsertCommand();
objOleDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objOleDBCommandBuilder.GetUpdateCommand();
objOleDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
if (dtDelete.IsNotNull())
{
objOleDBCommandBuilder.GetDeleteCommand();
objOleDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
objOdbcDBAdpater = new OdbcDataAdapter((OdbcCommand)IMainCommand);
objOdbcDBCommandBuilder = new OdbcCommandBuilder(objOdbcDBAdpater);
if (dtInsert.IsNotNull())
{
objOdbcDBCommandBuilder.GetInsertCommand();
objOdbcDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objOdbcDBCommandBuilder.GetUpdateCommand();
objOdbcDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
if (dtDelete.IsNotNull())
{
objOdbcDBCommandBuilder.GetDeleteCommand();
objOdbcDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
}
else
{
objSqlDBAdpater = null;
objOleDBAdpater = null;
objOdbcDBAdpater = null;
objSqlDBCommandBuilder = null;
objOleDBCommandBuilder = null;
objOdbcDBCommandBuilder = null;
}
}
if (blnRequiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
disconnection.Close();
disconnection.Dispose();
disconnection = null;
}
}
catch (System.Data.OleDb.OleDbException exOleDb)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exOleDb);
}
catch (System.Data.DBConcurrencyException exDBCE)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exDBCE);
}
catch (System.Exception ex)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (ex);
}
finally
{
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = null;
objSqlDBAdpater = null;
objSqlDBCommandBuilder = null;
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = null;
objOleDBAdpater = null;
objOleDBCommandBuilder = null;
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = null;
objOdbcDBAdpater = null;
objOdbcDBCommandBuilder = null;
}
}
}
public void SaveDataCollectionThroughCollection(Boolean requiredTransaction, String conName, params IEnumerable[] saveItems)
{
CommandExecutor command;
Boolean blnBeginTrans = false;
CustomList<BaseItem> itemNew;
CustomList<BaseItem> itemUpdate;
CustomList<BaseItem> itemDelete;
try
{
if (saveItems.IsNull() || saveItems.Count().IsZero())
{
throw new Exception("Collection not Initialized");
}
if (requiredTransaction.IsFalse())
{
if (conName.Length > 0)
{
OpenConnection(conName);
}
else
{
OpenConnection(String.Empty);
}
}
if (disconnection.IsNotNull())
{
if (requiredTransaction.IsFalse())
{
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
if (transaction.IsNull())
{
throw new Exception("Transaction is not initialized");
}
blnBeginTrans = true;
}
}
else
{
throw new Exception("Connection is not initialized");
}
//For Delete
saveItems = saveItems.Reverse();
foreach (IEnumerable saveList in saveItems)
{
CustomList<BaseItem> saveItemBase = saveList.ToCustomList<BaseItem>();
itemDelete = saveItemBase.GetChanges(ItemState.Deleted);
switch (ProviderType)
{
case Util.ConnectionLibrary.SQlClient:
command = new CommandExecutor(transaction, ProviderType);
if (itemDelete.IsNotNull() && itemDelete.Count.NotEquals(0))
{
command.Update(itemDelete, Util.OperationType.Delete);
}
break;
}
}
//For Insert Update
saveItems = saveItems.Reverse();
foreach (IEnumerable saveList in saveItems)
{
CustomList<BaseItem> saveItemBase = saveList.ToCustomList<BaseItem>();
itemNew = saveItemBase.GetChanges(ItemState.Added);
itemUpdate = saveItemBase.GetChanges(ItemState.Modified);
switch (ProviderType)
{
case Util.ConnectionLibrary.SQlClient:
command = new CommandExecutor(transaction, ProviderType);
if (itemNew.IsNotNull() && itemNew.Count.NotEquals(0))
{
command.Update(itemNew, Util.OperationType.Insert);
}
if (itemUpdate.IsNotNull() && itemUpdate.Count.NotEquals(0))
{
command.Update(itemUpdate, Util.OperationType.Update);
}
break;
}
}
if (requiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
disconnection.Close();
disconnection.Dispose();
disconnection = null;
}
}
catch (OleDbException exOleDb)
{
if (blnBeginTrans && requiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exOleDb);
}
catch (DBConcurrencyException exDbce)
{
if (blnBeginTrans && requiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exDbce);
}
catch (Exception ex)
{
if (blnBeginTrans && requiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (ex);
}
}
//zaki - Insert record and get Scope_Identity()
public object SaveDataCollectionThroughCollection(Boolean requiredTransaction, String conName, Util.CrudType opMode, IEnumerable saveItem)
{
CommandExecutor command;
Boolean blnBeginTrans = false;
CustomList<BaseItem> itemNew;
try
{
if (saveItem.IsNull())
{
throw new Exception("Collection not Initialized");
}
if (requiredTransaction.IsFalse())
{
if (conName.Length > 0)
{
OpenConnection(conName);
}
else
{
OpenConnection(String.Empty);
}
}
if (disconnection.IsNotNull())
{
if (requiredTransaction.IsFalse())
{
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
if (transaction.IsNull())
{
throw new Exception("Transaction is not initialized");
}
blnBeginTrans = true;
}
}
else
{
throw new Exception("Connection is not initialized");
}
CustomList<BaseItem> saveItemBase = saveItem.ToCustomList<BaseItem>();
itemNew = saveItemBase.GetChanges(ItemState.Added);
object retVal = null;
switch (ProviderType)
{
case Util.ConnectionLibrary.SQlClient:
command = new CommandExecutor(transaction, ProviderType);
if (itemNew.IsNotNull() && itemNew.Count.NotEquals(0))
{
retVal = command.Insert(itemNew, Util.OperationType.Insert);
}
break;
default: break;
}
if (requiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
disconnection.Close();
disconnection.Dispose();
disconnection = null;
}
return retVal;
}
catch (OleDbException exOleDb)
{
if (blnBeginTrans && requiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exOleDb);
}
catch (DBConcurrencyException exDbce)
{
if (blnBeginTrans && requiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exDbce);
}
catch (Exception ex)
{
if (blnBeginTrans && requiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (ex);
}
}