//导入数据库 public int insert_import_sys_customer(CustomerEn cus) { string sql = @"insert into cusdoc.Sys_Customer(Id, Code, name, ChineseAbbreviation, HSCode, CIQCode, ChineseAddress, EnglishName, EnglishAddress, Enabled, Remark, ISCUSTOMER,ISSHIPPER,ISCOMPANY) values(cusdoc.Sys_Customer_Id.nextval, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}, '{9}',{10},{11},{12})"; sql = string.Format(sql, cus.Code, cus.name, cus.ChineseAbbreviation, cus.HSCode, cus.CIQCode, cus.ChineseAddress, cus.EnglishName, cus.EnglishAddress, cus.Enabled, cus.Remark, cus.ISCUSTOMER, cus.ISSHIPPER, cus.ISCOMPANY); int i = DBMgr.ExecuteNonQuery(sql); return(i); }
private void importCustomer(ISheet sheet) { CustomerEn cus = new CustomerEn(); List <string> sqlList = new List <string>(); for (int i = 1; i <= sheet.LastRowNum; i++) { XSSFRow row = sheet.GetRow(i) as XSSFRow; cus.Code = row.GetCell(0).ToString2().Trim(); if (string.IsNullOrEmpty(cus.Code)) { setErrorInfo("客商代码", "代码为空", i); continue; } cus.HSCode = row.GetCell(1).ToString2().Trim(); cus.CIQCode = row.GetCell(2).ToString2().Trim(); cus.ChineseAbbreviation = row.GetCell(3).ToString2().Trim(); cus.name = row.GetCell(4).ToString2().Trim(); if (string.IsNullOrEmpty(cus.name)) { setErrorInfo("客商名称", "名称为空", i); continue; } cus.ChineseAddress = row.GetCell(5).ToString2().Trim(); cus.EnglishName = row.GetCell(6).ToString2().Trim(); cus.EnglishAddress = row.GetCell(7).ToString2().Trim(); cus.Enabled = row.GetCell(8).ToString2().Trim2() == "是" ? 1 : 0; cus.Remark = row.GetCell(8).ToString2().Trim(); string sql = @"insert into cusdoc.Sys_Customer(Id, Code, name, ChineseAbbreviation, HSCode, CIQCode, ChineseAddress, EnglishName, EnglishAddress, Enabled, Remark, ISCUSTOMER,ISSHIPPER,ISCOMPANY) values(cusdoc.Sys_Customer_Id.nextval, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}, '{9}',{10},{11},{12})"; sql = string.Format(sql, cus.Code, cus.name, cus.ChineseAbbreviation, cus.HSCode, cus.CIQCode, cus.ChineseAddress, cus.EnglishName, cus.EnglishAddress, cus.Enabled, cus.Remark, cus.ISCUSTOMER, cus.ISSHIPPER, cus.ISCOMPANY); sqlList.Add(sql); } int count = DBMgr.ExecuteNonQuery(sqlList); }
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action, JObject json_formdata) { Base_Company_Method bcm = new Base_Company_Method(); Sql.CustomerManage cm = new Sql.CustomerManage(); CustomerEn cus = new CustomerEn(); DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0); List <string> stringList = new List <string>(); //记住发生错误的行数 List <int> errorlines = new List <int>(); //记住插入成功的个数 int count = 0; //插入成功的个数(返回放入dictionary) List <int> successinsert = new List <int>(); //返回值 Dictionary <int, List <int> > returndic = new Dictionary <int, List <int> >(); for (int i = 0; i < dtExcel.Rows.Count; i++) { for (int j = 0; j < dtExcel.Columns.Count; j++) { stringList.Add(dtExcel.Rows[i][j].ToString()); } //客户编码 //海关编码 string code = stringList[0]; string HSCODE = stringList[1]; //国检编码 //中文名称 string CIQCODE = stringList[2]; string CHINESEABBREVIATION = stringList[3]; //中文简称 //中文地址 string name = stringList[4]; string CHINESEADDRESS = stringList[5]; //英文名称 //英文地址 string ENGLISHNAME = stringList[6]; string ENGLISHADDRESS = stringList[7]; //是否启用 //备注 string enabled = stringList[8] == "是"?"1":"0"; string remark = stringList[9]; //需要验证客户编码是否重复,客户编码是为空,中文简称不能为空 cus.Code = code; cus.HSCode = HSCODE; cus.CIQCode = CIQCODE; cus.ChineseAbbreviation = CHINESEABBREVIATION; cus.name = name; cus.ChineseAddress = CHINESEADDRESS; cus.EnglishName = ENGLISHNAME; cus.EnglishAddress = ENGLISHADDRESS; cus.Enabled = enabled.ToInt(); cus.Remark = remark; int p = cm.before_import_check(code).Rows.Count; if (cm.before_import_check(code).Rows.Count > 0 || string.IsNullOrEmpty(code) || string.IsNullOrEmpty(name)) { errorlines.Add(i + 2); } else { cm.insert_import_sys_customer(cus); count = count + 1; } stringList.Clear(); } successinsert.Add(count); returndic.Add(1, successinsert); returndic.Add(2, errorlines); return(returndic); }