public int Import(string dbCode, int lgIndex, string fileName) { DbHelperOledb oledb = new DbHelperOledb(); DataSet ds = oledb.ChangeExcelToDataSet(fileName); DbHelperSQL _DbH = new DbHelperSQL(dbCode); using (SqlConnection conn = new SqlConnection(_DbH.ConnectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction = trans; cmd.CommandTimeout = 200; try { this.PrepareImport(cmd, ds); trans.Commit(); } catch { trans.Rollback(); throw; } } } return(ds.Tables[0].Rows.Count); }
public int Import(string dbCode, int lgIndex, string fileName, string BID) { DbHelperOledb oledb = new DbHelperOledb(); DataSet ds = oledb.ChangeExcelToDataSet(fileName); Hashtable htstrSqlMain = new Hashtable(); Hashtable htstrSqlSub = new Hashtable(); SqlParameter[] parameters = null; StringBuilder strSql = new StringBuilder(); strSql.Append("Delete Sale_PriceContract_Frame where BID=@BID ;"); parameters = new SqlParameter[] { new SqlParameter("@BID", SqlDbType.VarChar, 25) }; parameters[0].Value = BID; htstrSqlMain.Add(strSql.ToString(), parameters); strSql.Clear(); int i = 1; foreach (DataRow dr in ds.Tables[0].Rows) { strSql.Append("delete Sale_PriceContract_Frame "); strSql.Append("where BID=@BID and FrameCode=@FrameCode ;"); //////////////////////// strSql.Append("insert into Sale_PriceContract_Frame("); strSql.Append("ID,BID,FrameCode,Price,PriceJM,InvTitle)"); strSql.Append(" values ("); strSql.Append("(select HKOERP.dbo.SF_GetID()),@BID,@FrameCode,@Price,@PriceJM,@InvTitle)"); parameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.VarChar, 25), new SqlParameter("@BID", SqlDbType.VarChar, 25), new SqlParameter("@FrameCode", SqlDbType.VarChar, 30), new SqlParameter("@Price", SqlDbType.Decimal, 9), new SqlParameter("@PriceJM", SqlDbType.Decimal, 9), new SqlParameter("@InvTitle", SqlDbType.NVarChar, 50) }; parameters[0].Value = ""; parameters[1].Value = BID; parameters[2].Value = dr["FrameCode"].ToString(); parameters[3].Value = dr["Price"].ToString(); parameters[4].Value = dr["PriceJM"].ToString(); parameters[5].Value = dr["InvTitle"].ToString(); strSql.Append("--" + i++.ToString()); htstrSqlSub.Add(strSql.ToString(), parameters); strSql.Clear(); } DbHelperSQL u = new DbHelperSQL(dbCode); u.ExecuteSqlTran(htstrSqlMain, htstrSqlSub); return(htstrSqlSub.Count); }
public int Import(string dbCode, int lgIndex, string fileName, string BID) { DbHelperOledb oledb = new DbHelperOledb(); DataSet ds = oledb.ChangeExcelToDataSet(fileName); Hashtable htstrSqlMain = new Hashtable(); Hashtable htstrSqlSub = new Hashtable(); SqlParameter[] parameters = null; StringBuilder strSql = new StringBuilder(); strSql.Append("Delete Sale_PriceContract_CusGroup_CusCode where GpCode=@GpCode ;"); parameters = new SqlParameter[] { new SqlParameter("@GpCode", SqlDbType.VarChar, 22) }; parameters[0].Value = BID; htstrSqlMain.Add(strSql.ToString(), parameters); strSql.Clear(); int i = 1; foreach (DataRow dr in ds.Tables[0].Rows) { strSql.Append("insert into Sale_PriceContract_CusGroup_CusCode("); strSql.Append("GpCode,CusCode)"); strSql.Append(" values (@GpCode,@CusCode);"); parameters = new SqlParameter[] { new SqlParameter("@GpCode", SqlDbType.NVarChar, 30), new SqlParameter("@CusCode", SqlDbType.NVarChar, 10) }; parameters[0].Value = dr["GpCode"].ToString(); parameters[1].Value = dr["CusCode"].ToString(); strSql.Append("--" + i++.ToString()); htstrSqlSub.Add(strSql.ToString(), parameters); strSql.Clear(); } DbHelperSQL u = new DbHelperSQL(dbCode); u.ExecuteSqlTran(htstrSqlMain, htstrSqlSub); return(htstrSqlSub.Count); }
public int Import(string dbCode, int lgIndex, string fileName) { DbHelperOledb oledb = new DbHelperOledb(); DataSet ds = oledb.ChangeExcelToDataSet(fileName); Hashtable htstrSql = new Hashtable(); SqlParameter[] parameters = null; StringBuilder strSql = new StringBuilder(); int i = 1; foreach (DataRow dr in ds.Tables[0].Rows) { strSql.Append("Delete B_Process where ProcessCode=@ProcessCode and ProcessName=@ProcessName;"); strSql.Append("insert into B_Process("); strSql.Append("ProcessCode,ProcessName,Remark,CF_Flag,KF_Flag)"); strSql.Append(" values ("); strSql.Append("@ProcessCode,@ProcessName,@Remark,@CF_Flag,@KF_Flag)"); parameters = new SqlParameter[] { new SqlParameter("@ProcessCode", SqlDbType.NVarChar, 20), new SqlParameter("@ProcessName", SqlDbType.NVarChar, 50), new SqlParameter("@Remark", SqlDbType.NVarChar, 100), new SqlParameter("@CF_Flag", SqlDbType.Bit, 1), new SqlParameter("@KF_Flag", SqlDbType.Bit, 1) }; parameters[0].Value = dr["ProcessCode"].ToString().Trim(); parameters[1].Value = dr["ProcessName"].ToString().Trim(); parameters[2].Value = dr["Remark"].ToString().Trim(); parameters[3].Value = Convert.ToBoolean(dr["CF_Flag"].ToString() == "" ? false : dr["CF_Flag"]); parameters[4].Value = Convert.ToBoolean(dr["KF_Flag"].ToString() == "" ? false : dr["KF_Flag"]); strSql.Append("--" + i++.ToString()); htstrSql.Add(strSql.ToString(), parameters); strSql.Clear(); } DbHelperSQL u = new DbHelperSQL(dbCode); u.ExecuteSqlTran(htstrSql); return(htstrSql.Count); }
public int Import(string dbCode, int lgIndex, string fileName) { DbHelperOledb oledb = new DbHelperOledb(); DataSet ds = oledb.ChangeExcelToDataSet(fileName); Hashtable htstrSql = new Hashtable(); SqlParameter[] parameters = null; StringBuilder strSql = new StringBuilder(); int i = 1; foreach (DataRow dr in ds.Tables[0].Rows) { strSql.Append("insert into B_ProcessControl("); strSql.Append("SubID,Mnumber,JuSe,ChaSe,ShuiYin,RanSe,UV,JingJia,Hardened,PaoGuang,CaiBian,CheBian,KaiKeng,PiHua,DaoBian,MianWan,ZuanKong,OtherProcess,ExtraProcess,IsForce)"); strSql.Append(" values ("); strSql.Append("@SubID,@Mnumber,@JuSe,@ChaSe,@ShuiYin,@RanSe,@UV,@JingJia,@Hardened,@PaoGuang,@CaiBian,@CheBian,@KaiKeng,@PiHua,@DaoBian,@MianWan,@ZuanKong,@OtherProcess,@ExtraProcess,@IsForce)"); parameters = new SqlParameter[] { new SqlParameter("@SubID", SqlDbType.Int, 4), new SqlParameter("@Mnumber", SqlDbType.NVarChar, 50), new SqlParameter("@JuSe", SqlDbType.NVarChar, 30), new SqlParameter("@ChaSe", SqlDbType.NVarChar, 30), new SqlParameter("@ShuiYin", SqlDbType.NVarChar, 30), new SqlParameter("@RanSe", SqlDbType.NVarChar, 30), new SqlParameter("@UV", SqlDbType.Bit, 1), new SqlParameter("@JingJia", SqlDbType.NVarChar, 30), new SqlParameter("@Hardened", SqlDbType.Bit, 1), new SqlParameter("@PaoGuang", SqlDbType.NVarChar, 30), new SqlParameter("@CaiBian", SqlDbType.NVarChar, 30), new SqlParameter("@CheBian", SqlDbType.NVarChar, 30), new SqlParameter("@KaiKeng", SqlDbType.NVarChar, 30), new SqlParameter("@PiHua", SqlDbType.NVarChar, 30), new SqlParameter("@DaoBian", SqlDbType.NVarChar, 30), new SqlParameter("@MianWan", SqlDbType.NVarChar, 30), new SqlParameter("@ZuanKong", SqlDbType.NVarChar, 30), new SqlParameter("@OtherProcess", SqlDbType.NVarChar, 30), new SqlParameter("@ExtraProcess", SqlDbType.NVarChar, 30), new SqlParameter("@IsForce", SqlDbType.Bit, 1) }; parameters[0].Value = --i; parameters[1].Value = dr["Mnumber"].ToString().Trim(); parameters[2].Value = dr["JuSe"].ToString().Trim(); parameters[3].Value = dr["ChaSe"].ToString().Trim(); parameters[4].Value = dr["ShuiYin"].ToString().Trim(); parameters[5].Value = dr["RanSe"].ToString().Trim(); parameters[6].Value = Convert.ToBoolean(dr["UV"].ToString() == "" ? false : dr["UV"]); parameters[7].Value = dr["JingJia"].ToString().Trim(); parameters[8].Value = Convert.ToBoolean(dr["Hardened"].ToString() == "" ? false : dr["Hardened"]); parameters[9].Value = dr["PaoGuang"].ToString().Trim(); parameters[10].Value = dr["CaiBian"].ToString().Trim(); parameters[11].Value = dr["CheBian"].ToString().Trim(); parameters[12].Value = dr["KaiKeng"].ToString().Trim(); parameters[13].Value = dr["PiHua"].ToString().Trim(); parameters[14].Value = dr["DaoBian"].ToString().Trim(); parameters[15].Value = dr["MianWan"].ToString().Trim(); parameters[16].Value = dr["ZuanKong"].ToString().Trim(); parameters[17].Value = dr["OtherProcess"].ToString().Trim(); parameters[18].Value = dr["ExtraProcess"].ToString().Trim(); parameters[19].Value = Convert.ToBoolean(dr["IsForce"].ToString() == "" ? false : dr["IsForce"]); strSql.Append("--" + i.ToString()); htstrSql.Add(strSql.ToString(), parameters); strSql.Clear(); } DbHelperSQL u = new DbHelperSQL(dbCode); u.ExecuteSqlTran(htstrSql); strSql.Clear(); strSql.Append(@" ;DELETE FROM B_ProcessControl WHERE B_ProcessControl.%%physloc%% NOT IN (SELECT MIN(b.%%physloc%%) FROM B_ProcessControl b GROUP BY b.Mnumber,b.JuSe,b.ChaSe,b.ShuiYin,b.RanSe,b.UV,b.JingJia, b.Hardened,b.PaoGuang,b.CaiBian,b.CheBian,b.KaiKeng, b.PiHua,b.DaoBian,b.MianWan,b.ZuanKong,b.OtherProcess, b.ExtraProcess,b.IsForce)" ); strSql.Append(@";update B_ProcessControl set SubID= (select N_ID from (select ROW_NUMBER() OVER (ORDER BY Mnumber) as N_ID,SubID from B_ProcessControl s ) as a where a.SubID=B_ProcessControl.SubID ) "); u.ExecuteSql(strSql.ToString()); return(htstrSql.Count); }
public int Import(string dbCode, int lgIndex, string fileName, string BID) { DbHelperOledb oledb = new DbHelperOledb(); DataSet ds = oledb.ChangeExcelToDataSet(fileName); Hashtable htstrSqlMain = new Hashtable(); Hashtable htstrSqlSub = new Hashtable(); SqlParameter[] parameters = null; StringBuilder strSql = new StringBuilder(); strSql.Append("Delete Sale_PriceContract_Lens_ProCost where BID=@BID ;"); parameters = new SqlParameter[] { new SqlParameter("@BID", SqlDbType.VarChar, 25) }; parameters[0].Value = BID; htstrSqlMain.Add(strSql.ToString(), parameters); strSql.Clear(); int i = 1; foreach (DataRow dr in ds.Tables[0].Rows) { strSql.Append("delete Sale_PriceContract_Lens_ProCost "); strSql.Append("where BID=@BID and LensCode=@LensCode and F_Set=@F_Set "); strSql.Append("and JY=@JY and UV=@UV and JS=@JS and RS=@RS and CS=@CS "); strSql.Append("and SY=@SY and CB=@CB and ChB=@ChB and KK=@KK and ZK=@ZK "); strSql.Append("and PiH=@PiH and PG=@PG and JJ=@JJ and OP=@OP ; "); //////////////////////// strSql.Append("insert into Sale_PriceContract_Lens_ProCost("); strSql.Append("ID,BID,LensCode,F_Set,InvTitle,JY,UV,JS,RS,CS,SY,CB,ChB,KK,ZK,PiH,PG,JJ,OP,P1,P2,P1JM,P2JM)"); strSql.Append(" values ("); strSql.Append("(select HKOERP.dbo.SF_GetID()),@BID,@LensCode,@F_Set,@InvTitle,@JY,@UV,@JS,@RS,@CS,@SY,@CB,@ChB,@KK,@ZK,@PiH,@PG,@JJ,@OP,@P1,@P2,@P1JM,@P2JM)"); parameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.VarChar, 25), new SqlParameter("@BID", SqlDbType.VarChar, 25), new SqlParameter("@LensCode", SqlDbType.VarChar, 30), new SqlParameter("@F_Set", SqlDbType.Bit, 1), new SqlParameter("@InvTitle", SqlDbType.NVarChar, 40), new SqlParameter("@JY", SqlDbType.Bit, 1), new SqlParameter("@UV", SqlDbType.Bit, 1), new SqlParameter("@JS", SqlDbType.VarChar, 15), new SqlParameter("@RS", SqlDbType.VarChar, 15), new SqlParameter("@CS", SqlDbType.VarChar, 15), new SqlParameter("@SY", SqlDbType.VarChar, 15), new SqlParameter("@CB", SqlDbType.VarChar, 15), new SqlParameter("@ChB", SqlDbType.VarChar, 15), new SqlParameter("@KK", SqlDbType.VarChar, 15), new SqlParameter("@ZK", SqlDbType.VarChar, 15), new SqlParameter("@PiH", SqlDbType.VarChar, 15), new SqlParameter("@PG", SqlDbType.VarChar, 15), new SqlParameter("@JJ", SqlDbType.VarChar, 15), new SqlParameter("@OP", SqlDbType.VarChar, 15), new SqlParameter("@P1", SqlDbType.Decimal, 9), new SqlParameter("@P2", SqlDbType.Decimal, 9), new SqlParameter("@P1JM", SqlDbType.Decimal, 9), new SqlParameter("@P2JM", SqlDbType.Decimal, 9) }; parameters[0].Value = ""; parameters[1].Value = BID; parameters[2].Value = dr["LensCode"].ToString(); parameters[3].Value = System.Convert.ToBoolean(dr["F_Set"].ToString() == "" ? false : dr["F_Set"]); parameters[4].Value = dr["InvTitle"].ToString(); parameters[5].Value = System.Convert.ToBoolean(dr["JY"].ToString() == "" ? false : dr["JY"]); parameters[6].Value = System.Convert.ToBoolean(dr["UV"].ToString() == "" ? false : dr["UV"]); parameters[7].Value = dr["JS"].ToString(); parameters[8].Value = dr["RS"].ToString(); parameters[9].Value = dr["CS"].ToString(); parameters[10].Value = dr["SY"].ToString(); parameters[11].Value = dr["CB"].ToString(); parameters[12].Value = dr["ChB"].ToString(); parameters[13].Value = dr["KK"].ToString(); parameters[14].Value = dr["ZK"].ToString(); parameters[15].Value = dr["PiH"].ToString(); parameters[16].Value = dr["PG"].ToString(); parameters[17].Value = dr["JJ"].ToString(); parameters[18].Value = dr["OP"].ToString(); parameters[19].Value = dr["P1"].ToString(); parameters[20].Value = dr["P2"].ToString(); parameters[21].Value = dr["P1JM"].ToString(); parameters[25].Value = dr["P2JM"].ToString(); strSql.Append("--" + i++.ToString()); htstrSqlSub.Add(strSql.ToString(), parameters); strSql.Clear(); } DbHelperSQL u = new DbHelperSQL(dbCode); u.ExecuteSqlTran(htstrSqlMain, htstrSqlSub); return(htstrSqlSub.Count); }