Sample Code for ASP. NET2.0 to call the MySQL Stored Procedure

The following articles mainly use ASP. the detailed description of the actual operation code for NET2.0 to call the MySQL stored procedure. We all know that MySQL has been added to the stored procedure since 5.0. Since it has not been too long, there are many questions about how to create a MySQL stored procedure.

Next, I have created one as I understand. in asp. the call method in net2.0 is similar to that in SQL, but there is little difference. The following is a paging stored procedure in MySQL. MySQL limite brings us great convenience. do not write too much code!

 
 

  
  
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 call method. Next time ....... The above content is an introduction to the MySQL stored procedure called by ASP. NET2.0. I hope you will get some benefits.