コード例 #1
0
 /// <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;
     }
 }