public string InsertProgram(ref M_Program proc) { string result = string.Empty; try { SQLiteParam param = new SQLiteParam(8) { CommandText = StoreProcedure.Instance.GetScript("ProgramInsert") }; param.ParamStringFixedLength(0, "@PROG_NAME", proc.PROG_NAME); param.ParamStringFixedLength(1, "@PROG_KEY", proc.PROG_KEY); param.ParamInt32(2, "@PROG_TYPE_ID", proc.PROG_GROUP.PROG_TYPE_ID); if (proc.ORDER_BY.HasValue) { param.ParamInt32(3, "@ORDER_BY", proc.ORDER_BY.Value); } else { param.ParamNull(3, "@ORDER_BY"); } param.ParamStringFixedLength(4, "@ICON", proc.ICON); param.ParamStringFixedLength(5, "@DESCRIPTION", proc.DESCRIPTION); param.ParamStringFixedLength(6, "@PROG_RESOURCE", proc.PROG_RESOURCE); param.ParamStringFixedLength(7, "@IS_ACTIVE", (proc.IS_ACTIVE ? "Y" : "N")); int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param); object resultid = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT SEQ FROM sqlite_sequence WHERE NAME = 'M_PROGRAM'"); if (resultid != null) { proc.PROG_ID = Convert.ToInt32(resultid); } result = "OK"; } catch (Exception ex) { throw ex; } return(result); }
public string UpdateProgram(M_Program proc) { string result = string.Empty; try { SQLiteParam param = new SQLiteParam(9) { CommandText = StoreProcedure.Instance.GetScript("ProgramUpdate") }; param.ParamStringFixedLength(0, "@PROG_NAME", proc.PROG_NAME); param.ParamStringFixedLength(1, "@PROG_KEY", proc.PROG_KEY); param.ParamInt32(2, "@PROG_TYPE_ID", proc.PROG_GROUP.PROG_TYPE_ID); if (proc.ORDER_BY.HasValue) { param.ParamInt32(3, "@ORDER_BY", proc.ORDER_BY.Value); } else { param.ParamNull(3, "@ORDER_BY"); } param.ParamStringFixedLength(4, "@ICON", proc.ICON); param.ParamStringFixedLength(5, "@DESCRIPTION", proc.DESCRIPTION); param.ParamStringFixedLength(6, "@PROG_RESOURCE", proc.PROG_RESOURCE); param.ParamStringFixedLength(7, "@IS_ACTIVE", (proc.IS_ACTIVE ? "Y" : "N")); param.ParamInt32(8, "@PROG_ID", proc.PROG_ID); int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param); result = "OK"; } catch (Exception ex) { throw ex; } return(result); }
public List <M_Program> GetProgramList(int?progId) { List <M_Program> lstProgram = null; M_Program mProgram; try { SQLiteParam param = new SQLiteParam(1) { CommandText = StoreProcedure.Instance.GetScript("GetProgramList") }; if (progId.HasValue) { param.ParamInt32(0, "@PROG_TYPE_ID", progId.Value); } else { param.ParamNull(0, "@PROG_TYPE_ID"); } SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param); if (SQLiteReader.Instance.HasRows) { lstProgram = new List <M_Program>(); while (SQLiteReader.Instance.SLReader.Read()) { mProgram = new M_Program(); mProgram.PROG_ID = SQLiteReader.Instance.GetInteger("PROG_ID"); mProgram.PROG_NAME = SQLiteReader.Instance.GetString("PROG_NAME"); mProgram.PROG_KEY = SQLiteReader.Instance.GetString("PROG_KEY"); mProgram.PROG_GROUP.PROG_TYPE_ID = SQLiteReader.Instance.GetInteger("PROG_TYPE_ID"); mProgram.PROG_GROUP.PROG_TYPE_NAME = SQLiteReader.Instance.GetString("PROG_TYPE_NAME"); mProgram.PROG_TYPE_ICON = SQLiteReader.Instance.GetString("PROG_TYPE_ICON"); if (!SQLiteReader.Instance.IsDBNull("ORDER_BY")) { mProgram.ORDER_BY = SQLiteReader.Instance.GetInteger("ORDER_BY"); } mProgram.ICON = SQLiteReader.Instance.GetString("ICON"); mProgram.IS_ACTIVE = (SQLiteReader.Instance.GetString("IS_ACTIVE").ToUpper() == "Y"); mProgram.DESCRIPTION = SQLiteReader.Instance.GetString("DESCRIPTION"); mProgram.PROG_RESOURCE = SQLiteReader.Instance.GetString("PROG_RESOURCE"); lstProgram.Add(mProgram); } } //Always call Close when done reading. SQLiteReader.Instance.Close(); } catch (Exception ex) { string re = ex.Message; lstProgram = null; } return(lstProgram); }
public void InsertStockByMachine(List <StockByMachine> lstStockByMachine, DateTime lastupdate) { int flag = 0; try { object result = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT IFNULL(MIN(FLAG), 1) + 1 FROM STOCK_BY_MACHINE"); flag = Convert.ToInt32(result, NumberFormatInfo.CurrentInfo); List <SQLiteParam> lstParam = new List <SQLiteParam>(); SQLiteParam param = null; foreach (StockByMachine SQLiteInsert in lstStockByMachine) { param = new SQLiteParam(18) { CommandText = StoreProcedure.Instance.GetScript("InsertStockByMachine") }; param.ParamStringFixedLength(0, "@PARTY_ID", SQLiteInsert.PARTY_ID); param.ParamStringFixedLength(1, "@PARTY_NAME", SQLiteInsert.PARTY_NAME); if (SQLiteInsert.START_DATE.HasValue) { param.ParamDateTime(2, "@START_DATE", SQLiteInsert.START_DATE.Value); } else { param.ParamNull(2, "@START_DATE"); } if (SQLiteInsert.END_DATE.HasValue) { param.ParamDateTime(3, "@END_DATE", SQLiteInsert.END_DATE.Value); } else { param.ParamNull(3, "@END_DATE"); } param.ParamStringFixedLength(4, "@STATUS", SQLiteInsert.STATUS); param.ParamInt32(5, "@PLAN_QTY", SQLiteInsert.PLAN_QTY); param.ParamStringFixedLength(6, "@PRODUCT_TYPE", SQLiteInsert.PRODUCT_TYPE); param.ParamStringFixedLength(7, "@PROD_TYPE_S", SQLiteInsert.PROD_TYPE_S); param.ParamStringFixedLength(8, "@PRODUCT_NO", SQLiteInsert.PRODUCT_NO); param.ParamStringFixedLength(9, "@PRODUCT_NAME", SQLiteInsert.PRODUCT_NAME); param.ParamInt32(10, "@STOCK_PCS", SQLiteInsert.STOCK_PCS); param.ParamInt32(11, "@STOCK_BOX", SQLiteInsert.STOCK_BOX); param.ParamStringFixedLength(12, "@MC_NO", SQLiteInsert.MC_NO); param.ParamStringFixedLength(13, "@MACHINE_NAME", SQLiteInsert.MACHINE_NAME); param.ParamInt32(14, "@MIN_BOX", SQLiteInsert.MIN_BOX); param.ParamInt32(15, "@MAX_BOX", SQLiteInsert.MAX_BOX); param.ParamInt32(16, "@FLAG", flag); param.ParamDateTime(17, "@lastupdate", lastupdate); lstParam.Add(param); } if (lstParam.Count <= 0) { return; } int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam); if (GlobalSqliteDB.Instance.LastException != null) { throw GlobalSqliteDB.Instance.LastException; } } catch (Exception ex) { throw ex; } }