public static string ForTable(DataTable Datas, string ParentKeyValue, string ParentKey, string ChildKey, string Json)
{
DataRow[] drs = Datas.Select(string.Format("{0}='{1}'", ParentKey, ParentKeyValue));
foreach (DataRow dr in drs)
{
if (Datas.Select(string.Format("{0}='{1}'", ParentKey, dr[ChildKey].ToString())).Length > 0)
{
Json += ConvertJson.ToParentJson(dr, Datas);
string jsons = string.Empty;
Json += ForTable(Datas, dr[ChildKey].ToString(), ParentKey, ChildKey, jsons);
Json = Json.TrimEnd(',');
Json += "]},";
}
else
{
Json += ConvertJson.ToNoParentJson(dr, Datas);
}
}
return(Json);
}
/// <summary>
/// 获取列表数据库
/// </summary>
/// <returns></returns>
public string GetData()
{
string json = "";
Database database = GetDatabase();
object obj = "0";
if (database != null)
{
string[] idFields = IdField.Split(',');
for (int i = 0; i < idFields.Length; i++)
{
idFields[i] = "[" + idFields[i] + "]";
}
string pkField = string.Join("+", idFields);
string[] sortFields = Sort.Split(',');
string[] orderFields = Order.Split(',');
for (int i = 0; i < sortFields.Length; i++)
{
sortFields[i] = "[" + sortFields[i] + "]";
if (orderFields.Length > i)
{
sortFields[i] = sortFields[i] + " " + orderFields[i];
}
}
string sortField = string.Join(",", sortFields);
Condition = Condition + Where;
string sql = string.Empty;
//多行递归
if (ParentField != "" && ChildField != "")
{
string lstr = string.Empty;
string sqls = string.Empty;
if (int.Parse(Page) > 1)
{
//是否关联查询
if (RelevanSearch == "true")
{
sql = "select * from (select row_number() over (order by " + sortField + ") as rowNum,* from " + TableName + " where 1=1 and ([" + ParentField + "] is NULL or [" + ParentField + "]='') " + Condition + " or (" + ChildField + " in (select " + ParentField + " from [" + TableName + "] where 1=1 " + Condition + ")) ) as t where rowNum between " + (int.Parse(Rows) * (int.Parse(Page) - 1) + 1) + " and " + (int.Parse(Rows) * (int.Parse(Page))) + " order by rowNum;";
}
else
{
sql = "select * from (select row_number() over (order by " + sortField + ") as rowNum,* from " + TableName + " where 1=1 and ([" + ParentField + "] is NULL or [" + ParentField + "]='') " + Condition + " ) as t where rowNum between " + (int.Parse(Rows) * (int.Parse(Page) - 1) + 1) + " and " + (int.Parse(Rows) * (int.Parse(Page))) + " order by rowNum;";
}
}
else
{
//是否关联查询
if (RelevanSearch == "true")
{
sql = "select top " + Rows + " * from (select row_number() over (order by " + sortField + ") as rowNum, * from [" + TableName + "] where 1=1 and ([" + ParentField + "] is NULL or [" + ParentField + "]='') " + Condition + " or (" + ChildField + " in (select " + ParentField + " from [" + TableName + "] where 1=1 " + Condition + ")) ) AS t ORDER BY rowNum;";
}
else
{
sql = "select top " + Rows + " * from (select row_number() over (order by " + sortField + ") as rowNum, * from [" + TableName + "] where 1=1 and ([" + ParentField + "] is NULL or [" + ParentField + "]='') " + Condition + " ) AS t ORDER BY rowNum;";
}
}
//是否关联查询
if (RelevanSearch == "true")
{
obj = database.ExecuteScalar("select count(*) from " + TableName + " where 1=1 and ([" + ParentField + "] is NULL or [" + ParentField + "]='') " + Condition + " or (" + ChildField + " in (select " + ParentField + " from [" + TableName + "] where 1=1 " + Condition + "))");
sql = sql + "select * from [" + TableName + "] where 1=1 " + Condition + " or (" + ChildField + " in (select " + ParentField + " from [" + TableName + "] where 1=1 " + Condition + ") and sysstatus<>-1) or (" + ParentField + " in (select " + ChildField + " from [" + TableName + "] where 1=1 " + Condition + ") and sysstatus<>-1) order by " + sortField;
}
else
{
obj = database.ExecuteScalar("select count(*) from " + TableName + " where 1=1 and ([" + ParentField + "] is NULL or [" + ParentField + "]='') " + Condition);
sql = sql + "select * from [" + TableName + "] where 1=1 " + Condition + " order by " + sortField;
}
DataSet doc = database.ExecuteDataSet(sql);
if (doc.Tables[0].Rows.Count > 0)
{
//ParentField TopGuid
//ChildField Guid
json = "[";
var temp = doc.Tables[0].Select();
var temp2 = doc.Tables[1].Select();
foreach (DataRow dr in doc.Tables[0].Select())
{
lstr = "";
DataRow[] drs = doc.Tables[1].Select(string.Format("{0}='{1}'", ParentField, dr[ChildField].ToString()));
if (drs.Length > 0)
{
json += ConvertJson.ToParentJson(dr, doc.Tables[0]);
json += ForTable(doc.Tables[1], dr[ChildField].ToString(), ParentField, ChildField, lstr);
json = json.TrimEnd(',');
json += "]},";
}
else
{
json += ConvertJson.ToNoParentJson(dr, doc.Tables[0]);
}
}
json = json.TrimEnd(',');
json += "]";
}
else
{
json = "[]";
}
}
//无递归
else
{
if (int.Parse(Page) > 1)
{
sql = "select * from (select row_number() over (order by " + sortField + ") as rowNum,* from " + TableName + " where 1=1" + Condition + " ) as t where rowNum between " + (int.Parse(Rows) * (int.Parse(Page) - 1) + 1) + " and " + (int.Parse(Rows) * (int.Parse(Page))) + " order by rowNum";
//sql = "select top " + rows + " * from [" + tableName + "] where " + pkField + " not in (select top " + (int.Parse(rows) * (int.Parse(page) - 1)).ToString() + " " + pkField + " from [" + tableName + "] where 1=1 " + condition + " order by " + sortField + " " + order + ") " + condition + " order by " + sortField + " " + order;
}
else
{
sql = "select top " + Rows + " * from (select row_number() over (order by " + sortField + ") as rowNum, * from [" + TableName + "] where 1=1 " + Condition + " ) AS t ORDER BY rowNum";
}
DataSet doc = database.ExecuteDataSet(sql);
obj = database.ExecuteScalar("select count(*) from " + TableName + " where 1=1 " + Condition);
if (doc.Tables[0].Rows.Count > 0)
{
json = ConvertJson.ToJson(doc.Tables[0]);
}
else
{
json = "[]";
}
}
}
else
{
json = "[]";
obj = "0";
}
json = "{\"total\":" + obj.ToString() + ",\"rows\":" + json + "}";
return(json);
}