/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public int ExecuteSqlTran(List <String> SQLStringList) { using (SqlConnection conn = new SqlConnection(GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(), ConfigurationManager.AppSettings["UserName"].ToString(), ConfigurationManager.AppSettings["DB"].ToString(), ConfigurationManager.AppSettings["Env"].ToString()))) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; int current = cmd.ExecuteNonQuery(); count += current == -1 ? 0 : current; } } tx.Commit(); return(count); } catch (Exception) { tx.Rollback(); return(0); } } }
//exec SP_Y_Inventory 'PD1111-2011-12-02-0001','aaaaaa',0 /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public string ExecuteProcdure(List <String> SQLStringList, out int count) { string result = ""; count = 0; using (SqlConnection conn = new SqlConnection(GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(), ConfigurationManager.AppSettings["UserName"].ToString(), ConfigurationManager.AppSettings["DB"].ToString(), ConfigurationManager.AppSettings["Env"].ToString()))) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; string Temp = cmd.ExecuteScalar().ToString(); if (Temp != "0") { result += Temp + " 条码错误;<br />"; } else { count++; } } } return(result); } catch (Exception) { return(""); } finally { conn.Close(); } } }
protected void Report_DataBind(object sender, StoreRefreshDataEventArgs e) { if (Request["shopid"] == null) { return; } CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(), ConfigurationManager.AppSettings["UserName"].ToString(), ConfigurationManager.AppSettings["DB"].ToString(), ConfigurationManager.AppSettings["Env"].ToString()); CJ_DevelopHelper.SqlStr = "select depotid from j_user where userid='" + Request["shopid"].ToString() + "'"; DataTable dt = CJ_DevelopHelper.SQL_ReturnDateTable; if (dt.Rows.Count <= 0) { return; } CJ_DevelopHelper.SqlStr = "select xl 系列,stycolorid 款式,get_date 上货日期,nums 销售,k_num 库存 from T_skctopsale_message where depotid1='" + dt.Rows[0][0].ToString() + "' and nums=0 order by xl desc"; Report_MainStore.DataSource = CJ_DevelopHelper.SQL_ReturnDateTable; Report_MainStore.DataBind(); }
protected void Btnsave_Click(object sender, DirectEventArgs e) { try { string filename = UpFile.PostedFile.FileName; string ext = Path.GetExtension(filename).ToLower(); if (ext != ".xls" && ext != ".xlsx") { X.Msg.Notify("失败", "<font color='red'>文件格式不正确,仅支持.xls(Excel)格式文件!</font>").Show(); return; } filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ext; UpFile.PostedFile.SaveAs(Server.MapPath("File/" + filename)); DataTable dt = new DataTable(); CJ_DevelopHelper.OtherCon_Str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("File/" + filename) + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"; CJ_DevelopHelper.SqlStr = "select 款式编号 as sku,色号 as color,F,F4,F5,F6,F7,F8,F9 from [sheet1$] where 款式编号<>''"; dt = CJ_DevelopHelper.Other_ReturnDateTable; if (dt.Rows.Count <= 0) { X.Msg.Alert("提醒", "导入的文件无记录!").Show(); return; } string errorSKU = ""; CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(), ConfigurationManager.AppSettings["UserName"].ToString(), ConfigurationManager.AppSettings["DB"].ToString(), ConfigurationManager.AppSettings["Env"].ToString()); string allsku = ""; int rowcount = 0; StringBuilder errtxt = new StringBuilder(); errtxt.AppendLine("款式编号\t色号\t尺码\t错因"); foreach (DataRow dr in dt.Rows) { string sku = dr[0].ToString().ToUpper(); if (sku.Length <= 0) { continue; } for (int i = 2; i <= 8; i++) { string size = i == 2 ? "F" : (i - 1).ToString(); if (dr[i].ToString().Trim().Length > 0) { if (int.Parse(dr[i].ToString()) < 0) { errorSKU += sku + dr[1].ToString().Trim() + size + " 数量为负;<br />"; errtxt.AppendLine(sku + "\t" + dr[1].ToString().Trim() + "\t" + size + "\t数量为负"); } else { allsku += "'" + sku + dr[1].ToString().Trim() + size + "',"; rowcount++; } } } } CJ_DevelopHelper.SqlStr = "select clothingid,styleid,colorid from j_clothing where clothingid in (" + allsku.TrimEnd(',') + ")"; DataTable dtDB = CJ_DevelopHelper.SQL_ReturnDateTable; //找到错款编号 if (rowcount != dtDB.Rows.Count) { string [] sku = allsku.TrimEnd(',').Split(','); for (int i = 0; i < sku.Length; i++) { int isfind = 0; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString().ToUpper() == sku[i].ToUpper().Trim('\'')) { isfind = 1; break; } } if (isfind == 0) { errorSKU += sku[i].Trim('\'') + " 款式错误;<br />"; errtxt.AppendLine(sku[i].Trim('\'').Substring(0, sku[i].Trim('\'').Length - 2) + "\t" + sku[i].Trim('\'').Substring(sku[i].Trim('\'').Length - 2, 1) + "\t" + sku[i].Trim('\'').Substring(sku[i].Trim('\'').Length - 1, 1) + "\t该款式无此尺码"); } } } if (errorSKU != "") { string str = DateTime.Now.ToString("yyyyMMddHHmmss") + "Back_ERROR" + ".txt"; CJ_DevelopHelper.FilePath = Server.MapPath("File/") + str; CJ_DevelopHelper.FileContent = errtxt.ToString(); if (!CJ_DevelopHelper.WriteFile) { X.Msg.Alert("提醒", "数据部分存在错误!<br />但生成异常文件时错误,请重试!").Show(); return; } X.Msg.Alert("提醒", "存在错误记录,请先检查!<br />请下载异常款文件<a href='File/" + str + "' target='_blank'>" + str + "</a>,修改后重新提交<br />错误提示:<br />" + errorSKU).Show(); return; } checknum_MainStore.DataSource = dt; checknum_MainStore.DataBind(); if (File.Exists(Server.MapPath("File/" + filename))) { File.Delete(Server.MapPath("File/" + filename)); } } catch (Exception error) { X.Msg.Alert("错误", error.Message + "!请认真检查文档格式<br />各个款式退货数量是否存在非数字").Show(); } }
private void LongAction(object state) { try { DataTable dt = (DataTable)Session["mainDT"]; string errorSKU = ""; CJ_DevelopHelper.SqlConn_Str = GetDBPwd.GetDBConString_MSSQL(ConfigurationManager.AppSettings["AuthCode"].ToString(), ConfigurationManager.AppSettings["UserName"].ToString(), ConfigurationManager.AppSettings["DB"].ToString(), ConfigurationManager.AppSettings["Env"].ToString()); int totalnum = 0; StringBuilder errtxt = new StringBuilder(); errtxt.AppendLine("条码\t数量"); int i = 1;//记录 List <string> sqlist = new List <string>(); this.Session["Total"] = dt.Rows.Count; int successDataCount = 0; foreach (DataRow dr in dt.Rows) { string sku = dr[0].ToString().ToUpper(); if (sku.Length <= 0) { continue; } if (int.Parse(dr[1].ToString()) <= 0) { errorSKU += sku + " 盘点数量为负或零;<br />"; continue; } else { sqlist.Add("exec SP_Y_Inventory '" + Session["listno"].ToString() + "','" + sku + "'," + dr[1].ToString().Trim() + ""); if (i % 50 == 0)//每50条,提交到数据库执行 { int tmpcount = 0; string temp = ExecuteProcdure(sqlist, out tmpcount); if (temp != "") { errorSKU += temp; } sqlist.Clear(); successDataCount += tmpcount; this.Session["LongActionProgress"] = i; } } i++; totalnum += int.Parse(dr[1].ToString()); } if (sqlist.Count > 0) { int tmpcount = 0; string temp = ExecuteProcdure(sqlist, out tmpcount); if (temp != "") { errorSKU += temp; } sqlist.Clear(); successDataCount += tmpcount; this.Session["LongActionProgress"] = i; } sqlist.Add("exec F22_RefreshDj 'd_inventory','" + Session["listno"].ToString() + "' ;"); ExecuteSqlTran(sqlist); if (errorSKU != "") { this.Session["result"] = "导入成功" + successDataCount.ToString() + "条!但部分数据导入失败,存在错误,错误数据请检查后重新提交<br />错误数据:<br />" + errorSKU; } else { this.Session["result"] = "恭喜您,全部导入成功(" + successDataCount.ToString() + "条)!"; } } catch (Exception error) { this.Session["result"] = "异常:" + error.Message + "!请认真检查盘点文档格式"; } finally { this.Session.Remove("mainDT"); this.Session.Remove("LongActionProgress"); this.Session.Remove("listno"); } }