/// <summary>
/// 查询数据库
/// </summary>
/// <returns></returns>
private DataSet QueryDS(QueryDataSetModel dsm, DBT dbt = DBT.sqllite)
{
DataSet ds = new DataSet();
string sql = string.Empty;
switch (dbt)
{
case DBT.sqllite:
sql = "SELECT " + dsm.columns + " FROM " + dsm.tableName + " WHERE " + dsm.whereStr + " ORDER BY " + dsm.orderBy + " LIMIT " + dsm.pageSize + " OFFSET " + dsm.pageSize * (dsm.pageIndex - 1);
if (dsm.total)
{
sql += "; select count(1) AS counts from " + dsm.tableName + " WHERE " + dsm.whereStr;
ds = DB.HelperSQLite.Query(sql);
}
else
{
ds = DB.HelperSQLite.Query(sql);
}
break;
}
return(ds);
}
public string QueryData(QueryInputModel qim)
{
//输入参数
string KeyWords = string.Empty;
if (!string.IsNullOrEmpty(Request["keywords"]))
{
KeyWords = Request["keywords"].ToString().OfSql();
}
//构查询实体
QueryDataSetModel dsm = new QueryDataSetModel();
dsm.pageIndex = Convert.ToInt32(Request["pageIndex"]);
dsm.pageSize = Convert.ToInt32(Request["pageSize"]);
//点击标题排序
string OrderBy = string.Empty;
if (Request.Form["sortField"].ToString().Length > 1)
{
string[] sortField = Request.Form["sortField"].ToString().Split(',');
string[] sortOrder = Request.Form["sortOrder"].ToString().Split(',');
for (int i = 0; i < sortField.Length; i++)
{
OrderBy += "," + sortField[i] + " " + sortOrder[i];
}
OrderBy = OrderBy.Substring(1);
}
switch (qim.uri.ToLower())
{
#region 公共查询
//表格配置
case "pptableconfig":
dsm.tableName = "view_table_config";
dsm.orderBy = "l_order";
dsm.whereStr = "vname='" + Request["vname"].ToString().OfSql() + "'";
dsm.total = false;
break;
//菜单
case "ppmenu":
dsm.tableName = "sys_menu";
dsm.orderBy = OrderBy == "" ? "m_order" : OrderBy;
dsm.whereStr = "m_pid=0";
dsm.total = true;
break;
#endregion
#region Setting 控制器
//角色查询
case "pprole":
dsm.tableName = "sys_role";
dsm.orderBy = OrderBy == "" ? "id" : OrderBy;
dsm.total = true;
break;
//操作日志
case "pplog":
dsm.tableName = "sys_log";
dsm.orderBy = OrderBy == "" ? "l_datetime desc" : OrderBy;
dsm.whereStr = KeyWords != "" ? string.Format("l_user like '{0}%' or l_action ='%{0}%' or l_content ='%{0}%' or l_ip ='%{0}%'", KeyWords) : "";
dsm.total = true;
break;
//列表配置
case "ppcolumn":
dsm.tableName = "sys_column";
dsm.orderBy = OrderBy == "" ? "id" : OrderBy;
dsm.whereStr = KeyWords != "" ? string.Format("vname like '{0}%' or field ='{0}'", KeyWords) : "";
dsm.total = true;
break;
//按钮列表
case "ppbutton":
dsm.tableName = "sys_button";
dsm.orderBy = OrderBy == "" ? "b_pid,b_order" : OrderBy;
break;
//用户查询
case "ppuser":
dsm.tableName = "view_sys_user";
dsm.orderBy = OrderBy == "" ? "u_state" : OrderBy;
dsm.whereStr = KeyWords != "" ? string.Format("u_name like '%{0}%'", KeyWords) : "";
dsm.total = true;
break;
#endregion
}
if (dsm.tableName.Length == 0 || dsm.orderBy.Length == 0)
{
throw new Exception("查询传参错误，缺失表名或排序列");
}
//得到数据集
DataSet ds = QueryDS(dsm, DBT.sqllite);
//输出数据
QueryOutputModel qom = new QueryOutputModel();
if (dsm.total)
{
qom.total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
}
qom.data = ds.Tables[0];
//第一次查询列配置
if (Convert.ToInt32(Request["columnsExists"]) == 0)
{
qom.columns = QueryColumns(dsm.tableName);
}
//列配置表名
qom.vname = dsm.tableName;
return(qom.ToJson());
}
