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); }