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

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))

BEGIN

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

  prepare sqlstmt from @strSQL;

  execute sqlstmt;

  deallocate prepare sqlstmt;

END $$

 

DELIMITER ;

 

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();

    }