public string QuerySysUser(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = from a in db.SysUser
join b in db.SysRole on a.RoleId equals b.Id
select new
{
a.Id,
a.Nickname,
a.RoleId,
a.Sign,
a.Status,
a.UserGroup,
a.UserName,
a.UserPwd,
a.CreateTime,
OldUserPwd = a.UserPwd,
RoleName = b.Name
};
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
public string QueryConfigTable(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = db.SysTableConfig.Where(x => x.ColHide != 2);
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
public string QuerySysRole(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = db.SysRole;
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
public string QuerySysTableConfig(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var query = db.SysTableConfig;
//query = query.Where(x => EF.Functions.Like(x.TableName, "%log%"));
Func.Common.QueryJoin(query, param, db, ref or);
}
return(or.ToJson());
}
/// <summary>
/// 查询
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public string QueryConfigTable(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var ru = new RepositoryUse())
{
var query = from a in ru.Context.Set <SysTableConfig>()
select a;
Func.Common.QueryJoin(query, param, ru, ref or);
}
return(or.ToJson());
}
public string QueryTableConfig()
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var queryHas = from a in db.SysTableConfig
group a by a.TableName into g
select g.Key;
var listHas = queryHas.ToList();
var dbname = db.Database.GetDbConnection().Database;
var sql = QueryScripts(db.TDB.ToString(), "name").Replace("@DataBaseName", dbname);
var dt = new DataTable();
var listRow = new List <object>();
if (!string.IsNullOrWhiteSpace(sql))
{
using (var conn = db.Database.GetDbConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
dt.Load(cmd.ExecuteReader());
}
foreach (DataRow dr in dt.Rows)
{
var key = dr[0].ToString();
var val = (listHas.Exists(x => x.ToLower() == key.ToLower()) ? "1" : "");
var row = new JObject
{
["name"] = key,
["exists"] = val
};
listRow.Add(row);
}
}
or.data = listRow;
or.total = listRow.Count;
}
return(or.ToJson());
}
public string QueryData(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
//根据标识检索是否存在对应的数据表，（表字典DicTable 可过滤掉不让查询的表，也可以自定义DicTable表字典）
//前端直传表名查询会有安全问题，所以通过uri标识在后台做对应且可过滤
if (Func.Common.DicTable.ContainsKey(param.uri))
{
//表名
param.tableName = Func.Common.DicTable[param.uri];
//条件
if (!string.IsNullOrWhiteSpace(param.wheres))
{
var jwhere = JObject.Parse(param.wheres) as JToken;
param.wheres = QueryDataVM.SqlQueryWhere(jwhere);
}
if (string.IsNullOrWhiteSpace(param.wheres))
{
param.wheres = "1=1";
}
//排序
param.sortOrderJoin = DataBase.OrderByJoin(param.sort, param.order);
//查询：列、数据表、总条数
if (param.columnsExists != 1)
{
or.columns = Func.Common.QuerySysTableConfigList(x => x.TableName == param.tableName);
}
var ds = Func.Common.QueryDS(param);
or.data = ds.Tables[0];
if (param.pagination == 1)
{
or.total = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
}
else
{
or.total = ds.Tables[0].Rows.Count;
}
}
return(or.ToJson());
}
public string QuerySysButton(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
using (var db = new ContextBase())
{
var list = db.SysButton.OrderBy(x => x.BtnOrder).ToList();
var tree = Func.Common.ListToTree(list, "Pid", "Id", new List <string> {
Guid.Empty.ToString()
});
or.data = tree.ToJArray();
//列
if (param.columnsExists != 1)
{
or.columns = db.SysTableConfig.Where(x => x.TableName == param.tableName).OrderBy(x => x.ColOrder).ToList();
}
}
return(or.ToJson());
}
/// <summary>
/// 查询
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public string QuerySysUser(QueryDataVM.GetParams param)
{
var or = new QueryDataVM.OutputResult();
#region 自定义SQL查询主体
//查询主体，select 开头 ，QueryJoin里面会处理分页排序及查询条件
string sql = "select a.*,UserPwd as OldUserPwd,b.Name as RoleName from SysUser a left join SysRole b on a.RoleID=b.ID";
//查询总条数，可选，不传，则以查询主体sql查询总条数，此处如果用主体SQL多了不必要的关联查询
string sqlcount = "select count(1) from SysUser";
Func.Common.QueryJoin(sql, param, ref or, sqlcount);
#endregion
#region linq查询，Like等SQL查询条件不支持
//using (var ru = new RepositoryUse())
//{
// var query = from a in ru.Context.Set<SysUser>()
// join b in ru.Context.Set<SysRole>() on a.RoleID equals b.ID
// select new
// {
// a.ID,
// a.RoleID,
// a.UserName,
// a.UserPwd,
// OldUserPwd = a.UserPwd,
// a.Nickname,
// a.CreateTime,
// a.Status,
// a.UserGroup,
// RoleName = b.Name
// };
// Func.Common.QueryJoin(query, param, ru, ref or);
//}
#endregion
return(or.ToJson());
}
public string QueryTableInfo(string names)
{
var listName = names.Split(',').ToList();
var innames = string.Join("','", listName);
var or = new QueryDataVM.OutputResult();
var dt = new DataTable();
ContextBase.TypeDB tdb;
using (var db = new ContextBase())
{
tdb = db.TDB;
var dbname = db.Database.GetDbConnection().Database;
var sql = QueryScripts(db.TDB.ToString(), "info").Replace("@DataBaseName", dbname).Replace("@TableName", innames);
if (!string.IsNullOrWhiteSpace(sql))
{
using (var conn = db.Database.GetDbConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
dt.Load(cmd.ExecuteReader());
}
}
or.data = dt;
or.total = dt.Rows.Count;
}
#region 其它处理
//mysql默认值，单独查询
switch (tdb)
{
case ContextBase.TypeDB.MySQL:
using (var db = new ContextBase())
{
var conn = db.Database.GetDbConnection();
conn.Open();
var cmd = conn.CreateCommand();
foreach (var name in listName)
{
cmd.CommandText = "desc " + name + ";";
var dtdefault = new DataTable();
dtdefault.Load(cmd.ExecuteReader());
foreach (DataRow dr in dt.Rows)
{
if (dr["表名"].ToString().ToLower() == name.ToLower())
{
var dv = dtdefault.Select("Field='" + dr["字段名"].ToString() + "'")[0]["Default"];
if (dv != DBNull.Value)
{
dr["默认值"] = dv;
}
}
}
}
cmd.Dispose();
conn.Dispose();
}
break;
}
#endregion
return(or.ToJson());
}
