private static void FillTable(string objName, string objCode, MDataTable dt)
{
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objCode));
MCellStruct cell = null;
int jointPrimaryCount = mdc.JointPrimary.Count;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row.Set(Config_Grid.ObjName, objName);
row.Set(Config_Grid.Field, cell.ColumnName);
row.Set(Config_Grid.Title, fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName);
row.Set(Config_Grid.Hidden, i == 0 && jointPrimaryCount < 2);
row.Set(Config_Grid.OrderNum, (i + 1) * 10);
row.Set(Config_Grid.Width, 100);
row.Set(Config_Grid.Sortable, i > 0);
row.Set(Config_Grid.Import, i > 0);
row.Set(Config_Grid.Export, i > 0);
row.Set(Config_Grid.Colspan, 1);
row.Set(Config_Grid.Rowspan, 1);
row.Set(Config_Grid.Edit, i > 0 || jointPrimaryCount > 1);
row.Set(Config_Grid.Frozen, i < 4);
row.Set(Config_Grid.Align, "center");
string value = DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + (cell.IsCanNull ? ",0" : ",1") + (cell.IsPrimaryKey ? ",1" : ",0");
row.Set(Config_Grid.DataType, value);
if (i == 0)
{
if (jointPrimaryCount < 2)
{
row.Set(Config_Grid.Formatter, "#");
}
}
else
{
switch (DataType.GetGroup(cell.SqlType))
{
case 2:
row.Set(Config_Grid.Formatter, "dateFormatter");
break;
case 3:
row.Set(Config_Grid.Formatter, "boolFormatter");
break;
default:
if (cell.MaxSize > 50)
{
row.Set(Config_Grid.Formatter, "stringFormatter");
}
break;
}
}
dt.Rows.Add(row);
}
}
private static void FillTable(string objName, MDataTable dt)
{
string formatObjName = objName.Contains(" ") ? objName.Substring(objName.LastIndexOf(' ')).Trim() : objName;
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objName));
MCellStruct cell = null;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row["ObjName"].Value = formatObjName;
row["Field"].Value = cell.ColumnName;
row["Title"].Value = fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName;
row["Hidden"].Value = i == 0;//隐藏主键
row["Formatter"].Value = i == 0 ? "#" : "";
row["OrderNum"].Value = (i + 1) * 10;
row["Width"].Value = 100;//第一个主键列<10，则由图标个数*36 自动控制。
row["Sortable"].Value = i > 0;
row["Import"].Value = i > 0 && !cell.IsCanNull;
row["Export"].Value = true;
row["Edit"].Value = i > 0;
row["Frozen"].Value = i < 4;
row["Align"].Value = "center";
row["DataType"].Value = DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + "," + (cell.IsCanNull ? 0 : 1);
if (i > 0)
{
switch (DataType.GetGroup(cell.SqlType))
{
//case 1:
// row["DataType"].Value = "int";
// break;
case 2:
//row["DataType"].Value = "datetime";
row["Formatter"].Value = "dateFormatter";
break;
case 3:
//row["DataType"].Value = "bool";
row["Formatter"].Value = "boolFormatter";
break;
default:
//row["DataType"].Value = "string";
if (cell.MaxSize > 50)
{
row["Formatter"].Value = "stringFormatter";
}
break;
}
}
dt.Rows.Add(row);
}
}
private static void FillTable(string objName, MDataTable dt)
{
string formatObjName = objName.Contains(" ") ? objName.Substring(objName.LastIndexOf(' ')).Trim() : objName;
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objName));
MCellStruct cell = null;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row.Set(Config_Grid.ObjName, formatObjName);
row.Set(Config_Grid.Field, cell.ColumnName);
row.Set(Config_Grid.Title, fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName);
row.Set(Config_Grid.Hidden, i == 0);
row.Set(Config_Grid.OrderNum, (i + 1) * 10);
row.Set(Config_Grid.Width, 100);
row.Set(Config_Grid.Sortable, i > 0);
row.Set(Config_Grid.Import, i > 0);
row.Set(Config_Grid.Export, i > 0);
row.Set(Config_Grid.Colspan, 1);
row.Set(Config_Grid.Rowspan, 1);
row.Set(Config_Grid.Edit, i > 0);
row.Set(Config_Grid.Frozen, i < 4);
row.Set(Config_Grid.Align, "center");
row.Set(Config_Grid.DataType, DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + "," + (cell.IsCanNull ? 0 : 1));
if (i == 0)
{
row.Set(Config_Grid.Formatter, "#");
}
else
{
switch (DataType.GetGroup(cell.SqlType))
{
case 2:
row.Set(Config_Grid.Formatter, "dateFormatter");
break;
case 3:
row.Set(Config_Grid.Formatter, "boolFormatter");
break;
default:
if (cell.MaxSize > 50)
{
row.Set(Config_Grid.Formatter, "stringFormatter");
}
break;
}
}
dt.Rows.Add(row);
}
}
protected override MDataTable Select(GridConfig.SelectType st)
{
switch (ObjName)
{
case "V_Test": //处理Demo中文本数据库
MDataTable dt = null;
using (MAction action = new MAction(CrossDb.GetEnum("Demo_TestA")))
{
dt = action.Select();
}
dt.JoinOnName = "ID";
MDataTable joinDt = dt.Join("Demo_TestB", "ID");
return(joinDt.Select(PageIndex, PageSize, GetWhere() + GetOrderBy("ID"), GridConfig.GetSelectColumns(ObjName, st)));
}
return(base.Select(st));
}
public static Dictionary <string, string> GetTitleField(string tableName)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(tableName));
if (mdc.Count > 0)
{
foreach (MCellStruct item in mdc)
{
string des = Convert.ToString(item.Description);
if (!string.IsNullOrEmpty(des))
{
des = des.Split(new char[] { ' ', ',', '(' })[0];
if (!dic.ContainsKey(des))
{
dic.Add(des, item.ColumnName);
}
}
}
}
return(dic);
}
/// <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, CrossDb.GetConn(dt.TableName), 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(CrossDb.GetEnum(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", "").Trim('.');
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;
MDataRowCollection 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>();
StringBuilder errText = new StringBuilder();
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.Append("[" + LangConst.The + "" + (i + 1) + LangConst.Row + "]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]" + LangConst.CantBeEmpty + "！\r\n");
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!isUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText.ToString());
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("[" + LangConst.The + (i + 1) + LangConst.Row + "]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (action.RecordsAffected == -2)//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[" + LangConst.The + (i + 1) + LangConst.Row + "]：" + 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 = "[" + LangConst.The + (i + 1) + LangConst.Row + "]：" + 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);
}
/// <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(CrossDb.GetEnum(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}]{1}", columnName, LangConst.CantBeEmpty);
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}{2}。", columnName, LangConst.SizeOver, cell.Struct.MaxSize);
cell.State = -1;
}
else if (!cell.FixValue())
{
sb.AppendFormat("[{0}]{1}。", columnName, LangConst.DataTypeError);
cell.State = -1;
}
}
}
if (sb.Length > 0)
{
result = false;
row.Set(LangConst.ErrorInfo, row.Get <string>(LangConst.ErrorInfo) + sb.ToString());
}
}
return(result);
}
