分页1

 

 public class DataPage
    {                                                          
        public struct PageParam
        {
            /// <summary>
            /// 表名或视图名称
            /// </summary>
            public string TableName;
            /// <summary>
            /// 键
            /// </summary>
            public string ID;
            /// <summary>
            /// 要查询的列
            /// </summary>
            public string SelectColumns;
            /// <summary>
            /// where 语句， 含where
            /// </summary>
            public string Where;
            /// <summary>
            /// 排序语句，含 order by
            /// </summary>
            public string OrderBy;
            /// <summary>
            /// 开始行号,查询结果不包含该行 (行号从1开始),如 40000
            /// </summary>
            public int RowStart;
            /// <summary>
            /// 结束行号,查询结果包含该行 (行号从1开始),如 40015
            /// </summary>
            public int RowEnd;

            /// <summary>
            /// 左关联
            /// </summary>
            public string LeftJoin;
        }
        public static string GetPageSql(PageParam param)
        {
            string SQL = "";
            SQL += " SELECT {SelectColumns} FROM {TableName} {LeftJoin}                  ";//
            SQL += " WHERE {ID} IN                                                       ";//
            SQL += " (                                                                   ";//
            SQL += "  SELECT  {ID}              ";// 唯一标示ID
            SQL += "  FROM                                                             ";//
            SQL += "  (                                                                ";//
            SQL += "   SELECT                                                       ";//
            SQL += "    row_number() OVER (ORDER BY C1) RowNo,                   ";//
            SQL += "    *                                                        ";//
            SQL += "   FROM                                                         ";//
            SQL += "   (                                                            ";//
            SQL += "    SELECT TOP {RowEnd}              ";// 结束行数，如 40010
            SQL += "    0 AS C1,                      ";// 0列
            SQL += "    {ID}                          ";// 唯一标示ID
            SQL += "    FROM  {TableName}                      ";// 表名或视图名
            SQL += "    {Where}                       ";// 查询条件
            SQL += "    {OrderBy}                    ";// 排序规则
            SQL += "   ) t                                                          ";//
            SQL += "  ) tt                                                             ";//
            SQL += "  WHERE RowNo > {RowStart}                    ";// 开始行数,如 40000
            SQL += " )                                                                   ";//
            SQL += " {OrderBy}                                                           ";//

            return
                SQL
                    .Replace('\t', ' ')
                    .Replace("  ", " ")
                    .Replace("{TableName}", param.TableName)
                    .Replace("{SelectColumns}", param.SelectColumns)
                    .Replace("{ID}", param.ID)
                    .Replace("{Where}", param.Where)
                    .Replace("{OrderBy}", param.OrderBy)
                    .Replace("{RowStart}", param.RowStart.ToString())
                    .Replace("{RowEnd}", param.RowEnd.ToString())
                    .Replace("{LeftJoin}", param.LeftJoin)
                    ;
        }

        public static string GetCountSql(PageParam param)
        {
            string SQL = " SELECT count(1) FROM {TableName} {Where}  ";

            return
                SQL
                    .Replace("{TableName}", param.TableName)
                    .Replace("{Where}", param.Where)
                    ;
        }
    }