You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
288 lines
11 KiB
288 lines
11 KiB
using System; |
|
using System.Collections.Generic; |
|
using System.Data; |
|
using System.Data.SQLite; |
|
using System.IO; |
|
using System.Linq; |
|
using System.Text; |
|
using System.Windows; |
|
using KGIS.Framework.DBOperator; |
|
using KGIS.Framework.Maps; |
|
using KGIS.Framework.Platform; |
|
using KGIS.Framework.Utils; |
|
using KGIS.Framework.Utils.Dialog; |
|
using KGIS.Framework.Utils.Helper; |
|
using KGIS.Framework.Utils.Utility; |
|
using Kingo.PluginServiceInterface; |
|
|
|
namespace Kingo.Plugin.DataDictionary.Views |
|
{ |
|
/// <summary> |
|
/// FrmDicManager.xaml 的交互逻辑 |
|
/// </summary> |
|
public partial class FrmDicManageImport : BaseWindow |
|
{ |
|
public FrmDicManageImport() |
|
{ |
|
InitializeComponent(); |
|
} |
|
|
|
private void btnBrowse_Click_1(object sender, RoutedEventArgs e) |
|
{ |
|
OpenFileDialog dialog = new OpenFileDialog(); |
|
dialog.DefaultExt = "kzd"; |
|
dialog.Filter = "kzd文件(*.kzd)|*.kzd|Excel文件|*.xlsx|Excel文件|*.xls"; |
|
if (dialog.ShowDialog()) |
|
{ |
|
this.txtPath.Text = dialog.FileName; |
|
} |
|
} |
|
|
|
private void btnImport_Click_1(object sender, RoutedEventArgs e) |
|
{ |
|
try |
|
{ |
|
if (string.IsNullOrWhiteSpace(txtPath.Text)) |
|
{ |
|
MessageHelper.Show("未获取到文件路径:" + txtPath.Text); |
|
return; |
|
} |
|
string path = txtPath.Text; |
|
if (!File.Exists(path)) |
|
{ |
|
MessageHelper.Show("文件不存在,请重新选择!"); |
|
return; |
|
} |
|
if (path.EndsWith(".kzd")) |
|
{ |
|
string tagrtPath = null; |
|
if (MapsManager.Instance.CurrProjectInfo is ProjectInfo) |
|
{ |
|
tagrtPath = (MapsManager.Instance.CurrProjectInfo as ProjectInfo).GetDicDataPath(); |
|
UpdateTable(path, tagrtPath); |
|
} |
|
if (string.IsNullOrWhiteSpace(tagrtPath)) |
|
{ |
|
tagrtPath = Path.Combine((MapsManager.Instance.CurrProjectInfo as ProjectInfo).GetProjFilePath(), "dic.mdb"); |
|
File.Copy(path, tagrtPath, true); |
|
} |
|
GetAllDic(tagrtPath); |
|
MessageHelper.Show("字典导入已完成!"); |
|
} |
|
else if (path.EndsWith(".xls") || path.EndsWith(".xlsx")) |
|
{ |
|
#region 支持导入Excel格式的权属单位代码 |
|
FrmDicManage.frmDicManage.ImportExcel(path); |
|
#endregion |
|
} |
|
else |
|
{ |
|
MessageHelper.Show("字典导入目前仅支持.kzd|.xlsx|.xls格式的数据,请选择正确的文件!"); |
|
return; |
|
} |
|
FrmDicManage.frmDicManage.LoadTree(); |
|
this.Close(); |
|
} |
|
catch (Exception ex) |
|
{ |
|
LogAPI.Debug("导入数据字典异常" + ex.Message); |
|
LogAPI.Debug("导入数据字典异常" + ex.StackTrace); |
|
MessageHelper.ShowError("导入数据字典异常:" + ex.Message); |
|
} |
|
} |
|
public void Insert_sql(DataTable dic, Dictionary<string, string> dics, IRDBHelper SqlHelper, string Table) |
|
{ |
|
#region 获取所有字段 |
|
StringBuilder columns = new StringBuilder(); |
|
foreach (var item in dics) |
|
{ |
|
columns.Append("," + item.Key); |
|
} |
|
#endregion |
|
|
|
#region 数据入库 |
|
int CycleIndex = 0; |
|
StringBuilder insertsql = new StringBuilder(); |
|
StringBuilder strsql = new StringBuilder(); |
|
StringBuilder stringBuilder = new StringBuilder(); |
|
var count = dic.Rows.Count; |
|
foreach (DataRow item0 in dic.Rows) |
|
{ |
|
CycleIndex++; |
|
StringBuilder data = new StringBuilder();//insert语句插入字段 |
|
foreach (var item1 in dics) |
|
{ |
|
if (item0[item1.Key].ToString().Trim() == "") |
|
{ |
|
data.Append(",null"); |
|
} |
|
else |
|
{ |
|
data.Append((",'" + item0[item1.Key].ToString().Trim().Replace("'", "''").Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", "") + "'")); |
|
} |
|
} |
|
insertsql.Append(string.Format("insert into " + Table + "({0})values({1});", columns.ToString().Substring(1), data.ToString().Substring(1))); |
|
#region 提交数据 |
|
if (count < 10000) |
|
{ |
|
if (CycleIndex < count) |
|
{ |
|
stringBuilder.Append(insertsql.ToString()); |
|
insertsql.Clear(); |
|
} |
|
else if (CycleIndex == count) |
|
{ |
|
stringBuilder.Append(insertsql.ToString()); |
|
var num = SqlHelper.ExecuteNonQuery(stringBuilder.ToString(), CommandType.Text); |
|
if (num < 0 && stringBuilder.ToString() != "") |
|
return; |
|
} |
|
} |
|
else |
|
{ |
|
strsql.Append(insertsql.ToString()); |
|
insertsql.Clear(); |
|
} |
|
if (CycleIndex == 10000) |
|
{ |
|
try |
|
{ |
|
var num = SqlHelper.ExecuteNonQuery(strsql.ToString(), CommandType.Text); |
|
if (num < 0 && strsql.ToString() != "") |
|
return; |
|
insertsql.Clear(); |
|
strsql.Clear(); |
|
count = count - CycleIndex; |
|
CycleIndex = 0; |
|
} |
|
catch (Exception exc) |
|
{ |
|
throw new Exception(exc.Message); |
|
} |
|
finally |
|
{ |
|
GC.Collect(); |
|
} |
|
} |
|
#endregion |
|
} |
|
#endregion |
|
} |
|
|
|
private void UpdateTable(string path, string tagrtPath) |
|
{ |
|
if (string.IsNullOrEmpty(path) || string.IsNullOrEmpty(tagrtPath)) return; |
|
List<string> tableName = new List<string>(); |
|
tableName.Add("Sys_DicDetail"); |
|
tableName.Add("Sys_DicManage"); |
|
tableName.Add("Sys_DicMappingTable"); |
|
tableName.Add("Sys_DicType"); |
|
|
|
IRDBHelper dbHelper = RDBFactory.CreateDbHelper(tagrtPath, DatabaseType.SQLite); |
|
List<string> tables = GetTableNames(tagrtPath); |
|
for (int i = 0; i < tables.Count; i++) |
|
{ |
|
if (tables[i].Contains("Sys_DicDetail") || tables[i].Contains("Sys_DicManage") || tables[i].Contains("Sys_DicMappingTable") || tables[i].Contains("Sys_DicType")) |
|
{ |
|
dbHelper.ExecuteDatatable(tables[i], $"delete from {tables[i]}", false); |
|
continue; |
|
} |
|
} |
|
|
|
IRDBHelper kzdHelper = RDBFactory.CreateDbHelper(path, DatabaseType.SQLite); |
|
try |
|
{ |
|
for (int i = 0; i < tableName.Count; i++) |
|
{ |
|
DataTable dtRST = kzdHelper.ExecuteDatatable(tableName[i], $"SELECT * FROM {tableName[i]}", false); |
|
DataTableToSQLite myTabInfo1 = new DataTableToSQLite(dtRST, path, false); |
|
myTabInfo1.ImportToSqliteBatch(dtRST, tagrtPath); |
|
} |
|
} |
|
catch (Exception ex) |
|
{ |
|
LogAPI.Debug(ex); |
|
return; |
|
} |
|
finally |
|
{ |
|
if (dbHelper != null && dbHelper.State != System.Data.ConnectionState.Closed) |
|
dbHelper.DisConnect(); |
|
if (kzdHelper != null && kzdHelper.State != System.Data.ConnectionState.Closed) |
|
kzdHelper.DisConnect(); |
|
} |
|
} |
|
|
|
private List<string> GetTableNames(string path) |
|
{ |
|
// 获取指定数据库中的所有表名 |
|
List<string> tables = new List<string>(); |
|
//string connectionString = @"Data Source='" + path + "';Version=3;"; |
|
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + path)) |
|
{ |
|
conn.Open(); |
|
// 获取数据库中的所有表名 |
|
string sqlTableNames = "select name from sqlite_master where type='table' order by name;"; |
|
// 创建命令对象 |
|
SQLiteCommand cmd = new SQLiteCommand(sqlTableNames, conn); |
|
using (SQLiteDataReader dr = cmd.ExecuteReader()) |
|
{ |
|
while (dr.Read()) |
|
{ |
|
// 表名 |
|
tables.Add(dr["Name"].ToString()); |
|
} |
|
} |
|
} |
|
return tables; |
|
} |
|
private List<DataDicTionary> GetAllDic(string dbPath) |
|
{ |
|
DataTable dt = null; |
|
List<DataDicTionary> result = new List<DataDicTionary>(); |
|
IRDBHelper rdbHelper = null; |
|
try |
|
{ |
|
//string dbPath = (MapsManager.Instance.CurrProjectInfo as ProjectInfo).GetDicDataPath(); |
|
if (!string.IsNullOrWhiteSpace(dbPath)) |
|
{ |
|
if (dbPath.EndsWith("sqlite")) |
|
{ |
|
rdbHelper = RDBFactory.CreateDbHelper(dbPath, DatabaseType.SQLite); |
|
} |
|
else if (dbPath.EndsWith("mdb")) |
|
{ |
|
string connStr = SysConfigsOprator.GetDBConnectionByName("MDBOledbConnection"); |
|
connStr = string.Format(connStr, dbPath); |
|
rdbHelper = RDBFactory.CreateDbHelper(connStr, DatabaseType.MSAccess); |
|
} |
|
string strSQL = "select * from Sys_DicDetail"; |
|
dt = rdbHelper.ExecuteDatatable("Dic", strSQL, true); |
|
if (dt != null) |
|
{ |
|
//Bug-12137 霍岩 2018-11-14 使用CODE字段按顺序排序 |
|
result = TBToList.ToList<DataDicTionary>(dt).OrderBy(x => x.CODE).ToList(); |
|
result.ForEach(x => x.DisplayName = x.CODE + "-" + x.NAME); |
|
} |
|
} |
|
} |
|
catch (Exception ex) |
|
{ |
|
LogAPI.Debug(ex); |
|
} |
|
finally |
|
{ |
|
if (rdbHelper != null) |
|
{ |
|
rdbHelper.DisConnect(); |
|
} |
|
if (dt != null) |
|
{ |
|
dt.Clear(); |
|
dt.Dispose(); |
|
} |
|
} |
|
return result; |
|
} |
|
} |
|
} |