//创建文件数据库，并添加50条数据。
void CreateTable()
{
Response.Write("文章见：http://www.cnblogs.com/cyq1162/p/3443244.html <hr />");
if (DBTool.ExistsTable(tableName))
{
using (MAction action = new MAction(tableName))
{
if (action.Fill("order by id desc"))
{
action.Delete("id<=" + action.Get <int>(0));
}
}
//DBTool.DropTable(tableName);
}
else
{
MDataColumn mdc = new MDataColumn();
mdc.Add("ID", SqlDbType.Int, true);
mdc.Add("Name");
mdc.Add("CreateTime", SqlDbType.DateTime);
DBTool.CreateTable(tableName, mdc);
}
MDataTable dt = new MDataTable(tableName);
dt.Columns = DBTool.GetColumns(tableName);
for (int i = 0; i < 60; i++)
{
dt.NewRow(true).Set(1, "Name_" + i).Set(2, DateTime.Now.AddSeconds(i));
}
dt.AcceptChanges(AcceptOp.Insert);
}
private void btnRemove_Click(object sender, EventArgs e)
{
MDataTable delDt = dt.GetSchema(false);
for (int i = 0; i < dgView.Rows.Count; i++)
{
if (Convert.ToString(dgView.Rows[i].Cells[0].Value) == "True")
{
dgView.Rows.RemoveAt(i);
delDt.NewRow(true).LoadFrom(dt.Rows[i]);
}
}
dgView.Refresh();
delDt.AcceptChanges(AcceptOp.Delete);
delDt.AcceptChanges(AcceptOp.Insert | AcceptOp.Truncate);
}
/// <summary>
/// 添加权限
/// </summary>
/// <returns></returns>
public string AddPromission()
{
string roleID = Query <string>("RoleID");
bool result = false;
MDataTable dt = GetTable(roleID);// MDataTable.CreateFrom(strArr, mdc);
if (dt != null && dt.Rows.Count > 0)
{
//删除该角色下面所有权限
using (MAction action = new MAction(TableNames.Sys_RoleAction))
{
action.BeginTransation();
action.Delete("RoleID='" + roleID + "'");
dt.DynamicData = action;
dt.SetState(1);
result = dt.AcceptChanges(AcceptOp.Insert);
if (!result)
{
action.RollBack();
}
action.EndTransation();
}
}
return(JsonHelper.OutResult(result, result ? "添加成功！" : "添加失败！"));
}
public static MDataTable Check(string objName, string objCode, MDataTable dt)
{
#if DEBUG
MDataTable newDt = dt.GetSchema(false);
FillTable(objName, objCode, newDt);//重新获取。
MDataTable addTable = dt.GetSchema(false);
bool needUpdate = false;
foreach (MDataRow row in newDt.Rows)
{
MDataRow mr = dt.FindRow(string.Format("Field='{0}'", row.Get <string>("Field")));
if (mr == null)//找不到，则添加行
{
row.Set("Hidden", true);
addTable.Rows.Add(row);
}
else if (mr["Field"].ToString() == mr["Title"].ToString()) // 找到，则试图设置中文名称。
{
mr.Set("Title", row.Get <string>("Title"));
needUpdate = true;
}
}
if (addTable.Rows.Count > 0)
{
addTable.AcceptChanges(AcceptOp.Auto, null, "ObjName", "Field");
}
if (needUpdate)
{
dt.AcceptChanges(AcceptOp.Update);
}
#endif
return(dt);
}
/// <summary>
/// 创建数据结构行
/// </summary>
public static MDataTable Create(string objName, string objCode, MDataTable schema)
{
FillTable(objName, objCode, schema);
if (schema.AcceptChanges(AcceptOp.Insert))
{
return(GetList(objName, SelectType.All));
}
return(schema);
}
/// <summary>
/// 批量向数据库写入瓦片数据xyz信息
/// </summary>
/// <param name="listdata"></param>
/// <returns></returns>
public static bool insertDatas(List <DiTu_DB> listdata)
{
using (MDataTable dt2 = MDataTable.CreateFrom(listdata))
{
dt2.TableName = TableNames.dt.ToString();
dt2.Conn = ConnectStr;
return(dt2.AcceptChanges(AcceptOp.InsertWithID));
}
}
/// <summary>
/// 创建数据结构行
/// </summary>
public static MDataTable Create(string objName, MDataTable schema)
{
string formatObjName = objName.Contains(" ") ? objName.Substring(objName.LastIndexOf(' ')).Trim() : objName;
FillTable(objName, schema);
if (schema.AcceptChanges(AcceptOp.Insert))
{
return(GetList(formatObjName, SelectType.All));
}
return(schema);
}
/// <summary>
/// 批量更新瓦片数据
/// </summary>
/// <param name="listdata"></param>
/// <returns></returns>
public static bool updateDatas(List <DiTu_DB> listdata)
{
using (MDataTable dt2 = MDataTable.CreateFrom(listdata))
{
dt2.SetState(2);
dt2.TableName = TableNames.dt.ToString();
dt2.Conn = ConnectStr;
return(dt2.AcceptChanges(AcceptOp.Update));
}
}
/// <summary>
/// 创建数据结构行
/// </summary>
public static MDataTable Create(string objName, string objCode, MDataTable schema)
{
bool result = false;
lock (obj)
{
FillTable(objName, objCode, schema);
result = schema.AcceptChanges(AcceptOp.Auto, null, Config_Grid.ObjName, Config_Grid.Field);
}
if (result)
{
return(GetList(objName, SelectType.All));
}
return(schema);
}
private void AddSuperAdminPromission(string menuID, string actionIDs)
{
if (!string.IsNullOrEmpty(actionIDs))
{
MDataTable dt = new MDataTable(TableNames.Sys_RoleAction.ToString());
dt.Columns.Add("RoleID");
dt.Columns.Add("MenuID");
dt.Columns.Add("ActionID");
foreach (string actionID in actionIDs.Split(','))
{
dt.NewRow(true).Set(0, UserAuth.SuperAdminRoleID)
.Set(1, menuID).Set(2, actionID);
}
dt.AcceptChanges(AcceptOp.Insert);
}
}
private void AddSuperAdminPromission(string menuid, string actionids)
{
if (!string.IsNullOrEmpty(actionids))
{
MDataTable dt = new MDataTable(U_AriesEnum.Sys_RoleAction.ToString());
dt.Columns.Add("RoleID");
dt.Columns.Add("MenuID");
dt.Columns.Add("ActionID");
foreach (string actionid in actionids.Split(','))
{
dt.NewRow(true).Set(0, UserAuth.SuperAdminRoleID)
.Set(1, menuid).Set(2, actionid);
}
dt.AcceptChanges(AcceptOp.Insert, CrossDb.GetConn(U_AriesEnum.Sys_RoleAction.ToString()));
}
}
void ResetMainState()
{
MDataTable table = null;
using (ProjectConfig config = new ProjectConfig())
{
table = config.Select();//更新其它的状态。
}
if (table.Rows.Count > 0)
{
foreach (MDataRow row in table.Rows)
{
row.Set("IsMain", false);
}
table.AcceptChanges(AcceptOp.Update);
}
}
/// <summary>
/// 数据结构行刷新
/// </summary>
/// <param name="objName"></param>
/// <param name="objCode"></param>
/// <param name="dt">原有数据</param>
/// <returns></returns>
public static bool Flesh(string objName, string objCode, MDataTable dt, out string msg)
{
bool result = false;
msg = LangConst.NoNewColumn;
MDataTable newDt = dt.GetSchema(false);
//移除表结构缓存
string tableKey = CacheManage.GetKey(CacheKeyType.Schema, objName);
string codeKey = CacheManage.GetKey(CacheKeyType.Schema, objCode);
CacheManage.LocalInstance.Remove(tableKey);
CacheManage.LocalInstance.Remove(codeKey);
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
IOHelper.Delete(AppConfig.RunPath + AppConfig.DB.SchemaMapPath + tableKey + ".ts");
IOHelper.Delete(AppConfig.RunPath + AppConfig.DB.SchemaMapPath + codeKey + ".ts");
}
FillTable(objName, objCode, newDt);//重新获取。
MDataTable addTable = dt.GetSchema(false);
// bool needUpdate = false;
foreach (MDataRow row in newDt.Rows)
{
MDataRow mr = dt.FindRow(string.Format("Field='{0}'", row.Get <string>("Field")));
if (mr == null)//找不到，则添加行
{
row.Set(Config_Grid.OrderNum, 255);
row.Set(Config_Grid.Hidden, true);
row.Set(Config_Grid.GridID, Guid.NewGuid());
addTable.Rows.Add(row);
}
}
if (addTable.Rows.Count > 0)
{
result = addTable.AcceptChanges(AcceptOp.InsertWithID);
if (!result)
{
msg = Convert.ToString((Exception)addTable.DynamicData);
}
}
return(result);
}
/// <summary>
/// 添加权限
/// </summary>
/// <returns></returns>
public string AddPromission()
{
string roleID = Query <string>("RoleID");
bool result = false;
//MDataColumn mdc = new MDataColumn();
//mdc.Add("RoleID", SqlDbType.NVarChar);
//mdc.Add("MenuID", SqlDbType.NVarChar);
//mdc.Add("ActionID", SqlDbType.NVarChar);
MDataTable dt = GetTable(roleID);// MDataTable.CreateFrom(strArr, mdc);
if (dt != null && dt.Rows.Count > 0)
{
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// MDataRow row = dt.Rows[i];
// if (row["MenuID"].IsNullOrEmpty || row["ActionID"].IsNullOrEmpty)
// {
// dt.Rows.RemoveAt(i);
// i--;
// continue;
// }
// row.Set("RoleID", roleID);
//}
//dt.TableName = TableNames.Sys_RoleAction.ToString();
//删除该角色下面所有权限
using (MAction action = new MAction(TableNames.Sys_RoleAction))
{
action.BeginTransation();
action.Delete("RoleID='" + roleID + "'");
dt.DynamicData = action;
dt.SetState(1);
result = dt.AcceptChanges(AcceptOp.Insert);
if (!result)
{
action.RollBack();
}
action.EndTransation();
}
}
return(JsonHelper.OutResult(result, result ? "添加成功！" : "添加失败！"));
}
/// <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>("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条件对应的主键值。
using (MAction action = new MAction(tables[0]))
{
action.SetAopOff();
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 == table) //过滤出属于本表的字段。
{
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>("IsUniqueOr");
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))
{
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));//记录上一个主键值。
}
if (action.Data.GetState() == 2)
{
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 (!string.IsNullOrEmpty(action.DebugInfo))//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0)
{
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 void btnSave_Click(object sender, EventArgs e)
{
bool result = dt.AcceptChanges(AcceptOp.Auto);
MessageBox.Show(result.ToString(), "Result");
}
private void btnUpdate_Click(object sender, EventArgs e)
{
bool result = dt.AcceptChanges(AcceptOp.Update);
rtxtText.Text = "AcceptChanges(Update):" + result;
}
