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