mysql 分页proc

1>>>>>>>>.C#

            string tbName="uinfo"; //-- -- 表名
            string fldName="iduse,uname,email";//-- -- 表的列名
            string strWhere = ""; //-- -- 查询条件
            int pageIndex=1;//-- -- 第几页 传入1就是显示第一页
            int pageSize=5;//-- -- 一页显示几条记录
            int orderType=0; //-- --0是升序 非0是降序
            string sortName="id"; //-- -- 排序字段
            MySqlParameter[] parameters = {
                    new MySqlParameter("?tbName", tbName),
                    new MySqlParameter("?fldName", fldName),
                    new MySqlParameter("?strWhere", strWhere),
                    new MySqlParameter("?pageIndex", pageIndex),
                    new MySqlParameter("?pageSize", pageSize),
                    new MySqlParameter("?orderType", orderType),
                    new MySqlParameter("?sortName", sortName),
            };
            DataSet ds = MySqlHelper.ExecuteDataSet(MySqlHelper.DBConnectionString, CommandType.StoredProcedure, "GetRecordAsPage", parameters);

2>>>>>>>>Mysql

use test;




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 * from uinfo;


DELIMITER $$ 
DROP PROCEDURE IF EXISTS `test`.`GetRecordAsPage` $$
CREATE PROCEDURE `test`.`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  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 ;




SELECT
`id`
, `iduse`
, `uname`
, `email`
FROM
`test`.`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 GetRecordAsPage('uinfo','iduse,uname,email','',1,2,1,'id');


CALL GetRecordCount('uinfo','')