/// <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="oldID">Old Key #1</param> /// <returns></returns> public int UpdateWithPK(Database database, InventoryOnhandDTO data, NZLong oldID) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" UPDATE " + data.TableName); sb.AppendLine(" SET "); //sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID + "=:ID"); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH + "=:YEAR_MONTH"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE + "=:PERIOD_BEGIN_DATE"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_END_DATE + "=:PERIOD_END_DATE"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ITEM_CD + "=:ITEM_CD"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOC_CD + "=:LOC_CD"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOT_NO + "=:LOT_NO"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OPEN_QTY + "=:OPEN_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.IN_QTY + "=:IN_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OUT_QTY + "=:OUT_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ADJUST_QTY + "=:ADJUST_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.STOCK_TAKE_QTY + "=:STOCK_TAKE_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY + "=:ON_HAND_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_BY + "=:UPD_BY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_DATE + "=GETDATE()"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_MACHINE + "=:UPD_MACHINE"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LAST_RECEIVE_DATE + "=:LAST_RECEIVE_DATE"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OLD_DATA + "=:OLD_DATA"); sb.AppendLine(" WHERE "); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID + "=:oldID"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters //req.Parameters.Add("ID", DataType.Default, data.ID.Value); req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, data.YEAR_MONTH.Value); req.Parameters.Add("PERIOD_BEGIN_DATE", DataType.DateTime, data.PERIOD_BEGIN_DATE.Value); req.Parameters.Add("PERIOD_END_DATE", DataType.DateTime, data.PERIOD_END_DATE.Value); req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value); req.Parameters.Add("LOC_CD", DataType.NVarChar, data.LOC_CD.Value); req.Parameters.Add("LOT_NO", DataType.NVarChar, data.LOT_NO.Value); req.Parameters.Add("OPEN_QTY", DataType.Number, data.OPEN_QTY.Value); req.Parameters.Add("IN_QTY", DataType.Number, data.IN_QTY.Value); req.Parameters.Add("OUT_QTY", DataType.Number, data.OUT_QTY.Value); req.Parameters.Add("ADJUST_QTY", DataType.Number, data.ADJUST_QTY.Value); req.Parameters.Add("STOCK_TAKE_QTY", DataType.Number, data.STOCK_TAKE_QTY.Value); req.Parameters.Add("ON_HAND_QTY", DataType.Number, data.ON_HAND_QTY.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("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value); req.Parameters.Add("@OLD_DATA", DataType.Default, 0); req.Parameters.Add("oldID", DataType.Default, oldID.Value); #endregion return(db.ExecuteNonQuery(req)); }
/// <summary> /// Load with specified primary key. /// </summary> /// <param name="database"></param> /// <param name="ID">Key #1</param> /// <returns></returns> public InventoryOnhandDTO LoadByPK(Database database, NZLong ID) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement string tableName = EVOFramework.Data.DTOUtility.ReadTableName(typeof(InventoryOnhandDTO)); sb.AppendLine(" SELECT "); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.YEAR_MONTH); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_END_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ITEM_CD); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOC_CD); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOT_NO); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OPEN_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.IN_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OUT_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ADJUST_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.STOCK_TAKE_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_BY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_MACHINE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_BY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_MACHINE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LAST_RECEIVE_DATE); sb.AppendLine(" FROM " + tableName); sb.AppendLine(" WHERE "); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID + "=:ID"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("ID", DataType.Default, ID.Value); #endregion List <InventoryOnhandDTO> list = db.ExecuteForList <InventoryOnhandDTO>(req); if (list != null && list.Count > 0) { return(list[0]); } return(null); }
/// <summary> /// Delete record with key. /// </summary> /// <param name="database"></param> /// <param name="ID">Key #1</param> /// <returns></returns> public int Delete(Database database, NZLong ID) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement string tableName = EVOFramework.Data.DTOUtility.ReadTableName(typeof(InventoryOnhandDTO)); sb.AppendLine(" DELETE FROM " + tableName); sb.AppendLine(" WHERE "); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID + "=:ID"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("ID", DataType.Default, ID.Value); #endregion return(db.ExecuteNonQuery(req)); }