#region
|
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.OleDb;
|
using CSFrameworkV5.Common;
|
using CSFrameworkV5.Core;
|
using CSFrameworkV5.Library.CommonForms;
|
|
#endregion
|
|
namespace CSFrameworkV5.Library.CommonClass
|
{
|
/// <summary>
|
/// 导入数据通用接口
|
/// </summary>
|
public interface IImporterSource
|
{
|
/// <summary>
|
/// 是否连接
|
/// </summary>
|
bool IsConnected { get; }
|
|
/// <summary>
|
/// 创建连接
|
/// </summary>
|
/// <returns></returns>
|
DbConnection CreateConnection();
|
|
/// <summary>
|
/// 获取用于预览的数据
|
/// </summary>
|
/// <returns></returns>
|
DataTable GetData(string tableName);
|
|
/// <summary>
|
/// 取数据库清单
|
/// </summary>
|
/// <returns></returns>
|
List<string> GetDatabaseList();
|
|
/// <summary>
|
/// 取表结构
|
/// </summary>
|
/// <returns></returns>
|
List<string> GetFields(string tableName);
|
|
/// <summary>
|
/// 取数据
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
DataTable GetTable(string tableName, string orderBy);
|
|
/// <summary>
|
/// 取数据库中所有资料表
|
/// </summary>
|
/// <returns></returns>
|
List<string> GetTableNameList();
|
|
/// <summary>
|
/// 测试连接
|
/// </summary>
|
/// <returns></returns>
|
bool TestConnection(out string msg);
|
}
|
|
/// <summary>
|
/// 导入数据到目标表的接口
|
/// </summary>
|
public abstract class IImporterTarget
|
{
|
protected DataTable _DataSource;
|
protected DataTable _DataTarget;
|
|
private List<OperateReportItem> _ReportList =
|
new List<OperateReportItem>();
|
|
protected string _ReportTitle = "导入数据";
|
|
public IImporterTarget()
|
{
|
}
|
|
public IImporterTarget(DataTable dataTarget)
|
{
|
_DataTarget = dataTarget;
|
}
|
|
/// <summary>
|
/// 本次导入错误信息
|
/// </summary>
|
public string ErrorInfo { get; set; }
|
|
/// <summary>
|
/// 返回导入的记录数
|
/// </summary>
|
public int LastImportCounter { get; set; }
|
|
/// <summary>
|
/// 导入失败的记录数
|
/// </summary>
|
public int LastImportFailed { get; set; }
|
|
protected void AddReport(bool isError, string stepText, string content,
|
string refID)
|
{
|
if (_ReportList != null)
|
_ReportList.Add(new OperateReportItem(isError, stepText, refID,
|
content));
|
}
|
|
protected virtual void BeginImport(DataTable dataSource)
|
{
|
}
|
|
/// <summary>
|
/// 清空目标表的数据
|
/// </summary>
|
public abstract void ClearData();
|
|
protected virtual void EndImport(int success, int failed)
|
{
|
}
|
|
/// <summary>
|
/// 获取目标表的字段清单
|
/// </summary>
|
/// <param name="data"></param>
|
/// <returns></returns>
|
public List<FieldEntity> GetFieldList()
|
{
|
var fieldListTarget = new List<FieldEntity>();
|
foreach (DataColumn col in _DataTarget.Columns)
|
fieldListTarget.Add(new FieldEntity(col.ColumnName,
|
col.ColumnName));
|
|
return fieldListTarget;
|
}
|
|
protected virtual object GetFieldValue(DataRow sourceRow,
|
DataRow targetRow, FieldMapping mapping)
|
{
|
var T = targetRow.Table.Columns[mapping.TargetField].DataType;
|
var o = sourceRow[mapping.SourceField];
|
|
//日期类型兼容
|
if (T == typeof(DateTime))
|
{
|
if (o == null || o.ToStringEx().Trim() == string.Empty)
|
return DBNull.Value;
|
|
return ConvertEx.ToDateTime(o);
|
}
|
|
if (T == typeof(int) || T == typeof(int) || T == typeof(long))
|
return ConvertEx.ToInt(o);
|
|
if (T == typeof(double) || T == typeof(float) ||
|
T == typeof(decimal)) return ConvertEx.ToDecimal(o);
|
|
return sourceRow[mapping.SourceField];
|
}
|
|
/// <summary>
|
/// 获取字段映射关系
|
/// </summary>
|
/// <returns></returns>
|
public abstract List<FieldMapping> GetMapping();
|
|
/// <summary>
|
/// 开始导入数据
|
/// </summary>
|
/// <param name="source">数据源</param>
|
/// <param name="fieldMapping">字段映射</param>
|
/// <param name="progress">进度条控件</param>
|
/// <returns>返回操作报告</returns>
|
public virtual List<OperateReportItem> Import(
|
DataTable source,
|
List<FieldMapping> fieldMapping,
|
IProgressBar progress)
|
{
|
_DataSource = source;
|
_ReportList.Clear();
|
|
LastImportCounter = 0;
|
LastImportFailed = 0;
|
|
progress.Position = 1;
|
progress.MinValue = 0;
|
progress.MaxValue = source.Rows.Count;
|
progress.Reset();
|
|
DataRow targetRow;
|
var isImport = false; //需要导入当前记录
|
var index = 0;
|
|
//开始导入
|
BeginImport(_DataSource);
|
|
foreach (DataRow R in source.Rows)
|
{
|
if (IsEmptyRow(R)) continue; //不导入空记录
|
|
index = source.Rows.IndexOf(R);
|
|
targetRow = _DataTarget.NewRow();
|
|
foreach (var mp in fieldMapping)
|
if (mp.SourceField != "" && mp.TargetField != "")
|
{
|
isImport = true; //有匹配成功的字段
|
|
targetRow[mp.TargetField] =
|
GetFieldValue(R, targetRow, mp);
|
}
|
|
//没有映射关系的字段不导入数据
|
if (isImport) //当前记录需要导入
|
{
|
targetRow.EndEdit();
|
|
var errMsg = "";
|
//提交数据
|
if (PostImportedData(targetRow, out errMsg))
|
{
|
_ReportList.Add(new OperateReportItem(false,
|
_ReportTitle, "行号:" + index.ToStringEx(),
|
"导入成功."));
|
LastImportCounter++;
|
}
|
else
|
{
|
_ReportList.Add(new OperateReportItem(true,
|
_ReportTitle, "<错误行：" + index.ToStringEx() + ">",
|
errMsg));
|
LastImportFailed++;
|
}
|
}
|
|
progress.Position++;
|
}
|
|
AddReport(false, _ReportTitle, "导入结束。", "");
|
AddReport(false, _ReportTitle,
|
string.Format("成功{0},失败{1}", LastImportCounter,
|
LastImportFailed), "");
|
|
EndImport(LastImportCounter, LastImportFailed);
|
|
return _ReportList;
|
}
|
|
protected bool IsEmptyRow(DataRow R)
|
{
|
var str = "";
|
for (var i = 0; i <= R.Table.Columns.Count - 1; i++)
|
{
|
str = ConvertEx.ToString(R[i]).Trim();
|
str = str.Replace("0", "").Replace(".", "");
|
if (str != "") return false;
|
}
|
|
return true;
|
}
|
|
/// <summary>
|
/// 匹配字段名
|
/// </summary>
|
/// <param name="data"></param>
|
/// <param name="fieldName"></param>
|
/// <returns></returns>
|
public string MatchField(string fieldName)
|
{
|
foreach (DataColumn col in _DataTarget.Columns)
|
if (col.ColumnName.Trim() == fieldName.Trim())
|
return fieldName.Trim();
|
|
return "";
|
}
|
|
/// <summary>
|
/// 提交数据
|
/// </summary>
|
/// <param name="drTarge">当前记录</param>
|
/// <returns></returns>
|
protected abstract bool PostImportedData(DataRow drTarge,
|
out string errMsg);
|
}
|
|
/// <summary>
|
/// 数据导入接口基类
|
/// </summary>
|
public class ImporterDataSourceBase : IImporterSource
|
{
|
#region IImporterDataSource Members
|
|
public virtual DbConnection CreateConnection()
|
{
|
throw new Exception("CreateConnection方法没有实现！");
|
}
|
|
public virtual bool TestConnection(out string msg)
|
{
|
msg = "";
|
try
|
{
|
using (var conn = CreateConnection())
|
{
|
conn.Open();
|
conn.Close();
|
return true;
|
}
|
}
|
catch (Exception ex)
|
{
|
msg = "连接数据库失败！" + ex.Message;
|
return false;
|
}
|
}
|
|
/// <summary>
|
/// 是否连接
|
/// </summary>
|
public bool IsConnected
|
{
|
get
|
{
|
string msg;
|
return TestConnection(out msg);
|
}
|
}
|
|
public virtual DataTable GetTable(string tableName, string orderBy)
|
{
|
throw new Exception("GetTable方法没有实现！");
|
}
|
|
/// <summary>
|
/// 取所有表名
|
/// </summary>
|
/// <returns></returns>
|
public virtual List<string> GetTableNameList()
|
{
|
var list = new List<string>();
|
|
using (var conn = CreateConnection())
|
{
|
var dt = conn.GetSchema("Tables");
|
foreach (DataRow row in dt.Rows)
|
if (row[3].ToStringEx() == "TABLE" ||
|
row[3].ToStringEx() == "BASE TABLE")
|
list.Add(row[2].ToStringEx());
|
}
|
|
return list;
|
}
|
|
/// <summary>
|
/// 取指定表所有字段名称
|
/// </summary>
|
/// <returns></returns>
|
public virtual List<string> GetFields(string tableName)
|
{
|
var list = new List<string>();
|
|
DbConnection conn = null;
|
OleDbCommand cmd = null;
|
OleDbDataReader dr = null;
|
|
try
|
{
|
using (conn = CreateConnection())
|
{
|
using (cmd = new OleDbCommand())
|
{
|
var sql = "SELECT TOP 1 * FROM " + tableName;
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(sql);
|
cmd.Connection = conn as OleDbConnection;
|
using (dr = cmd.ExecuteReader())
|
{
|
for (var i = 0; i < dr.FieldCount; i++)
|
list.Add(dr.GetName(i));
|
}
|
}
|
}
|
}
|
finally
|
{
|
if (conn != null) conn.Dispose();
|
|
if (cmd != null) cmd.Dispose();
|
|
if (dr != null) dr.Dispose();
|
}
|
|
return list;
|
}
|
|
public virtual List<string> GetDatabaseList()
|
{
|
throw new Exception("GetDatabaseList方法没有实现！");
|
}
|
|
/// <summary>
|
/// 获取用于预览的数据
|
/// </summary>
|
/// <returns></returns>
|
public virtual DataTable GetData(string tableName)
|
{
|
return GetTable(tableName, "");
|
}
|
|
#endregion
|
}
|
|
/// <summary>
|
/// 从Excel文件导入数据
|
/// </summary>
|
public class ImportDataFromExcel : ImporterDataSourceBase
|
{
|
private string _FileName = "";
|
|
public ImportDataFromExcel(string fileName)
|
{
|
_FileName = fileName;
|
}
|
|
public override DbConnection CreateConnection()
|
{
|
// IMEX=1 可把混合型作为文本型读取，避免null值
|
var conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
|
_FileName +
|
";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
|
return new OleDbConnection(conn);
|
}
|
|
public override DataTable GetTable(string tableName, string orderBy)
|
{
|
DataTable dt = null;
|
|
var sql = "SELECT * FROM " + tableName +
|
(!string.IsNullOrWhiteSpace(orderBy)
|
? " ORDER BY " + orderBy
|
: "");
|
|
using (var conn = CreateConnection())
|
{
|
var cmd = conn.CreateCommand() as OleDbCommand;
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(sql);
|
dt = new DataTable();
|
DbDataAdapter adp = new OleDbDataAdapter(cmd);
|
adp.Fill(dt);
|
}
|
|
return dt;
|
}
|
}
|
|
/// <summary>
|
/// 字段实体类定义
|
/// </summary>
|
public class FieldEntity
|
{
|
private string _FieldName;
|
private string _TitleName;
|
|
public FieldEntity(string fieldName, string titleName)
|
{
|
_FieldName = fieldName;
|
_TitleName = titleName;
|
}
|
|
public string FieldName
|
{
|
get => _FieldName;
|
set => _FieldName = value;
|
}
|
|
public string TitleName
|
{
|
get => _TitleName;
|
set => _TitleName = value;
|
}
|
}
|
|
/// <summary>
|
/// 字段名称映射
|
/// </summary>
|
public class FieldMapping
|
{
|
private string _SourceField;
|
private string _TargetField;
|
|
public FieldMapping(string sourceField, string targetField)
|
{
|
_SourceField = sourceField;
|
_TargetField = targetField;
|
}
|
|
/// <summary>
|
/// 映射关系是否有效
|
/// </summary>
|
public bool IsValid => !string.IsNullOrEmpty(_SourceField) &&
|
!string.IsNullOrEmpty(_TargetField);
|
|
/// <summary>
|
/// 来源字段
|
/// </summary>
|
public string SourceField
|
{
|
get => _SourceField;
|
set => _SourceField = value;
|
}
|
|
/// <summary>
|
/// 目标表字段
|
/// </summary>
|
public string TargetField
|
{
|
get => _TargetField;
|
set => _TargetField = value;
|
}
|
}
|
|
/// <summary>
|
/// 进度条
|
/// </summary>
|
public interface IProgressBar
|
{
|
int MaxValue { get; set; }
|
|
int MinValue { get; set; }
|
|
int Position { get; set; }
|
void Reset();
|
}
|
}
|
