public JsonController(JsonToSql jsonTo, DbContext _db, IOptions <List <Role> > _roles)
{
jsonToSql = jsonTo;
db = _db;
roles = _roles.Value;
}
public ResponseMessage ExecuteDataSource(long ProjectID, [FromBody] ExcuteConfig config)
{
// update tablename set column=value where id=id
//Stream req = Request.Body;
//req.Seek(0, System.IO.SeekOrigin.Begin);
//string json = new StreamReader(req).ReadToEnd();
//var config = JsonConvert.DeserializeObject<ExcuteConfig>(json);
// 判断是否有权限
var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);
if (userproject == null)
{
return(new ResponseMessage(MessageResult.Error, "项目不存在"));
}
// 解析配置
if (config == null)
{
return(new ResponseMessage(MessageResult.Error, "配置错误"));
}
var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == config.connectid);
if (connect == null)
{
return(new ResponseMessage(MessageResult.Error, "连接不存在"));
}
// 解析json为sql语句;
var jts = new JsonToSql(connect.Type);
var querylist = new List <SelectConfig>();
var sqllist = new List <SqlObject>();
foreach (var _sqlConfig in config.Configs)
{
var sql = jts.jsontosql(_sqlConfig);
// 查询单独处理
if (_sqlConfig.SelectToken("type").ToObject <string>() == "select")
{
var obj = new SelectConfig()
{
name = _sqlConfig.SelectToken("name").ToObject <string>(),
database = _sqlConfig.SelectToken("database").ToObject <string>(),
table = _sqlConfig.SelectToken("table").ToObject <string>(),
sql = sql.Sql,
parameters = sql.Parameters
};
querylist.Add(obj);
}
else
{
sqllist.Add(sql);
}
}
var rows = ExecuteSqlTran2(connect, sqllist);
var jobject = new JObject();
// 执行查询操作
foreach (var obj in querylist)
{
var name = obj.name; // obj.SelectToken("name").ToObject<string>();
var sql = obj.sql; // obj.SelectToken("sql").ToObject<string>();
// 条件中的记录集处理
{
foreach (var jobj in jobject)
{
var columns = jobj.Value.SelectToken("columns").ToObject <JArray>();
foreach (var _column in columns)
{
var key = string.Format("[{0}.{1}]", jobj.Key, _column.SelectToken("name"));
if (sql.Contains(key))
{
// 如果sql中包含了特殊字符，则进行数据组装
var dd = jobj.Value.SelectToken("data")
.Select(m => "'" + m.SelectToken(_column.SelectToken("name").ToObject <string>()).ToObject <string>() + "'");
var result = string.Join(",", dd);
if (string.IsNullOrEmpty(result))
{
result = "''";
}
sql = sql.Replace(key, result);
}
}
}
}
var ds = Query(connect, sql, obj.parameters);
//ds1.Tables[0].TableName = name;
// 返回数据
var jarray = new JArray();
foreach (DataRow _row in ds.Tables[0].Rows)
{
var jobj = new JObject();
foreach (DataColumn _column in ds.Tables[0].Columns)
{
jobj.Add(_column.ColumnName, JToken.FromObject(_row[_column.ColumnName]).ToString());
//jobj.Add(_column.ColumnName, _column.DataType.ToString());
}
jarray.Add(jobj);
}
var columnarray = new JArray();
foreach (DataColumn _column in ds.Tables[0].Columns)
{
var jobj = new JObject();
jobj.Add("name", _column.ColumnName);
jobj.Add("type", _column.DataType.ToString());
jobj.Add("key", _column.Table.PrimaryKey.Contains(_column));
columnarray.Add(jobj);
}
jobject.Add(name, new JObject()
{
{ "database", obj.database },
{ "table", obj.table },
{ "columns", columnarray },
{ "data", jarray },
{ "totalItems", long.Parse(ds.Tables[1].Rows[0][0].ToString()) },
{ "beginindex", 0 },
{ "takecount", 20 },
{ "connectid", config.connectid },
});
}
return(new ResponseMessage(MessageResult.Success, "成功", new
{
connectid = config.connectid,
rows = rows,
data = jobject
}));
//return Json(new
//{
// rows= rows,
// data = jobject
//});
////return Content(sql + "=============" + rows);
//return null;
}
