示例#1
0
 public JsonController(JsonToSql jsonTo, DbContext _db, IOptions <List <Role> > _roles)
 {
     jsonToSql = jsonTo;
     db        = _db;
     roles     = _roles.Value;
 }
示例#2
0
        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;
        }