C#

ASP.Net Mysql分页存储过程

来自 |已发表

版权声明：转载原创文章请以超链接形式请注明原文章出处，尊重作者，尊重原创！


恰饭广告




实现效果：

实现效果

MySql分页存储过程： 

CREATE DEFINER=`bing`@`%` PROCEDURE `proc_FenYe`(IN `p_table_name` varchar(100),IN `p_order_string` varchar(100),IN `p_page_size` int,IN `p_page_now` int,IN `p_where_string` varchar(100),IN `p_fields` varchar(100),OUT `p_out_rows` int)
    SQL SECURITY INVOKER
BEGIN
	#Routine body goes here...
  /*定义变量*/
    DECLARE m_begin_row INT DEFAULT 0;
    DECLARE m_limit_string VARCHAR(128);
    /*构造语句*/
    SET m_begin_row = (p_page_now - 1) * p_page_size;
    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);
         SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string ,' ', p_order_string, m_limit_string );
    /*预处理*/
     PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
  SET p_out_rows = @ROWS_TOTAL;
    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
END

调用存储过程：

CALL proc_FenYe('表名','排序',每页显示条数,第几页,' 查询条件','字段',@abc);
SELECT @abc;

例如：

CALL proc_FenYe('Student','order by SId',2,1,'where SId=166122001','SId,SName',@abc);
SELECT @abc;

BLL层： 

        public object loadData(FenYeProc user)
        {
            return stuDAL.loadData(user);
        }
        #region 总条数
        public int Count()
        {
            int count = StudentDAL.count;
            return count;
        }
        #endregion
        public void UserUpdate(StudentInfo user)
        {
            stuDAL.UserUpdate(user);
        }
        public void Delete(StudentInfo user)
        {
            stuDAL.Delete(user);
        }
        public string MultiDel(StudentInfo user)
        {
            return stuDAL.MultiDel(user);
        }
        public int DelCount()
        {
            int i = stuDAL.DelCount;
            return i;
        }

DAL层： 

        MySqlHelper mySqlHelper = new MySqlHelper();
        StudentInfo stuInfo = new StudentInfo();
        public static int count;
        #region 分页
        public object loadData(FenYeProc user)
        {
            MySqlParameter[] paraValues = {
                new MySqlParameter("?p_table_name", MySqlDbType.VarChar, 100),
                    new MySqlParameter("?p_order_string", MySqlDbType.VarChar, 100),
                    new MySqlParameter("?p_page_size", MySqlDbType.Int32),
                    new MySqlParameter("?p_page_now", MySqlDbType.Int32),
                    new MySqlParameter("?p_where_string", MySqlDbType.VarChar,100),
                    new MySqlParameter("?p_fields", MySqlDbType.VarChar,100),
                    new MySqlParameter("?p_out_rows", MySqlDbType.Int32),
            };
            paraValues[0].Value = user.tableName;
            paraValues[1].Value = user.orderby;
            paraValues[2].Value = user.PageSize;
            paraValues[3].Value = user.PageNum;
            paraValues[4].Value = user.Where;
            paraValues[5].Value = user.Column;
            paraValues[6].Direction = ParameterDirection.Output;
            object obj = mySqlHelper.ExecuteProcTable("proc_FenYe", paraValues);
            count = Convert.ToInt32(paraValues[6].Value);
            return obj;
        }
        #endregion
        #region 更新
        public void UserUpdate(StudentInfo user)
        {
            mySqlHelper.ExecuteNonQuery(@"UPDATE Student SET SName=@name,SSex=@sex,SAge=@age,SCredit=@credit,SPhone=@tel where SId=@id",
                 new MySqlParameter("@id", user.id),
                new MySqlParameter("@name", user.name),
                new MySqlParameter("@sex", user.sex),
                new MySqlParameter("@age", user.age),
                new MySqlParameter("@credit", user.credit),
                new MySqlParameter("@tel", user.tel));
        }
        #endregion
        #region 删除
        public void Delete(StudentInfo user)
        {
            mySqlHelper.ExecuteNonQuery("delete from Student where SId=@id",
                new MySqlParameter("id", user.id));
        }
        #endregion
        #region 删除选中
        public int DelCount;
        public string MultiDel(StudentInfo user)
        {
            string s = mySqlHelper.ExecuteSqlNonQuery("DELETE FROM Student where SId in " + user.delId).ToString();
            DelCount = Convert.ToInt32(s);
            return s;
        }
        #endregion

Model层：

StudentInfo.cs 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SelectCourse.Model
{
    public class StudentInfo
    {
        public int id { get; set; }
        public string password { get; set; }
        public string name { get; set; }
        public string sex { get; set; }
        public int age { get; set; }
        public string credit { get; set; }
        public string tel { get; set; }
        public string picurl { get; set; }
        public string delId { get; set; }
    }
}

FenYeProc.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SelectCourse.Model
{
    public class FenYeProc
    {
        public string tableName { get; set; }
        public string orderby { get; set; }
        public int PageSize { get; set; }
        public int PageNum { get; set; }
        public string Where { get; set; }
        public string Column { get; set; }
    }
}

