/// <summary>
/// 检测GUID，若空，补值。
/// </summary>
private bool CheckGUIDAndDateTime(DataBaseType dal)
{
bool fillGUID = false;
int groupID;
for (int i = 0; i < mdt.Columns.Count; i++)
{
MCellStruct ms = mdt.Columns[i];
groupID = DataType.GetGroup(ms.SqlType);
if (groupID == 2)
{
for (int j = 0; j < mdt.Rows.Count; j++)
{
if (dal == DataBaseType.MsSql && mdt.Rows[j][i].StringValue == DateTime.MinValue.ToString())
{
mdt.Rows[j][i].Value = SqlDateTime.MinValue;
}
else if (dal == DataBaseType.Oracle && mdt.Rows[j][i].StringValue == SqlDateTime.MinValue.ToString())
{
mdt.Rows[j][i].Value = SqlDateTime.MinValue;
}
}
}
else if (ms.IsPrimaryKey && (groupID == 4 || (groupID == 0 && ms.MaxSize >= 36)))
{
string defaultValue = Convert.ToString(ms.DefaultValue);
bool isGuid = defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid;
if (isGuid && !fillGUID)
{
fillGUID = true;
}
for (int k = 0; k < mdt.Rows.Count; k++)
{
if (mdt.Rows[k][i].IsNullOrEmpty)
{
mdt.Rows[k][i].Value = isGuid ? Guid.NewGuid().ToString() : defaultValue;
}
}
}
}
return(fillGUID);
}
/// <summary>
/// 复制一行
/// </summary>
/// <returns></returns>
public MDataRow Clone()
{
MDataRow row = new MDataRow();
for (int i = 0; i < base.Count; i++)
{
MCellStruct mcb = base[i].Struct;
MDataCell mdc = new MDataCell(ref mcb);
mdc.strValue = base[i].strValue;
mdc.CellValue.Value = base[i].CellValue.Value;
mdc.CellValue.State = base[i].CellValue.State;
mdc.CellValue.IsNull = base[i].CellValue.IsNull;
row.Add(mdc);
}
//row._Table = _Table;//不能带，会造成单行移除列时，移除的是原引用的行，而不是自身
row.RowError = RowError;
row.TableName = TableName;
row.Conn = Conn;
return(row);
}
/// <summary>
/// 克隆一个对象。
/// </summary>
/// <returns></returns>
public MCellStruct Clone()
{
MCellStruct ms = new MCellStruct(dalType);
ms.ColumnName = ColumnName;
ms.SqlType = SqlType;
ms.IsAutoIncrement = IsAutoIncrement;
ms.IsCanNull = IsCanNull;
ms.MaxSize = MaxSize;
ms.Scale = Scale;
ms.IsPrimaryKey = IsPrimaryKey;
ms.IsUniqueKey = IsUniqueKey;
ms.IsForeignKey = IsForeignKey;
ms.FKTableName = FKTableName;
ms.SqlTypeName = SqlTypeName;
ms.DefaultValue = DefaultValue;
ms.Description = Description;
ms.MDataColumn = MDataColumn;
ms.AlterOp = AlterOp;
return(ms);
}
internal void Load(MCellStruct ms)
{
ColumnName = ms.ColumnName;
SqlTypeName = ms.SqlTypeName;
SqlType = ms.SqlType;
IsAutoIncrement = ms.IsAutoIncrement;
IsCanNull = ms.IsCanNull;
MaxSize = ms.MaxSize;
Scale = ms.Scale;
IsPrimaryKey = ms.IsPrimaryKey;
IsUniqueKey = ms.IsUniqueKey;
IsForeignKey = ms.IsForeignKey;
FKTableName = ms.FKTableName;
AlterOp = ms.AlterOp;
IsJsonIgnore = ms.IsJsonIgnore;
if (ms.DefaultValue != null)
{
DefaultValue = ms.DefaultValue;
}
if (!string.IsNullOrEmpty(ms.Description))
{
Description = ms.Description;
}
}
/// <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 void Add(string columnName, SqlDbType sqlType, object value)
{
MCellStruct cs = new MCellStruct(columnName, sqlType, false, true, -1);
Add(new MDataCell(ref cs, value));
}
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);
}
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 MDataCell(ref MCellStruct dataStruct, object value)
{
Init(dataStruct, value);
}
/// <summary>
/// 原型模式（Prototype Method）
/// </summary>
/// <param name="dataStruct"></param>
internal MDataCell(ref MCellStruct dataStruct)
{
Init(dataStruct, null);
}
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);
}