public static string GetPSTransactionID() { lock (syncObject) { var dt = SqlDbService.GetDBRecords("select isnull(TransactionNo,0) from [dbo].[PSProfile]", DataSource.CSCMaster); int TransID = (int)dt.Rows[0][0]; if (TransID == 0 || TransID == int.MaxValue) { TransID = 1; } else { TransID += 1; } SqlDbService.ExecuteCmd("update [dbo].[PSProfile] set TransactionNo=" + TransID, DataSource.CSCMaster); return(string.Format("{0:0000000000}", TransID)); } }
private void PopulateStock(DataTable dt) { if (dt.Rows.Count == 0) { return; } string sTableName; SqlDataAdapter oDA; DataRow tdr = null; DateTime oDatetime = DateTime.Now; using (SqlConnection oCon = new SqlConnection(GetConnectionString(DataSource.CSCMaster))) { DataTable dtSchema_STOCKMST = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.STOCKMST"); DataTable oDT_STOCKMST = SqlDbService.GetTableTemplate("CSCMaster.dbo.STOCKMST", oCon.ConnectionString); DataTable dtSchema_StockPrice = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.Stock_Prices"); DataTable oDT_StockPrice = SqlDbService.GetTableTemplate("CSCMaster.dbo.Stock_Prices", oCon.ConnectionString); DataTable dtSchema_STOCK_BR = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.STOCK_BR"); DataTable oDT_STOCK_BR = SqlDbService.GetTableTemplate("CSCMaster.dbo.STOCK_BR", oCon.ConnectionString); DataTable dtSchema_PLUMAST = SqlDbService.GetTableSchema(oCon.ConnectionString, "CSCMaster.dbo.PLUMAST"); DataTable oDT_PLUMAST = SqlDbService.GetTableTemplate("CSCMaster.dbo.PLUMAST", oCon.ConnectionString); oCon.Open(); SqlTransaction trans = oCon.BeginTransaction(); try { //CSCMaster.dbo.STOCKMST sTableName = "CSCMaster.dbo.STOCKMST"; oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, oCon, dtSchema_STOCKMST); oDA.InsertCommand.Transaction = trans; //dbSchema = SqlDbService.GetTableSchema(oCon.ConnectionString, sTableName); //oDT = SqlDbService.GetTableTemplate(sTableName, oCon.ConnectionString); foreach (DataRow sdr in dt.Rows) { tdr = oDT_STOCKMST.NewRow(); SqlDbService.InitializeRow(tdr, dtSchema_STOCKMST); tdr["STOCK_CODE"] = sdr["UpcNumber"]; tdr["DESCRIPT"] = sdr["Name"]; tdr["STOCK_TYPE"] = "V"; tdr["Vendor"] = "ALL"; tdr["DEPT"] = "40"; tdr["PR_TYPE"] = "R"; tdr["PR_UNITS"] = "$"; tdr["Availability"] = 1; tdr["CreationDate"] = oDatetime; tdr["UpdateDate"] = oDatetime; tdr["Packing"] = 1; tdr["ElgLoyalty"] = 1; tdr["PROD_DISC"] = 0; tdr["STD_COST"] = 0; tdr["AVG_COST"] = 0; tdr["AVG_UNITS"] = 0; tdr["LATE_COST"] = 0; tdr["SUB_DEPT"] = ""; tdr["Sub_Detail"] = ""; tdr["SERIAL"] = 0; tdr["S_BY_WGHT"] = 0; tdr["QualTaxRebate"] = 0; tdr["ElgTaxRebate"] = 0; tdr["ElgTaxExemption"] = 0; oDT_STOCKMST.Rows.Add(tdr); } oDA.Update(oDT_STOCKMST); //CSCMaster.dbo.Stock_Prices sTableName = "CSCMaster.dbo.Stock_Prices"; oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, trans.Connection, dtSchema_StockPrice); oDA.InsertCommand.Transaction = trans; foreach (DataRow sdr in dt.Rows) { tdr = oDT_StockPrice.NewRow(); SqlDbService.InitializeRow(tdr, dtSchema_StockPrice); tdr["Stock_Code"] = sdr["UpcNumber"]; tdr["Price_Number"] = 1; tdr["Price"] = double.Parse(sdr["Amount"].ToString()); tdr["VendorID"] = "ALL"; tdr["OrderNo"] = 0; tdr["Margin"] = 0; tdr["UpdateDate"] = oDatetime; oDT_StockPrice.Rows.Add(tdr); } oDA.Update(oDT_StockPrice); //CSCMaster.dbo.STOCK_BR sTableName = "CSCMaster.dbo.STOCK_BR"; oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, trans.Connection, dtSchema_STOCK_BR); oDA.InsertCommand.Transaction = trans; foreach (DataRow sdr in dt.Rows) { tdr = oDT_STOCK_BR.NewRow(); SqlDbService.InitializeRow(tdr, dtSchema_STOCK_BR); tdr["STOCK_CODE"] = sdr["UpcNumber"]; tdr["BRANCH"] = "01"; tdr["IN_STOCK"] = -7; tdr["AVAIL"] = -7; tdr["ONORDER_IC"] = 0; tdr["ORDCOST_IC"] = 0; tdr["ONORDER_PO"] = 0; tdr["ALLOC_LAY"] = 0; tdr["ALLOC_OE"] = 0; tdr["RETURNED"] = 0; tdr["HOLD"] = 0; tdr["WASTE"] = 0; tdr["STOCK_TAKE"] = 0; tdr["TAKE_ENTER"] = 0; tdr["MIN_QTY"] = 0; tdr["MAX_QTY"] = 0; tdr["REORD_QTY"] = 0; tdr["LAST_SALE"] = DateTime.Now.ToShortDateString(); tdr["HOLD"] = 0; oDT_STOCK_BR.Rows.Add(tdr); } oDA.Update(oDT_STOCK_BR); //CSCMaster.dbo.Stock_Prices sTableName = "CSCMaster.dbo.PLUMAST"; oDA = SqlDbService.CreateInsertSqlDataAdapter(sTableName, trans.Connection, dtSchema_PLUMAST); oDA.InsertCommand.Transaction = trans; foreach (DataRow sdr in dt.Rows) { tdr = oDT_PLUMAST.NewRow(); SqlDbService.InitializeRow(tdr, dtSchema_PLUMAST); tdr["PLU_CODE"] = sdr["UpcNumber"]; tdr["PLU_PRIM"] = sdr["UpcNumber"]; tdr["PLU_TYPE"] = "S"; oDT_PLUMAST.Rows.Add(tdr); } oDA.Update(oDT_PLUMAST); trans.Commit(); oDA.Dispose(); oCon.Close(); oCon.Dispose(); } catch (Exception ex) { trans.Rollback(); throw ex; } } }