UI层：

Student.aspx 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Student.aspx.cs" Inherits="SelectCourse.Student" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
    <script type="text/javascript">
        　　function integer() {
            　　　　var num = document.getElementById("txtGo").value;
            　　　　if (num == "") {
                　　　　　　alert('请输入内容');
                　　　　　　return false;
            　　　　}
            　　　　if (!(/(^[1-9]\d*$)/.test(num))) {
                　　　　　　alert('输入的不是正整数');
                　　　　　　return false;
            } else {
                return true;
            　　　　}
        　　}
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="btnCheck" runat="server" OnClick="btnCheck_Click" Text="全选" />
            <asp:Button ID="btnMultiDel" runat="server" OnClick="btnMultiDel_Click" Text="删除选中" />
            <asp:DropDownList ID="DropDownList2" runat="server">
                <asp:ListItem Value="all">全部</asp:ListItem>
                <asp:ListItem Value="id">按学号查询</asp:ListItem>
                <asp:ListItem Value="name">按姓名查询</asp:ListItem>
            </asp:DropDownList>
            <asp:TextBox ID="txtQuery" runat="server"></asp:TextBox>
            <asp:Button ID="btnQuery" runat="server" Text="查询" OnClick="btnQuery_Click" />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"  OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowDeleting="GridView1_RowDeleting" DataKeyNames="SId">
                <Columns>
                    <asp:TemplateField HeaderText="选择">
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox1" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="学号">
                        <EditItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Eval("SId") %>'></asp:Label>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("SId") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="姓名">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem,"SName") %>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("SName") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="性别">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem,"SSex") %>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtSex" runat="server" Text='<%# Eval("SSex") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="年龄">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem,"SAge") %>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtAge" runat="server" Text='<%# Eval("SAge") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="学分">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem,"SCredit") %>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtCredit" runat="server" Text='<%# Eval("SCredit") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="联系电话">
                        <ItemTemplate>
                            <%# DataBinder.Eval(Container.DataItem,"SPhone") %>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtTel" runat="server" Text='<%# Eval("SPhone") %>'></asp:TextBox>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="操作">
                        <EditItemTemplate>
                            <asp:Button ID="btnUpdate" runat="server" Text="更新" CommandName="Update" />
                            <asp:Button ID="btnCancel" runat="server" Text="取消" CommandName="Cancel" />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Button ID="btnEdit" runat="server" Text="编辑" CommandName="Edit" />
                            <asp:Button ID="Button2" runat="server" Text="删除" CommandName="Delete" />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
        <asp:Button ID="btnFirst" runat="server" OnClick="btnFirst_Click" Text="首页" />
        <asp:Button ID="btnPre" runat="server" OnClick="btnPre_Click" Text="上一页" />
        <asp:Button ID="btnNext" runat="server" OnClick="btnNext_Click" Text="下一页" />
        <asp:Button ID="btnLast" runat="server" OnClick="btnLast_Click" Text="尾页" />
        <asp:TextBox ID="txtGo" runat="server" Width="81px"></asp:TextBox>
        <asp:Button ID="btnGo" runat="server" OnClick="btnGo_Click" Text="跳转到" />
        <asp:Label ID="Label3" runat="server" Text="每页显示条数："></asp:Label>
        <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem>5</asp:ListItem>
            <asp:ListItem>10</asp:ListItem>
            <asp:ListItem>20</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="btnOK" runat="server" OnClick="btnOK_Click" Text="确认" />
        <p>
            <asp:Label ID="pageNow" runat="server"></asp:Label>
            <asp:Label ID="pageCount" runat="server"></asp:Label>
            <asp:Label ID="pageAll" runat="server"></asp:Label>
        </p>
    </form>
</body>
</html>

Student.aspx.cs

