/// <summary> /// 导入 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ImprnBut_Click(object sender, EventArgs e) { try { ImprnBut.Enabled = false; DateTime start = DateTime.Now; if (lbOpposite.Items.Count > 0) { var keyValuePairs = new List <KeyValuePair <string, string> >(); foreach (var item in lbOpposite.Items) { var tempArray = item.ToString().Split('-'); keyValuePairs.Add(new KeyValuePair <string, string>(tempArray[0], tempArray[1])); } if (keyValuePairs.Count > 0) { var listField = new List <string>(); var listValue = new List <string>(); var listCommandText = new List <string>(); List <ZtoUserEntity> userList = null; if (ckUseDefaultSendMan.Checked) { var userManager = new ZtoUserManager(BillPrintHelper.DbHelper); // 获取默认发件人 userList = userManager.GetList <ZtoUserEntity>(new KeyValuePair <string, object>(ZtoUserEntity.FieldIsDefault, 1), new KeyValuePair <string, object>(ZtoUserEntity.FieldIssendorreceive, 1)); } foreach (DataRow data in _execldt.Rows) { foreach (var keyValuePair in keyValuePairs) { // Excel每一列的值 var value = data[keyValuePair.Key].ToString(); // Excel的值要特殊处理一些符号(不然插入数据库有问题的) if (!string.IsNullOrEmpty(value)) { value = value.Replace("'", "").Replace("\"", "").Replace("=", "").Replace(" ", ""); } // 获取到实际的数据库列名称 var dataField = BillPrintHelper.GetFieldByName(keyValuePair.Value); listField.Add(dataField); //if (dataField == ZtoPrintBillEntity.FieldOrderNumber) //{ // if (string.IsNullOrEmpty(value)) // { // value = Guid.NewGuid().ToString("N"); // } //} listValue.Add(value); } // 使用默认发件人 if (ckUseDefaultSendMan.Checked) { if (userList.Any()) { var userEntity = userList.FirstOrDefault(); listField.Add(ZtoPrintBillEntity.FieldSendMan); listValue.Add(userEntity.Realname); if (!string.IsNullOrEmpty(userEntity.Mobile) && string.IsNullOrEmpty(userEntity.TelePhone)) { listField.Add(ZtoPrintBillEntity.FieldSendPhone); listValue.Add(userEntity.Mobile); } if (string.IsNullOrEmpty(userEntity.Mobile) && !string.IsNullOrEmpty(userEntity.TelePhone)) { listField.Add(ZtoPrintBillEntity.FieldSendPhone); listValue.Add(userEntity.TelePhone); } if (!string.IsNullOrEmpty(userEntity.Mobile) && !string.IsNullOrEmpty(userEntity.TelePhone)) { listField.Add(ZtoPrintBillEntity.FieldSendPhone); listValue.Add(userEntity.Mobile); } listField.Add(ZtoPrintBillEntity.FieldSendProvince); listValue.Add(userEntity.Province); listField.Add(ZtoPrintBillEntity.FieldSendCity); listValue.Add(userEntity.City); listField.Add(ZtoPrintBillEntity.FieldSendCounty); listValue.Add(userEntity.County); listField.Add(ZtoPrintBillEntity.FieldSendDeparture); listValue.Add(userEntity.Province); listField.Add(ZtoPrintBillEntity.FieldSendAddress); listValue.Add(userEntity.Address); listField.Add(ZtoPrintBillEntity.FieldSendPostcode); listValue.Add(userEntity.Postcode); listField.Add(ZtoPrintBillEntity.FieldSendDate); listValue.Add(DateTime.Now.ToString(BaseSystemInfo.DateFormat)); listField.Add(ZtoPrintBillEntity.FieldCreateOn); listValue.Add(DateTime.Now.ToString(BaseSystemInfo.DateTimeFormat)); } } // 如果没有导入订单号这个字段就要手动导入了,因为这个很重要 if (listField.All(p => p != ZtoPrintBillEntity.FieldOrderNumber)) { listField.Add(ZtoPrintBillEntity.FieldOrderNumber); listValue.Add(Guid.NewGuid().ToString("N")); } // 到付 if (listField.All(p => p != ZtoPrintBillEntity.FieldToPayMent)) { listField.Add(ZtoPrintBillEntity.FieldToPayMent); listValue.Add("0"); } // 代收 if (listField.All(p => p != ZtoPrintBillEntity.FieldGoodsPayMent)) { listField.Add(ZtoPrintBillEntity.FieldGoodsPayMent); listValue.Add("0"); } string tempCommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", ZtoPrintBillEntity.TableName, string.Join(",", listField), "'" + string.Join("','", listValue) + "'"); listCommandText.Add(tempCommandText); listField.Clear(); listValue.Clear(); } if (listCommandText.Count > 0) { var resultCount = 0; // 用事务插入最快还有删除 using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SQLite, BillPrintHelper.BillPrintConnectionString)) { dbHelper.BeginTransaction(); foreach (var commandText in listCommandText) { try { resultCount = resultCount + (dbHelper.ExecuteNonQuery(commandText)); } catch (Exception ex) { dbHelper.RollbackTransaction(); } } dbHelper.CommitTransaction(); } TimeSpan ts = DateTime.Now - start; XtraMessageBox.Show(string.Format("成功导入{0}条,耗时:{1}", resultCount, string.Format("{0}分{1}秒", ts.Minutes, ts.Seconds)), AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information); Close(); } else { XtraMessageBox.Show("导入失败", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information); } } } else { XtraMessageBox.Show("请从左边选择对应的导入关系列", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception exception) { XtraMessageBox.Show(exception.Message, AppMessage.MSG0000); } finally { ImprnBut.Enabled = true; } }