jquery.easyuiの使用の詳細と、出会った問題は、みんなが使用する時に回り道を少なくすることを提供します(二)

前回はいくつかの間違いを説明しましたが、もちろん大神たちにはおかずの一皿でしたが、今日はバックグラウンドでデータを要求し、ページを分けてjsonデータに戻ります.
くだらないことを言わないでコードを献上します

    private string QueryList(ArrayList arrayList)

    {

        //ArrayList ret = new ArrayList();

        string order = this.Request.Form["order"];

        string sort = this.Request.Form["sort"];

        string rows = this.Request.Form["rows"];

        string page = this.Request.Form["page"];

        //page=1&rows=10&sort=UserName&order=desc

        int iSortIndex, iPage, iRows;

        string[] colums = new string[] {  "moveid", "daytime", "outsite", "insite", "inputman", "handleman", "outdate", "outid", "availability", "invaliddate", "status", "preconcert", "checked", "chkdate", "fin_checked", "remark", "used", "deleted"};

        //if (!int.TryParse(sidx, out iSortIndex))

        //{

        //    iSortIndex = -1;

        //}

        iSortIndex = 1;

        if (!int.TryParse(page, out iPage))

        {

            iPage = 1;

        }

        if (!int.TryParse(rows, out iRows) || iRows > 100 || iRows <= 0)

        {

            iRows = 20;

        }

        int total = 0;

             //    

        JGDataQuery gq = new JGDataQuery("move");

        //      

        if (!string.IsNullOrEmpty(Request.Form["IdOrName"]))

        {

            if (Erp.Common.Verify.IsNumber(Request.Form["IdOrName"]))

            {

                gq.AddQueryParam("moveid", Request.Form["IdOrName"]);

            } 

        }

　　　　　　

        DataTable dt = gq.GetListByPage(colums, iPage, iRows, 1, sort,out total);
　　　　　//  json

        return Erp.Common.ToEasyJson.TableToJson(dt,total); 



    }

上のクエリーデータはクラスをカプセル化して、クエリー条件とデータのページングsqlを含んで、もちろんストレージプロセスを使うのが最も良くて、最も速いですが、システムの関連表が多すぎて、面倒な頻繁な変更のために、sqlをつづって、拡張の便利さのために、こっそり怠けています

public class JGDataQuery

{



    public string TableName { get; private set; }

    string sqlWhere;

    List<SqlParameter> sqlParaList = new List<SqlParameter>();

    public JGDataQuery(string tableName)

    {

        //

        //TODO:            

        //

        if (string.IsNullOrEmpty(tableName))

        {

            throw new NullReferenceException("      ;");

        }

        TableName = tableName;

    }



    public void AddQueryParam(string name, object paramValue)

    {

        sqlWhere += " and " + name + "=@" + name;

        sqlParaList.Add(new SqlParameter(name, paramValue));

    }

    /// <summary>

    ///       

    /// </summary>

    /// <param name="columnName"></param>

    /// <param name="key"></param>

    /// <param name="keyValue"></param>

    public void AddInParams(string columnName, List<string> listParams)

    {

        sqlWhere += " and " + columnName + "  in (";

        foreach (string item in listParams)

        {

            sqlWhere += item + ',';

        }



        if (sqlWhere.Length > 0)

        {

            sqlWhere = sqlWhere.Remove(sqlWhere.LastIndexOf(','), 1);

        }

        sqlWhere += " )    ";

    }

    public void AddLikeQueryParam(string name, object paramValue)

    {

        sqlWhere += " and " + name + " like '%@" + name + "%'  ";

        sqlParaList.Add(new SqlParameter(name, paramValue));

    }

    public void AddLetterThanParm(string name, object paramValue)

    {

        sqlWhere += " and " + name + "<@" + name;

        sqlParaList.Add(new SqlParameter(name, paramValue));

    }

    public void AddGreaterThanParm(string name, object paramValue)

    {

        sqlWhere += " and " + name + ">@" + name;

        sqlParaList.Add(new SqlParameter(name, paramValue));

    }

    public void AddNoEqualParam(string name, object paramValue)

    {

        sqlWhere += name + "<>@" + name + " and ";

        sqlParaList.Add(new SqlParameter(name, paramValue));

    }

    public void AddBetweenQueryParam(string starttime, string endtime, object paramValue1, object paramValue2)

    {

        sqlWhere += " and " + starttime + " < @" + starttime;

        sqlWhere += " and " + endtime + "< @" + endtime;

        sqlParaList.Add(new SqlParameter(starttime, paramValue1));

        sqlParaList.Add(new SqlParameter(endtime, paramValue2));

    }

