C# MySql分页存储过程的应用

存储过程:

获取范围内的数据

 

DELIMITER $$ 


DROP PROCEDURE IF EXISTS `studb`.`GetRecordAsPage` $$


CREATE PROCEDURE `studb`.`GetRecordAsPage` (in tbName varchar(800),in fldName varchar(1000),


in strWhere varchar(500),in pageIndex int,in pageSize int,in orderType int,in sortName varchar(50))


BEGIN


declare startRow int;


declare sqlStr varchar(1000);


declare limitTemp varchar(1000);


declare orderTemp varchar(1000); 


set startRow = (pageIndex-1)*pageSize; 


set sqlStr = CONCAT('SELECT ',fldName,' from ',tbName);


set limitTemp = CONCAT(' limit ',startRow,',',pageSize);


set orderTemp = CONCAT(' order by ',sortName);


if orderType = 0 then


set orderTemp = CONCAT(orderTemp,' ASC ');


else


set orderTemp = CONCAT(orderTemp,' DESC ');


end if; 


set @sqlString = CONCAT(sqlStr,' ',strWhere,orderTemp,limitTemp); 


prepare sqlstmt from @sqlString;


execute sqlstmt;


deallocate prepare sqlstmt; 


END $$ 


DELIMITER ;

获取条件下的总记录数据

DELIMITER $$


DROP PROCEDURE IF EXISTS `GetRecordCount` $$


-- --CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRecordCount`(in tbName varchar(800),in strWhere varchar(500))

CREATE  PROCEDURE `GetRecordCount`(in tbName varchar(800),in strWhere varchar(500))


BEGIN


set @strSQL=concat('select count(*) as countStr from ',tbName,strWhere);


prepare sqlstmt from @strSQL;


execute sqlstmt;


deallocate prepare sqlstmt;


END $$


DELIMITER ;

测试

建表语句

CREATE TABLE `uinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`iduse` varchar(200) DEFAULT NULL COMMENT '用户编号',
`uname` varchar(200) DEFAULT NULL COMMENT '用户名称',
`email` varchar(200) DEFAULT NULL COMMENT 'Email',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

测试脚本

SELECT
`id`
, `iduse`
, `uname`
, `email`
FROM
`mostimpressive_dscj`.`uinfo`;

SET @tbName='uinfo'; -- -- 表名
SET @fldName='iduse,uname,email';-- -- 表的列名
SET @strWhere = ''; -- -- 查询条件
SET @pageIndex=1;-- -- 第几页 传入1就是显示第一页
SET @pageSize=5;-- -- 一页显示几条记录
SET @orderType=0; -- --0是升序 非0是降序
SET @sortName='id'; -- -- 排序字段

CALL Dscj_GetPaged('uinfo','iduse,uname,email','',1,1,1,'id')
-- --SELECT iduse,uname,email from uinfo order by id ASC limit 0,4

-- --传入表名，返回总的记录条数
CALL GetRecordCount('uinfo','')

C#程序

