C#通用分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
// An highlighted block
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="Constring">数据库连接字符串</param>
        /// <param name="SqlData">要查询的语句</param>
        /// <param name="sqlwhere">语句条件</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">一页多少条</param>
        /// <returns></returns>
        public DataTable GetPaginData(string Constring, string SqlData, string sqlwhere, string orderBy, int pageIndex, int pageSize)
        {
            string sqlFormat = @"select *
                                from (select *,COUNT(1) OVER() AS TotalCount,ROW_NUMBER() over (order by {2}) AS RowNumber
                                      from {0}
                                      where 1 = 1 {1}) T
                                where RowNumber between @skip and @end
                                order by {2}";

            if (!string.IsNullOrEmpty(sqlwhere))
            {
                sqlwhere = "and " + sqlwhere;
                //如果where语句后面有and就去除最后一个
                sqlwhere = sqlwhere.Remove(sqlwhere.LastIndexOf("a"),3);
            }

            string Sqlstring = string.Format(sqlFormat, SqlData, sqlwhere, orderBy);


            int skip = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            SqlParameter[] Parameters = new SqlParameter[]
            {
                new SqlParameter("@skip", skip),
                new SqlParameter("@end", end)
            };           

            return GetPagingTable(Constring, Sqlstring, Parameters);
        }

        /// <summary>
        /// 执行sql，返回datatable
        /// </summary>
        /// <param name="Constring">数据库连接字符串</param>
        /// <param name="Sqlstring">要执行的Sql</param>
        /// <param name="Parameters">参数</param>
        /// <returns></returns>
        private DataTable GetPagingTable(string Constring, string Sqlstring, SqlParameter[] Parameters)
        {
            using (SqlConnection SqlCon = new SqlConnection(Constring))
            {
                using (SqlCommand SqlCom = new SqlCommand(Sqlstring, SqlCon))
                {
                    foreach (SqlParameter s in Parameters)
                    {
                        SqlCom.Parameters.Add(s);
                    }
                    SqlDataAdapter sda = new SqlDataAdapter(SqlCom);
                    DataTable datatable = new DataTable();
                    try
                    {
                        sda.Fill(datatable);
                        return datatable;
                    }
                    catch (System.Exception)
                    {
                        return datatable;
                    }
                    finally
                    {
                        sda.Dispose();
                    }
                          
                }
            }
        }