void DataScriptMapModify_Loaded(object sender, RoutedEventArgs e)
{
// columnConnection.Source = TextImportHelper.GetDataTable(@"G:\workspace\动控实现数据管理\src\TestData\data.txt", ',');
//columnConnection.Target = TableDAL.getTableStructure("MDS_IMP_DATA_TEST");
if (DataScript.FileType == "mdb")
{
AccessImportHelper helper = new AccessImportHelper(this.DataScriptRule.DesFile);
columnConnection.Source = helper.getDataTable();
}
else if (DataScript.FileType == "db")
{
columnConnection.Source = SQLiteImportHelper.GetDataTable(this.DataScriptRule.DesFile);
}
else if (DataScript.FileType == "xls/xlsx")
{
columnConnection.Source = ExcelImportHelper.GetDataTable(this.DataScriptRule.DesFile);
}
else
{
columnConnection.Source = TextImportHelper.GetDataTable(this.DataScriptRule.DesFile, this.DataScriptRule.getColSeperatorChar());
}
columnConnection.FID = FID;
columnConnection.BusinessPK = this.DataScriptRule.DesBusinessPk;
columnConnection.Target = TableDAL.getTableStructure(this.DataScriptRule.DesTable);
}
void ImportMapModify_Loaded(object sender, RoutedEventArgs e)
{
if (DataScript.FileType == "mdb")
{
AccessImportHelper helper = new AccessImportHelper(sourceFile);
columnConnection.Source = helper.getDataTable();
}
else if (DataScript.FileType == "db")
{
columnConnection.Source = SQLiteImportHelper.GetDataTable(sourceFile);
}
else if (DataScript.FileType == "xls/xlsx")
{
columnConnection.Source = ExcelImportHelper.GetDataTable(sourceFile);
}
else
{
columnConnection.Source = TextImportHelper.GetDataTable(sourceFile, this.DataScriptRule.getColSeperatorChar());
}
columnConnection.Target = TableDAL.getTableStructure(this.DataScriptRule.DesTable);
columnConnection.BusinessPK = DataScriptRule.DesBusinessPk;
columnConnection.FID = FID;
columnConnection.ShowComplete += columnConnection_ShowComplete;
}
private void openTemplateFile_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
System.Windows.Forms.OpenFileDialog dialog = new System.Windows.Forms.OpenFileDialog();
if (FileType.SelectedValue.ToString() == "xls/xlsx")
{
dialog.Filter = "(Excel 文件)|*.xls;*.xlsx";
}
if (FileType.SelectedValue.ToString() == "txt")
{
dialog.Filter = "(文本文件)|*.txt";
}
if (FileType.SelectedValue.ToString() == "dat")
{
dialog.Filter = "(数据文件)|*.dat";
}
if (FileType.SelectedValue.ToString() == "mdb")
{
dialog.Filter = "(数据文件)|*.mdb";
}
if (FileType.SelectedValue.ToString() == "db")
{
dialog.Filter = "(sqlite文件)|*.db";
}
if (FileType.SelectedValue.ToString() == "fws10")
{
dialog.Filter = "(fws10文件)|*.fws10";
}
System.Windows.Forms.DialogResult result = dialog.ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
txtTemplageFile.Text = dialog.FileName;
if (FileType.SelectedValue.ToString() != "xls/xlsx" && FileType.SelectedValue.ToString() != "mdb" && FileType.SelectedValue.ToString() != "db")
{
string[] columns = TextImportHelper.GetColumns(txtTemplageFile.Text, DataScriptRule.getColSeperatorChar(GetColSperator()));
if (columns.Length < 2)
{
MessageBox.Show("数据文件列分隔符不存在，请选择相应列分隔符！");
}
if (columns.Contains(System.Configuration.ConfigurationManager.AppSettings["pk"]))
{
nextButton.Visibility = System.Windows.Visibility.Visible;
}
else
{
nextButton.Visibility = System.Windows.Visibility.Collapsed;
MessageBox.Show(string.Format("数据文件中缺少默认主键列（{0}）,请重新选择正确数据文件！", ConfigurationManager.AppSettings["pk"]));
}
}
}
}
private void txt2db()
{
char separator = this.dataScriptRule.getColSeperatorChar();
DataTable dataTable = new DataTable();
// 获取列头，创建表结构
string[] columnNames = TextImportHelper.GetColumns(sourceFile, separator);
// 如果是temp表，去掉无用列
if (tableName != this.dataScriptRule.DesTable)
{
dropColumn(tableName, columnNames, structList);
}
for (int i = 0; i < columnNames.Length; i++)
{
DataColumn col = new DataColumn(columnNames[i].TrimEnd('\n').TrimEnd('\r'));
dataTable.Columns.Add(col);
}
DateTime begin = DateTime.Now;
Console.WriteLine(begin);
StreamReader sr = new StreamReader(sourceFile, Encoding.Default);
sr.ReadLine();
while (true)
{
// 读一行数据
string row = sr.ReadLine();
// 空数据，结束读取
if (string.IsNullOrEmpty(row))
{
insertDataTable(dataTable, structList, tableName);
break;
}
row = row.Trim();
// 根据分隔符取数据
string[] columnDatas = row.Split(separator);
// 创建一个新行
DataRow dr = dataTable.NewRow();
for (int i = 0; i < columnDatas.Length; i++)
{
dr[i] = columnDatas[i];
}
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count >= 1000)
{
insertDataTable(dataTable, structList, tableName);
dataTable.Rows.Clear();
}
}
sr.Close();
}
private List <string> getHeaders()
{
List <string> result = new List <string>();
DataTable dt = new DataTable();
string fileType = System.IO.Path.GetExtension(this.sourceFile);
if (fileType == ".xls" || fileType == ".xlsx")
{
dt = ExcelImportHelper.GetDataTable(this.sourceFile);
}
else
{
dt = TextImportHelper.GetDataTable(this.sourceFile, this.dataScriptRule.getColSeperatorChar());
}
foreach (DataColumn column in dt.Columns)
{
result.Add(column.ColumnName);
}
return(result);
}
private DataTable GetDataTable(string fileName)
{
DataTable dt;
if (FileType.SelectedValue.ToString() == "mdb")
{
AccessImportHelper helper = new AccessImportHelper(fileName);
dt = helper.getDataTable();
}
else if (FileType.SelectedValue.ToString() == "xls/xlsx")
{
dt = ExcelImportHelper.GetDataTable(fileName);
}
else if (FileType.SelectedValue.ToString() == "db")
{
dt = SQLiteImportHelper.GetDataTable(fileName);
}
else
{
dt = TextImportHelper.GetDataTable(fileName, DataScriptRule.getColSeperatorChar(GetColSperator()));
}
return(dt);
}
private void nextButton_Click(object sender, RoutedEventArgs e)
{
string erromsg = "";
if (string.IsNullOrEmpty(txtTemplageFile.Text.Trim()))
{
erromsg += "请选择样例文件！\r\n";
}
if (Des_Table.SelectedIndex == -1)
{
erromsg += "请选择目标数据表！\r\n";
}
if (Des_Table.SelectedIndex == 0 && string.IsNullOrEmpty(txtTableName.Text))
{
erromsg += "请输入数据表名称！\r\n";
}
if (Des_Table.SelectedIndex == 0 && string.IsNullOrEmpty(txtTableCode.Text))
{
erromsg += "请输入数据表代码！\r\n";
}
if (cbSave.IsChecked.Value)
{
if (string.IsNullOrEmpty(MidsScriptCode.Text.Trim()))
{
erromsg += "请输入解析器编码！\r\n";
}
else
{
if (!checkCode())
{
erromsg += "解析器编码重复！\r\n";
}
}
if (string.IsNullOrEmpty(MidsScriptName.Text.Trim()))
{
erromsg += "请输入解析器名称！\r\n";
}
}
if (!string.IsNullOrEmpty(erromsg))
{
MessageBox.Show(erromsg);
return;
}
builderScript();
if (cbSave.IsChecked.Value)
{
insert();
}
DataTable dt = new DataTable();
if (FileType.SelectedValue.ToString() == "xls/xlsx")
{
dt = ExcelImportHelper.GetDataTable(txtTemplageFile.Text.Trim());
}
else
{
dt = TextImportHelper.GetDataTable(txtTemplageFile.Text.Trim(), DataScriptRule.getColSeperatorChar(GetColSperator()));
}
TableDAL.CreateTable(txtTableCode.Text.ToUpper(), dt);
int count = ObjtableInfoDAL.Count(txtTableCode.Text.ToUpper());
if (count == 0)
{
ObjtableInfo oinfo = new ObjtableInfo();
oinfo.FID = Guid.NewGuid().ToString().Replace("-", "");
oinfo.CreatedBy = MainWindow.UserID;
oinfo.LastUpdatedBy = MainWindow.UserID;
oinfo.ObjectTableCode = txtTableCode.Text.ToUpper();
oinfo.ObjectTableName = txtTableName.Text;
oinfo.Status = "02";
oinfo.Version = 1;
oinfo.LastUpdateIp = "127.0.0.1";
oinfo.LastUpdateDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
oinfo.CreationDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
ObjtableInfoDAL.Insert(oinfo);
}
ImportMapModify mapModify = new ImportMapModify();
mapModify.FID = FID;
mapModify.DataScriptRule = dRule;
mapModify.DataScript = dScript;
mapModify.sourceFile = txtTemplageFile.Text.Trim();
mapModify.isAutoDrawLine = true;
ImportStack.Push(this);
MainWindow window = App.Current.MainWindow as MainWindow;
window.StartPage(mapModify);
}
private void nextButton_Click(object sender, RoutedEventArgs e)
{
string erromsg = "";
if (string.IsNullOrEmpty(MidsScriptCode.Text.Trim()))
{
erromsg += "请输入解析器编码！\r\n";
}
if (string.IsNullOrEmpty(TaskName.Text.Trim()))
{
erromsg += "请输入试验名称！\r\n";
}
if (string.IsNullOrEmpty(MidsScriptName.Text.Trim()))
{
erromsg += "请输入解析器名称！\r\n";
}
if (string.IsNullOrEmpty(txtTemplageFile.Text.Trim()))
{
erromsg += "请选择样例文件！\r\n";
}
else if (FileType.SelectedValue.ToString() != "xls/xlsx" && FileType.SelectedValue.ToString() != "mdb" && FileType.SelectedValue.ToString() != "db")
{
string[] columns = TextImportHelper.GetColumns(txtTemplageFile.Text, DataScriptRule.getColSeperatorChar(GetColSperator()));
if (columns.Length < 2)
{
erromsg += "数据文件列分隔符不存在，请选择相应列分隔符！\r\n";
}
}
dScript = new DataScript();
dScript.MidsScriptCode = MidsScriptCode.Text.Trim();
dScript.MidsScriptName = MidsScriptName.Text.Trim();
dScript.MidsScriptVesion = MidsScriptVesion.Text.Trim();
dScript.FileType = FileType.SelectedValue.ToString();
dScript.IndexKey = IndexKey.Text.Trim();
dScript.ValidFlag = ValidFlag.Text.Trim();
dScript.ApplyTestProject = ApplyTestProject.SelectedValue.ToString();
dScript.CreationDate = DateTime.Now;
dScript.LastUpdateDate = DateTime.Now;
dScript.LastUpdateIp = "127.0.0.1";
dScript.Version = 1;
dScript.LastUpdatedBy = MainWindow.UserID;
dScript.CreatedBy = MainWindow.UserID;
dScript.ScriptType = ScriptType.SelectedIndex;
dScript.TableName = txtTableName.Text;
dScript.ProjectCode = ProjectCode.SelectedValue.ToString();
dScript.TaskName = TaskName.Text.Trim();
// 这是sqlitedb格式，需要计算tablenameex
if (FileType.SelectedValue.ToString() == "db")
{
int count = SQLiteImportHelper.GetTableNames(txtTemplageFile.Text).Count();
string tablenames = "";
for (int i = 1; i <= count; i++)
{
tablenames += string.Format("{0}_{1},", txtTableName.Text, i);
}
dScript.TableNameExt = tablenames.TrimEnd(',');
}
dRule = new DataScriptRule();
dRule.ColSperator = GetColSperator();
dRule.ColnameLines = int.Parse(ColName_Lines.Text.Trim());
dRule.DesTable = txtTableName.Text;
dRule.DesFile = txtTemplageFile.Text;
dRule.CreationDate = DateTime.Now;
dRule.LastUpdateDate = DateTime.Now;
dRule.LastUpdateIp = "127.0.0.1";
dRule.Version = 1;
dRule.DesBusinessPk = "";
dRule.CreatedBy = MainWindow.UserID;
dRule.LastUpdatedBy = MainWindow.UserID;
// 我发现，现在就没有更新逻辑
//if (string.IsNullOrEmpty(FID))
//{
// var oldScript = scripts.FirstOrDefault(it => it.ScriptType == dScript.ScriptType && it.ProjectCode == dScript.ProjectCode && it.TaskName == dScript.TaskName);
// if (oldScript != null)
// {
// FID = oldScript.FID;
// dScript.FID = FID;
// }
//}
if (!string.IsNullOrEmpty(erromsg))
{
MessageBox.Show(erromsg);
return;
}
var oldScript = scripts.FirstOrDefault(it => it.ScriptType == dScript.ScriptType && it.ProjectCode == dScript.ProjectCode && it.TaskName == dScript.TaskName);
if (oldScript != null)
{
dScript.FID = Guid.NewGuid().ToString().Replace("-", "");
FID = dScript.FID;
dRule.FID = dScript.FID;
dRule.MdsImpDataScriptID = dScript.FID;
insertScript();
// 正常表
updateTableStructure();
if (FileType.SelectedValue.ToString() == "db")
{
// sqlite 多表
foreach (string tname in dScript.TableNameExt.Split(','))
{
updateTableStructure(tname);
}
}
}
else
{
dScript.FID = Guid.NewGuid().ToString().Replace("-", "");
FID = dScript.FID;
dRule.FID = dScript.FID;
dRule.MdsImpDataScriptID = dScript.FID;
insertScript();
// 正常表
insertTableStructure();
if (FileType.SelectedValue.ToString() == "db")
{
// sqlite 多表,再把多表搞一下。
foreach (string tname in dScript.TableNameExt.Split(','))
{
insertTableStructure(tname);
}
}
}
DataScriptMapModify mapModify = new DataScriptMapModify();
mapModify.FID = FID;
mapModify.DataScriptRule = dRule;
mapModify.DataScript = dScript;
MainWindow window = App.Current.MainWindow as MainWindow;
ImportStack.Push(this);
window.StartPage(mapModify);
}
private bool txt2db()
{
char separator = this.dataScriptRule.getColSeperatorChar();
// 获取列头，创建表结构
string[] columnNames = TextImportHelper.GetColumns(sourceFile, separator);
// 根据分隔符计算列是否有问题
if (columnNames.Length <= 1)
{
log.Error(string.Format("BetchLogic > txt2db > 文件与规则的分隔符 [ {0} ] 不匹配", this.dataScriptRule.ColSperator));
SendMessageEvent(false, string.Format("文件与规则的分隔符 [ {0} ] 不匹配", this.dataScriptRule.getColSeperatorChar()));
SendCompleteEvent("导入失败");
return(false);
}
DataTable dt = TextImportHelper.GetDataTable(this.sourceFile, this.dataScriptRule.getColSeperatorChar());
if (dt.Columns.Count <= 1)
{
log.Error(string.Format("BetchLogic > txt2db > 文件与规则的分隔符 [ {0} ] 不匹配", this.dataScriptRule.ColSperator));
SendMessageEvent(false, string.Format("文件与规则的分隔符 [ {0} ] 不匹配", this.dataScriptRule.getColSeperatorChar()));
SendCompleteEvent("导入失败");
return(false);
}
this.calColumnMap(dt);
DataTable dataTable = new DataTable();
// 如果是temp表，去掉无用列
if (tableName != this.dataScriptRule.DesTable)
{
dropColumn(tableName, columnNames, structList);
log.Info(string.Format("BetchLogic > run > 判断临时表，去掉扩展列"));
}
for (int i = 0; i < columnNames.Length; i++)
{
DataColumn col = new DataColumn(columnNames[i].TrimEnd('\n').TrimEnd('\r'));
dataTable.Columns.Add(col);
}
DateTime begin = DateTime.Now;
Console.WriteLine(begin);
log.Info(string.Format("BetchLogic > run > 开始导入:{0}", begin));
StreamReader sr = new StreamReader(sourceFile, Encoding.Default);
string header = sr.ReadLine();
int count = 0;
while (true)
{
// 读一行数据
string row = sr.ReadLine();
// 空数据，结束读取
if (string.IsNullOrEmpty(row))
{
insertDataTable(dataTable, structList, tableName);
break;
}
row = row.Trim();
// 根据分隔符取数据
string[] columnDatas = row.Split(separator);
if (columnNames.Length != columnDatas.Length)
{
SendMessageEvent(false, string.Format("[{0}]\r\n[{1}]\r\n列头共 [ {2} ] 列，与行数据 [ {3} ] 列不匹配,请检查数据数量及分隔符；",
header, row, columnNames.Length, columnDatas.Length));
SendCompleteEvent("导入失败");
return(false);
}
// 创建一个新行
DataRow dr = dataTable.NewRow();
for (int i = 0; i < columnDatas.Length; i++)
{
dr[i] = columnDatas[i];
}
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count >= 10000)
{
insertDataTable(dataTable, structList, tableName);
dataTable.Rows.Clear();
log.Info(string.Format("BetchLogic > run > 凑够10000行写一次库 :{0}", count));
SendMessageEvent(string.Format("写入数据：{0} , 表 [ {1} ]", count, tableName));
}
count++;
}
sr.Close();
log.Info(string.Format("BetchLogic > run > 全部写入完成:{0}", count));
SendMessageEvent(string.Format("写完数据：{0} , 表 [ {1} ]", count, tableName));
return(true);
}
private void openTemplateFile_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
dataScript = ScriptName.SelectedItem as DataScript;
dataRule = DataScriptRuleDAL.getInfo(dataScript.FID);
System.Windows.Forms.OpenFileDialog dialog = new System.Windows.Forms.OpenFileDialog();
if (FileType.SelectedValue.ToString() == "xls/xlsx")
{
dialog.Filter = "(Excel 文件)|*.xls;*.xlsx";
}
else if (FileType.SelectedValue.ToString() == "dat")
{
dialog.Filter = "(dat 文件)|*.dat";
}
else if (FileType.SelectedValue.ToString() == "db")
{
dialog.Filter = "(sqlite 文件)|*.db";
}
else if (FileType.SelectedValue.ToString() == "mdb")
{
dialog.Filter = "(mdb 文件)|*.mdb";
}
else if (FileType.SelectedValue.ToString() == "fws10")
{
dialog.Filter = "(fws10 文件)|*.fws10";
}
else
{
dialog.Filter = "(文本文件)|*.txt";
}
System.Windows.Forms.DialogResult result = dialog.ShowDialog();
DataTable dt = new DataTable();
if (result == System.Windows.Forms.DialogResult.OK)
{
txtTemplageFile.Text = dialog.FileName;
if (FileType.SelectedValue.ToString() == "mdb")
{
AccessImportHelper helper = new AccessImportHelper(dialog.FileName);
dt = helper.getDataTable();
}
else if (FileType.SelectedValue.ToString() == "xls/xlsx")
{
dt = ExcelImportHelper.GetDataTable(dialog.FileName);
}
else if (FileType.SelectedValue.ToString() == "db")
{
dt = SQLiteImportHelper.GetDataTable(dialog.FileName);
}
else
{
dt = TextImportHelper.GetDataTable(dialog.FileName, dataRule.getColSeperatorChar());
}
dataGrid.ItemsSource = dt.AsDataView();
importGrid.ItemsSource = dt;
checkStruct(dt, dataScript);
}
}