protected void get_data()
 
    {
 
        string strWhere = " where idmde='" + DropDownList1.SelectedValue + "' and idmke='" + DropDownList2.SelectedValue + "'";
 
        string fldName = "iduse,uname,email,euser,werks"; 

        int pageSize = 5;
 
        int pageCount = 1;
 
        int pageCountMod = 0;
 
        int doCount = 0;
 
        int pageIndex = System.Convert.ToInt32(pageIndexLab.Text.ToString());
 
 
 
        myco = new MySqlConnection(strconn);
 
        myco.Open();
 
        mycomm = new MySqlCommand("GetRecordCount", myco);
 
        mycomm.CommandType = CommandType.StoredProcedure;
 
 
 
        MySqlParameter[] para = new MySqlParameter[]{
 
            new MySqlParameter("@tbName",MySqlDbType.VarChar,20),
 
            new MySqlParameter("@strWhere",MySqlDbType.VarChar,500)};
 
        para[0].Value = "uinfo";
 
        para[1].Value = strWhere;
 
        mycomm.Parameters.AddRange(para);
 
 
 
        MySqlDataReader mydr=mycomm.ExecuteReader();
 
        while(mydr.Read()){
 
            pageCount = System.Convert.ToInt32(mydr["countStr"].ToString()) ;
 
            pageCountMod = pageCount % pageSize;
 
            pageCount= pageCount/ pageSize;
 
            if (pageCount == 0)
 
            {
 
                //pageCount++;
 
            }
 
            else
 
            {
 
                if (pageCountMod != 0)
 
                {
 
                    pageCount++;
 
                }
 
            }
 
            pageCountLab.Text = pageCount.ToString();
 
        }
 
 
 
        if (pageIndex == 0)
 
        {
 
            pageIndex++;
 
        }
 
       

        mydr.Close();
 
        mycomm.Dispose();
 
 
 
        mycomm = new MySqlCommand("GetRecordAsPage", myco);
 
        mycomm.CommandType = CommandType.StoredProcedure;
 
 
 
        para.Initialize();
 
        para = new MySqlParameter[]{
 
            new MySqlParameter("@tbName",MySqlDbType.VarChar,20),
 
            new MySqlParameter("@fldName",MySqlDbType.VarChar,1000),
 
            new MySqlParameter("@strWhere",MySqlDbType.VarChar,500),
 
            new MySqlParameter("@pageIndex",MySqlDbType.Int32),
 
            new MySqlParameter("@pageSize",MySqlDbType.Int32),
 
            new MySqlParameter("@orderType",MySqlDbType.Int16),
 
            new MySqlParameter("@sortName",MySqlDbType.VarChar,50)};
 
 
 
        para[0].Value = "uinfo";
 
        para[1].Value = fldName;
 
        para[2].Value = strWhere;
 
        para[3].Value = pageIndex;
 
        para[4].Value = pageSize;
 
        para[5].Value = 0;
 
        para[6].Value = "iduse";
 
 
 
        mycomm.Parameters.AddRange(para);
 
 
 
        MySqlDataAdapter myAdapter = new MySqlDataAdapter(mycomm);
 
       

 
 
        DataSet myDataSet = new DataSet();
 
        DataView m_DataView = new DataView();
 
 
 
        myAdapter.Fill(myDataSet);
 
        m_DataView = myDataSet.Tables[0].DefaultView;
 
 
 
        GridView1.DataSource = m_DataView;
 
        GridView1.DataBind();
 
 
 
        GridView2.DataSource = m_DataView;
 
        GridView2.DataBind();
 
 
 
        GridView3.DataSource = m_DataView;
 
        GridView3.DataBind();
 
 
 
        DropDownpage.Items.Clear();
 
 
 
        if (pageCount != 0)
 
        {
 
            doCount = 0;
 
            while (doCount < pageCount)
 
            {
 
                doCount++;
 
                DropDownpage.Items.Add(new ListItem(doCount.ToString()+"页", doCount.ToString()));
 
            }
 
        }
 
        if (pageIndex == 1)
 
        {           

            Button_IndexFirst.Enabled = false;
 
            Button_IndexPrevious.Enabled = false;
 
            Button_IndexNext.Enabled = true;
 
            Button_IndexLast.Enabled = true;
 
           

        }
 
        else if (pageIndex == pageCount)
 
        {
 
            Button_IndexFirst.Enabled = true;
 
            Button_IndexPrevious.Enabled = true;
 
            Button_IndexNext.Enabled = false;
 
            Button_IndexLast.Enabled = false;
 
           

        }
 
        else
 
        {
 
            Button_IndexFirst.Enabled = true;
 
            Button_IndexPrevious.Enabled = true;
 
            Button_IndexNext.Enabled = true;
 
            Button_IndexLast.Enabled = true;
 
           

        }
 
 
 
        if (pageCount == 0 || pageCount == 1)
 
        {
 
            pageIndex = pageCount;
 
            Button_IndexFirst.Enabled = false;
 
            Button_IndexPrevious.Enabled = false;
 
            Button_IndexNext.Enabled = false;
 
            Button_IndexLast.Enabled = false;
 
            DropDownpage.Enabled = false;
 
        }
 
        else
 
        {
 
            DropDownpage.Enabled = true;
 
        }
 
 
 
        pageIndexLab.Text = pageIndex.ToString();
 
 
 
        if (pageIndex != 0)
 
        {
 
            DropDownpage.SelectedItem.Selected = false;
 
            DropDownpage.Items.FindByValue(pageIndex.ToString()).Selected = true;
 
        }
 
       

 
 
        myco.Close();
 
    }
 
 
 
 
 
    protected void Button_IndexFirst_Click(object sender, EventArgs e)
 
    {
 
        pageIndexLab.Text = "1";
 
        if (pageCountLab.Text.Equals("0"))
 
        {
 
            pageIndexLab.Text = "0";
 
        }
 
        get_data();
 
    }
 
    protected void Button_IndexPrevious_Click(object sender, EventArgs e)
 
    {
 
        int pageIndex = System.Convert.ToInt32(pageIndexLab.Text.ToString());
 
        pageIndex--;
 
        if (pageIndex < 0)
 
        {
 
            pageIndex = 0;
 
        }
 
        pageIndexLab.Text = pageIndex.ToString();
 
        get_data();
 
    }
 
    protected void Button_IndexNext_Click(object sender, EventArgs e)
 
    {
 
        int pageIndex = System.Convert.ToInt32(pageIndexLab.Text.ToString());
 
        int pageCount = System.Convert.ToInt32(pageCountLab.Text.ToString());
 
        pageIndex++;
 
        if (pageIndex > pageCount)
 
        {
 
            pageIndex = pageCount;
 
        }
 
        pageIndexLab.Text = pageIndex.ToString();
 
        get_data();
 
    }
 
    protected void Button_IndexLast_Click(object sender, EventArgs e)
 
    {
 
        int pageIndex = System.Convert.ToInt32(pageIndexLab.Text.ToString());
 
        int pageCount = System.Convert.ToInt32(pageCountLab.Text.ToString());
 
        pageIndex = pageCount;
 
        pageIndexLab.Text = pageIndex.ToString();
 
        get_data();
 
    }
 
    protected void DropDownpage_SelectedIndexChanged(object sender, EventArgs e)
 
    {
 
        pageIndexLab.Text = DropDownpage.SelectedValue;
 
        get_data();
 
    }