存储过程:
获取范围内的数据
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 = ;
int pageCount = ;
int pageCountMod = ;
int doCount = ;
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,),
new MySqlParameter("@strWhere",MySqlDbType.VarChar,)};
para[].Value = "uinfo";
para[].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 == )
{
//pageCount++;
}
else
{
if (pageCountMod != )
{
pageCount++;
}
}
pageCountLab.Text = pageCount.ToString();
}
if (pageIndex == )
{
pageIndex++;
}
mydr.Close();
mycomm.Dispose();
mycomm = new MySqlCommand("GetRecordAsPage", myco);
mycomm.CommandType = CommandType.StoredProcedure;
para.Initialize();
para = new MySqlParameter[]{
new MySqlParameter("@tbName",MySqlDbType.VarChar,),
new MySqlParameter("@fldName",MySqlDbType.VarChar,),
new MySqlParameter("@strWhere",MySqlDbType.VarChar,),
new MySqlParameter("@pageIndex",MySqlDbType.Int32),
new MySqlParameter("@pageSize",MySqlDbType.Int32),
new MySqlParameter("@orderType",MySqlDbType.Int16),
new MySqlParameter("@sortName",MySqlDbType.VarChar,)};
para[].Value = "uinfo";
para[].Value = fldName;
para[].Value = strWhere;
para[].Value = pageIndex;
para[].Value = pageSize;
para[].Value = ;
para[].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[].DefaultView;
GridView1.DataSource = m_DataView;
GridView1.DataBind();
GridView2.DataSource = m_DataView;
GridView2.DataBind();
GridView3.DataSource = m_DataView;
GridView3.DataBind();
DropDownpage.Items.Clear();
if (pageCount != )
{
doCount = ;
while (doCount < pageCount)
{
doCount++;
DropDownpage.Items.Add(new ListItem(doCount.ToString()+"页", doCount.ToString()));
}
}
if (pageIndex == )
{
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 == || pageCount == )
{
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 != )
{
DropDownpage.SelectedItem.Selected = false;
DropDownpage.Items.FindByValue(pageIndex.ToString()).Selected = true;
}
myco.Close();
}
protected void Button_IndexFirst_Click(object sender, EventArgs e)
{
pageIndexLab.Text = "";
if (pageCountLab.Text.Equals(""))
{
pageIndexLab.Text = "";
}
get_data();
}
protected void Button_IndexPrevious_Click(object sender, EventArgs e)
{
int pageIndex = System.Convert.ToInt32(pageIndexLab.Text.ToString());
pageIndex--;
if (pageIndex < )
{
pageIndex = ;
}
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();
}