.net调用mysql存储过程

MySqlConnection mySqlcon = new MySqlConnection(Common.AppSettings.GetAppSeting(“ERPDB”));
MySqlDataAdapter command = new MySqlDataAdapter();
DataSet ds = new DataSet();
command.SelectCommand = new MySqlCommand();
command.SelectCommand.Connection = mySqlcon;
command.SelectCommand.CommandText = “AreaList”;
command.SelectCommand.CommandType = CommandType.StoredProcedure;
MySqlParameter para = new MySqlParameter("?pid", MySqlDbType.VarChar, 10);//mysql的存储过程参数是以?打头的
para.Value = req.Pid;
command.SelectCommand.Parameters.Add(para);
command.Fill(ds);
var lstDataTable = ds.Tables.Cast().ToList();//DataSet转List
return new BaseResult() { Code = 0, Msg = “成功”, Data = ds };

CREATE DEFINER=fanliao@% PROCEDURE AreaList(IN pid int)
BEGIN
DECLARE flag INT DEFAULT -99;
CREATE TEMPORARY TABLE IF NOT EXISTS lsb_Qu
(
Id int(11) not null,
pid int,
code VARCHAR(50),
name VARCHAR(50),
name_en VARCHAR(50),
name_pinyin VARCHAR(50),
path VARCHAR(50),
level int,
remarks VARCHAR(100)
);
TRUNCATE TABLE lsb_Qu;
CREATE TEMPORARY TABLE IF NOT EXISTS lsb_Re(

	 Id                int(11) not null,
   pid               int,
   code              VARCHAR(50),
   name              VARCHAR(50),
   name_en           VARCHAR(50),
   name_pinyin       VARCHAR(50),
   path              VARCHAR(50),
   level             int,
   remarks           VARCHAR(100)
	);
  TRUNCATE TABLE lsb_Re;
	
	INSERT into lsb_Qu(
	id,
	pid,
	code,
	name,
	name_en,
	name_pinyin,
	path,
	level,
	remarks
	)SELECT 
	sys_area.id, 
	sys_area.pid,
	sys_area.code,
	sys_area.`name`,
	sys_area.name_en,
	sys_area.name_pinyin,
	sys_area.path,
	sys_area.`level`,
	sys_area.remarks
  FROM sys_area where sys_area.pid = `pid`;
	
	INSERT into lsb_Re(
	id,
	pid,
	code,
	name,
	name_en,
	name_pinyin,
	path,
	level,
	remarks
	)SELECT 
	sys_area.id, 
	sys_area.pid,
	sys_area.code,
	sys_area.`name`,
	sys_area.name_en,
	sys_area.name_pinyin,
	sys_area.path,
	sys_area.`level`,
	sys_area.remarks
  FROM sys_area where sys_area.pid = `pid`;
	
	select count(1) INTO flag
	FROM sys_area
	where sys_area.pid = `pid`;
	
	WHILE flag > 0 DO
   CREATE TEMPORARY TABLE if not EXISTS lsb_temp(
	 Id                int(11) not null,
   pid               int,
   code              VARCHAR(50),
   name              VARCHAR(50),
   name_en           VARCHAR(50),
   name_pinyin       VARCHAR(50),
   path              VARCHAR(50),
   level             int,
   remarks           VARCHAR(100)
	 );
	 truncate TABLE lsb_temp;
	 
	 
	 INSERT INTO lsb_temp(
	 Id, 
	 pid, 
	 code, 
	 name, 
	 name_en,
	 name_pinyin,
	 path,
	 level,
	 remarks)SELECT
	 sys_area.id,
	 sys_area.pid,
	 sys_area.`code`,
	 sys_area.`name`,
	 sys_area.name_en,
	 sys_area.name_pinyin,
	 sys_area.path,
	 sys_area.`level`,
	 sys_area.remarks
		FROM sys_area, lsb_QU
		where sys_area.pid = lsb_Qu.Id;
		
		DELETE from lsb_Qu;
		
		insert into lsb_Qu(
		Id, 
		pid, 
		code, 
		name, 
		name_en, 
		name_pinyin,
		path,
		level,
		remarks)SELECT
	 lsb_temp.id,
	 lsb_temp.pid,
	 lsb_temp.`code`,
	 lsb_temp.`name`,
	 lsb_temp.name_en,
	 lsb_temp.name_pinyin,
	 lsb_temp.path,
	  lsb_temp.`level`,lsb_temp.remarks
		FROM lsb_temp;
		
		
		insert into lsb_Re(
		Id, 
		pid, 
		code, 
		name, 
		name_en, 
		name_pinyin,
		path,level,
		remarks)SELECT
	 lsb_temp.id,
	 lsb_temp.pid,
	 lsb_temp.`code`,
	 lsb_temp.`name`,
	 lsb_temp.name_en,
	 lsb_temp.name_pinyin,
	 lsb_temp.path,
	  lsb_temp.`level`,
		lsb_temp.remarks
		FROM lsb_temp;
		
		Drop TABLE lsb_temp;
		
		select COUNT(1)into flag FROM sys_area, lsb_Qu where sys_area.pid = lsb_Qu.Id;

END WHILE;

select Id, pid, code, name, name_en, name_pinyin,path,level,remarks FROM lsb_Re;

END