using SelectCourse.BLL;
using SelectCourse.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace SelectCourse
{
    public partial class Student : System.Web.UI.Page
    {
        FenYeProc fenye = new FenYeProc();
        StudentInfo user = new StudentInfo();
        StudentBLL studentBLL = new StudentBLL();
        int count = 1;
        private void loadData()
        {
            string where = "";
            count = Convert.ToInt32(ViewState["count"]);
            fenye.tableName = "Student";
            fenye.orderby = "order by SId";
            fenye.PageSize = Convert.ToInt32(DropDownList1.SelectedItem.Text);
            fenye.PageNum = count;
            if (DropDownList2.SelectedItem.Value=="all")
            {
                where = "where 1=1";
            }
            if (DropDownList2.SelectedItem.Value == "id")
            {
                int id = Convert.ToInt32(txtQuery.Text);
                where = "where SId =" + id;
            }
            if (DropDownList2.SelectedItem.Value == "name")
            {
                string s = txtQuery.Text;
                where = "where SName = '" + s + "'";
            }
            fenye.Where = where;
            fenye.Column = "SId,SName,SSex,SAge,SCredit,SPhone";
            GridView1.DataSource = studentBLL.loadData(fenye);
            GridView1.DataBind();
        }
        #region 计算总页数
        protected int _Page()
        {
            int allPage = studentBLL.Count() / Convert.ToInt32(DropDownList1.SelectedItem.Text);
            int remainder = studentBLL.Count() % Convert.ToInt32(DropDownList1.SelectedItem.Text);
            if (remainder != 0)
            {
                allPage++;
            }
            return allPage;
        }
        #endregion
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["count"] = 1;
                loadData();
                int allPage = _Page();
                pageNow.Text = "当前页：" + count;
                pageCount.Text = "共有：" + studentBLL.Count()+"条数据";
                pageAll.Text = "总页数：" + allPage.ToString();
            }
        }
        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            GridView1.EditIndex = -1;
            loadData();
        }
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            loadData();
        }
        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            user.id = Convert.ToInt32(((Label)GridView1.Rows[e.RowIndex].FindControl("Label2")).Text);
            user.name = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtName")).Text;
            user.sex = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSex")).Text;
            user.age = Convert.ToInt32(((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAge")).Text);
            user.credit = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtCredit")).Text;
            user.tel = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtTel")).Text;
            studentBLL.UserUpdate(user);
            GridView1.EditIndex = -1;
            loadData();
        }
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            user.id = Convert.ToInt32(((Label)GridView1.Rows[e.RowIndex].FindControl("Label1")).Text);
            studentBLL.Delete(user);
            loadData();
        }
        protected void btnFirst_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            int allPage = _Page();
            pageNow.Text = "当前页：" + count;
            pageCount.Text = "共有：" + studentBLL.Count() + "条数据";
            pageAll.Text = "总页数：" + allPage.ToString();
        }
        protected void btnPre_Click(object sender, EventArgs e)
        {
            if (Convert.ToInt32(ViewState["count"]) > 1)
            {
                ViewState["count"] = Convert.ToInt32(ViewState["count"]) - 1;
                loadData();
                pageNow.Text = "当前页：" + count;
            }
        }
        protected void btnNext_Click(object sender, EventArgs e)
        {
            int allPage = _Page();
            if (Convert.ToInt32(ViewState["count"]) < allPage)
            {
                ViewState["count"] = Convert.ToInt32(ViewState["count"]) + 1;
                loadData();
                pageNow.Text = "当前页：" + count;
            }
        }
        protected void btnLast_Click(object sender, EventArgs e)
        {
            ViewState["count"] = _Page();
            loadData();
            pageNow.Text = "当前页：" + count;
        }
        protected void btnGo_Click(object sender, EventArgs e)
        {
            try
            {
                ViewState["count"] = Convert.ToInt32(txtGo.Text);
            }
            catch
            {
                return;
            }
            loadData();
            int allPage = _Page();
            if (Convert.ToInt32(ViewState["count"]) <= allPage)
            {
                pageNow.Text = "当前页：" + count;
            }
            else
            {
                btnFirst_Click(null,null);
                ClientScript.RegisterStartupScript(this.GetType(), "", "alert('超出范围');", true);
            }
        }
        protected void btnOK_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            int allPage = _Page();
            pageNow.Text = "当前页：" + count;
            pageCount.Text = "共有：" + studentBLL.Count() + "条数据";
            pageAll.Text = "总页数："+ allPage.ToString();
        }
        protected void btnCheck_Click(object sender, EventArgs e)
        {
            int rowCount = this.GridView1.Rows.Count;
            for (int i = 0; i < rowCount; i++)
            {
                GridViewRow row = GridView1.Rows[i];
                CheckBox cbCheck = row.FindControl("CheckBox1") as CheckBox;
                cbCheck.Checked = !cbCheck.Checked;
            }
        }
        protected void btnMultiDel_Click(object sender, EventArgs e)
        {
            string sqlText = "(";
            foreach (GridViewRow objGVR in this.GridView1.Rows)
            {
                //判断当前行是否为数据行;
                if (objGVR.RowType == DataControlRowType.DataRow)
                {
                    CheckBox objCB = objGVR.FindControl("CheckBox1") as CheckBox;
                    if (objCB.Checked)
                    {
                        //获取选中行的主键;
                        sqlText += this.GridView1.DataKeys[objGVR.RowIndex]["SId"].ToString() + ",";
                    }
                }
            }
            sqlText = sqlText.Substring(0, sqlText.Length - 1) + ")";
            if (sqlText == ")")
            {
                Response.Write("没有选中任何记录");
            }
            else
            {
                user.delId = sqlText;
                studentBLL.MultiDel(user);
                loadData();
                Response.Write("已删除" + studentBLL.DelCount() + "条记录");
            }
        }
        protected void btnQuery_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            int allPage = _Page();
            pageNow.Text = "当前页：" + count;
            pageCount.Text = "共有：" + studentBLL.Count() + "条数据";
            pageAll.Text = "总页数：" + allPage.ToString();
        }
    }
}

存储过程参考文档：https://www.cnblogs.com/joeylee/archive/2012/10/08/2715660.html

原文链接：https://www.idaobin.com/archives/1311.html

让我恰个饭吧.ヘ(￣ω￣ヘ)

支付宝 ——————- 微信
图片加载中图片加载中



恰饭广告


广告

你可能也喜欢

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

34 − = 27