SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6493字,纯文字阅读大概需要10分钟。
内容图文
一.SqlServer 中
1. 创建表
create table testuser( id int, --primary key, names varchar(50), address varchar(50), paw varchar(50) )
2.创建存储过程
create proc testp12(@idint ,@namesvarchar(50),@addressvarchar(50),@pawvarchar(50)) asbegindeclare@maxidintif@idisnullor@id<5beginreturn-1; end--set IDENTITY_INSERT testuser oninsertinto testuser(id,names,address,paw)values(@id,@names,@address,@paw) --set IDENTITY_INSERT testuser offselect@maxid=(selectmax(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 = id; cmd.Parameters.Add(new SqlParameter("@names", SqlDbType.VarChar, 50)); cmd.Parameters["@names"].Value = names; cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50)); cmd.Parameters["@address"].Value = address; cmd.Parameters.Add(new SqlParameter("@paw", SqlDbType.VarChar, 50)); cmd.Parameters["@paw"].Value = pwd; cmd.Parameters.Add(new SqlParameter("@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) ) RETURNSINT label_pro: BEGINdeclare _maxid int; declare returnid int; if _id isnullor _id<5thenbeginset returnid=-1; RETURN returnid; leave label_pro; end ; endif; -- set IDENTITY_INSERT testuser oninsertinto testuser(id,`names`,address,paw)values(_id,_names,_address,_paw); -- set IDENTITY_INSERT testuser offset _maxid=(selectmax(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>publicvoid 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 = id; cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50)); cmd.Parameters["_names"].Value = names; cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50)); cmd.Parameters["_address"].Value = address; cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50)); cmd.Parameters["_paw"].Value = 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:BEGINdeclare _maxid int; -- declare returnid int;if _id isnullor _id<5thenbeginset returnid=-1; leave label_pro; end ; endif; -- set IDENTITY_INSERT testuser oninsertinto testuser(id,`names`,address,paw)values(_id,_names,_address,_paw); -- set IDENTITY_INSERT testuser offset _maxid=(selectmax(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>publicvoid 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 = id; cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50)); cmd.Parameters["_names"].Value = names; cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50)); cmd.Parameters["_address"].Value = address; cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50)); cmd.Parameters["_paw"].Value = 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(@selectionint) RETURNS@tableTABLE ( id char(4) primarykeynotnull, names nvarchar(4) null ) ASBEGINIF@selection=0INSERTINTO@tableSELECT id,names FROM testuser ELSEINSERTINTO@tableSELECT id,names FROM testuser ReturnEND
这些例子都是我实践得到了。
记录一点点,收货一点点。
原文:http://www.cnblogs.com/annabook/p/3835456.html
内容总结
以上是互联网集市为您收集整理的SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写全部内容,希望文章能够帮你解决SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。