public DataSet PaymentTypeSearch(string searchText, int pageOffset, int pageSize, string sortColumn = "PAYMENT_TYPE_CODE", bool sortOrder = false)
{
TalentDataAccess talentSqlAccessDetail = new TalentDataAccess();
ErrorObj err = new ErrorObj();
DataSet paymentTypeData = new DataSet();
var strQuery = "SELECT COUNT(*) FROM tbl_payment_type WHERE PAYMENT_TYPE_DESCRIPTION LIKE @SearchText; SELECT PAYMENT_TYPE_CODE AS ID, PAYMENT_TYPE_DESCRIPTION AS DESCRIPTION, \'tbl_payment_type\' as TABLE_NAME FROM tbl_payment_type WHERE PAYMENT_TYPE_DESCRIPTION like @SearchText";
try
{
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandText = strQuery;
talentSqlAccessDetail.CommandElements.CommandParameter.Clear();
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@SearchText", "%" + searchText + "%"));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
paymentTypeData = talentSqlAccessDetail.ResultDataSet;
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(paymentTypeData);
}
/// <summary>
/// Functionality to provide the gateway to access the Data Access Layer
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="allowSerialize"></param>
/// <returns></returns>
/// <remarks></remarks>
public ErrorObj SQLAccess(DestinationDatabase destinationDatabase, bool allowSerialize = true)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDataAccess DBDataAccessEntity = new DBDataAccess();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
//Utilities.LogSQL(DBDataAccessEntity.CommandElement);
err = DBDataAccessEntity.AccessDatabase();
if (!err.HasError && !(DBDataAccessEntity.ResultDataSet == null))
{
ResultDataSet = DBDataAccessEntity.ResultDataSet;
// Serialize the transaction
if (allowSerialize && this.CommandElements.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
SetConnectionStringParameters();
Utilities.SerializeTransaction(this, Settings);
}
}
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected ErrorObj TALENTTKTOpen()
{
ErrorObj err = new ErrorObj();
//---------------------------------------------------------------------------------
// Attempt to open database
//
string connectionString = string.Empty;
connectionString = Settings.BackOfficeConnectionString;
try
{
if (conTALENTTKT == null)
{
conTALENTTKT = new iDB2Connection(connectionString);
conTALENTTKT.Open();
}
else if (conTALENTTKT.State != ConnectionState.Open)
{
conTALENTTKT = new iDB2Connection(connectionString);
conTALENTTKT.Open();
}
}
catch (Exception ex)
{
const string strError = "Could not establish connection to the TALENTTKT database";
err.ErrorMessage = ex.Message;
err.ErrorStatus = strError;
err.ErrorNumber = "TACDBAC-P5";
err.HasError = true;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected ErrorObj TALENTTKTClose()
{
ErrorObj err = new ErrorObj();
//------------------------------------------------------------------------
// Warning : Using ErrorObj here when closing the DB does cause a
// problem as it will clear the err object when an actual
// error has occured elsewhere
//------------------------------------------------------------------------
try
{
if (!(conTALENTTKT == null))
{
if (conTALENTTKT.State == ConnectionState.Open)
{
conTALENTTKT.Close();
}
}
conTALENTTKT = null;
}
catch (Exception ex)
{
const string strError4 = "Failed to close the TALENTTKT database connection";
err.ErrorMessage = ex.Message;
err.ErrorStatus = strError4;
err.ErrorNumber = "TACDBAC-P6";
err.HasError = true;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
return(err);
}
return(err);
}
/// <summary>
/// Prepares the command element
/// Assign the command instance properties from DESQLCommand instance
/// </summary>
/// <param name="givenSqlCommand">The given SQL command.</param>
/// <returns>ErrorObj</returns>
private ErrorObj PrepareCommandElement(SqlCommand givenSqlCommand)
{
ErrorObj err = new ErrorObj();
if ((!(err.HasError)) && HasValue(_commandElement.CommandText))
{
givenSqlCommand.CommandText = _commandElement.CommandText;
}
else
{
err.ErrorMessage = ERROR_COMMANDTEXT;
err.HasError = true;
}
if ((!(err.HasError)) && HasValue(_commandElement.CommandType))
{
givenSqlCommand.CommandType = _commandElement.CommandType;
}
else
{
err.ErrorMessage = ERROR_COMMANDTYPE;
err.HasError = true;
}
if (!(err.HasError))
{
err = AttachParametersToCommand(givenSqlCommand);
}
return(err);
}
/// <summary>
/// Use this for Insert, Update and Delete
/// Functionality to provide the gateway to access the Data Access Layer with Transaction object
/// If any exception transaction will be rollbacked here
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="givenTransaction">The given transaction.</param>
/// <returns></returns>
public ErrorObj DB2Access(DestinationDatabase destinationDatabase, iDB2Transaction givenTransaction)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDB2Access DBDataAccessEntity = new DBDB2Access();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
err = DBDataAccessEntity.AccessWithTransaction(givenTransaction);
if (!err.HasError && !(DBDataAccessEntity.ResultDataSet == null))
{
ResultDataSet = DBDataAccessEntity.ResultDataSet;
}
else
{
givenTransaction.Rollback();
//before call this get the previous error details
string errMessage = err.ErrorMessage;
err = EndTransaction(destinationDatabase, givenTransaction);
if (err.HasError)
{
errMessage = errMessage + ";" + err.ErrorMessage;
}
//whether end transaction gives error or not
//always assign err object has error
err.HasError = true;
err.ErrorMessage = errMessage;
}
return(err);
}
/// <summary>
/// Replaces the search-text passed with the replace text into the control/page text DB tables
/// It is not in use anymore.
/// </summary>
/// <param name="searchString"></param>
/// <param name="replaceString"></param>
/// <returns></returns>
/// <remarks></remarks>
public bool ReplaceSearchText(string searchString, string replaceString)
{
TalentSystemDefaults.TalentDataAccess talentSqlAccessDetail = new TalentSystemDefaults.TalentDataAccess();
ErrorObj err = new ErrorObj();
string query = "UPDATE tbl_page_text_lang SET TEXT_CONTENT = CAST( REPLACE(CAST(TEXT_CONTENT as NVarchar(max)), @searchString, @replaceString) AS NText) WHERE TEXT_CONTENT LIKE @searchText AND TEXT_CONTENT NOT LIKE \'%<\' + @searchString + \'>%\'; " +
"UPDATE tbl_control_text_lang SET CONTROL_CONTENT = CAST( REPLACE(CAST(CONTROL_CONTENT as NVarchar(max)), @searchString, @replaceString) AS NText) WHERE CONTROL_CONTENT LIKE @searchText AND CONTROL_CONTENT NOT LIKE \'%<\' + @searchString + \'>%\' ;";
try
{
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = query;
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@searchText", "%" + searchString + "%"));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@searchString", searchString));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@replaceString", replaceString));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if (!err.HasError)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(false);
}
/// <summary>
/// Returns text/control lang data from Front-End database
/// </summary>
/// <param name="searchText"></param>
/// <param name="pageOffset"></param>
/// <param name="pageSize"></param>
/// <param name="sortColumn"></param>
/// <param name="sortOrder"></param>
/// <returns></returns>
/// <remarks></remarks>
public DataSet TextSearch(string searchText, int pageOffset, int pageSize, string sortColumn = "TEXT_CODE", bool sortOrder = false)
{
TalentSystemDefaults.TalentDataAccess talentSqlAccessDetail = new TalentSystemDefaults.TalentDataAccess();
ErrorObj err = new ErrorObj();
DataSet textSearchData = new DataSet();
try
{
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteDataSet;
talentSqlAccessDetail.CommandElements.CommandType = CommandType.StoredProcedure;
talentSqlAccessDetail.CommandElements.CommandText = "usp_SearchText_GetData";
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@searchText", searchText));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@sortColumn", sortColumn));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@sortOrder", sortOrder, SqlDbType.Bit));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@pageOffset", pageOffset, SqlDbType.Int));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@pageSize", pageSize, SqlDbType.Int));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if ((!(err.HasError)) && (!(talentSqlAccessDetail.ResultDataSet == null)))
{
textSearchData = talentSqlAccessDetail.ResultDataSet;
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(textSearchData);
}
protected int DB2Access(TalentDB2Access talentDB2AccessDetail, iDB2Transaction givenTransaction)
{
int affectedRows = 0;
if (settings.EnableDB2AccessQueue)
{
settings.DB2AccessQueue.Add(talentDB2AccessDetail);
}
else
{
ErrorObj err = new ErrorObj();
if (givenTransaction == null)
{
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT);
}
else
{
err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT, givenTransaction);
}
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(talentDB2AccessDetail.ResultDataSet.Tables[0].Rows[0][0]);
}
}
return(affectedRows);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected ErrorObj TalentSQLClose()
{
ErrorObj err = new ErrorObj();
try
{
if (!(conSql2005 == null))
{
if (conSql2005.State == ConnectionState.Open)
{
conSql2005.Close();
}
}
}
catch (Exception ex)
{
const string strError4 = "Failed to close the Sql2005 database connection";
err.ErrorMessage = ex.Message;
err.ErrorStatus = strError4;
err.ErrorNumber = "TACDBAC-P2c";
err.HasError = true;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
return(err);
//TalentLogger.Logging(CLASSNAME, "TalentAdminClose", strError4, err, ex, LogTypeConstants.TCDBACCESSLOG, Settings.BusinessUnit, Settings.Partner, Settings.LoginId)
}
return(err);
}
/// <summary>
/// Ends the transaction and disposes any unhandled transaction object
/// </summary>
/// <param name="err">The error object as ref</param>
/// <param name="givenTransaction">The given transaction.</param>
public void EndTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, SqlTransaction givenTransaction)
{
if (!(givenTransaction.Connection == null))
{
givenTransaction.Dispose();
givenTransaction = null;
}
err = ConnectionByDestinationDBClose(destinationDatabase);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected ErrorObj Sql2005Open()
{
ErrorObj err = new ErrorObj();
short iCounter = (short)0;
//---------------------------------------------------------------------------------
// Attempt to open database
//
const string strError = "Could not establish connection to the SQL database";
try
{
while (iCounter < 10)
{
if (conSql2005 == null)
{
conSql2005 = new SqlConnection(Settings.FrontEndConnectionString);
conSql2005.Open();
}
else if (conSql2005.State != ConnectionState.Open)
{
conSql2005 = new SqlConnection(Settings.FrontEndConnectionString);
conSql2005.Open();
}
//---------------------------------------------------------------------------------
// Possible the server needs to wake up so
//
if (conSql2005.State == ConnectionState.Open)
{
break;
}
else
{
iCounter++;
if (iCounter > 10)
{
err.ErrorMessage = conSql2005.State.ToString();
err.ErrorStatus = strError;
err.ErrorNumber = "TACDBAC-P1a";
err.HasError = true;
break;
}
System.Threading.Thread.Sleep(2500); // Sleep for 2.5 seconds
}
//---------------------------------------------------------------------------------
}
}
catch (Exception ex)
{
err.ErrorMessage = ex.Message;
err.ErrorStatus = strError;
err.ErrorNumber = "TACDBAC-P1b";
err.HasError = true;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="destinationDatabase"></param>
/// <returns></returns>
/// <remarks></remarks>
private ErrorObj ConnectionByDestinationDBClose(DestinationDatabase destinationDatabase)
{
ErrorObj err = new ErrorObj();
if (destinationDatabase == TalentSystemDefaults.DestinationDatabase.TALENTTKT)
{
err = TALENTTKTClose();
}
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected virtual ErrorObj AccessDataBaseTALENTTKT()
{
ErrorObj err = new ErrorObj();
//--------------------------------------------------------------------
if (!err.HasError)
{
}
//--------------------------------------------------------------------
return(err);
}
private dynamic Execute(TalentDB2Access db2Access, ref ErrorObj err)
{
int affectedRows = 0;
err = db2Access.DB2Access(DestinationDatabase.TALENTTKT);
if ((!(err.HasError)) && (!(db2Access.ResultDataSet == null)))
{
affectedRows = System.Convert.ToInt32(db2Access.ResultDataSet.Tables[0].Rows[0][0]);
}
return(affectedRows);
}
protected override ErrorObj AccessDataBaseTALENTTKT()
{
ErrorObj err = new ErrorObj();
switch (_settings.FunctionName)
{
case UpdateDB2Data:
err = AccessDatabaseTD001S();
break;
}
return(err);
}
/// <summary>
/// Ends the transaction and close the reader by passing the object to DBDataAccess
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="givenTransaction">The given transaction.</param>
/// <param name="readerToClose">The reader to close.</param>
/// <returns>Error Object</returns>
public ErrorObj EndTransaction(DestinationDatabase destinationDatabase, iDB2Transaction givenTransaction, iDB2DataReader readerToClose)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDB2Access DBDataAccessEntity = new DBDB2Access();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
DBDataAccessEntity.EndTransaction(destinationDatabase, ref err, givenTransaction, readerToClose);
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected ErrorObj TalentSQLOpen()
{
ErrorObj err = new ErrorObj();
short iCounter = (short)0;
const string strError = "Could not establish connection to the SQL database";
try
{
while (iCounter < 10)
{
if (conSql2005 == null)
{
conSql2005 = new SqlConnection(Settings.BackOfficeConnectionString);
conSql2005.Open();
}
else if (conSql2005.State != ConnectionState.Open)
{
conSql2005 = new SqlConnection(Settings.BackOfficeConnectionString);
conSql2005.Open();
}
if (conSql2005.State == ConnectionState.Open)
{
break;
}
else
{
iCounter++;
if (iCounter > 10)
{
err.ErrorMessage = conSql2005.State.ToString();
err.ErrorStatus = strError;
err.ErrorNumber = "TACDBAC-P2a";
err.HasError = true;
break;
}
System.Threading.Thread.Sleep(2500);
}
}
}
catch (Exception ex)
{
err.ErrorMessage = ex.Message;
err.ErrorStatus = strError;
err.ErrorNumber = "TACDBAC-P2b";
err.HasError = true;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, err.ErrorNumber, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
//TalentLogger.Logging(CLASSNAME, "TalentAdminOpen", strError, err, ex, LogTypeConstants.TCDBACCESSLOG, Settings.BusinessUnit, Settings.Partner, Settings.LoginId)
}
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="destinationDatabase"></param>
/// <returns></returns>
/// <remarks></remarks>
private ErrorObj ConnectionByDestinationDBClose(DestinationDatabase destinationDatabase)
{
ErrorObj err = new ErrorObj();
if (destinationDatabase == TalentSystemDefaults.DestinationDatabase.SQL2005)
{
err = Sql2005Close();
}
else if (destinationDatabase == TalentSystemDefaults.DestinationDatabase.TALENT_CONFIG |
destinationDatabase == TalentSystemDefaults.DestinationDatabase.TALENT_DEFINITION)
{
err = TalentSQLClose();
}
return(err);
}
/// <summary>
/// XML Comment
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
protected override ErrorObj AccessDataBaseTalentSQL()
{
ErrorObj err = new ErrorObj();
SqlCommand SqlCommandEntity = new SqlCommand();
SqlCommandEntity.Connection = conSql2005;
try
{
err = PrepareCommandElement(SqlCommandEntity);
if (!(err.HasError))
{
if (_commandElement.CommandExecutionType == CommandExecution.ExecuteDataSet)
{
this.ResultDataSet = ExecuteDataSet(SqlCommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
this.ResultDataSet = ExecuteNonQuery(SqlCommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteReader)
{
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteScalar)
{
}
}
}
catch (SqlException ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
if (_commandElement.CommandType == CommandType.StoredProcedure)
{
err.ErrorMessage = err.ErrorMessage + " (Server=" + ex.Server.ToString() + ", Procedure=" + ex.Procedure.ToString() + ", LineNumber=" + ex.LineNumber.ToString() + ")";
}
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
//TalentLogger.Logging(CLASSNAME, "AccessDataBaseTalentConfig", "SQL Exception Occured" & GetStackFrameDetails(), err, ex, LogTypeConstants.TCDBDATAOBJECTSLOG, Settings.BusinessUnit, Settings.Partner, Settings.LoginId)
}
catch (Exception ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
//TalentLogger.Logging(CLASSNAME, "AccessDataBaseTalentConfig", "Exception Occured" & GetStackFrameDetails(), err, ex, LogTypeConstants.TCDBDATAOBJECTSLOG, Settings.BusinessUnit, Settings.Partner, Settings.LoginId)
}
return(err);
}
/// <summary>
/// Ends the transaction and disposes any unhandled transaction object and closes the reader object
/// </summary>
/// <param name="err">The error object as ref</param>
/// <param name="givenTransaction">The given transaction.</param>
/// <param name="readerToClose">The reader to close.</param>
public void EndTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, SqlTransaction givenTransaction, SqlDataReader readerToClose)
{
if (!(givenTransaction.Connection == null))
{
givenTransaction.Dispose();
givenTransaction = null;
}
if (!(readerToClose == null))
{
if (!(readerToClose.IsClosed))
{
readerToClose.Close();
}
readerToClose.Dispose();
}
err = ConnectionByDestinationDBClose(destinationDatabase);
}
/// <summary>
/// XML Comment
/// </summary>
/// <param name="ds"></param>
/// <param name="err"></param>
/// <returns></returns>
/// <remarks></remarks>
private string CheckResponseForError(DataSet ds, ErrorObj err)
{
string errorCode = string.Empty;
// The dataset will give us errors from the back end
if (!(ds == null))
{
System.Data.DataTable errorTable = null;
try
{
errorTable = ds.Tables[0];
}
catch (Exception)
{
errorCode = "AAC";
}
if (!(errorTable == null))
{
foreach (System.Data.DataRow row in errorTable.Rows)
{
if (!row["ReturnCode"].Equals(string.Empty))
{
errorCode = System.Convert.ToString(row["ReturnCode"]);
break;
}
}
}
else
{
errorCode = "AAD";
}
}
else
{
errorCode = "AAB";
}
// The error object will give us any errors with comms to the back end.
if (!(errorCode.Length > 0))
{
if (err.HasError)
{
errorCode = err.ErrorStatus;
}
}
return(errorCode);
}
/// <summary>
/// Access the data base DB2 TalentTKT
/// This is called by DBAccess Class
/// </summary>
/// <returns></returns>
protected override ErrorObj AccessDataBaseTALENTTKT()
{
ErrorObj err = new ErrorObj();
iDB2Command db2CommandEntity = new iDB2Command();
db2CommandEntity.Connection = conTALENTTKT;
try
{
err = PrepareCommandElement(db2CommandEntity);
if (!(err.HasError))
{
if (_commandElement.CommandExecutionType == CommandExecution.ExecuteDataSet)
{
this.ResultDataSet = ExecuteDataSet(db2CommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
this.ResultDataSet = ExecuteNonQuery(db2CommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteReader)
{
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteScalar)
{
}
}
}
catch (iDB2Exception ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
if (_commandElement.CommandType == CommandType.StoredProcedure)
{
err.ErrorMessage = err.ErrorMessage + " (Source=" + ex.Source + ", ErrorCode=" + ex.ErrorCode.ToString() + ", MessageDetails=" + ex.MessageDetails + ")";
}
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
catch (Exception ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
return(err);
}
/// <summary>
/// Access the data base SQL 2005
/// This is called by DBAccess Class
/// </summary>
/// <returns></returns>
protected override ErrorObj AccessDataBaseSQL2005()
{
ErrorObj err = new ErrorObj();
SqlCommand SqlCommandEntity = new SqlCommand();
SqlCommandEntity.Connection = conSql2005;
try
{
err = PrepareCommandElement(SqlCommandEntity);
if (!(err.HasError))
{
if (_commandElement.CommandExecutionType == CommandExecution.ExecuteDataSet)
{
this.ResultDataSet = ExecuteDataSet(SqlCommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
this.ResultDataSet = ExecuteNonQuery(SqlCommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteReader)
{
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteScalar)
{
}
}
}
catch (SqlException ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
if (_commandElement.CommandType == CommandType.StoredProcedure)
{
err.ErrorMessage = err.ErrorMessage + " (Server=" + ex.Server.ToString() + ", Procedure=" + ex.Procedure.ToString() + ", LineNumber=" + ex.LineNumber.ToString() + ")";
}
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
catch (Exception ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
return(err);
}
public ErrorObj DB2DefaultsAccess(DestinationDatabase destinationDatabase, DEDB2Defaults deDB2Defaults)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DB2Defaults dbDB2Access = new DB2Defaults();
dbDB2Access.Settings = Settings;
dbDB2Access.DEDB2Defaults = deDB2Defaults;
err = dbDB2Access.AccessDatabase();
if (!err.HasError && !(dbDB2Access.ResultDataSet == null))
{
ResultDataSet = dbDB2Access.ResultDataSet;
}
return(err);
}
/// <summary>
/// Functionality to provide the gateway to access the Data Access Layer for DB2
/// </summary>
/// <param name="destinationDatabase"></param>
/// <returns></returns>
/// <remarks></remarks>
public ErrorObj DB2Access(DestinationDatabase destinationDatabase)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDB2Access DBDataAccessEntity = new DBDB2Access();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
err = DBDataAccessEntity.AccessDatabase();
if (!err.HasError && !(DBDataAccessEntity.ResultDataSet == null))
{
ResultDataSet = DBDataAccessEntity.ResultDataSet;
}
return(err);
}
/// <summary>
/// Execute the command obejct with transaction
/// </summary>
/// <param name="givenTransaction">The given transaction.</param>
/// <returns>Error Object</returns>
public ErrorObj AccessWithTransaction(SqlTransaction givenTransaction)
{
ErrorObj err = new ErrorObj();
SqlCommand SqlCommandEntity = new SqlCommand();
SqlCommandEntity.Transaction = givenTransaction;
SqlCommandEntity.Connection = givenTransaction.Connection;
err = PrepareCommandElement(SqlCommandEntity);
if (!(err.HasError))
{
try
{
if (_commandElement.CommandExecutionType == CommandExecution.ExecuteDataSet)
{
this.ResultDataSet = ExecuteDataSet(SqlCommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
this.ResultDataSet = ExecuteNonQuery(SqlCommandEntity);
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteReader)
{
}
else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteScalar)
{
}
}
catch (SqlException sqlEx)
{
err.HasError = true;
err.ErrorMessage = sqlEx.Message + "; Err Code:" + System.Convert.ToString(sqlEx.ErrorCode) + "; Line:" + System.Convert.ToString(sqlEx.LineNumber) + "; Name:" + sqlEx.Procedure;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, sqlEx.StackTrace.ToString(), err.ErrorMessage);
}
catch (Exception ex)
{
err.HasError = true;
err.ErrorMessage = ex.Message;
Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage);
}
}
return(err);
}
/// <summary>
/// Use this for Insert, Update and Delete
/// Functionality to provide the gateway to access the Data Access Layer with Transaction object
/// If any exception transaction will be rollbacked here
/// </summary>
/// <param name="destinationDatabase"></param>
/// <param name="givenTransaction">The given transaction.</param>
/// <param name="allowSerialize">To allow serialization of TalentDataAccess instance </param>
/// <returns></returns>
public ErrorObj SQLAccess(DestinationDatabase destinationDatabase, SqlTransaction givenTransaction, bool allowSerialize = true)
{
string ModuleName = GetModuleNameByDestinationDB(destinationDatabase);
ErrorObj err = new ErrorObj();
this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName);
Settings.ModuleName = ModuleName;
DBDataAccess DBDataAccessEntity = new DBDataAccess();
DBDataAccessEntity.Settings = Settings;
DBDataAccessEntity.CommandElement = _commandElements;
err = DBDataAccessEntity.AccessWithTransaction(givenTransaction);
if (!err.HasError && !(DBDataAccessEntity.ResultDataSet == null))
{
ResultDataSet = DBDataAccessEntity.ResultDataSet;
// Serialize the transaction
if (allowSerialize && this.CommandElements.CommandExecutionType == CommandExecution.ExecuteNonQuery)
{
SetConnectionStringParameters();
Utilities.SerializeTransaction(this, Settings);
}
}
else
{
givenTransaction.Rollback();
//before call this get the previous error details
string errMessage = err.ErrorMessage;
err = EndTransaction(destinationDatabase, givenTransaction);
if (err.HasError)
{
errMessage = errMessage + ";" + err.ErrorMessage;
}
//whether end transaction gives error or not
//always assign err object has error
err.HasError = true;
err.ErrorMessage = errMessage;
}
return(err);
}
/// <summary>
/// Updates text/control lang data into Front-End database
/// </summary>
/// <param name="modifiedRows"></param>
/// <returns></returns>
/// <remarks></remarks>
public bool SaveTextSearchData(System.Collections.Generic.List <TextSearchEntity> modifiedRows, Dictionary <string, string> tableList)
{
TalentSystemDefaults.TalentDataAccess talentSqlAccessDetail = new TalentSystemDefaults.TalentDataAccess();
ErrorObj err = new ErrorObj();
System.Text.StringBuilder xmlData = new System.Text.StringBuilder();
System.Collections.Generic.List <TextSearchEntity> gridRows = new System.Collections.Generic.List <TextSearchEntity>();
//Dim tables As New List(Of String)
int errorCode = 0;
string errorMessage = string.Empty;
try
{
talentSqlAccessDetail.Settings = settings;
talentSqlAccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery;
talentSqlAccessDetail.CommandElements.CommandText = "usp_SearchText_SetData";
talentSqlAccessDetail.CommandElements.CommandType = CommandType.StoredProcedure;
// get list of tables to be updated
//tables = modifiedRows.Select(Function(x) x.TableName).Distinct().ToList()
xmlData.AppendLine("<data>");
// mention tables to be updated
xmlData.AppendLine("<tables>");
foreach (var pair in tableList)
{
xmlData.AppendLine(string.Format("<table id=\"{0}\">{1}</table>", pair.Value, pair.Key));
}
xmlData.AppendLine("</tables>");
// mention rows to be updated
xmlData.AppendLine("<rows>");
foreach (var row in modifiedRows)
{
xmlData.AppendLine(string.Format("<row tab=\"{0}\" id=\"{1}\">", row.TableName, row.ID.ToString()));
if (row.Text_Code != string.Empty)
{
xmlData.AppendLine("<text_code>" + row.Text_Code + "</text_code>");
}
if (row.Text_Content != string.Empty)
{
xmlData.AppendLine("<text_content>" + Utilities.GetHTMLEncodedStringForDB(System.Convert.ToString(row.Text_Content)).Replace("'", "''") + "</text_content>");
}
xmlData.AppendLine("</row>");
}
xmlData.AppendLine("</rows>");
xmlData.AppendLine("</data>");
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@xml", xmlData.ToString(), SqlDbType.Xml));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@Error_Code", errorCode, SqlDbType.Int, ParameterDirection.Output));
talentSqlAccessDetail.CommandElements.CommandParameter.Add(ConstructParameter("@Error_Message", errorMessage, SqlDbType.VarChar, ParameterDirection.Output));
err = talentSqlAccessDetail.SQLAccess(DestinationDatabase.SQL2005);
if (!err.HasError)
{
return(true);
}
else
{
// read the output error parameters
errorCode = System.Convert.ToInt32(talentSqlAccessDetail.CommandElements.CommandParameter[1].ParamValue);
errorMessage = System.Convert.ToString(talentSqlAccessDetail.CommandElements.CommandParameter[2].ParamValue);
return(false);
}
}
catch (Exception)
{
throw;
}
finally
{
talentSqlAccessDetail = null;
}
return(false);
}
public int Execute(DESettings settings)
{
int affectedRows = 0;
System.Collections.Generic.List <TalentDB2Access> removeList = new System.Collections.Generic.List <TalentDB2Access>();
foreach (TalentDB2Access db2Access in this)
{
bool hasQuote = false;
foreach (DEDB2Parameter param in db2Access.CommandElements.CommandParameter)
{
if (param.ParamValue != null && System.Convert.ToString(param.ParamValue).Contains("'"))
{
hasQuote = true;
break;
}
}
if (hasQuote)
{
ErrorObj err = default(ErrorObj);
affectedRows += System.Convert.ToInt32(Execute(db2Access, ref err));
if (err.HasError)
{
throw new Exception(err.ErrorMessage);
}
removeList.Add(db2Access);
}
}
foreach (TalentDB2Access item in removeList)
{
this.Remove(item);
}
if (Count > 0)
{
if (Count == 1)
{
ErrorObj err = default(ErrorObj);
affectedRows += Execute(this[0], ref err);
if (err.HasError)
{
throw new Exception(err.ErrorMessage);
}
}
else
{
ErrorObj err = default(ErrorObj);
settings.FunctionName = "UpdateDB2Data";
string xmlString = GetXML();
DEDB2Defaults deDB2Defaults = new DEDB2Defaults();
deDB2Defaults.XMLString = xmlString;
TalentDB2Access talentDB2AccessDetail = new TalentDB2Access();
DataTable outputDataTable = default(DataTable);
try
{
talentDB2AccessDetail.Settings = settings;
//Execute
err = talentDB2AccessDetail.DB2DefaultsAccess(DestinationDatabase.TALENTTKT, deDB2Defaults);
if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null)))
{
outputDataTable = talentDB2AccessDetail.ResultDataSet.Tables[1];
affectedRows = System.Convert.ToInt32(outputDataTable.Rows[0]["AffectedRows"]);
}
}
catch (Exception)
{
throw;
}
finally
{
talentDB2AccessDetail = null;
}
}
}
Clear();
return(affectedRows);
}