internal static MDataTable Join(MDataTable dtA, string tableName, string joinOnName, params string[] appendColumns)
{
MDataTable dtB = null;
using (MAction action = new MAction(tableName, dtA.Conn))
{
if (!action.Data.Columns.Contains(joinOnName))
{
joinOnName = action.Data.Columns.FirstPrimary.ColumnName;
}
action.SetAopOff();
action.dalHelper.IsAllowRecordSql = false;//屏蔽SQL日志记录 2000数据库大量的In条件会超时。
if (appendColumns.Length > 0)
{
List<string> items = new List<string>(appendColumns.Length + 1);
items.AddRange(appendColumns);
if (!items.Contains(joinOnName))
{
items.Add(joinOnName);
}
action.SetSelectColumns(items.ToArray());
}
string whereIn = SqlCreate.GetWhereIn(action.Data[joinOnName].Struct, dtA.GetColumnItems<string>(dtA.joinOnIndex, BreakOp.NullOrEmpty, true), action.DalType);
dtB = action.Select(whereIn);
dtB.JoinOnName = joinOnName;
}
return Join(dtA, dtB, appendColumns);
}
public static void BindList(object ct, MDataTable source)
{
if (ct is ListControl)
{
BindList(ct as ListControl, source);
}
else if (ct is Win.ListControl)
{
BindList(ct as Win.ListControl, source);
}
else //wpf
{
Type t = ct.GetType();
PropertyInfo p = t.GetProperty("ItemsSource");
if (p != null)
{
p.SetValue(ct, source, null);
p = t.GetProperty("SelectedValuePath");
if (p != null)
{
p.SetValue(ct, source.Columns[0].ColumnName, null);
p = t.GetProperty("DisplayMemberPath");
p.SetValue(ct, source.Columns[source.Columns.Count > 1 ? 1 : 0].ColumnName, null);
}
}
}
}
/// <summary>
/// 将行的数据转成两列（ColumnName、Value）的表
/// </summary>
public MDataTable ToTable()
{
MDataTable dt = this.Columns.ToTable();
MCellStruct msValue = new MCellStruct("Value", SqlDbType.NVarChar);
MCellStruct msState = new MCellStruct("State", SqlDbType.Int);
dt.Columns.Insert(1, msValue);
dt.Columns.Insert(2, msState);
for (int i = 0; i < Count; i++)
{
dt.Rows[i][1].Value = this[i].Value;
dt.Rows[i][2].Value = this[i].State;
}
return(dt);
}
private static string MDataTableToFile(MDataTable dt, bool keepID, DalType dalType)
{
string path = Path.GetTempPath() + dt.TableName + ".txt";
using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
{
if (dalType == DalType.Oracle)
{
sw.WriteLine();//先输出空行（Oracle需要空行在前）
}
MCellStruct ms;
string value;
foreach (MDataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
ms = dt.Columns[i];
if (!keepID && ms.IsAutoIncrement)
{
continue;
}
else
{
value = row[i].ToString();
if (ms.SqlType == SqlDbType.Bit && value != "1")
{
value = (value.ToLower() == "true") ? "1" : "0";
}
value = value.Replace("\\", "\\\\");//处理转义符号
sw.Write(value);
}
if (i != dt.Columns.Count - 1)//不是最后一个就输出
{
sw.Write(AppConst.SplitChar);
}
}
sw.WriteLine();
}
}
if (Path.DirectorySeparatorChar == '\\')
{
path = path.Replace(@"\", @"\\");
}
return(path);
}
/// <summary>
/// 进行列修正（只有移除 和 修正类型，若无主键列，则新增主键列）
/// </summary>
private void FixTable(MDataColumn column)
{
if (column.Count > 0)
{
bool tableIsChange = false;
for (int i = mdt.Columns.Count - 1; i >= 0; i--)
{
if (!column.Contains(mdt.Columns[i].ColumnName))//没有此列
{
if (!tableIsChange)
{
mdt = mdt.Clone();//列需要变化时，克隆一份，不变更原有数据。
tableIsChange = true;
}
mdt.Columns.RemoveAt(i);
}
else
{
MCellStruct ms = column[mdt.Columns[i].ColumnName]; //新表的字段
Type valueType = mdt.Columns[i].ValueType; //存档的字段的值的原始类型。
bool isChangeType = mdt.Columns[i].SqlType != ms.SqlType;
mdt.Columns[i].Load(ms);
if (isChangeType)
{
//修正数据的数据类型。
foreach (MDataRow row in mdt.Rows)
{
row[i].FixValue();//重新自我赋值修正数据类型。
}
}
}
}
//主键检测，若没有，则补充主键
if (column.JointPrimary != null && column.JointPrimary.Count > 0)
{
if (!mdt.Columns.Contains(column[0].ColumnName) && (column[0].IsPrimaryKey || column[0].IsAutoIncrement))
{
MCellStruct ms = column[0].Clone();
mdt = mdt.Clone();//列需要变化时，克隆一份，不变更原有数据。
ms.MDataColumn = null;
mdt.Columns.Insert(0, ms);
}
}
}
}
/// <summary>
/// 将表结构的数据转成Table显示
/// </summary>
/// <returns></returns>
public MDataTable ToTable()
{
string tableName = string.Empty;
if (_Table != null)
{
tableName = _Table.TableName;
}
MDataTable dt = new MDataTable(tableName);
dt.Columns.Add("ColumnName");
dt.Columns.Add("MaxSize");
dt.Columns.Add("Scale");
dt.Columns.Add("IsCanNull");
dt.Columns.Add("IsAutoIncrement");
dt.Columns.Add("SqlType");
dt.Columns.Add("IsPrimaryKey");
dt.Columns.Add("IsUniqueKey");
dt.Columns.Add("IsForeignKey");
dt.Columns.Add("FKTableName");
dt.Columns.Add("DefaultValue");
dt.Columns.Add("Description");
dt.Columns.Add("TableName");
for (int i = 0; i < Count; i++)
{
MCellStruct ms = this[i];
dt.NewRow(true)
.Set(0, ms.ColumnName)
.Set(1, ms.MaxSize)
.Set(2, ms.Scale)
.Set(3, ms.IsCanNull)
.Set(4, ms.IsAutoIncrement)
.Set(5, ms.SqlType)
.Set(6, ms.IsPrimaryKey)
.Set(7, ms.IsUniqueKey)
.Set(8, ms.IsForeignKey)
.Set(9, ms.FKTableName)
.Set(10, ms.DefaultValue)
.Set(11, ms.Description)
.Set(12, ms.TableName);
}
return(dt);
}
public static MDataTable Select(MDataTable table, int pageIndex, int pageSize, object whereObj, params object[] selectColumns)
{
if (table == null)
{
return(null);
}
MDataTable sTable = table.GetSchema(true);
sTable.RecordsAffected = table.Rows.Count;
if (table.Rows.Count == 0) // 正常情况下，看是否需要处理列移除
{
FilterColumns(ref sTable, selectColumns); //列查询过滤
return(sTable);
}
MDataRowCollection findRows = FindAll(table, whereObj);
if (findRows != null)
{
sTable.RecordsAffected = findRows.Count; //设置记录总数
FilterPager(findRows, pageIndex, pageSize); //进行分页筛选，再克隆最后的数据。
for (int i = 0; i < findRows.Count; i++)
{
if (i < findRows.Count)//内存表时（表有可能在其它线程被清空）
{
MDataRow row = findRows[i];
if (row == null)
{
break;
}
sTable.NewRow(true).LoadFrom(row);
}
}
findRows = null;
}
if (selectColumns != null && selectColumns.Length > 0)
{
FilterColumns(ref sTable, selectColumns);//列查询过滤，由于查询的条件可能包含被移除列，所以只能在最后才过滤
}
//进行条件查询
return(sTable);
}
public static MDataRowCollection FindAll(MDataTable table, object whereObj)
{
if (table != null && table.Rows.Count > 0)
{
MDataRowCollection findRows = new MDataRowCollection();
string where = Convert.ToString(whereObj).Trim();
if (where == "" || where == "1=1")
{
findRows.AddRange(table.Rows);
return(findRows);
}
string whereStr = SqlFormat.GetIFieldSql(whereObj);
string orderby;
SplitWhereOrderby(ref whereStr, out orderby);
List <TFilter> group2 = null;
List <TFilter> filters = GetTFilter(whereStr, table.Columns, out group2);
IList <MDataRow> rows = table.Rows;
if (filters.Count > 0)
{
rows = table.Rows.FindAll(delegate(MDataRow row)
{
return(CompareMore(row, filters) && (group2.Count == 0 || CompareMore(row, group2)));
}
);
}
findRows.AddRange(rows); //添加找到的行。
filters = null;
if (!string.IsNullOrEmpty(orderby) && rows.Count > 1) //进行数组排序
{
findRows.Sort(orderby);
//MDataRowCollection sortRows = new MDataRowCollection();
//sortRows.AddRange(rows);
//sortRows.Sort(orderby);
//return sortRows;
}
return(findRows);
}
return(null);
}
public static MDataRow FindRow(MDataTable table, object whereObj)
{
if (table.Rows.Count > 0)
{
if (Convert.ToString(whereObj).Trim() == "")
{
return(table.Rows[0]);
}
string whereStr = SqlFormat.GetIFieldSql(whereObj);
string orderby;
SplitWhereOrderby(ref whereStr, out orderby);
MDataRowCollection sortRows = null;
if (!string.IsNullOrEmpty(orderby) && table.Rows.Count > 1)//进行数组排序
{
sortRows = new MDataRowCollection();
sortRows.AddRange(table.Rows);
sortRows.Sort(orderby);
}
List <TFilter> group2 = null;
List <TFilter> filters = GetTFilter(whereStr, table.Columns, out group2);
if (filters.Count > 0)
{
if (sortRows == null)
{
sortRows = table.Rows;
}
for (int i = 0; i < sortRows.Count; i++)
{
if (CompareMore(sortRows[i], filters) && (group2.Count == 0 || CompareMore(sortRows[i], group2)))
{
return(sortRows[i]);
}
}
}
}
return(null);
}
/// <summary>
/// 将表结构的数据转成Table显示
/// </summary>
/// <returns></returns>
public MDataTable ToTable()
{
string tableName = string.Empty;
if (_Table != null)
{
tableName = _Table.TableName;
}
MDataTable dt = new MDataTable(tableName);
dt.Columns.Add("ColumnName,DataType,SqlType,MaxSize,Scale");
dt.Columns.Add("IsPrimaryKey,IsAutoIncrement,IsCanNull,IsUniqueKey,IsForeignKey", SqlDbType.Bit);
dt.Columns.Add("TableName,FKTableName,DefaultValue,Description");
for (int i = 0; i < Count; i++)
{
MCellStruct ms = this[i];
dt.NewRow(true)
.Sets(0, ms.ColumnName, ms.ValueType.Name, ms.SqlType, ms.MaxSize, ms.Scale)
.Sets(5, ms.IsPrimaryKey, ms.IsAutoIncrement, ms.IsCanNull, ms.IsUniqueKey, ms.IsForeignKey)
.Sets(10, ms.TableName, ms.FKTableName, ms.DefaultValue, ms.Description);
}
return(dt);
}
public static MDataTable Select(MDataTable table, int pageIndex, int pageSize, object whereObj, params object[] selectColumns)
{
if (table == null)
{
return null;
}
MDataTable sTable = table.GetSchema(true);
sTable.RecordsAffected = table.Rows.Count;
if (table.Rows.Count == 0)// 正常情况下，看是否需要处理列移除
{
FilterColumns(ref sTable, selectColumns);//列查询过滤
return sTable;
}
List<MDataRow> findRows = FindAll(table, whereObj);
if (findRows != null)
{
FilterPager(findRows, pageIndex, pageSize);//进行分页筛选，再克隆最后的数据。
foreach (MDataRow mr in findRows)
{
sTable.NewRow(true).LoadFrom(mr);
}
findRows = null;
}
if (selectColumns != null && selectColumns.Length > 0)
{
FilterColumns(ref sTable, selectColumns);//列查询过滤，由于查询的条件可能包含被移除列，所以只能在最后才过滤
}
//进行条件查询
return sTable;
}
public static List<MDataRow> FindAll(MDataTable table, object whereObj)
{
if (table != null && table.Rows.Count > 0)
{
if (Convert.ToString(whereObj).Trim() == "")
{
return table.Rows;
}
string whereStr = SqlFormat.GetIFieldSql(whereObj);
string orderby;
SplitWhereOrderby(ref whereStr, out orderby);
TFilter[] filters = GetTFilter(whereStr, table.Columns);
List<MDataRow> rows = table.Rows;
if (filters.Length > 0)
{
rows = table.Rows.FindAll(delegate(MDataRow row)
{
return CompareMore(row, filters);
}
);
}
filters = null;
if (!string.IsNullOrEmpty(orderby) && rows.Count > 1)//进行数组排序
{
MDataRowCollection sortRows = new MDataRowCollection();
sortRows.AddRange(rows);
sortRows.Sort(orderby);
return sortRows;
}
return rows;
}
return null;
}
public static int GetCount(MDataTable table, object whereObj)
{
int count = 0;
if (table.Rows.Count > 0)
{
if (Convert.ToString(whereObj).Trim() == "")
{
return table.Rows.Count;
}
TFilter[] filters = GetTFilter(whereObj, table.Columns);
if (filters.Length > 0)
{
foreach (MDataRow row in table.Rows)
{
if (CompareMore(row, filters))
{
count++;
}
}
}
filters = null;
}
return count;
}
public MDataTableBatchAction(MDataTable mTable, string conn)
{
Init(mTable, conn);
}
internal bool BulkCopyUpdate()
{
int count = 0, pageSize = 5000;
MDataTable dt = null;
bool result = false;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
if (action.DataBaseVersion.StartsWith("08"))
{
pageSize = 1000;
}
count = mdt.Rows.Count / pageSize + 1;
DalBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary;
string columnName = keyColumn.ColumnName;
for (int i = 0; i < count; i++)
{
dt = mdt.Select(i + 1, pageSize, null);//分页读取
if (dt != null && dt.Rows.Count > 0)
{
#region 核心逻辑
string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DataBaseType);
MDataTable dtData = action.Select(whereIn); //获取远程数据。
dtData.Load(dt, keyColumn); //重新加载赋值。
//处理如果存在IsDeleted，会被转Update（导致后续无法Insert）、外层也有判断，不会进来。
result = action.Delete(whereIn, true);
if (result)
{
dtData.DynamicData = action;
result = dtData.AcceptChanges(AcceptOp.InsertWithID);
}
if (!result)
{
if (_dalHelper == null) //有外部时由外部控制，没外部时直接回滚。
{
action.RollBack(); //回滚被删除的代码。
}
break;
}
#endregion
}
}
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//还原。
}
}
return(result);
}
public static MDataRow FindRow(MDataTable table, object whereObj)
{
if (table.Rows.Count > 0)
{
if (Convert.ToString(whereObj).Trim() == "")
{
return table.Rows[0];
}
string whereStr = SqlFormat.GetIFieldSql(whereObj);
string orderby;
SplitWhereOrderby(ref whereStr, out orderby);
MDataRowCollection sortRows = null;
if (!string.IsNullOrEmpty(orderby) && table.Rows.Count > 1)//进行数组排序
{
sortRows = new MDataRowCollection();
sortRows.AddRange(table.Rows);
sortRows.Sort(orderby);
}
TFilter[] filters = GetTFilter(whereStr, table.Columns);
if (filters.Length > 0)
{
if (sortRows == null)
{
sortRows = table.Rows;
}
for (int i = 0; i < sortRows.Count; i++)
{
if (CompareMore(sortRows[i], filters))
{
return sortRows[i];
}
}
}
}
return null;
}
/// <summary>
/// 批量更新或插入。
/// </summary>
/// <param name="dt"></param>
/// <param name="excelRow"></param>
/// <returns></returns>
public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null)
{
if (excelRow == null)
{
MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique);
string[] names = null;
if (dtImportUnique != null && dtImportUnique.Rows.Count > 0)
{
names = new String[dtImportUnique.Rows.Count];
for (int i = 0; i < dtImportUnique.Rows.Count; i++)
{
names[i] = dtImportUnique.Rows[i].Get<string>(Config_Grid.Field);
}
}
return dt.AcceptChanges(AcceptOp.Auto, null, names);
}
bool result = true;
//获取相关配置
string[] tables = excelRow.Get<string>(Config_Excel.TableNames).Split(',');
MDataTable configTable = GetExcelInfo(excelRow.Get<string>(Config_Excel.ExcelID));
Dictionary<string, string> rowPrimaryValue = new Dictionary<string, string>();//存档每个表每行的主键值。
Dictionary<string, string> wherePrimaryValue = new Dictionary<string, string>();//存档where条件对应的主键值。
int acceptType = excelRow.Get<int>(Config_Excel.AcceptType);
using (MAction action = new MAction(tables[0]))
{
action.SetAopState(AopOp.CloseAll);
action.BeginTransation();
AppConfig.Debug.OpenDebugInfo = false;
IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend();
foreach (var table in tables)
{
GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存，提前调用，能降低内存。
action.ResetTable(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
action.Data.Clear();
var row = dt.Rows[i];
foreach (var cell in row)//遍历所有数据行
{
if (cell.Struct.TableName != null && cell.Struct.TableName.ToLower() == table.ToLower())//过滤出属于本表的字段。
{
string[] items = cell.ColumnName.Split('.');
string columnName = items[items.Length - 1];
action.Set(columnName, cell.Value);
}
}
#region 检测是否需要插入外键。
MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1");
if (foreignTable != null)
{
foreach (var foreignRow in foreignTable.Rows)
{
string formatter = foreignRow.Get<string>("Formatter");
string fTableName = foreignRow.Get<string>("ForeignTable");
if (string.IsNullOrEmpty(formatter))
{
//获取主键外值键
string key = fTableName + i;
if (rowPrimaryValue.ContainsKey(key))
{
string value = rowPrimaryValue[key];
action.Set(foreignRow.Get<string>("Field"), value);
}
}
else // 从其它自定义列取值。
{
MDataCell cell = row[formatter];
cell = cell ?? row[fTableName + "." + formatter];
if (cell != null)
{
action.Set(foreignRow.Get<string>("Field"), cell.Value);
}
}
}
foreignTable = null;
}
#endregion
#region //获取唯一联合主键,检测是否重复
string where = string.Empty;
List<MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1");
if (rowList != null && rowList.Count > 0)
{
bool isUniqueOr = excelRow.Get<bool>(Config_Excel.WhereType);
List<MDataCell> cells = new List<MDataCell>();
string errText = string.Empty;
int errorCount = 0;
foreach (var item in rowList)
{
var cell = action.Data[item.Get<string>(Config_ExcelInfo.Field)];
if (cell != null)
{
if (cell.IsNullOrEmpty) // 唯一主键是必填写字段
{
errorCount++;
errText += "[第" + (i + 1) + "行数据]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空！\r\n";
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!isUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText);
goto err;
}
}
MDataCell[] item2s = cells.ToArray();
where = action.GetWhere(!isUniqueOr, item2s);
item2s = null;
rowList = null;
}
if (!string.IsNullOrEmpty(where))
{
MDataRow data = action.Data.Clone();
action.SetSelectColumns(action.Data.PrimaryCell.ColumnName);
if (action.Fill(where))//根据条件查出主键ID (数据被清空)
{
string key = table + where;
if (wherePrimaryValue.ContainsKey(key))
{
rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。
}
else
{
rowPrimaryValue.Add(table + i, action.Get<string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。
}
action.Data.LoadFrom(data, RowOp.IgnoreNull, false);//还原数据。
if (action.Data.GetState() == 2 && acceptType != 1)//排除掉仅插入选项
{
ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row);
if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where)))
{
continue;//已经存在了，更新，准备下一条。
}
else
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (action.RecordsAffected == -2)//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0 || acceptType == 2)//仅更新则跳过插入
{
continue;//没有可映射插入的列。
}
//插入前，调用函数（插入特殊主键等值）
string errMsg;
ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg);
if (excelResult == ExcelResult.Ignore)
{
continue;
}
if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID))
{
result = false;
action.RollBack();
if (string.IsNullOrEmpty(errMsg))
{
errMsg = "[第" + (i + 1) + "行数据]：" + action.DebugInfo;
}
dt.DynamicData = new Exception(errMsg);
excelConfigExtend.OnInsertError(errMsg, dt);
goto err;
}
//插入后事件（可以触发其它事件）
excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1);
string primaryKey = action.Get<string>(action.Data.PrimaryCell.ColumnName);
rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。
if (!wherePrimaryValue.ContainsKey(table + where))
{
wherePrimaryValue.Add(table + where, primaryKey);
}
}
}
err:
action.EndTransation();
excelConfigExtend.Dispose();
}
return result;
}
private static string MDataTableToFile(MDataTable dt, bool keepID)
{
string path = Path.GetTempPath() + dt.TableName + ".txt";
using (StreamWriter sw = new StreamWriter(path, false, Encoding.UTF8))
{
MCellStruct ms;
string value;
foreach (MDataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
ms = dt.Columns[i];
if (!keepID && ms.IsAutoIncrement)
{
continue;
}
else
{
value = row[i].ToString();
if (ms.SqlType == SqlDbType.Bit && value != "1")
{
value = (value.ToLower() == "true") ? "1" : "0";
}
value = value.Replace("\\", "\\\\");//处理转义符号
sw.Write(value);
}
if (i != dt.Columns.Count - 1)//不是最后一个就输出
{
sw.Write(AppConst.SplitChar);
}
}
sw.WriteLine();
}
}
if (Path.DirectorySeparatorChar == '\\')
{
path = path.Replace(@"\", @"\\");
}
return path;
}
/// <summary>
/// 进行列修正（只有移除 和 修正类型，若无主键列，则新增主键列）
/// </summary>
private void FixTable(MDataColumn column)
{
if (column.Count > 0)
{
bool tableIsChange = false;
for (int i = mdt.Columns.Count - 1; i >= 0; i--)
{
if (!column.Contains(mdt.Columns[i].ColumnName))//没有此列
{
if (!tableIsChange)
{
mdt = mdt.Clone();//列需要变化时，克隆一份，不变更原有数据。
tableIsChange = true;
}
mdt.Columns.RemoveAt(i);
}
else
{
MCellStruct ms = column[mdt.Columns[i].ColumnName];//新表的字段
Type valueType = mdt.Columns[i].ValueType;//存档的字段的值的原始类型。
bool isChangeType = mdt.Columns[i].SqlType != ms.SqlType;
mdt.Columns[i].Load(ms);
if (isChangeType)
{
//修正数据的数据类型。
foreach (MDataRow row in mdt.Rows)
{
row[i].FixValue();//重新自我赋值修正数据类型。
}
}
}
}
//主键检测，若没有，则补充主键
if (column.JointPrimary != null && column.JointPrimary.Count > 0)
{
if (!mdt.Columns.Contains(column[0].ColumnName) && (column[0].IsPrimaryKey || column[0].IsAutoIncrement))
{
MCellStruct ms = column[0].Clone();
mdt = mdt.Clone();//列需要变化时，克隆一份，不变更原有数据。
ms.MDataColumn = null;
mdt.Columns.Insert(0, ms);
}
}
}
}
public MDataTableBatchAction(MDataTable mTable)
{
Init(mTable, string.Empty);
}
public MDataTableBatchAction(MDataTable mTable, string conn)
{
Init(mTable, conn);
}
internal static MDataTable Join(MDataTable dtA, MDataTable dtB, params string[] columns)
{
//记录 id as Pid 映射的列名，中间记录，修改dtB的列名，后面还原
Dictionary <string, string> mapName = new Dictionary <string, string>();
#region 判断条件
int aIndex = dtA.joinOnIndex;
if (aIndex == -1 && dtA.Columns.FirstPrimary != null)
{
aIndex = dtA.Columns.GetIndex(dtA.Columns.FirstPrimary.ColumnName);
}
int bIndex = dtB.joinOnIndex;
if (bIndex == -1 && dtB.Columns.FirstPrimary != null)
{
bIndex = dtB.Columns.GetIndex(dtB.Columns.FirstPrimary.ColumnName);
}
if (aIndex == -1 || bIndex == -1)
{
Error.Throw("set MDataTable's JoinOnName first");
}
#endregion
#region 构建新表及表结构
MDataTable joinTable = new MDataTable("V_" + dtA.TableName);
joinTable.Conn = dtA.Conn;
joinTable.Columns.AddRange(dtA.Columns.Clone());
if (columns.Length == 0)
{
joinTable.Columns.AddRange(dtB.Columns.Clone());
}
else
{
foreach (string column in columns)
{
string[] items = column.Split(' ');
string name = items[0];
MCellStruct ms = null;
if (dtB.Columns.Contains(name))
{
ms = dtB.Columns[name].Clone();
}
if (items.Length > 1)
{
name = items[items.Length - 1];
if (ms == null && dtB.Columns.Contains(name))
{
ms = dtB.Columns[name].Clone();
}
}
if (ms != null)
{
if (ms.ColumnName != name)
{
dtB.Columns[ms.ColumnName].ColumnName = name;//修改DtB的列名，结尾再还原。
mapName.Add(name, ms.ColumnName);
ms.ColumnName = name;
}
joinTable.Columns.Add(ms);
}
}
}
#endregion
List <string> noFind = new List <string>();
Dictionary <string, string> yesFind = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
string v1 = string.Empty;
MDataRow row, joinRow;
int count = dtB.Rows.Count;
for (int i = 0; i < dtA.Rows.Count; i++)
{
row = dtA.Rows[i];
if (count == 0 || row[aIndex].IsNullOrEmpty || noFind.Contains(row[aIndex].StringValue))
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(row);//后载加A表（同名则复盖）
}
else
{
v1 = row[aIndex].StringValue;
if (yesFind.ContainsKey(v1)) // 找到已匹配的数据
{
string[] items = yesFind[v1].Split(',');
foreach (string item in items)
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(dtB.Rows[int.Parse(item)]); //先加载B表
joinRow.LoadFrom(row); //后载加A表（同名则复盖）
}
}
else
{
bool isFind = false;
for (int j = 0; j < dtB.Rows.Count; j++)
{
if (v1 == dtB.Rows[j][bIndex].StringValue)//找到
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(dtB.Rows[j]); //先加载B表
joinRow.LoadFrom(row); //后载加A表（同名则复盖）
isFind = true;
if (yesFind.ContainsKey(v1))
{
yesFind[v1] = yesFind[v1] + "," + j;
}
else
{
yesFind.Add(v1, j.ToString());
}
}
}
if (!isFind)
{
noFind.Add(v1);
joinRow = joinTable.NewRow(true); //找不到时，只加载A表。
joinRow.LoadFrom(row); //后载加A表（同名则复盖）
}
}
}
}
//还原DtB的列
if (mapName.Count > 0)
{
foreach (KeyValuePair <string, string> item in mapName)
{
dtB.Columns[item.Key].ColumnName = item.Value;
}
}
#region 注销临时变量
noFind.Clear();
noFind = null;
yesFind.Clear();
yesFind = null;
mapName = null;
#endregion
return(joinTable);
}
/// <summary>
/// 两表LeftJoin关联
/// </summary>
/// <param name="dt">关联表</param>
/// <param name="appendColumns">追加显示的列，没有指定则追加关联表的所有列</param>
/// <returns></returns>
public MDataTable Join(MDataTable dt, params string[] appendColumns)
{
return(MDataTableJoin.Join(this, dt, appendColumns));
}
/// <summary>
/// 验证基础数据(数据类型、长度、是否为Null）
/// </summary>
/// <returns></returns>
public static bool ValidateData(MDataTable dt, MDataRow info)
{
bool result = true;
string[] tables = null;
List<string> requiredList = new List<string>();//必填项表。
if (info != null)
{
tables = info.Get<string>(Config_Excel.TableNames, string.Empty).Split(',');
MDataTable dtRequired = GetExcelInfo(info.Get<string>(0));//必填项表。
if (dtRequired != null && dtRequired.Rows.Count > 0)
{
dtRequired = dtRequired.Select(Config_ExcelInfo.IsRequired + "=1");
if (dtRequired != null && dtRequired.Rows.Count > 0)
{
foreach (var row in dtRequired.Rows)
{
requiredList.Add(row.Get<string>(Config_ExcelInfo.TableName) + row.Get<string>(Config_ExcelInfo.Field));
}
}
}
}
else
{
tables = dt.TableName.Split(',');
}
bool isOK = false;
foreach (var table in tables)//重置列头。
{
MDataColumn mdc = DBTool.GetColumns(table);
foreach (var cs in dt.Columns)
{
string[] items = cs.ColumnName.Split('.');
if (cs.TableName == table)
{
int index = mdc.GetIndex(items[items.Length - 1]);
if (index > -1)
{
isOK = true;//至少需要一个列对应上，若没有，则模板错误
cs.SqlType = mdc[index].SqlType;
cs.IsCanNull = mdc[index].IsCanNull;
if (requiredList.Contains(table + mdc[index].ColumnName))//要求必填
{
cs.IsCanNull = false;
}
cs.MaxSize = mdc[index].MaxSize;
}
}
}
}
if (!isOK) { return false; }
foreach (var row in dt.Rows)
{
StringBuilder sb = new StringBuilder();
foreach (var cell in row)
{
if (!string.IsNullOrEmpty(cell.Struct.TableName))
{
string columnName = string.IsNullOrEmpty(cell.Struct.Description) ? cell.Struct.ColumnName : cell.Struct.Description;
if (!cell.Struct.IsCanNull && cell.IsNullOrEmpty)
{
sb.AppendFormat("[{0}]不允许为空。", columnName);
cell.State = -1;
}
else if (cell.Struct.MaxSize != -1 && cell.ToString().Length > cell.Struct.MaxSize && cell.Struct.SqlType != System.Data.SqlDbType.Bit)
{
sb.AppendFormat("[{0}]长度超过{1}。", columnName, cell.Struct.MaxSize);
cell.State = -1;
}
else if (!cell.FixValue())
{
sb.AppendFormat("[{0}]数据类型错误。", columnName);
cell.State = -1;
}
}
}
if (sb.Length > 0)
{
result = false;
row.Set("错误信息", row.Get<string>("错误信息") + sb.ToString());
}
}
return result;
}
/// <summary>
/// 只保留查询的列
/// </summary>
private static void FilterColumns(ref MDataTable table, params object[] selectColumns)
{
if (selectColumns != null && selectColumns.Length > 0)
{
#region 列移除
bool contain = false;
for (int i = 0; i < table.Columns.Count; i++)
{
contain = false;
foreach (string columnName in selectColumns)
{
if (string.Compare(table.Columns[i].ColumnName, columnName, true) == 0)
{
contain = true;
break;
}
}
if (!contain)
{
table.Columns.RemoveAt(i);
i--;
}
}
#endregion
}
}
private void Init(MDataTable mTable, string conn)
{
if (mTable.Columns == null || mTable.Columns.Count == 0)
{
Error.Throw("MDataTable's columns can't be null or columns'length can't be zero");
}
if (string.IsNullOrEmpty(mTable.TableName))
{
Error.Throw("MDataTable's tablename can't null or empty");
}
mdt = sourceTable = mTable;
if (mdt.TableName.IndexOfAny(new char[] { '(', ')' }) > -1)
{
mdt.TableName = mdt.TableName.Substring(mdt.TableName.LastIndexOf(')') + 1).Trim();
}
_Conn = !string.IsNullOrEmpty(conn) ? conn : mTable.Conn;
if (!DBTool.ExistsTable(mdt.TableName, _Conn, out dalTypeTo, out database))
{
DBTool.CreateTable(mdt.TableName, mdt.Columns, _Conn);
}
MDataColumn column = DBTool.GetColumns(mdt.TableName, _Conn);
FixTable(column);//
if (mdt.Columns.Count == 0)
{
Error.Throw("After fix table columns, length can't be zero");
}
SetDbBaseForTransaction();
}
internal MDataColumn(MDataTable table)
{
_Table = table;
}
internal MDataRowCollection(MDataTable dt)
{
RowList = new List <MDataRow>(100);
_Table = dt;
}
public void OnInsertError(string errMsg, MDataTable dt)
{
}
internal static MDataTable Join(MDataTable dtA, MDataTable dtB, params string[] columns)
{
#region 判断条件
int aIndex = dtA.joinOnIndex;
if (aIndex == -1 && dtA.Columns.FirstPrimary != null)
{
aIndex = dtA.Columns.GetIndex(dtA.Columns.FirstPrimary.ColumnName);
}
int bIndex = dtB.joinOnIndex;
if (bIndex == -1 && dtB.Columns.FirstPrimary != null)
{
bIndex = dtB.Columns.GetIndex(dtB.Columns.FirstPrimary.ColumnName);
}
if (aIndex == -1 || bIndex == -1)
{
Error.Throw("set MDataTable's JoinOnName first");
}
#endregion
#region 构建新表及表结构
MDataTable joinTable = new MDataTable("V_" + dtA.TableName);
joinTable.Columns.AddRange(dtA.Columns.Clone());
if (columns.Length == 0)
{
joinTable.Columns.AddRange(dtB.Columns.Clone());
}
else
{
foreach (string column in columns)
{
if (dtB.Columns.Contains(column))
{
joinTable.Columns.Add(dtB.Columns[column].Clone());
}
}
}
#endregion
List<string> noFind = new List<string>();
Dictionary<string, string> yesFind = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
string v1 = string.Empty;
MDataRow row, joinRow;
int count = dtB.Rows.Count;
for (int i = 0; i < dtA.Rows.Count; i++)
{
row = dtA.Rows[i];
if (count == 0 || row[aIndex].IsNullOrEmpty || noFind.Contains(row[aIndex].strValue))
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(row);//后载加A表（同名则复盖）
}
else
{
v1 = row[aIndex].strValue;
if (yesFind.ContainsKey(v1)) // 找到已匹配的数据
{
string[] items = yesFind[v1].Split(',');
foreach (string item in items)
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(dtB.Rows[int.Parse(item)]);//先加载B表
joinRow.LoadFrom(row);//后载加A表（同名则复盖）
}
}
else
{
bool isFind = false;
for (int j = 0; j < dtB.Rows.Count; j++)
{
if (v1 == dtB.Rows[j][bIndex].strValue)//找到
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(dtB.Rows[j]);//先加载B表
joinRow.LoadFrom(row);//后载加A表（同名则复盖）
isFind = true;
if (yesFind.ContainsKey(v1))
{
yesFind[v1] = yesFind[v1] + "," + j;
}
else
{
yesFind.Add(v1, j.ToString());
}
}
}
if (!isFind)
{
noFind.Add(v1);
}
}
}
}
#region 注销临时变量
noFind.Clear();
noFind = null;
yesFind.Clear();
yesFind = null;
#endregion
return joinTable;
}
public MDataTableBatchAction(MDataTable mTable)
{
Init(mTable, string.Empty);
}
/// <summary>
/// 两表LeftJoin关联
/// </summary>
/// <param name="dt">关联表</param>
/// <param name="appendColumns">追加显示的列，没有指定则追加关联表的所有列</param>
/// <returns></returns>
public MDataTable Join(MDataTable dt, params string[] appendColumns)
{
return MDataTableJoin.Join(this, dt, appendColumns);
}
private static string MDataTableToFile(MDataTable dt, bool keepid, DataBaseType dalType)
{
string path = Path.GetTempPath() + dt.TableName + ".csv";
using (StreamWriter sw = new StreamWriter(path, false, new UTF8Encoding(false)))
{
MCellStruct ms;
string value;
foreach (MDataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
#region 设置值
ms = dt.Columns[i];
if (!keepid && ms.IsAutoIncrement)
{
continue;
}
else if (dalType == DataBaseType.MySql && row[i].IsNull)
{
sw.Write("\\N");//Mysql用\N表示null值。
}
else
{
value = row[i].ToString();
if (ms.SqlType == SqlDbType.Bit)
{
int v = (value.ToLower() == "true" || value == "1") ? 1 : 0;
if (dalType == DataBaseType.MySql)
{
byte[] b = new byte[1];
b[0] = (byte)v;
value = System.Text.Encoding.UTF8.GetString(b);//mysql必须用字节存档。
}
else
{
value = v.ToString();
}
}
else
{
value = value.Replace("\\", "\\\\");//处理转义符号
}
sw.Write(value);
}
if (i != dt.Columns.Count - 1)//不是最后一个就输出
{
sw.Write(AppConst.SplitChar);
}
#endregion
}
sw.Write('|');
sw.WriteLine();
sw.Write('|');
}
}
if (Path.DirectorySeparatorChar == '\\')
{
path = path.Replace(@"\", @"\\");
}
return(path);
}
public MDataView(ref MDataTable dt)
{
table = dt;
}
public MDataView(ref MDataTable dt)
{
table = dt;
//ListChanged += MDataView_ListChanged;
}
internal MDataColumn(MDataTable table)
{
structList = new List <MCellStruct>();
_Table = table;
}
public static MDataRow FindRow(MDataTable table, object whereObj)
{
if (table.Rows.Count > 0)
{
if (Convert.ToString(whereObj).Trim() == "")
{
return table.Rows[0];
}
TFilter[] filters = GetTFilter(whereObj, table.Columns);
if (filters.Length > 0)
{
foreach (MDataRow row in table.Rows)
{
if (CompareMore(row, filters))
{
return row;
}
}
}
}
return null;
}
internal static MDataTable Join(MDataTable dtA, MDataTable dtB, params string[] columns)
{
#region 判断条件
int aIndex = dtA.joinOnIndex;
if (aIndex == -1 && dtA.Columns.FirstPrimary != null)
{
aIndex = dtA.Columns.GetIndex(dtA.Columns.FirstPrimary.ColumnName);
}
int bIndex = dtB.joinOnIndex;
if (bIndex == -1 && dtB.Columns.FirstPrimary != null)
{
bIndex = dtB.Columns.GetIndex(dtB.Columns.FirstPrimary.ColumnName);
}
if (aIndex == -1 || bIndex == -1)
{
Error.Throw("set MDataTable's JoinOnName first");
}
#endregion
#region 构建新表及表结构
MDataTable joinTable = new MDataTable("V_" + dtA.TableName);
joinTable.Columns.AddRange(dtA.Columns.Clone());
if (columns.Length == 0)
{
joinTable.Columns.AddRange(dtB.Columns.Clone());
}
else
{
foreach (string column in columns)
{
if (dtB.Columns.Contains(column))
{
joinTable.Columns.Add(dtB.Columns[column].Clone());
}
}
}
#endregion
List <string> noFind = new List <string>();
Dictionary <string, string> yesFind = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
string v1 = string.Empty;
MDataRow row, joinRow;
int count = dtB.Rows.Count;
for (int i = 0; i < dtA.Rows.Count; i++)
{
row = dtA.Rows[i];
if (count == 0 || row[aIndex].IsNullOrEmpty || noFind.Contains(row[aIndex].strValue))
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(row);//后载加A表（同名则复盖）
}
else
{
v1 = row[aIndex].strValue;
if (yesFind.ContainsKey(v1)) // 找到已匹配的数据
{
string[] items = yesFind[v1].Split(',');
foreach (string item in items)
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(dtB.Rows[int.Parse(item)]); //先加载B表
joinRow.LoadFrom(row); //后载加A表（同名则复盖）
}
}
else
{
bool isFind = false;
for (int j = 0; j < dtB.Rows.Count; j++)
{
if (v1 == dtB.Rows[j][bIndex].strValue)//找到
{
joinRow = joinTable.NewRow(true);
joinRow.LoadFrom(dtB.Rows[j]); //先加载B表
joinRow.LoadFrom(row); //后载加A表（同名则复盖）
isFind = true;
if (yesFind.ContainsKey(v1))
{
yesFind[v1] = yesFind[v1] + "," + j;
}
else
{
yesFind.Add(v1, j.ToString());
}
}
}
if (!isFind)
{
noFind.Add(v1);
joinRow = joinTable.NewRow(true); //找不到时，只加载A表。
joinRow.LoadFrom(row); //后载加A表（同名则复盖）
}
}
}
}
#region 注销临时变量
noFind.Clear();
noFind = null;
yesFind.Clear();
yesFind = null;
#endregion
return(joinTable);
}
public static MDataTable Select(MDataTable table, int pageIndex, int pageSize, object whereObj)
{
return Select(table, pageIndex, pageSize, whereObj);
}
private void GatherChildrenID(MDataTable dt, string parentID, StringBuilder sb, string parentName = "ParentID")
{
if (!string.IsNullOrEmpty(parentID))
{
List<MDataRow> rows = dt.FindAll(parentName + "='" + parentID + "'");
if (rows != null)
{
string id = string.Empty;
foreach (MDataRow row in rows)
{
id = row.Get<string>(0);
sb.Append("'" + id + "',");
GatherChildrenID(dt, id, sb, parentName);
}
}
}
}
public static MDataTable[] Split(MDataTable table, object whereObj)
{
MDataTable[] mdt2 = new MDataTable[2];
mdt2[0] = table.GetSchema(false);
mdt2[1] = table.GetSchema(false);
if (table.Rows.Count > 0)
{
if (Convert.ToString(whereObj).Trim() == "")
{
mdt2[0] = table;
}
else
{
TFilter[] filters = GetTFilter(whereObj, table.Columns);
if (filters.Length > 0)
{
foreach (MDataRow row in table.Rows)
{
if (CompareMore(row, filters))
{
mdt2[0].Rows.Add(row, false);
}
else
{
mdt2[1].Rows.Add(row, false);
}
}
}
}
}
return mdt2;
}
/// <summary>
/// 接收Post过来的Json权限
/// </summary>
/// <returns></returns>
private MDataTable GetTable(string roleID)
{
MDataTable rowAction = new MDataTable(TableNames.Sys_RoleAction.ToString());
rowAction.Columns.Add("RoleID", SqlDbType.NVarChar);
rowAction.Columns.Add("MenuID", SqlDbType.NVarChar);
rowAction.Columns.Add("ActionID", SqlDbType.NVarChar);
MDataTable dt;
if (Query<string>("all") == "1")
{
dt = SysMenu.MenuTable;
}
else
{
dt = MDataTable.CreateFrom(Query<string>("data"));
}
if (dt != null && dt.Rows.Count > 0)
{
string menuID = string.Empty;
foreach (MDataRow row in dt.Rows)
{
menuID = row.Get<string>("MenuID");
if (!string.IsNullOrEmpty(menuID))
{
string[] ActionIDs = row.Get<string>("ActionIDs", "").Split(',');
foreach (string actionID in ActionIDs)
{
if (!string.IsNullOrEmpty(actionID))
{
rowAction.NewRow(true).Set(0, roleID).Set(1, menuID).Set(2, actionID);
}
}
}
}
}
return rowAction;
}
private static void BindList(Win.ListControl listControl, MDataTable source)
{
try
{
listControl.DataSource = new MDataView(ref source);
listControl.ValueMember = source.Columns[0].ColumnName;
listControl.DisplayMember = source.Columns[source.Columns.Count > 1 ? 1 : 0].ColumnName;
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
/// <summary>
/// ����ļ��Ƿ��ѱ��Ĺ�
/// </summary>
/// <param name="isNeedToReloadTable"></param>
private void CheckFileChanged(bool isNeedToReloadTable)
{
if (isNeedToReloadTable && IOHelper.IsLastFileWriteTimeChanged(_FileFullName, ref _lastWriteTimeUtc))//�Ѿ����Ĺ�
{
if (_tableList.ContainsKey(_FileFullName))
{
try
{
_tableList[_FileFullName].Rows.Clear();
_tableList.Remove(_FileFullName);
}
catch// (Exception err)
{
}
}
_Table = null;//��Ҫ���¼������ݡ�
}
}
static bool IsCanCache(MDataTable dt)
{
foreach (MCellStruct item in dt.Columns)
{
if (DataType.GetGroup(item.SqlType) == 999)//只存档基础类型
{
return false;
}
}
return true;
}
private static void BindList(ListControl listControl, MDataTable source)
{
listControl.DataSource = source;
listControl.DataValueField = source.Columns[0].ColumnName;
listControl.DataTextField = source.Columns[source.Columns.Count > 1 ? 1 : 0].ColumnName;
listControl.DataBind();
}
public static MDataTable Select(MDataTable table, int pageIndex, int pageSize, object whereObj)
{
return(Select(table, pageIndex, pageSize, whereObj));
}
internal bool Auto()
{
bool result = true;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
DalBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
action.dalHelper.IsRecordDebugInfo = false;//屏蔽SQL日志记录 2000数据库大量的In条件会超时。
if ((jointPrimaryIndex != null && jointPrimaryIndex.Count == 1) || (jointPrimaryIndex == null && mdt.Columns.JointPrimary.Count == 1))
//jointPrimaryIndex == null && mdt.Columns.JointPrimary.Count == 1 && mdt.Rows.Count <= 10000
//&& (!action.DalVersion.StartsWith("08") || mdt.Rows.Count < 1001)) //只有一个主键-》组合成In远程查询返回数据-》
{
#region 新逻辑
MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary;
string columnName = keyColumn.ColumnName;
//计算分组处理
int pageSize = 5000;
if (action.DataBaseVersion.StartsWith("08"))
{
pageSize = 1000;
}
int count = mdt.Rows.Count / pageSize + 1;
for (int i = 0; i < count; i++)
{
MDataTable dt = mdt.Select(i + 1, pageSize, null);//分页读取
if (dt != null && dt.Rows.Count > 0)
{
string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DataBaseType);
action.SetSelectColumns(columnName);
MDataTable keyTable = action.Select(whereIn); //拿到数据，准备分拆上市
MDataTable[] dt2 = dt.Split(SqlCreate.GetWhereIn(keyColumn, keyTable.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), DataBaseType.None)); //这里不需要格式化查询条件。
result = dt2[0].Rows.Count == 0;
if (!result)
{
MDataTable updateTable = dt2[0];
updateTable.SetState(2, BreakOp.Null);
updateTable.DynamicData = action;
result = updateTable.AcceptChanges(AcceptOp.Update, _Conn, columnName);
if (!result)
{
sourceTable.DynamicData = updateTable.DynamicData;
}
}
if (result && dt2[1].Rows.Count > 0)
{
MDataTable insertTable = dt2[1];
insertTable.DynamicData = action;
bool keepid = !insertTable.Rows[0].PrimaryCell.IsNullOrEmpty;
result = insertTable.AcceptChanges((keepid ? AcceptOp.InsertWithID : AcceptOp.Insert), _Conn, columnName);
if (!result)
{
sourceTable.DynamicData = insertTable.DynamicData;
}
}
}
}
#endregion
#region 旧逻辑，已不用 分拆处理 本地比较分拆两个表格【更新和插入】-》分开独立处理。
/*
* string columnName = mdt.Columns.FirstPrimary.ColumnName;
* string whereIn = SqlCreate.GetWhereIn(mdt.Columns.FirstPrimary, mdt.GetColumnItems<string>(columnName, BreakOp.NullOrEmpty, true), action.DalType);
* action.SetSelectColumns(mdt.Columns.FirstPrimary.ColumnName);
* dt = action.Select(whereIn);
*
* MDataTable[] dt2 = mdt.Split(SqlCreate.GetWhereIn(mdt.Columns.FirstPrimary, dt.GetColumnItems<string>(columnName, BreakOp.NullOrEmpty, true), DalType.None));//这里不需要格式化查询条件。
* result = dt2[0].Rows.Count == 0;
* if (!result)
* {
* dt2[0].SetState(2, BreakOp.Null);
* dt2[0].DynamicData = action;
* MDataTableBatchAction m1 = new MDataTableBatchAction(dt2[0], _Conn);
* m1.SetJoinPrimaryKeys(new string[] { columnName });
* result = m1.Update();
* if (!result)
* {
* sourceTable.DynamicData = dt2[0].DynamicData;
* }
* }
* if (result && dt2[1].Rows.Count > 0)
* {
* dt2[1].DynamicData = action;
* MDataTableBatchAction m2 = new MDataTableBatchAction(dt2[1], _Conn);
* m2.SetJoinPrimaryKeys(new string[] { columnName });
* result = m2.Insert(!dt2[1].Rows[0].PrimaryCell.IsNullOrEmpty);
* if (!result)
* {
* sourceTable.DynamicData = dt2[1].DynamicData;
* }
* }
*/
#endregion
}
else
{
// action.BeginTransation();
foreach (MDataRow row in mdt.Rows)
{
#region 循环处理
action.ResetTable(row, false);
string where = SqlCreate.GetWhere(action.DataBaseType, GetJoinPrimaryCell(row));
bool isExists = action.Exists(where);
if (action.RecordsAffected == -2)
{
result = false;
}
else
{
if (!isExists)
{
action.AllowInsertID = !row.PrimaryCell.IsNullOrEmpty;
action.Data.SetState(1, BreakOp.Null);
result = action.Insert(InsertOp.None);
}
else
{
action.Data.SetState(2);
result = action.Update(where);
}
}
if (!result)
{
string msg = "Error On : MDataTable.AcceptChanges.Auto." + mdt.TableName + " : [" + where + "] : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.Write(msg, LogType.DataBase);
break;
}
#endregion
}
}
action.dalHelper.IsRecordDebugInfo = true;//恢复SQL日志记录
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//还原
}
}
return(result);
}
internal MDataRowCollection(MDataTable dt)
{
_Table = dt;
}
/// <summary>
/// 导入时：把中文列头翻译成英文列头（同时处理列头结构）。
/// 并返回字典：key:列头,value:格式化名
/// </summary>
public static Dictionary<string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName)
{
if (info == null)
{
return GridConfig.SetHeaderField(dt, objName);
}
else
{
Dictionary<string, string> formatDic = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
MDataTable infoConfig = GetExcelInfo(info.Get<string>(0));
if (infoConfig != null)
{
//附加自定义列。
foreach (var configRow in infoConfig.Rows)
{
string formatter = configRow.Get<string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。
{
string excelName = configRow.Get<string>(Config_ExcelInfo.ExcelName);
if (!dt.Columns.Contains(excelName))
{
MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar);
ms.TableName = configRow.Get<string>(Config_ExcelInfo.TableName);
dt.Columns.Insert(dt.Columns.Count - 1, ms);
}
}
}
MDataRow infoRow;
foreach (MCellStruct item in dt.Columns)
{
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + item.ColumnName + "'");
if (infoRow == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。
{
string columnName = item.ColumnName.Split('_')[0];
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + columnName + "'");
}
if (infoRow != null)
{
string field = infoRow.Get<string>(Config_ExcelInfo.Field);
if (string.IsNullOrEmpty(field))
{
continue;
}
item.Description = item.ColumnName;//把中文列名放到描述里。
item.TableName = infoRow.Get<string>(Config_ExcelInfo.TableName);
if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0)
{
int index = dt.Columns.GetIndex(field);
if (index < 0)
{
item.ColumnName = field;//
}
else // 字段同名
{
item.ColumnName = item.TableName + "." + field;
//修改上一个，也增加表名。
dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName;
}
}
string formatter = infoRow.Get<string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项
{
if (formatter.Length > 2 && formatter[0] == '#')
{
//item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本）
formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]);
}
else
{
item.DefaultValue = SqlCode.FormatPara(formatter);//如果不是#开头的，设置为默认值（同时处理@参数）。
}
}
}
}
}
return formatDic;
}
}
