private void readExcel(string filename) { try { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\""; using (OleDbConnection conn = new OleDbConnection(connstring)) { conn.Open(); DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字 //for(int k=0;k<sheetsName.Rows.Count;k++) //{ // log.Info(sheetsName.Rows[k][2].ToString()); //} string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字 string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring); DataSet set = new DataSet(); ada.Fill(set); DataTable dt = set.Tables[0]; SQLHelper.DbHelperSQL.SetConnectionString(""); ArrayList al = new ArrayList(); for (int i = 1; i < dt.Rows.Count; i++) { if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "") { break; } else { sql = "select count(*) from Provider where ProviderNo='" + dt.Rows[i][0].ToString() + "'"; if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0) { sql = "update Provider set Rank='" + dt.Rows[i][2].ToString() + "',Stype='" + dt.Rows[i][3].ToString() + "',Address='" + dt.Rows[i][4].ToString() + "',ContactMan='" + dt.Rows[i][5].ToString() + "',Telephone='" + dt.Rows[i][6].ToString() + "',Fax='" + dt.Rows[i][7].ToString() + "',Email='" + dt.Rows[i][8].ToString() + "',PurchaseMan='" + dt.Rows[i][9].ToString() + "',IsValid='" + dt.Rows[i][10].ToString() + "' where ProviderNo='" + dt.Rows[i][0].ToString() + "'"; al.Add(sql); } else { string spid = dt.Rows[i][0].ToString(); spid = spid.Substring(2); spid = "SP" + spid.PadLeft(3, '0'); sql = "insert into Provider(ProviderNo,name,Rank,Stype,Address,ContactMan,Telephone,Fax,Email,PurchaseMan,IsValid) values('" + spid + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')"; al.Add(sql); FileOper.writeLog(sql); } } } if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al)) { Alert.Show("导入成功"); } else { Alert.Show("导入失败"); } } } catch (Exception ee) { log.Info(ee.ToString()); } finally { BindGrid(); } }
protected void btnPLSend_Click(object sender, EventArgs e) { try { if (Grid2.GetModifiedData().Count != 0 || Grid2.GetNewAddedList().Count != 0) { Alert.Show("表格还有未保存的数据,请先保存!"); return; } if (Grid2.SelectedRowIndexArray.Length == 0) { Alert.Show("请选择物料"); return; } StringBuilder sql = new StringBuilder(); ArrayList al = new ArrayList(); string s = "", orderno = "", prono = "", pname = ""; SQLHelper.DbHelperSQL.SetConnectionString(""); foreach (int i in Grid2.SelectedRowIndexArray) { orderno = Grid1.Rows[Grid1.SelectedRowIndex].Values[1].ToString(); prono = Grid1.Rows[Grid1.SelectedRowIndex].Values[3].ToString(); pname = Grid1.Rows[Grid1.SelectedRowIndex].Values[4].ToString(); s = "select count(*) from Instruction where OrderNo='" + orderno + "' and ProNo='" + prono + "' and itemno='" + Grid2.Rows[i].Values[2].ToString() + "' and IsConfirm=0"; if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(s, 30)) > 0) { continue; } s = "select (case when sum(ConfirmQuantity) is null then 0 else sum(ConfirmQuantity) end) from instruction where orderno='" + orderno + "' and prono='" + prono + "' and itemno='" + Grid2.Rows[i].Values[2].ToString() + "'"; sql.Clear(); sql.Append("insert into Instruction(OrderNo,ProNo,ProName,ItemNo,ItemName,Spec,Material,SurfaceDeal,UsingQuantity,Sclass,MainFrom,Inputer,InputeDate,IsConfirm,IsPlan,ReceiveDept,Receiver,BarCode,BomSN)"); sql.Append(" values("); sql.Append("'" + orderno + "',"); sql.Append("'" + prono + "',"); sql.Append("'" + pname + "',"); sql.Append("'" + Grid2.Rows[i].Values[2].ToString() + "',"); //ItemNo sql.Append("'" + Grid2.Rows[i].Values[3].ToString() + "',"); //ItemName sql.Append("'" + Grid2.Rows[i].Values[4].ToString() + "',"); //Spec sql.Append("'" + Grid2.Rows[i].Values[5].ToString() + "',"); //Material sql.Append("'" + Grid2.Rows[i].Values[6].ToString() + "',"); //SurfaceDeal sql.Append("" + Grid2.Rows[i].Values[8].ToString() + "-(" + s + "),"); //UsingQuantity sql.Append("'" + Grid2.Rows[i].Values[9].ToString() + "',"); //Sclass sql.Append("'" + Grid2.Rows[i].Values[12].ToString() + "',"); //MainFrom sql.Append("'" + User.Identity.Name + "',"); sql.Append("getdate(),"); sql.Append("0,"); sql.Append("0,"); sql.Append("'" + ddlDept.SelectedText + "',"); sql.Append("'" + ddlUser.SelectedValue + "',"); sql.Append("'" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + "',"); sql.Append(Grid2.DataKeys[i][0]); sql.Append(")"); al.Add(sql.ToString()); System.Threading.Thread.Sleep(1); FileOper.writeLog(sql.ToString()); } if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al)) { Alert.Show("发送成功"); BindGrid2(); } else { Alert.Show("发送失败"); } } catch (Exception ee) { Alert.Show(ee.ToString()); } }
private void readExcel(string filename) { string fsn = ""; try { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/OrderFile/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\""; ArrayList al = new ArrayList(); using (OleDbConnection conn = new OleDbConnection(connstring)) { conn.Open(); DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字 //for(int k=0;k<sheetsName.Rows.Count;k++) //{ // log.Info(sheetsName.Rows[k][2].ToString()); //} string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字 string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring); DataSet set = new DataSet(); ada.Fill(set); DataTable dt = set.Tables[0]; int i = 0; SQLHelper.DbHelperSQL.SetConnectionString(""); string OrderNo = "", LotNo = "", ClientCode = "", ClientOrderNo = "", RecOrderPersonID = "", RecOrderPerson = "", RecOrderDate = "", SendOrderDate = "", OutGoodsDate = "", checkman = "", CheckDate = "", ContainerType = "", ischeck = "", OrderType = ""; //产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期 //获取表头 添加orderheader if (dt != null && dt.Rows.Count >= 6) { OrderNo = dt.Rows[1][2].ToString(); if (OrderNo.Length == 12 && OrderNo.Substring(0, 2) == "RL" && CommFunction.IsNumeric(OrderNo.Substring(2, 10))) { } else { Alert.Show("订单号格式不正确,禁止导入"); return; } OrderType = dt.Rows[1][5].ToString(); LotNo = dt.Rows[1][8].ToString(); ClientCode = dt.Rows[1][11].ToString(); ClientOrderNo = dt.Rows[2][2].ToString(); RecOrderPerson = dt.Rows[3][2].ToString(); checkman = dt.Rows[3][11].ToString(); ContainerType = dt.Rows[3][8].ToString(); RecOrderDate = dt.Rows[4][2].ToString(); CheckDate = dt.Rows[4][8].ToString(); OutGoodsDate = dt.Rows[4][11].ToString(); if (CheckDate != "" || checkman != "") { ischeck = "1"; } else { ischeck = "0"; } sql = "select sn from OrderHeader where OrderNo='" + OrderNo + "'"; log.Info(sql); DataTable dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30); if (dtitem == null || dtitem.Rows.Count == 0) { //sql = "insert into allitem(itemno,name) values('" + dt.Rows[2][4].ToString() + "','" + dt.Rows[2][2].ToString() + "')"; //log.Info("sqlallitem::::" + sql); //al.Add(sql); sql = "insert into OrderHeader(OrderNo,LotNo,ClientCode,ClientOrderNo,RecOrderPerson,RecOrderDate,SendOrderDate,OutGoodsDate,Inputer,InputerDate,IsCheck,OrderExcel,ContainerType,Checker,CheckDate,OrderType) values('" + OrderNo + "','" + LotNo + "','" + ClientCode + "','" + ClientOrderNo + "','" + RecOrderPerson + "','" + RecOrderDate.Replace(".", "-") + "','" + SendOrderDate.Replace(".", "-") + "','" + OutGoodsDate.Replace(".", "-") + "','" + User.Identity.Name + "',getdate()," + ischeck + ",'" + filename + "','" + ContainerType + "','" + checkman + "','" + CheckDate + "','" + OrderType + "')"; log.Info("sqlbase::::" + sql); al.Add(sql); //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30); } else { Alert.Show("订单号已存在"); return; //产品名称、产品编号、版本、瑞麟编号、客户编号、客户代号、日期 //sql = "update OrderHeader set LotNo='" + LotNo + "',ClientCode='" + ClientCode + "',ClientOrderNo='" + ClientOrderNo + "',RecOrderPerson='" + RecOrderPerson + "',RecOrderDate='" + RecOrderDate.Replace(".", "-") + "',SendOrderDate='" + SendOrderDate.Replace(".", "-") + "',OutGoodsDate='" + OutGoodsDate.Replace(".", "-") + "',Updater='" + User.Identity.Name + "',UpdateDate=getdate(),OrderExcel='" + filename + "',ContainerType='" + ContainerType + "',Checker='" + checkman + "',CheckDate='" + CheckDate + "',IsCheck="+ischeck+",OrderType='"+OrderType+"' where OrderNo='" + OrderNo + "'"; //log.Info("sqlbase::::" + sql); //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30); } //sql = "select max(sn) from OrderHeader where OrderNo='" + OrderNo + "'"; SQLHelper.DbHelperSQL.SetConnectionString(""); fsn = "select max(sn) from OrderHeader where OrderNo='" + OrderNo + "'"; #region orderdtl add for (i = 6; i < dt.Rows.Count; i++) { if (dt.Rows[i][0].ToString() == "" || dt.Rows[i][1].ToString() == "") { break; } else { #region orderdtl add if (dt.Rows[i][2].ToString() != "配件") { sql = "select count(*) from rlitems where itemno='" + dt.Rows[i][2].ToString() + "'"; if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0) { //sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][2].ToString() + "'"; //al.Add(sql); //FileOper.writeLog(sql); } else { sql = "insert into rlitems(itemno,itemname,surfacedeal,Unit,sclass) values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','成品类')"; al.Add(sql); FileOper.writeLog(sql); } } else { sql = "insert into rlitems(itemno,itemname,surfacedeal,Unit,sclass) values('" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','配件类')"; al.Add(sql); FileOper.writeLog(sql); } sql = "insert into OrderDetail(FSN,OrderNo,ClinetNo,ItemNo,ItemName,Quantity,Demand1,Demand2,Remark,Inputer,InputerDate,Color,Unit,IsNew,IsPackingmaterials,CountryPackVer,IsChange) values((" + fsn + "),'" + OrderNo + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][5].ToString() + ",'" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')"; FileOper.writeLog(sql); al.Add(sql); //料号,名称,规格,材质,表面处理或颜色,底数,类别 //ItemNo,Name,Spec,MaterialNo,ItemColor,AddReserve1,ClassName //sql = "select top 1 * from OrderDetail where itemno='" + dt.Rows[i][2].ToString() + "' and OrderNo='" + OrderNo + "' "; //dtitem = SQLHelper.DbHelperSQL.ReturnDataTable(sql, 30); //if (dtitem == null || dtitem.Rows.Count == 0) //{ // //log.Info("sqlallitem::::" + sql); // //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30); // //bomsn,物料sn,料号,名称,规格,材质,表面处理,用量,分类 // sql = "insert into OrderDetail(FSN,OrderNo,ClinetNo,ItemNo,ItemName,Quantity,Demand1,Demand2,Remark,Inputer,InputerDate,Color,Unit,IsNew,IsPackingmaterials,CountryPackVer,IsChange) values((" + fsn + "),'" + OrderNo + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][1].ToString() + "'," + dt.Rows[i][5].ToString() + ",'" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "','','" + User.Identity.Name + "',getdate(),'" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "')"; // log.Info("sqldtl::::" + sql); // al.Add(sql); // //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30); //} //else //{ // sql = "update OrderDetail set ClinetNo='" + dt.Rows[i][3].ToString() + "',Quantity=" + dt.Rows[i][5].ToString() + ",Demand1='" + dt.Rows[i][11].ToString() + "',Demand2='" + dt.Rows[i][12].ToString() + "',Updater='" + User.Identity.Name + "',UpdateDate=getdate(),Color='" + dt.Rows[i][4].ToString() + "',Unit='" + dt.Rows[i][6].ToString() + "',IsNew='" + dt.Rows[i][7].ToString() + "',IsPackingmaterials='" + dt.Rows[i][8].ToString() + "',CountryPackVer='" + dt.Rows[i][9].ToString() + "',IsChange='" + dt.Rows[i][10].ToString() + "' where itemno='" + dt.Rows[i][1].ToString() + "' and ItemName='" + dt.Rows[i][2].ToString() + "' and OrderNo='" + OrderNo + "' "; // log.Info("sqldtl::::" + sql); // al.Add(sql); // //SQLHelper.DbHelperSQL.ExecuteSql(sql, 30); //} #endregion } } #endregion } else { Alert.Show("没有要导入的数据"); } } if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al)) { Alert.Show("导入成功"); } else { Alert.Show("导入失败"); } } catch (Exception ee) { Alert.Show("导入失败"); log.Info(ee.ToString()); } finally { BindGrid(); } }
private void readExcel(string filename) { try { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\""; using (OleDbConnection conn = new OleDbConnection(connstring)) { conn.Open(); DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字 //for(int k=0;k<sheetsName.Rows.Count;k++) //{ // log.Info(sheetsName.Rows[k][2].ToString()); //} string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字 string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring); DataSet set = new DataSet(); ada.Fill(set); DataTable dt = set.Tables[0]; SQLHelper.DbHelperSQL.SetConnectionString(""); ArrayList al = new ArrayList(); for (int i = 1; i < dt.Rows.Count; i++) { if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "") { break; } else { sql = "select count(*) from rlitems where itemno='" + dt.Rows[i][0].ToString() + "'"; if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0) { //sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][0].ToString() + "'"; //al.Add(sql); } else { sql = "insert into rlitems(itemno,ItemName,spec,Material,surfacedeal,UnitWeight,Sclass,Unit,SupplierId,MainFrom) values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString().Replace("'", "''") + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "',(select sn from dbo.Provider where Name='" + dt.Rows[i][8].ToString() + "'),'" + dt.Rows[i][9].ToString() + "')"; al.Add(sql); FileOper.writeLog(sql); } } } if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al)) { Alert.Show("导入成功"); } else { Alert.Show("导入失败"); } } } catch (Exception ee) { log.Info(ee.ToString()); } finally { BindGrid(); } }
protected void btnSave_Click(object sender, EventArgs e) { try { if (Grid1.GetModifiedData().Count == 0 && Grid1.GetNewAddedList().Count == 0) { Alert.Show("表格数据没有变化!"); return; } ArrayList al = new ArrayList(); string sql = "", sqlval = ""; // 新增数据 List <Dictionary <string, object> > newAddedList = Grid1.GetNewAddedList(); //for (int i = 0; i < newAddedList.Count; i++) //{ // sql = "insert into rlitems(itemno,itemname,Spec,Material,Price,SupplierId,UnitWeight,ProcessCost,ProcessCostType,SurfaceDeal,BaseNum,Sclass,WorkShop,MainFrom,StoreHouse,ProUsingQuantity,ZongCheng) values("; // sql += "'" + newAddedList[i]["ItemNo"].ToString() + "',"; // sql += "'" + newAddedList[i]["ItemName"].ToString() + "',"; // sql += "'" + newAddedList[i]["Spec"].ToString() + "',"; // sql += "'" + newAddedList[i]["Material"].ToString() + "',"; // sql += "" + newAddedList[i]["Price"].ToString() + ","; // sql += "" + newAddedList[i]["SupplierId"].ToString() + ","; // sql += "" + newAddedList[i]["UnitWeight"].ToString() + ","; // sql += "" + newAddedList[i]["ProcessCost"].ToString() + ","; // sql += "'" + newAddedList[i]["ProcessCostType"].ToString() + "',"; // sql += "'" + newAddedList[i]["SurfaceDeal"].ToString() + "',"; // sql += "" + newAddedList[i]["BaseNum"].ToString() + ","; // sql += "'" + newAddedList[i]["Sclass"].ToString() + "',"; // sql += "'" + newAddedList[i]["WorkShop"].ToString() + "',"; // sql += "'" + newAddedList[i]["MainFrom"].ToString() + "',"; // sql += "'" + newAddedList[i]["StoreHouse"].ToString() + "',"; // sql += "" + newAddedList[i]["ProUsingQuantity"].ToString() + ","; // sql += "'" + newAddedList[i]["ZongCheng"].ToString() + "')"; // log.Info("sql add item:" + sql); // al.Add(sql); //} for (int i = 0; i < newAddedList.Count; i++) { sql = "insert into rlitems("; sqlval = " values("; foreach (string key in newAddedList[i].Keys) { sql += key + ","; sqlval += "'" + newAddedList[i][key].ToString() + "',"; } sql = sql.TrimEnd(new char[] { ',' }) + ")"; sqlval = sqlval.TrimEnd(new char[] { ',' }) + ")"; sql = sql + sqlval; al.Add(sql); log.Info("sql item add:::" + sql); } //Alert.Show(sql); //return; //s = ""; // 修改的现有数 Dictionary <int, Dictionary <string, object> > modifiedDict = Grid1.GetModifiedDict(); SQLHelper.DbHelperSQL.SetConnectionString(""); foreach (int rowIndex in modifiedDict.Keys) { sql = "update rlitems set "; for (int i = 0; i < Grid1.Columns.Count; i++) { if (modifiedDict[rowIndex].ContainsKey(Grid1.Columns[i].ColumnID)) { sql += Grid1.Columns[i].ColumnID + "='" + modifiedDict[rowIndex][Grid1.Columns[i].ColumnID].ToString() + "',"; } } sql = sql.TrimEnd(new char[] { ',' }); sql += " where sn=" + Grid1.DataKeys[rowIndex][0]; FileOper.writeLog(sql); al.Add(sql); } //al.Add(sql); //sql = "update AllItem set ItemNo='" + modifiedDict[rowIndex]["ItemNo"].ToString() + "',Name='" + modifiedDict[rowIndex]["Name"].ToString() + "',Spec='" + modifiedDict[rowIndex]["Spec"].ToString() + "',MaterialNo='" + modifiedDict[rowIndex]["MaterialNo"].ToString() + "',ItemColor='" + modifiedDict[rowIndex]["ItemColor"].ToString() + "',AddReserve1='" + modifiedDict[rowIndex]["AddReserve1"].ToString() + "',ClassName='" + modifiedDict[rowIndex]["ClassName"].ToString() + "' where sn=" + GetSelectedDataKeyID(Grid1); //Alert.Show(s); SQLHelper.DbHelperSQL.SetConnectionString(""); if (SQLHelper.DbHelperSQL.ExecuteSqlTran(al)) { Alert.Show("保存成功"); BindGrid(); } else { Alert.Show("保存失败"); } } catch (Exception ee) { Alert.Show(ee.ToString()); } }
private void BindGrid() { //string[] colstr = cols.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); //string[] operstr=opers.ToString().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); using (var appdb = new AppContext()) { //IQueryable<Instruction> q = appdb.instruction; var q = from a in appdb.instruction join d in appdb.allitems on a.ItemNo equals d.ItemNo into d_join from e in d_join.DefaultIfEmpty() join b in appdb.v_userinfor on a.Receiver equals b.userid into userjoin from c in userjoin.DefaultIfEmpty() select new { a.SN, a.OrderNo, a.ProNo, a.ProName, a.ItemNo, a.ItemName, a.MainFrom, a.UsingQuantity, a.ConfirmQuantity, a.RealUsingQuantity, a.IsConfirm, a.Spec, a.Material, a.SurfaceDeal, a.Sclass, a.ReceiveDept, a.Receiver, c.ChineseName, a.ConfirmDate, a.BarCode, a.Remark, a.BomSN, a.IsPlan, e.SupplierId }; q = q.Where(u => u.RealUsingQuantity != 0 && u.IsConfirm == 1); if (rbtmakethod.SelectedValue != "") { q = q.Where(u => u.MainFrom == rbtmakethod.SelectedValue); } if (rbtIsPlan.SelectedValue != "") { int k = int.Parse(rbtIsPlan.SelectedValue); q = q.Where(u => u.IsPlan == k); } // 在职务名称中搜索 string searchText = ttbSearchMessage.Text.Trim(); if (!String.IsNullOrEmpty(searchText)) { q = q.Where(t => t.ProName.Contains(searchText) || t.ItemName.Contains(searchText) || t.ProNo.Contains(searchText) || t.ItemNo.Contains(searchText)); } foreach (DictionaryEntry de in htClickColsName) { switch (de.Key.ToString()) { case "OrderNo": q = q.Where(u => u.OrderNo == de.Value.ToString()); break; case "ProNo": q = q.Where(u => u.ProNo == de.Value.ToString()); break; case "ProName": q = q.Where(u => u.ProName == de.Value.ToString()); break; case "ItemNo": q = q.Where(u => u.ItemNo == de.Value.ToString()); break; case "ItemName": q = q.Where(u => u.ItemName == de.Value.ToString()); break; case "Spec": q = q.Where(u => u.Spec == de.Value.ToString()); break; case "Material": q = q.Where(u => u.Material == de.Value.ToString()); break; case "SurfaceDeal": q = q.Where(u => u.SurfaceDeal == de.Value.ToString()); break; case "Sclass": q = q.Where(u => u.Sclass == de.Value.ToString()); break; case "MainFrom": q = q.Where(u => u.MainFrom == de.Value.ToString()); break; case "UsingQuantity": q = q.Where(u => u.UsingQuantity.ToString() == de.Value.ToString()); break; case "ReceiveDept": q = q.Where(u => u.ReceiveDept == de.Value.ToString()); break; case "Receiver": q = q.Where(u => u.Receiver == de.Value.ToString()); break; case "ConfirmDate": q = q.Where(u => u.ConfirmDate.ToString() == de.Value.ToString()); break; } FileOper.writeLog("----------------------------------------------------------"); FileOper.writeLog("de.key::" + de.Key + ":::de.value::" + de.Value); FileOper.writeLog("----------------------------------------------------------"); } FileOper.writeLog("----------------------------------------------------------"); FileOper.writeLog(q.ToString()); FileOper.writeLog("----------------------------------------------------------"); // 在查询添加之后,排序和分页之前获取总记录数 Grid1.RecordCount = q.Count(); // 排列和分页 q = SortAndPage(q, Grid1); Grid1.DataSource = q; Grid1.DataBind(); } }
private void BindGrid() { using (var appdb = new AppContext()) { //IQueryable<ProducePlan> q = appdb.produceplan; var qbom = from e in appdb.bombase from f in appdb.bomdtl where e.SN == f.FSN select new { e.ProNo, e.ProName, SaleOrderNo = e.OrderNo, f.ItemNo, f.ItemName, f.IsValid, BomSN = e.SN }; if (rbtIsState.SelectedValue != "") { int k = int.Parse(rbtIsState.SelectedValue); if (k == 0) { qbom = qbom.Where(u => u.IsValid == 0); } } var q = from a in appdb.produceplan //join b in appdb.allitems on a.ItemNo equals b.ItemNo into joinitem //from c in joinitem.DefaultIfEmpty() join d in qbom on new { a.ProNo, a.ItemNo, a.SaleOrderNo, a.BomSN } equals new { d.ProNo, d.ItemNo, d.SaleOrderNo, d.BomSN } into d_join from g in d_join.DefaultIfEmpty() select new { a.ItemNo, a.ItemName, a.ProName, a.ProNo, a.ProPlanNo, a.SaleOrderNo, a.Spec, a.Quantity, a.PlanStartDate, a.PlanFinishDate, a.WorkShop, a.SN, a.BomSN, a.State, g.IsValid, a.ZhuangPeiDate, a.ISN }; // 在职务名称中搜索 string searchText = ttbSearchMessage.Text.Trim(); if (!String.IsNullOrEmpty(searchText)) { q = q.Where(t => t.ItemName.Contains(searchText) || t.ItemNo.Contains(searchText) || t.Spec.Contains(searchText)); } if (rbtIsState.SelectedValue != "") { int k = int.Parse(rbtIsState.SelectedValue); q = q.Where(u => u.State == k); } foreach (DictionaryEntry de in htClickColsName) { switch (de.Key.ToString()) { case "OrderNo": q = q.Where(u => u.SaleOrderNo == de.Value.ToString()); break; case "ProNo": q = q.Where(u => u.ProNo == de.Value.ToString()); break; case "ProName": q = q.Where(u => u.ProName == de.Value.ToString()); break; case "ItemNo": q = q.Where(u => u.ItemNo == de.Value.ToString()); break; case "ItemName": q = q.Where(u => u.ItemName == de.Value.ToString()); break; case "Spec": q = q.Where(u => u.Spec == de.Value.ToString()); break; case "ProPlanNo": q = q.Where(u => u.ProPlanNo == de.Value.ToString()); break; case "SurfaceDeal": q = q.Where(u => u.ProPlanNo == de.Value.ToString()); break; case "Quantity": q = q.Where(u => u.Quantity.ToString() == de.Value.ToString()); break; case "WorkShop": q = q.Where(u => u.WorkShop == de.Value.ToString()); break; case "PlanFinishDate": q = q.Where(u => u.PlanFinishDate.ToString() == de.Value.ToString()); break; } FileOper.writeLog("----------------------------------------------------------"); FileOper.writeLog("de.key::" + de.Key + ":::de.value::" + de.Value); FileOper.writeLog("----------------------------------------------------------"); } // 在查询添加之后,排序和分页之前获取总记录数 Grid1.RecordCount = q.Count(); // 排列和分页 q = SortAndPage(q, Grid1); Grid1.DataSource = q; Grid1.DataBind(); } }
private void readExcel(string filename) { try { string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/upload/" + filename) + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\""; using (OleDbConnection conn = new OleDbConnection(connstring)) { conn.Open(); DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字 //for(int k=0;k<sheetsName.Rows.Count;k++) //{ // log.Info(sheetsName.Rows[k][2].ToString()); //} string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字 string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring); DataSet set = new DataSet(); ada.Fill(set); DataTable dt = set.Tables[0]; string sn = GetQueryValue("sn"); string itemno = GetQueryValue("itemno"); SQLHelper.DbHelperSQL.SetConnectionString(""); ArrayList al = new ArrayList(); for (int i = 1; i < dt.Rows.Count; i++) { if (dt.Rows[i][0].ToString() == "" && dt.Rows[i][1].ToString() == "" && dt.Rows[i][2].ToString() == "" && dt.Rows[i][3].ToString() == "" && dt.Rows[i][4].ToString() == "" && dt.Rows[i][5].ToString() == "" && dt.Rows[i][6].ToString() == "" && dt.Rows[i][7].ToString() == "" && dt.Rows[i][8].ToString() == "" && dt.Rows[i][9].ToString() == "" && dt.Rows[i][10].ToString() == "" && dt.Rows[i][11].ToString() == "" && dt.Rows[i][12].ToString() == "") { break; } else { //sql = "select count(*) from ProcessRoute where ProcessCode='" + dt.Rows[i][0].ToString() + "'"; //if (int.Parse(SQLHelper.DbHelperSQL.GetSingle(sql, 30)) > 0) //{ // //sql = "update rlitems set surfacedeal='" + dt.Rows[i][4].ToString() + "' where itemno='" + dt.Rows[i][0].ToString() + "'"; // //al.Add(sql); //} //else //{ //} sql = "insert into ProcessRoute (itemno,itemsn,ProcessingSeq,ProcessCode,ProcessName,MoldelNo,EquipmentNoName,Nature,Team,Department,WorkBatch,FixPerTime,ChangePerTime,Price,Remark) values('" + itemno + "'," + sn + ",'" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString().Replace("'", "''") + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "','" + dt.Rows[i][12].ToString() + "')"; al.Add(sql); FileOper.writeLog(sql); } } if (al.Count > 0 && SQLHelper.DbHelperSQL.ExecuteSqlTran(al)) { Alert.Show("导入成功"); } else { Alert.Show("导入失败"); } } } catch (Exception ee) { log.Info(ee.ToString()); } finally { BindGrid(); } }