/// <summary>
/// 创建使用指定值的行，并将其添加到 MDataRowCollection 中
/// </summary>
/// <param name="values"></param>
public void Add(params object[] values)
{
if (_Table != null)
{
if (values != null && values.Length <= _Table.Columns.Count)
{
_Table.NewRow(true).LoadFrom(values);
}
}
}
/// <summary>
/// 将行的数据转成两列（ColumnName、Value）的表
/// </summary>
public MDataTable ToTable()
{
MDataTable dt = new MDataTable(TableName);
dt.Columns.Add("ColumnName");
dt.Columns.Add("Value");
for (int i = 0; i < Count; i++)
{
dt.NewRow(true).Set(0, this[i].ColumnName).Set(1, this[i].ToString());
}
return(dt);
}
/// <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);
}
/// <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);
}
/// <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;
}
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;
}
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);
}
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);
}
