/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ImprnBut_Click(object sender, EventArgs e)
{
try
{
ImprnBut.Enabled = false;
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>();
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.GetUserFieldByName(keyValuePair.Value);
listField.Add(dataField);
listValue.Add(value);
}
listField.Add(ZtoUserEntity.FieldIssendorreceive);
listValue.Add("0");
string tempCommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", ZtoUserEntity.TableName, string.Join(",", listField), "'" + string.Join("','", listValue) + "'");
listCommandText.Add(tempCommandText);
listField.Clear();
listValue.Clear();
}
// 开始执行sql
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();
}
XtraMessageBox.Show(string.Format("成功导入{0}条", resultCount), 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;
}
}
