ASP.NET2.0调用MySql的存储过程

MYSQL 从5.0开始便加入了存储过程,因为它出现的时间还不是很久,现在有很多都在问,MYSQL存储过程怎么建立,下我就我所理解的建了一个.在asp.net2.0下调用非常方法,跟SQL差不多,但也有少少差别,下面就是MYSQL一个分页的的存储过程;MYSQL的limite给我们带来很大的方便.不要写太多的代码!


DELIMITER $$;


 


DROP PROCEDURE IF EXISTS `mytest`.`MyPage`$$


 


CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPage`(


tableName varchar(100),


fieldsName VARCHAR(100),


pageIndex int,


pageSize int,


sortName VARCHAR(500),


strWhere varchar(500)


)


BEGIN


DECLARE fieldlist VARCHAR(200);


if fieldsName=”||fieldsName=null THEN


   set fieldlist=’*’;


else


   set fieldlist=fieldsName;


end if;


if strWhere=”||strWhere=null then


     if sortName=”||sortName=null then


         set @strSQL=concat(‘select ‘,fieldlist,’ from ‘ , tableName,’ limit ‘,(pageindex-1)*pageSize,’,’,pageSize);


     else


         set @strSQL=concat(‘select ‘,fieldlist,’ from ‘ , tableName,’ order by ‘,sortName,’ limit ‘,(pageindex-1)*pageSize,’,’,pageSize);


     end if;


else


    if sortName=”||sortName=null then


        set @strSQL=concat(‘select ‘,fieldlist,’ from ‘ , tableName,’ where ‘,strWhere,’ limit ‘,(pageindex-1)*pageSize,’,’,pageSize);


    else


        set @strSQL=concat(‘select ‘,fieldlist,’ from ‘ , tableName,’ where ‘,strWhere,’ order by ‘,sortName,’ limit ‘,(pageindex-1)*pageSize,’,’,pageSize);


    end if;


end if;


PREPARE stmt1 FROM @strSQL;


EXECUTE stmt1;


DEALLOCATE PREPARE stmt1;


END$$


 


DELIMITER ;$$


Asp.net2.0调用方法.下次给出来…….