public List <APICallLogItem> Get(DateTime dteDateCreated_GToEQ, out string LastError)
{
List <APICallLogItem> lstAPICallLogItems = null;
LastError = String.Empty;
try
{
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblAPICallLogItem_SelBy_DateCreated_GToEQ";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("DateCreated", dteDateCreated_GToEQ));
lstAPICallLogItems = DBUtil.DataTableToList <APICallLogItem>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(lstAPICallLogItems);
}
public List <OrderSystemOfRecord> GetAllRecords(out string LastError)
{
List <OrderSystemOfRecord> lstMigrationStatuses = null;
LastError = String.Empty;
try
{
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblOrderSystemOfRecord_SelAll";
objQuery.CommandType = CommandType.StoredProcedure;
lstMigrationStatuses = DBUtil.DataTableToList <OrderSystemOfRecord>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(lstMigrationStatuses);
}
public List <APICallLogItem> GetByFK(int intOrderAdddressID, out string LastError)
{
List <APICallLogItem> lstOrderAddress = null;
LastError = String.Empty;
try
{
if (intOrderAdddressID == 0)
{
LastError = "The value supplied for 'intOrderAdddressID' cannot be zero and must be a valid integer that corresponds to an existing record";
return(null);
}
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblAPICallLogItem_SelByFK_OrderAddressID_EQ";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("OrderAddressID", (object)intOrderAdddressID ?? String.Empty));
lstOrderAddress = DBUtil.DataTableToList <APICallLogItem>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(lstOrderAddress);
}
public List <SystemConfigItem> GetAllSystemConfigItems(out string LastError)
{
List <SystemConfigItem> lstSystemConfigItems = null;
LastError = String.Empty;
try
{
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblSystemConfigItem_SelAll";
objQuery.CommandType = CommandType.StoredProcedure;
lstSystemConfigItems = DBUtil.DataTableToList <SystemConfigItem>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(lstSystemConfigItems);
}
public MigrationStatus GetByPK(int intMigrationStatusID, out string LastError)
{
MigrationStatus objMigrationStatus = null;
LastError = String.Empty;
try
{
if (intMigrationStatusID == 0)
{
LastError = "The identity value supplied for the records PK cannot be zero and must be a valid integer --> MigrationStatusID";
return(null);
}
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblMigrationStatus_SelByPK_MigrationStatusID_EQ";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("MigrationStatusID", intMigrationStatusID));
objMigrationStatus = DBUtil.DataTableToObject <MigrationStatus>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(objMigrationStatus);
}
public SystemConfigItem Get(int intSystemConfigItemID, out string LastError)
{
SystemConfigItem objSystemConfigItems = null;
LastError = String.Empty;
try
{
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblSystemConfigItem_SelByIDENT_SystemConfigItemID_EQ";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("SystemConfigItemID", intSystemConfigItemID));
objSystemConfigItems = DBUtil.DataTableToObject <SystemConfigItem>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(objSystemConfigItems);
}
public int Count(string strWhereClause, out string LastError)
{
int intCount = 0;
LastError = String.Empty;
try
{
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblOrderAddress_SelByDyn_H1_Custom_Ct";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("DynamicWhereClause", strWhereClause));
intCount = (SqlServerDataAccess.ExecuteScalar(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(intCount);
}
public List <OrderAddress> Search(string strWhereClause, string strOrderByClause, out string LastError, int?intPageNumber = null, int?intPageSize = null)
{
List <OrderAddress> lstOrderAddress = new List <OrderAddress>();
LastError = String.Empty;
try
{
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblOrderAddress_SelByDyn_H1_Custom";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("DynamicWhereClause", strWhereClause));
objQuery.Parameters.Add(new SqlParameter("DynamicOrderByClause", strOrderByClause));
objQuery.Parameters.Add(new SqlParameter("RowsPerPage", intPageSize ?? 100000));
objQuery.Parameters.Add(new SqlParameter("PageNumber", intPageNumber ?? 1));
lstOrderAddress = DBUtil.DataTableToList <OrderAddress>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(lstOrderAddress);
}
public bool UpdateOptimistic(out string LastError)
{
bool blnRecordUpdated = false;
int? intNumberOfRecordsUpdated;
LastError = String.Empty;
try
{
if ((this.SystemConfigItemID == 0) || (this.SystemConfigItemID == null))
{
LastError = "This object's identity column MUST be populated, so that we know which record needs update --> SystemConfigItemID";
return(false);
}
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblSystemConfigItem_UpdateOptimistic";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("DataHash", (object)this.DataHash ?? DBNull.Value));
objQuery.Parameters.Add(new SqlParameter("SystemConfigItemID", (object)this.SystemConfigItemID ?? DBNull.Value));
AddCommonSQLParams(ref objQuery);
// Create parameter with Direction as Output
SqlParameter outputIdParam = new SqlParameter("UpdateResult", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
objQuery.Parameters.Add(outputIdParam);
intNumberOfRecordsUpdated = SqlServerDataAccess.ExecuteNonQuery(objQuery);
int intResultValue = (int)outputIdParam.Value;
if (intResultValue == -1)
{
LastError = String.Format("The update failed. Concurrent action violation. Another user or process may have modified the record, as the datahash provided on the object is no longer valid. DataHash provided = [{0}]", this.DataHash);
}
blnRecordUpdated = (intResultValue == 1);
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(blnRecordUpdated);
}
private DataTable Get(string strTSQL)
{
try
{
_objLogger.Debug(String.Concat("About to execute query --> [", strTSQL, "]"));
DataTable dt = SqlServerDataAccess.Execute
(strTSQL
, CommandType.Text
);
return(dt);
}
catch (Exception ex)
{
_objLogger.Warn(String.Concat("Query Execution Failed --> [", strTSQL, "]"));
throw new ArgumentException(String.Format("DataAccess error: {0}", ex.Message));
}
}
public OrderAddress Get(int intOrderSystemOfRecordID, string strCDWCustomerOrderNumber, string strCDWAddressOne, string strCDWCity, string strCDWState, string strCDWPostalCode, string strCDWCountry, string strCDWFloor, string strCDWRoom, string strCDWSuite, out string LastError)
{
OrderAddress objOrderAddress = null;
LastError = String.Empty;
try
{
if (intOrderSystemOfRecordID == 0)
{
LastError = "The value supplied for 'intOrderSystemOfRecordID' cannot be zero and must be a valid integer that corresponds to an enum";
return(null);
}
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblOrderAddress_SelBy_OrderSystemOfRecordID_EQ_CDWCustomerOrderNumber_EQ_CDWAddressOne_EQ_CDWCity_EQ_CDWState_EQ_C_TRUNC";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("OrderSystemOfRecordID", (object)intOrderSystemOfRecordID ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWCustomerOrderNumber", (object)strCDWCustomerOrderNumber ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWAddressOne", (object)strCDWAddressOne ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWCity", (object)strCDWCity ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWState", (object)strCDWState ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWPostalCode", (object)strCDWPostalCode ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWCountry", (object)strCDWCountry ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWFloor", (object)strCDWFloor ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWRoom", (object)strCDWRoom ?? String.Empty));
objQuery.Parameters.Add(new SqlParameter("CDWSuite", (object)strCDWSuite ?? String.Empty));
objOrderAddress = DBUtil.DataTableToObject <OrderAddress>(SqlServerDataAccess.Execute(objQuery));
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(objOrderAddress);
}
public bool Insert(out string LastError)
{
bool blnRecordsInserted = false;
int? intID;
LastError = String.Empty;
try
{
if (this.OrderAddressLogItemID > 0)
{
LastError = "This object's identity column is already populated, which is not allowed since the field is automatically assigned by the database --> OrderAddressID";
return(false);
}
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblOrderAddressLogItem_Insert";
objQuery.CommandType = CommandType.StoredProcedure;
AddCommonSQLParams(ref objQuery);
intID = SqlServerDataAccess.ExecuteScalar(objQuery);
blnRecordsInserted = intID > 0;
this.OrderAddressLogItemID = intID;
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(blnRecordsInserted);
}
public int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] parameters)
{
int returnCode = 0;
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = commandText;
cmd.CommandType = commandType;
cmd.Connection = _sqlConnection;
cmd.Transaction = _sqlTrx;
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(SqlServerDataAccess.MakeEmptyStringNull(p));
}
returnCode = cmd.ExecuteNonQuery();
return(returnCode);
}
public bool Update(out string LastError)
{
bool blnRecordUpdated = false;
int? intNumberOfRecordsUpdated;
LastError = String.Empty;
try
{
if ((this.OrderAddressID == 0) || (this.OrderAddressID == null))
{
LastError = "This object's identity column MUST be populated, so that we know which record needs update --> OrderAddressID";
return(false);
}
SqlCommand objQuery = new SqlCommand();
objQuery.CommandText = "tblOrderAddress_Update";
objQuery.CommandType = CommandType.StoredProcedure;
// Add the parameter to the Parameters collection.
objQuery.Parameters.Add(new SqlParameter("OrderAddressID", (object)this.OrderAddressID ?? DBNull.Value));
AddCommonSQLParams(ref objQuery);
intNumberOfRecordsUpdated = SqlServerDataAccess.ExecuteNonQuery(objQuery);
blnRecordUpdated = (intNumberOfRecordsUpdated == 1);
}
catch (System.Data.SqlClient.SqlException exSql)
{
LastError = String.Concat("", exSql.Message, exSql.Number, null);
}
catch (Exception ex)
{
LastError = String.Concat("", ex.Message, null);
}
return(blnRecordUpdated);
}
