public int executeNonQuery(string pstrSqlQuery, AuditLog_HeaderMaster objAuditLogHeader, bool isAuditRequired)
{
int lintTemp = 0;
using (SqlConnection con = new SqlConnection(mstrConnString))
{
con.Open();
SqlTransaction sqlTrans = con.BeginTransaction();
try
{
SqlCommand myCommand = new SqlCommand(pstrSqlQuery, con, sqlTrans);
myCommand.CommandTimeout = 600;
bool mustCloseConnection = false;
if ((CommandParameters != null) && (CommandParameters.Length > 0))
{
PrepareCommand(myCommand, con, sqlTrans, mDbCommandType, pstrSqlQuery, CommandParameters, out mustCloseConnection);
}
lintTemp = myCommand.ExecuteNonQuery();
if (isAuditRequired && objAuditLogHeader.Trans_Desc != "" && objAuditLogHeader.TblPrimaryKeys.Trim() != "")
{
myCommand = new SqlCommand("Txn_AuditLog", con, sqlTrans);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@TransTypeID", objAuditLogHeader.TransTypeID);
myCommand.Parameters.AddWithValue("@RecordedBy", objAuditLogHeader.RecordedBy);
myCommand.Parameters.AddWithValue("@RecordedByName", objAuditLogHeader.RecordedByName);
myCommand.Parameters.AddWithValue("@RecFor", objAuditLogHeader.RecFor);
myCommand.Parameters.AddWithValue("@RecForClientID", objAuditLogHeader.RecForClientID);
myCommand.Parameters.AddWithValue("@RecForPolicyID", objAuditLogHeader.RecForPolicyID);
myCommand.Parameters.AddWithValue("@RecForPolicyVerID", objAuditLogHeader.RecForPolicyVerID);
myCommand.Parameters.AddWithValue("@RecForIDAddnl", objAuditLogHeader.RecForIDAddnl);
myCommand.Parameters.AddWithValue("@ScrMenuCode", objAuditLogHeader.ScrMenuCode);
myCommand.Parameters.AddWithValue("@TblName", objAuditLogHeader.TblName);
myCommand.Parameters.AddWithValue("@TblPrimaryKeys", objAuditLogHeader.TblPrimaryKeys);
myCommand.Parameters.AddWithValue("@TblPrimaryKeysValues", objAuditLogHeader.TblPrimaryKeysValues);
myCommand.Parameters.AddWithValue("@Trans_Desc", objAuditLogHeader.Trans_Desc);
myCommand.Parameters.AddWithValue("@TransLogDetails", objAuditLogHeader.TransLogDetails);
myCommand.Parameters.AddWithValue("@IsEndorse", objAuditLogHeader.IsEndorse);
myCommand.Parameters.AddWithValue("@Trans_Desc_Text", objAuditLogHeader.Trans_Desc_Text);
myCommand.Parameters.AddWithValue("@IsRenew", objAuditLogHeader.IsRenew);
myCommand.ExecuteNonQuery();
}
sqlTrans.Commit();
}
catch (SqlException sqlError)
{
sqlTrans.Rollback();
throw sqlError;
}
finally
{
con.Close();
con.Dispose();
SqlConnection.ClearPool(con);
}
}
return(lintTemp);
}
public DataSet SaveAuditLog_HeaderDetail(AuditLog_HeaderMaster objAuditLog_HeaderMaster)
{
object[] parameters = new object[] {
objAuditLog_HeaderMaster.TransTypeID,
objAuditLog_HeaderMaster.RecordedBy,
objAuditLog_HeaderMaster.RecordedByName,
objAuditLog_HeaderMaster.RecFor,
objAuditLog_HeaderMaster.RecForClientID,
objAuditLog_HeaderMaster.RecForPolicyID,
objAuditLog_HeaderMaster.RecForPolicyVerID,
objAuditLog_HeaderMaster.RecForIDAddnl,
objAuditLog_HeaderMaster.ScrMenuCode,
objAuditLog_HeaderMaster.TblName,
objAuditLog_HeaderMaster.TblPrimaryKeys,
objAuditLog_HeaderMaster.TblPrimaryKeysValues,
objAuditLog_HeaderMaster.Trans_Desc,
objAuditLog_HeaderMaster.TransLogDetails,
objAuditLog_HeaderMaster.IsEndorse,
objAuditLog_HeaderMaster.Trans_Desc_Text,
objAuditLog_HeaderMaster.IsRenew
};
dataAccessDS = new DataAccess();
return(dataAccessDS.LoadDataSet(parameters, "Txn_AuditLog", "Txn_AuditLog"));
}
public DataTable getDataReader(string pstrSqlQuery, AuditLog_HeaderMaster objAuditLogHeader, bool isAuditRequired)
{
//closeSqlConn();
//Trace.Warn( ("GET DATA READER >>> " + pstrSqlQuery);
OpenConnection();
SqlDataAdapter da = null;
SqlTransaction sqlTrans = null;
DataTable dt = new DataTable();
string commandtext = pstrSqlQuery;
//Trace.Warn("Connection Timeout Period====" + lobjConnection.ConnectionTimeout.ToString());
try
{
//lobjConnection.Open();
sqlTrans = lobjConnection.BeginTransaction();
//SqlCommand myCommand = new SqlCommand(pstrSqlQuery, lobjConnection, sqlTrans);
SqlCommand myCommand = new SqlCommand();
myCommand.CommandTimeout = 0;
bool closeConn = false;
PrepareCommand(myCommand, lobjConnection, sqlTrans, mDbCommandType, commandtext, CommandParameters, out closeConn);
da = new SqlDataAdapter(myCommand);
da.Fill(dt);
if (dt.Columns.Contains("PrimaryKeyValue"))
{
if (objAuditLogHeader.TblPrimaryKeys.Trim() == "" && dt.Rows.Count > 0 && dt.Rows[0]["PrimaryKeyValue"] != null)
{
if (dt.Columns.Contains("Result") && (dt.Rows[0]["Result"].ToString().ToLower() == "y" || dt.Rows[0]["Result"].ToString().ToLower() == "1" || dt.Rows[0]["Result"].ToString().ToLower() == "true"))
{
objAuditLogHeader.TblPrimaryKeys = objAuditLogHeader.TblPrimaryKeysValues = dt.Rows[0]["PrimaryKeyValue"].ToString();
}
}
}
if (isAuditRequired && objAuditLogHeader.Trans_Desc != "" && objAuditLogHeader.TblPrimaryKeys.Trim() != "")
{
myCommand = new SqlCommand("Txn_AuditLog", lobjConnection, sqlTrans);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@TransTypeID", objAuditLogHeader.TransTypeID);
myCommand.Parameters.AddWithValue("@RecordedBy", objAuditLogHeader.RecordedBy);
myCommand.Parameters.AddWithValue("@RecordedByName", objAuditLogHeader.RecordedByName);
myCommand.Parameters.AddWithValue("@RecFor", objAuditLogHeader.RecFor);
myCommand.Parameters.AddWithValue("@RecForClientID", objAuditLogHeader.RecForClientID);
myCommand.Parameters.AddWithValue("@RecForPolicyID", objAuditLogHeader.RecForPolicyID);
myCommand.Parameters.AddWithValue("@RecForPolicyVerID", objAuditLogHeader.RecForPolicyVerID);
myCommand.Parameters.AddWithValue("@RecForIDAddnl", objAuditLogHeader.RecForIDAddnl);
myCommand.Parameters.AddWithValue("@ScrMenuCode", objAuditLogHeader.ScrMenuCode);
myCommand.Parameters.AddWithValue("@TblName", objAuditLogHeader.TblName);
myCommand.Parameters.AddWithValue("@TblPrimaryKeys", objAuditLogHeader.TblPrimaryKeys);
myCommand.Parameters.AddWithValue("@TblPrimaryKeysValues", objAuditLogHeader.TblPrimaryKeysValues);
myCommand.Parameters.AddWithValue("@Trans_Desc", objAuditLogHeader.Trans_Desc);
myCommand.Parameters.AddWithValue("@TransLogDetails", objAuditLogHeader.TransLogDetails);
myCommand.Parameters.AddWithValue("@IsEndorse", objAuditLogHeader.IsEndorse);
myCommand.Parameters.AddWithValue("@Trans_Desc_Text", objAuditLogHeader.Trans_Desc_Text);
myCommand.Parameters.AddWithValue("@IsRenew", objAuditLogHeader.IsRenew);
myCommand.ExecuteNonQuery();
}
sqlTrans.Commit();
}
catch (SqlException sqlError)
{
sqlTrans.Rollback();
throw sqlError;
}
finally
{
//lobjConnection.Close();
CloseSqlConnection();
}
return(dt);
}