/// <summary>
/// Update record by using the table's primary key.
/// </summary>
/// <param name="database"></param>
/// <param name="data">Data which to update.</param>
/// <param name="oldPROCESS_CD">Old Key #1</param>
/// <param name="oldNG_CRITERIA_CD">Old Key #2</param>
/// <returns></returns>
public int UpdateWithPK(Database database, NGCriteriaDTO data, String oldPROCESS_CD, String oldNG_CRITERIA_CD)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" UPDATE " + data.TableName);
sb.AppendLine(" SET ");
sb.AppendLine(" " + NGCriteriaDTO.eColumns.UPD_BY + "=@UPD_BY");
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.UPD_DATE + "=GETDATE()");
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.UPD_MACHINE + "=@UPD_MACHINE");
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.PROCESS_CD + "=@PROCESS_CD");
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.NG_CRITERIA_CD + "=@NG_CRITERIA_CD");
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.NG_CRITERIA_DESC + "=@NG_CRITERIA_DESC");
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.OLD_DATA + "=@OLD_DATA");
sb.AppendLine(" WHERE ");
sb.AppendLine(" " + NGCriteriaDTO.eColumns.PROCESS_CD + "=@oldPROCESS_CD");
sb.AppendLine(" AND " + NGCriteriaDTO.eColumns.NG_CRITERIA_CD + "=@oldNGCriteria");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
req.Parameters.Add("@UPD_BY", DataType.NVarChar, data.UPD_BY.Value);
req.Parameters.Add("@UPD_MACHINE", DataType.NVarChar, data.UPD_MACHINE.Value);
req.Parameters.Add("@PROCESS_CD", DataType.NVarChar, data.PROCESS_CD.Value);
req.Parameters.Add("@NG_CRITERIA_CD", DataType.NVarChar, data.NG_CRITERIA_CD.Value);
req.Parameters.Add("@NG_CRITERIA_DESC", DataType.NVarChar, data.NG_CRITERIA_DESC.Value);
req.Parameters.Add("@OLD_DATA", DataType.Default, data.OLD_DATA.Value);
req.Parameters.Add("@oldPROCESS_CD", DataType.NVarChar, oldPROCESS_CD);
req.Parameters.Add("@oldNG_CRITERIA_CD", DataType.NVarChar, oldNG_CRITERIA_CD);
#endregion
return(db.ExecuteNonQuery(req));
}
/// <summary>
/// Check exist before manipulate data. If found record will update data. Otherwise insert new data.
/// </summary>
/// <param name="database"></param>
/// <param name="data"></param>
/// <returns></returns>
public int AddNewOrUpdate(Database database, NGCriteriaDTO data)
{
Database db = UseDatabase(database);
if (Exist(database, data.PROCESS_CD, data.NG_CRITERIA_CD))
{
return(UpdateWithoutPK(db, data));
}
return(AddNew(db, data));
}
/// <summary>
/// Insert new record into database.
/// </summary>
/// <param name="database"></param>
/// <param name="data"></param>
/// <returns></returns>
public int AddNew(Database database, NGCriteriaDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" INSERT INTO " + data.TableName + "(");
sb.AppendLine(" " + NGCriteriaDTO.eColumns.CRT_BY);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.CRT_DATE);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.CRT_MACHINE);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.UPD_BY);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.UPD_DATE);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.UPD_MACHINE);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.PROCESS_CD);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.NG_CRITERIA_CD);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.NG_CRITERIA_DESC);
sb.AppendLine(" ," + NGCriteriaDTO.eColumns.OLD_DATA);
sb.AppendLine(") VALUES(");
sb.AppendLine(" @CRT_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,@CRT_MACHINE");
sb.AppendLine(" ,@UPD_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,@UPD_MACHINE");
sb.AppendLine(" ,@PROCESS_CD");
sb.AppendLine(" ,@NG_CRITERIA_CD");
sb.AppendLine(" ,@NG_CRITERIA_DESC");
sb.AppendLine(" ,@OLD_DATA");
sb.AppendLine(" )");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
req.Parameters.Add("@CRT_BY", DataType.NVarChar, data.CRT_BY.Value);
req.Parameters.Add("@CRT_MACHINE", DataType.NVarChar, data.CRT_MACHINE.Value);
req.Parameters.Add("@UPD_BY", DataType.NVarChar, data.UPD_BY.Value);
req.Parameters.Add("@UPD_MACHINE", DataType.NVarChar, data.UPD_MACHINE.Value);
req.Parameters.Add("@PROCESS_CD", DataType.NVarChar, data.PROCESS_CD.Value);
req.Parameters.Add("@NG_CRITERIA_CD", DataType.NVarChar, data.NG_CRITERIA_CD.Value);
req.Parameters.Add("@NG_CRITERIA_DESC", DataType.NVarChar, data.NG_CRITERIA_DESC.Value);
req.Parameters.Add("@OLD_DATA", DataType.Default, data.OLD_DATA.Value);
#endregion
return(db.ExecuteNonQuery(req));
}
