首页 > 代码库 > SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写
SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写
一.SqlServer 中
1. 创建表
create table testuser( id int, --primary key, names varchar(50), address varchar(50), paw varchar(50))
2.创建存储过程
create proc testp12(@id int ,@names varchar(50),@address varchar(50),@paw varchar(50))as begin declare @maxid int if @id is null or @id<5 begin return -1; end --set IDENTITY_INSERT testuser on insert into testuser(id,names,address,paw)values(@id,@names,@address,@paw)--set IDENTITY_INSERT testuser off select @maxid=(select max(id) from testuser) return @maxidend
3.c#对应的代码处理
#region"SqlServer中存储过程Return返回值处理" /* public void getresult(int id, string names, string address, string pwd) { string str = " server=192.168.xxx;user id=xx;password=xxxxxxxx;database=xxxxx_xx;min pool size=4;max pool size=4;packet size=3072"; SqlConnection conn = new SqlConnection(str); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "testp12"; cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)); cmd.Parameters["@id"].Value = http://www.mamicode.com/id;"@names", SqlDbType.VarChar, 50)); cmd.Parameters["@names"].Value = http://www.mamicode.com/names;"@address", SqlDbType.VarChar, 50)); cmd.Parameters["@address"].Value = http://www.mamicode.com/address;"@paw", SqlDbType.VarChar, 50)); cmd.Parameters["@paw"].Value = http://www.mamicode.com/pwd;"@return", SqlDbType.Int)); cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue; cmd.Connection = conn; cmd.ExecuteNonQuery(); conn.Close(); Response.Write(cmd.Parameters["@return"].Value.ToString()); Response.Write("<br/>"); Response.Write("hello world"); }*/ #endregion
二.MySQL中
1.创建表
create table testuser( id int, names varchar(50), address varchar(50), paw varchar(50))
2.创建函数
create FUNCTION testp12(_id int , _names varchar(50), _address varchar(50), _paw varchar(50) )RETURNS INT label_pro: BEGIN declare _maxid int; declare returnid int; if _id is null or _id<5 then begin set returnid=-1; RETURN returnid; leave label_pro; end ;end if;-- set IDENTITY_INSERT testuser on insert into testuser(id,`names`,address,paw)values(_id,_names,_address,_paw);-- set IDENTITY_INSERT testuser off set _maxid=(select max(id) from testuser); set returnid=_maxid; RETURN returnid;end
3.C#代码实现----函数实现
#region"函数实现SqlServer中存储过程功能Return" /// <summary> /// 函数实现SqlServer中存储过程功能 /// </summary> /// <param name="id"></param> /// <param name="names"></param> /// <param name="address"></param> /// <param name="pwd"></param> public void getresult(int id, string names, string address, string pwd) { string str = "Server=192.168.xxxx;Database=xxxx_xxx; port=xxxx;user id=xxxxx;password= providerName=MySql.Data.MySqlClient"; MySqlConnection conn = new MySqlConnection(str); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "fnetravel2_db.testp12"; cmd.Parameters.Add(new MySqlParameter("_id", MySqlDbType.Int32)); cmd.Parameters["_id"].Value =http://www.mamicode.com/ id; cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50)); cmd.Parameters["_names"].Value =http://www.mamicode.com/ names; cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50)); cmd.Parameters["_address"].Value =http://www.mamicode.com/ address; cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50)); cmd.Parameters["_paw"].Value =http://www.mamicode.com/ pwd; // cmd.Parameters.Add(new MySqlParameter("returnid", MySqlDbType.Int32)); //cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("returnid", MySqlDbType.Int32, 11); //设置参数的类型为输出参数,默认情况下是输入, cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue; cmd.Connection = conn; cmd.ExecuteNonQuery(); string name = cmd.Parameters["returnid"].Value.ToString(); conn.Close(); Response.Write(name); Response.Write("<br/>"); Response.Write("hello world"); } #endregion
4.存储过程实现
create PROCEDURE testp122(in _id int ,in _names varchar(50),in _address varchar(50),in _paw varchar(50),out returnid int )label_pro:BEGIN declare _maxid int; -- declare returnid int; if _id is null or _id<5 then begin set returnid=-1; leave label_pro; end ;end if;-- set IDENTITY_INSERT testuser on insert into testuser(id,`names`,address,paw)values(_id,_names,_address,_paw);-- set IDENTITY_INSERT testuser off set _maxid=(select max(id) from testuser); set returnid=_maxid;end
5.C#代码实现----存储过程
#region"mysql存储过程实现SqlServer中存储过程功能Return" /// <summary> /// /// </summary> /// <param name="id"></param> /// <param name="names"></param> /// <param name="address"></param> /// <param name="pwd"></param> public void getresult(int id, string names, string address, string pwd) { string str = "Server=192.168.xxxxxx;Database=xxxxx_dxxxxxxb; port=xxxxx;user id=xxxxx;password= providerName=MySql.Data.MySqlClient"; MySqlConnection conn = new MySqlConnection(str); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "fnetravel2_db.testp122"; cmd.Parameters.Add(new MySqlParameter("_id", MySqlDbType.Int32)); cmd.Parameters["_id"].Value =http://www.mamicode.com/ id; cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50)); cmd.Parameters["_names"].Value =http://www.mamicode.com/ names; cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50)); cmd.Parameters["_address"].Value =http://www.mamicode.com/ address; cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50)); cmd.Parameters["_paw"].Value =http://www.mamicode.com/ pwd; // cmd.Parameters.Add(new MySqlParameter("returnid", MySqlDbType.Int32)); //cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("returnid", MySqlDbType.Int32, 11); //设置参数的类型为输出参数,默认情况下是输入, cmd.Parameters["returnid"].Direction = ParameterDirection.Output; cmd.Connection = conn; cmd.ExecuteNonQuery(); string name = cmd.Parameters["returnid"].Value.ToString(); conn.Close(); Response.Write(name); Response.Write("<br/>"); Response.Write("hello world"); } } #endregion
看到上面想到是不是SqlServer中也可以实现这个功能，但是测试发现SqlServer中函数不允许insert语句。但是如果函数返回表则是可以的。如这个函数：
CREATE FUNCTION func(@selection int)RETURNS @table TABLE(id char(4) primary key not null,names nvarchar(4) null)ASBEGINIF @selection = 0INSERT INTO @table SELECT id,names FROM testuserELSEINSERT INTO @table SELECT id,names FROM testuserReturnEND
这些例子都是我实践得到了。
记录一点点，收货一点点。
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。