    /// <summary>

    /// Or    

    /// </summary>

    string sqlOrWhere = string.Empty;

    public void AddlikeOrParam(string name, object paramValue)

    {

        //sqlOrWhere += name + " like @" + name + " or ";

        //sqlParaList.Add(new SqlParameter(name, " '%" + paramValue + "%' "));



        sqlOrWhere += name + " like '%" + paramValue + "%' or ";

        sqlParaList.Add(new SqlParameter(name, " '%" + paramValue + "%' "));

    }

    /// <summary>

    ///            

    /// </summary>

    /// <param name="columns">         </param>

    /// <param name="page">   </param>

    /// <param name="pageSize">  </param>

    /// <param name="sortIndex">    </param>

    /// <param name="sortDirction">    </param>

    /// <returns></returns>

    public DataTable GetListByPage(string[] columns, int page, int pageSize, int sortIndex, string sortDirction, out int total)

    {

        StringBuilder strSql = new StringBuilder();

        strSql.Append("SELECT ");

        if (columns == null || columns.Length == 0)

            strSql.Append(" * ");

        else

        {

            for (int i = 0; i < columns.Length; i++)

            {

                strSql.Append(columns[i] + ",");

            }

            strSql.Remove(strSql.Length - 1, 1);

        }

        strSql.Append(" FROM ( ");

        strSql.Append(" SELECT ROW_NUMBER() OVER (");



        if (sortIndex >= 0 && sortIndex < columns.Length - 1)

        {

            strSql.Append(" order by T." + columns[sortIndex]);

            if (sortDirction != null && (sortDirction == "asc" || sortDirction == "desc"))

            {

                strSql.Append("  " + sortDirction);

            }

        }



        strSql.Append(")AS Row, T.*  from " + TableName + " T ");

        if (sqlParaList != null && sqlParaList.Count > 0)

        {

            strSql.Append(" where 1=1");

            if (!string.IsNullOrEmpty(sqlWhere))

            {

                strSql.Append(sqlWhere.TrimEnd(','));

            }

            if (!string.IsNullOrEmpty(sqlOrWhere))

            {

                strSql.Append(" and ( " + sqlOrWhere.Substring(0, sqlOrWhere.Length - 3) + " )");

            }

        }

        strSql.Append(" ) TT");

        strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (pageSize * (page - 1) + 1), pageSize * page);

        //       

        total = GetListCount(pageSize);

        return SqlHelper.ExecuteDataTable(SqlHelper.SHOP_CONN_STR, CommandType.Text, strSql.ToString(), sqlParaList.ToArray());

    }

    /// <summary>

    ///        

    /// </summary>

    /// <returns></returns>

    public int GetListCount(int pageSize)

    {



        StringBuilder strSql = new StringBuilder("select count(1)" + " from " + TableName);

        if (sqlParaList != null && sqlParaList.Count > 0)

        {

            strSql.Append(" where 1=1");

            if (!string.IsNullOrEmpty(sqlWhere))

            {

                strSql.Append(sqlWhere.TrimEnd(','));

            }

            if (!string.IsNullOrEmpty(sqlOrWhere))

            {

                strSql.Append(" and (" + sqlOrWhere.Substring(0, sqlOrWhere.Length - 3) + ")");

            }

        }

        object obj = SqlHelper.ExecuteScalar(SqlHelper.SHOP_CONN_STR, CommandType.Text, strSql.ToString(), sqlParaList.ToArray());

        int RowsCount = 0;

        if (obj != null)

        {

            return RowsCount = (int)obj;

        }

        else

        {

            return 0;

        }

        //int total = 0;

        // //if (RowsCount % pageSize == 0)

        // //{

        // //    total = RowsCount / pageSize;

        // //}

        // //else

        // //{

        // //    total = RowsCount / pageSize + 1;

        // //}

        // return total;

    }

jsonをつなぐ方法

      public static string TableToJson(DataTable table,int total)

        {

            if (table == null)

                return "";

            StringBuilder sb = new StringBuilder();

            sb.Append("{");

            sb.Append("\"total\":" + total + ",");

            sb.Append("\"rows\":[");

            foreach (DataRow row in table.Rows)

            {

                sb.Append("{");

                foreach (DataColumn col in table.Columns)

                {

                    sb.Append("\"" + col.ColumnName + "\":\"" + row[col.ColumnName] + "\",");

                }

                sb.Remove(sb.Length - 1, 1);

                sb.Append("},");

            }

            sb.Remove(sb.Length - 1, 1);

            sb.Append("]");

            sb.Append("}");

            return sb.ToString();

        }