Exemple #1
0
 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);
 }
Exemple #2
0
        /// <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);
        }