//数据库操作
private void ImportData(ImportDGViewModel dGViewModel)
{
if (CheckIsSampleCommentExist(dGViewModel))
{
// string sql = "select "+ dGViewModel.CsvType+" from MCA_Pool where SampleComment='"+dGViewModel.SampleComment+"'";
//SqlHelper sqlHelper = new SqlHelper();
// sqlHelper.getSomeDate(sql);
// bool isExist = (bool)(sqlHelper.dt.DefaultView[0][0]);
bool isExist = dGViewModel.CsvType == "Ag" ? dGViewModel.Csv_VM.Ag : dGViewModel.Csv_VM.W;
if (isExist)
{
dGViewModel.Comment = "该数据已经存在";
dGViewModel.OperationResult = "取消导入";
}
else
{
ImportPicker(dGViewModel);
}
}
else
{
ImportMCAPoolDBTable(dGViewModel.Csv_VM);
ImportPicker(dGViewModel);
}
}
private void UpdateMCAPoolDBTable(ImportDGViewModel dGViewModel)
{
string sql = "update MCA_Pool set " + dGViewModel.CsvType + "=1 where SampleComment ='" + dGViewModel.SampleComment + "'";
SqlHelper sqlHelper = new SqlHelper();
sqlHelper.getSomeDate(sql);
}
//数据库操作
private void ImportData(ref ImportDGViewModel dGViewModel)
{
if (CheckIsSampleCommentExist(dGViewModel.SampleComment))
{
string sql = "select " + dGViewModel.CsvType + " from MCA_Pool where SampleComment='" + dGViewModel.SampleComment + "'";
SqlHelper sqlHelper = new SqlHelper();
sqlHelper.getSomeDate(sql);
bool isExist = (bool)(sqlHelper.dt.DefaultView[0][0]);
if (isExist)
{
dGViewModel.Comment = "该数据已经存在";
dGViewModel.OperationResult = "取消导入";
}
else
{
ImportPicker(ref dGViewModel);
}
}
else
{
// try
// {
ImportMCAPoolDBTable(dGViewModel.Csv_VM);
//}
// catch (Exception e) { dGViewModel.Comment = e.Message; dGViewModel.OperationResult = "导入失败"; return; }
ImportPicker(ref dGViewModel);
}
}
private void UpdateMCAPoolDBTable(ImportDGViewModel dGViewModel)
{
// string sql = "update MCA_Pool set "+dGViewModel.CsvType+"=1 where SampleComment ='"+dGViewModel.SampleComment+"'";
string sql = string.Format("update MCA_POOL set {0}=1 where UID='{1}'", dGViewModel.CsvType, dGViewModel.Csv_VM.GUID); //2019-3-5修改
SqlHelper sqlHelper = new SqlHelper();
sqlHelper.getSomeDate(sql);
}
//赋值obc_import,并执行检查
private ObservableCollection <ImportDGViewModel> OBCImportVM(string[] filepath)
{
ObservableCollection <ImportDGViewModel> obc = new ObservableCollection <ImportDGViewModel>();
foreach (string fp in filepath)
{
ImportDGViewModel idgvm = new ImportDGViewModel(fp);
CheckFileInOBC(idgvm);
obc.Add(idgvm);
}
return(obc);
}
private bool CheckIsSampleCommentExist(ImportDGViewModel dGViewModel)
{
bool isexist = false;
SqlHelper sqlhelper = new SqlHelper();
string sql = "select UID,Ag,W from MCA_Pool where SampleComment='" + dGViewModel.SampleComment + "' and FileDir='" + dGViewModel.Csv_VM.FileDir + "'";
sqlhelper.getSomeDate(sql);
if (sqlhelper.dt.DefaultView.Count > 0)
{
isexist = true;
dGViewModel.Csv_VM.GUID = sqlhelper.dt.DefaultView[0][0].ToString();
dGViewModel.Csv_VM.Ag = (bool)sqlhelper.dt.DefaultView[0][1];
dGViewModel.Csv_VM.W = (bool)sqlhelper.dt.DefaultView[0][2];
}
return(isexist);
}
private void button_import_Click(object sender, RoutedEventArgs e)
{
foreach (ImportDGViewModel dg in obc_import)
{
ImportDGViewModel dg_c = dg;
if (!string.IsNullOrEmpty(dg_c.CsvType))
{
ImportData(dg_c);
dg.Comment = dg_c.Comment;
dg.Csv_VM = dg_c.Csv_VM;
dg.OperationResult = dg_c.OperationResult;
}
else
{
dg.OperationResult = "未执行";
}
}
}
//检查文件内容
private void CheckFileInOBC(ImportDGViewModel dGViewModel)
{
if (!File.Exists(dGViewModel.FilePath))
{
dGViewModel.Comment = "File Not Exists";
return;
}
ExcelOper excel = new ExcelOper(dGViewModel.FilePath);
try
{
// CsvHelper csv = new CsvHelper();
//DataTable dt = csv.readCsvTxt(dGViewModel.FilePath);
DataTable dt = excel.GetContentFromExcel();
if (dt.DefaultView[5][1].ToString().Trim() == "Ag")
{
dGViewModel.CsvType = "Ag";
}
else if (dt.DefaultView[5][1].ToString().Trim() == "W")
{
dGViewModel.CsvType = "W";
}
else
{
dGViewModel.Comment = "未能识别文件内容";
return;
}
dGViewModel.SampleComment = dt.DefaultView[3][1].ToString();
dGViewModel.Csv_VM = new CSVValueViewModel();
dGViewModel.Csv_VM = CSVVM(dGViewModel.CsvType, dt);
dGViewModel.FileDir = dGViewModel.Csv_VM.FileDir;
}
catch (Exception)
{
dGViewModel.Comment = "未能识别文件内容";
return;
}
finally { excel.Quit(); }
}
//根据type导入Ag或W表
private void ImportPicker(ImportDGViewModel dGViewModel)
{
if (dGViewModel.CsvType == "Ag")
{
try
{ ImportMACAgDBTable(dGViewModel.Csv_VM); dGViewModel.OperationResult = "导入成功"; }
catch (Exception e)
{ dGViewModel.Comment = e.Message; dGViewModel.OperationResult = "导入失败"; return; }
}
if (dGViewModel.CsvType == "W")
{
try
{
ImportMCAWDBTable(dGViewModel.Csv_VM); dGViewModel.OperationResult = "导入成功";
}
catch (Exception e)
{ dGViewModel.Comment = e.Message; dGViewModel.OperationResult = "导入失败"; return; }
}
UpdateMCAPoolDBTable(dGViewModel);
}
