C#调用MySQL存储过程
- 博客分类:
- C#.NET
- CREATE PROCEDURE `yuan_user_cc`(in xname varchar(100),in xemail varchar(100),in xpassword varchar(100),out xid integer)
- begin
- insert into yuan_user(name,email,password)values(xname,xemail,xpassword);
- select id into xid from yuan_user where name=xname;
- end
CREATE PROCEDURE `yuan_user_cc`(in xname varchar(100),in xemail varchar(100),in xpassword varchar(100),out xid integer)
begin
insert into yuan_user(name,email,password)values(xname,xemail,xpassword);
select id into xid from yuan_user where name=xname;
end
- private string shuju_lianjie(string name, string email, string password)
- {
- Int16 id;//返回的id值
- MySqlConnection mysqlcon;
- MySqlDataAdapter mysqldata = new MySqlDataAdapter();
- DataSet dataset = new DataSet();
- mysqlcon = new MySqlConnection("server=localhost; user id=root; password=123; database=yuan_test; pooling=false;charset=gb2312");
- mysqldata.SelectCommand = new MySqlCommand();
- mysqldata.SelectCommand.Connection = mysqlcon;
- mysqldata.SelectCommand.CommandText = "yuan_user_cc";
- mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
- //设置参数,添加到数据库
- MySqlParameter name_parameter = new MySqlParameter("?xname", MySqlDbType.VarChar,100);//mysql的存储过程参数是以?打头的!!!!
- name_parameter.Value = name;
- mysqldata.SelectCommand.Parameters.Add(name_parameter);
- //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- MySqlParameter email_parameter = new MySqlParameter("?xemail", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!!
- email_parameter.Value = email;
- mysqldata.SelectCommand.Parameters.Add(email_parameter);
- //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- MySqlParameter password_parameter = new MySqlParameter("?xpassword", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!!
- password_parameter.Value = password;
- mysqldata.SelectCommand.Parameters.Add(password_parameter);
- //++++++++++++++++++++++++++返回值++++++++++++++++++++++++++++++++++++++++++
- MySqlParameter id_parameter = new MySqlParameter("?xid", MySqlDbType.Int16,15);//mysql的存储过程参数是以?打头的!!!!
- id_parameter.Direction=ParameterDirection.Output;
- mysqldata.SelectCommand.Parameters.Add(id_parameter);
- //+++++++++++++++++++++++++打开数据库,插入数据,返回id值+++++++++++++++++++
- try
- {
- mysqldata.Fill(dataset, "yuan_user_cc");
- id = (Int16)id_parameter.Value;
- return id.ToString();
- }
- catch (MySqlException ex)
- {
- MessageBox.Show(ex.Message);
- return String.Empty;
- }
- }