asp.net 结合mysql存储过程进行分页代码

来源：互联网发布：什么是软件什么是硬件编辑：程序博客网时间：2024/06/06 03:29

转：http://blog.csdn.net/smallbelial

自己写的，刚刚初成，还没有进行更多的细化，不过如果有朋友喜欢，可以看看

其中用到的存储过程在这里：

http://blog.csdn.net/smallbelial/archive/2008/11/23/3353829.aspx

        /// <summary>
        /// 分页显示
        /// </summary>
        /// <param name="conn">连接数据库字符串</param>
        /// <param name="perPage">每页显示条数</param>
        /// <param name="columnList">查询的字段字符</param>
        /// <param name="tableName">查询的表名</param>
        /// <param name="condition">where条件(不用写where)</param>
        /// <param name="orderStr">排序条件(不用写order by)</param>
        /// <param name="pageInfo">返回页码的数组，0，1，2，3分别为总记录集数，总页数，上一页，下一页，4开始为页码</param>
        /// <returns>此页的数据记录集</returns>
        public static DataTable PageList(string conn, int perPage, string url, string columnList, string tableName, string condition, string orderStr, out string[] pageInfo)
        {
            int pageNo = 1;         //当前页码
            int totalCnt = 1;       //记录集总数
            int pageCnt = 0;        //总页数

DataTable dt = new DataTable(); //用于返回的DataTable

            using (MySqlConnection myConn = new MySqlConnection(conn))
            {
                MySqlDataAdapter adp = new MySqlDataAdapter();
                MySqlCommand cmd = new MySqlCommand();
                if (!string.IsNullOrEmpty(System.Web.HttpContext.Current.Request["pageNo"]))
                {
                    try
                    {
                        pageNo = int.Parse(System.Web.HttpContext.Current.Request["pageNo"].ToString());
                    }
                    finally
                    {

}
}//得到当前页面值

                cmd.Connection = myConn;
                myConn.Open();
                cmd.CommandText = "p_pageList";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("m_pageNo", MySqlDbType.Int32, 11);
                cmd.Parameters.Add("m_perPageCnt", MySqlDbType.Int32, 11);
                cmd.Parameters.Add("m_column", MySqlDbType.VarChar, 1000);
                cmd.Parameters.Add("m_table", MySqlDbType.VarChar, 1000);
                cmd.Parameters.Add("m_condition", MySqlDbType.VarChar, 1000);
                cmd.Parameters.Add("m_orderBy", MySqlDbType.VarChar, 200);
                cmd.Parameters.Add("m_totalCnt", MySqlDbType.Int32, 11);

                cmd.Parameters["m_pageNo"].Value = pageNo;
                cmd.Parameters["m_perPageCnt"].Value = perPage;
                cmd.Parameters["m_column"].Value = columnList;
                cmd.Parameters["m_table"].Value = tableName;
                cmd.Parameters["m_condition"].Value = condition;
                cmd.Parameters["m_orderBy"].Value = orderStr;
                cmd.Parameters["m_totalCnt"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                adp.SelectCommand = cmd;
                adp.Fill(dt);
                totalCnt = int.Parse(cmd.Parameters["m_totalCnt"].Value.ToString());
            }

            pageCnt = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(totalCnt) / perPage));

            if (pageNo > pageCnt)
            {
                pageNo = pageCnt;

}//如果当前页码大于总页数，则当前页为最后一页

            //处理页码地址参数
            string pageNumLink = string.Empty; //页码的链接地址
            if (string.IsNullOrEmpty(url.Trim()))
            {
                pageNumLink = "<a href=/"?pageNo=";
            }
            else if (url.IndexOf('?') >= 0)
            {
                pageNumLink = "<a href=/"" + url + "&pageNo=";
            }
            else
            {
                pageNumLink = "<a href=/"" + url + "?pageNo=";
            }

            //上一页，下一页字符串赋值
            string abovePage = pageNumLink + (pageNo-1).ToString()+ "/">上一页</a>";
            string nextPage = pageNumLink + (pageNo + 1).ToString() + "/">下一页</a>";
            if (pageNo == 1)
            {
                abovePage = string.Empty;

}//如果当前页为第一页，则不显示“上一页”字符串

            if (pageNo == pageCnt)
            {
                nextPage = string.Empty;

            }//如果当前页为最后一页，则不显示“下一页”字符串

            pageInfo = new string[14];
            pageInfo[0] = totalCnt.ToString(); //显示记录集总数
            pageInfo[1] = pageCnt.ToString();   //显示总页数
            pageInfo[2] = abovePage;            //显示上一页
            pageInfo[3] = nextPage;             //显示下一页

int startIndex;
int endIndex;

            startIndex = (pageNo / 10) * 10 + 1;   //起始页
            if (pageNo % 10 == 0)
            {
                startIndex = startIndex - 10;
            }
            endIndex = startIndex + 9;      //结束页

            if (endIndex > pageCnt)
            {
                endIndex = pageCnt;

}//如果结束页大于总页数，则结束页为最后一页值

            int pageIndex = 4;
            for (int i = startIndex; i <= endIndex; i++)
            {

                string pageValue = pageNumLink + i.ToString() + "/">" + i.ToString() + "</a>";
                if (i == pageNo)
                {
                    pageValue = i.ToString();
                }
                pageInfo[pageIndex] = pageValue;
                pageIndex++;
            }

return dt;
}

本文来自CSDN博客，转载请标明出处：file:///C:/Documents%20and%20Settings/Administrator/桌面/新建文件夹/asp_net%20结合mysql存储过程进行分页代码%20-%20smallbelial的专栏%20-%20CSDN博客.htm