aspnet调用sqlserver分页存储过程

最新推荐文章于 2018-05-28 16:50:23 发布

麻烦的一笔

最新推荐文章于 2018-05-28 16:50:23 发布

阅读量347

点赞数

分类专栏： aspnet

本文链接：https://blog.csdn.net/qwe8254/article/details/51774365

版权

aspnet 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

/// <summary>
        /// 获取分页的一个datatable并返回符合条件的记录的总条数
        /// </summary>
        /// <param name="tablename">查询的表的名称</param>
        /// <param name="field">查询的字段</param>
        /// <param name="wheresql">不带where的where语句</param>
        /// <param name="orderby">排序字段及排序,请自行加上order by</param>
        /// <param name="pagesize">取的条数</param>
        /// <param name="page">当前页数</param>
        /// <param name="count">总记录数</param>
        public static DataTable PagenationDataTable(string tablename, string tablefield, string wheresql, string orderby, int pagesize, int page, out int count)
        {
            string countsql = "select count(*) from " + tablename;
            if (!string.IsNullOrEmpty(wheresql))
                countsql = countsql + " where " + wheresql;
            DataTable reuslt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                SqlDataAdapter sda = new SqlDataAdapter("CommonPagenation", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddRange(new SqlParameter[] {
                             new SqlParameter("@tableField",tablefield),
                             new SqlParameter("@tableName",tablename),
                             new SqlParameter("@selectWhere",wheresql),
                             new SqlParameter("@selectID","ID"),
                             new SqlParameter("@selectOrder",orderby),
                             new SqlParameter("@pageNo",page),
                             new SqlParameter("@pageSize",pagesize)});
                sda.Fill(reuslt);

                SqlCommand cmd = new SqlCommand(countsql, conn);
                count = Convert.ToInt32(cmd.ExecuteScalar());
            }
            return reuslt;
        }

        /// <summary>
        /// 获取分页的一个datatable并返回符合条件的记录的总条数
        /// </summary>
        /// <param name="id">字段</param>
        /// <param name="tablename">查询的表的名称</param>
        /// <param name="tablefield">查询的字段</param>
        /// <param name="wheresql">不带where的where语句</param>
        /// <param name="orderby">排序字段及排序,请自行加上order by</param>
        /// <param name="pagesize">取的条数</param>
        /// <param name="page">当前页数</param>
        /// <param name="count">总记录数</param>
        public static DataTable PagenationDataTable(string id, string tablename, string tablefield, string wheresql, string orderby, int pagesize, int page, out int count)
        {
            string countsql = "select count(*) from " + tablename;
            if (!string.IsNullOrEmpty(wheresql))
                countsql = countsql + " where " + wheresql;
            DataTable reuslt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                SqlDataAdapter sda = new SqlDataAdapter("CommonPagenation", conn);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand.Parameters.AddRange(new SqlParameter[] {
                             new SqlParameter("@tableField",tablefield),
                             new SqlParameter("@tableName",tablename),
                             new SqlParameter("@selectWhere",wheresql),
                             new SqlParameter("@selectID",id),
                             new SqlParameter("@selectOrder",orderby),
                             new SqlParameter("@pageNo",page),
                             new SqlParameter("@pageSize",pagesize)});
                sda.Fill(reuslt);

                SqlCommand cmd = new SqlCommand(countsql, conn);
                count = Convert.ToInt32(cmd.ExecuteScalar());
            }
            return reuslt;
        }

前台调用和mysql的前台调用基本一致，具体参考aspnet调用mysql存储过程