internal bool BulkCopyDelete()
{
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);
result = action.Delete(whereIn) || action.RecordsAffected == 0;
if (result)
{
sourceTable.RecordsAffected += action.RecordsAffected;//记录总删除的数量。
}
else
{
sourceTable.RecordsAffected = 0;
string msg = "Error On : MDataTable.AcceptChanges.Delete." + mdt.TableName + " : where (" + whereIn + ") : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.Write(msg, LogType.DataBase);
break;
}
#endregion
}
}
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//还原。
}
}
return(result);
}
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.SetAopState(CYQ.Data.Aop.AopOp.CloseAll);
action.dalHelper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//屏蔽SQL日志记录 2000数据库大量的In条件会超时。
if (appendColumns.Length > 0)
{
if (appendColumns.Length == 1)
{
appendColumns = appendColumns[0].Split(',');
}
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.DataBaseType);
dtB = action.Select(whereIn);
dtB.JoinOnName = joinOnName;
}
return(Join(dtA, dtB, appendColumns));
}
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));
}
internal bool BulkCopyUpdate()
{
int count = 0, pageSize = 5000;
MDataTable dt = null;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
if (action.DalVersion.StartsWith("08"))
{
pageSize = 1000;
}
count = mdt.Rows.Count / pageSize + 1;
DbBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
bool result = false;
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.DalType);
MDataTable dtData = action.Select(whereIn); //获取远程数据。
dtData.Load(dt); //重新加载赋值。
result = action.Delete(whereIn); //如果存在IsDeleted，会被转Update（导致后续无法Insert）
if (result)
{
dtData.DynamicData = action;
result = dtData.AcceptChanges(AcceptOp.InsertWithID);
}
if (!result)
{
break;
}
#endregion
}
}
if (_dalHelper == null)
{
action.BeginTransation();
}
else
{
action.dalHelper = sourceHelper;//还原。
}
}
return(true);
}
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);
}
