private Task <JsonResult> fetchJson(SqlKeySetting sqlsetting, string delivery = null)
{
List <String> cols; List <Object[]> data; List <Type> colTypes;
return(Task.Run(() =>
{
try
{
using (var ds = DBHelper.RunQuery(sqlsetting.SQL, ConfigurationManager.AppSettings[sqlsetting.DBCONN]).Tables[0])
{
DynmicService.GetTableRowsDataCompact(ds, out cols, out data, out colTypes);
return new JsonResult
{
Data = new
{
data = data,
ErrorMsg = "" + delivery,
names = cols,
types = colTypes.Select(s => s.Name.ToLower()),
sqlsetting = sqlsetting
},
ContentType = "application/json",
ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"),
MaxJsonLength = Int32.MaxValue,
RecursionLimit = 64,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
}
}
catch (Exception e)
{
cols = new List <string>();
data = new List <object[]>();
return new JsonResult()
{
Data = new
{
data = data,
ErrorMsg = e.Message,
names = cols,
sqlsetting = sqlsetting
},
ContentType = "application/json",
ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"),
MaxJsonLength = Int32.MaxValue,
RecursionLimit = 64,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
}
}));
}
public async Task <ActionResult> dmls(List <DmlIndex> dmls, SqlKeySetting sqlsetting, string memo)
{
using (var svc = new OrmService(AppConfigs.sqlfaceconn))
{
object msg;
bool failed = false;
var setting = (await svc.FilterWhereAsync <SqlKeySetting>(s => s.SQLKEY == sqlsetting.SQLKEY && s.STS == "A")).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(sqlsetting.DML_WHERE_COLS))
{
setting.DML_WHERE_COLS = sqlsetting.DML_WHERE_COLS;//主键列,前端重算:根据后端配置+视图列 综合决定
}
if (!setting.EvalAuthorized(this.User.Identity.Name, Helper.Roles(this)))
{
return(Json("你没有权限修改执行!"));
}
var checklist = setting.ALLOWED_DML.Split(',');
var headcheck = new List <string>();
foreach (var ck in checklist)
{
switch (ck.ToUpper())
{
case "U":
headcheck.Add("UPDATE " + setting.DML_ENTITY.ToUpper().Trim());
break;
case "I":
headcheck.Add("INSERT INTO " + setting.DML_ENTITY.ToUpper().Trim());
break;
case "D":
headcheck.Add("DELETE " + setting.DML_ENTITY.ToUpper().Trim());
break;
default:
break;
}
}
//行号,DML摘要,DML语句
Dictionary <int, string[]> befores = new Dictionary <int, string[]>();
//行号,执行结果:insert回填主键,update,和Delete则填结果数据即可
Dictionary <string, string> outs = new Dictionary <string, string>();
Hashtable safeSaveContext = new Hashtable();
safeSaveContext.Add("setting", sqlsetting);
safeSaveContext.Add("befores", befores);
safeSaveContext.Add("outs", outs);
//if(dmls.Any(s=> headcheck.Any(h=>s.IndexOf(h)>=0)))
foreach (var dml in dmls)
{
//如果在限定的头校验中都不匹配
if (headcheck.All(s => dml.sql.IndexOf(s) == -1))
{
throw new ApplicationException(dml.sql + ",不被允许!服务端校验未通过!");
}
befores.Add(dml.index, new string[] { dml.sql[0].ToString().ToUpper(), dml.sql });
}
try
{
DMLHelper.safeRun(safeSaveContext);
msg = outs;
}
catch (Exception e)
{
msg = (e.Message);
failed = true;
}
//var logservice = new OrmService<AP_ACTION_LOG_DBA>(db);
//var seqservice = new OrmService<CustomSequence>(db);
var type = msg.GetType();
var msgmean = "";
if (type == typeof(string))
{
msgmean = (string)msg;
}
else
{
msgmean = Newtonsoft.Json.JsonConvert.SerializeObject(msg);
}
var risk = String.Format("执行DML,sql-key={0},语句列表={1},数据列表={2}",
setting.SQLKEY, msgmean, memo);
risk = risk.Substring(0, Math.Min(3900, risk.Length));
var log = new AP_ACTION_LOG_DBA
{
LOG_ID = CustomSequence.GetNextVal("AP_ACTION_LOG_DBA_ID", svc),
ACTION_BRIEF = setting.DML_ENTITY,
ACTION_IP = GetUserIp,
ACTION_PAGE = this.Request.RawUrl,
ACTION_PARAM = risk,
ACTION_RESULT = msgmean,
ACTION_TIME = DateTime.Now,
USER_ID = this.User.Identity.Name
};
await svc.CreateAsync <AP_ACTION_LOG_DBA>(log);
var r = new { msg = msg, hasError = failed };
return(Json(r));
}
}
