0.简介
【定义】:存储过程(StoredProcedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的免费精选名字大全并给出参数(如果该存储过程带有参数)来执行它。
【优缺点】:存储过程优缺点都非常的明显!几乎每一篇讨论存储过程的文字,都是会说其他优点是balabala,缺点是balabala,然而最后作者的结论都是:“我不推荐使用存储过程”。
具体的存储过程的优缺点这里就不详述了!
公司旧项目使用存储过程实现业务逻辑,没办法只能研究了一下🙃!
闲言碎语不要讲,书归正传,下面就开始存储过程!
1.语法细节
变量与变量之间使用逗号隔开,语句结尾无标点符号
声明变量:declare@variate_namevariate_type
,例如声明并赋值:declare@namenvarchar(50)='shanzm'
变量赋值:set@variate_name=value
打印变量:print@variate_name
begin……end之间的SQL语句称之为一个代码块
可以使用if……else实现逻辑判断
创建存储过程:createprocedurepro_name
执行存储过程:executepro_name
输出参数:存储过程返回的是SQL语句查阅结果,在定义参数后,添加output,设置为一个输出参数(和C#中输出参数类似),相当于多了一个返回值!
创建存储过程的基本形式:
createprocedurepro_name_tableName @param1param1_type, @param2param2_type, as begin --sql语句 end go
2.示例1:模拟转账
①示例背景:使用存储过程,模拟在一张存款表中实现用户与用户之间的转账
②准备工作1:在数据库中创建表szmBank
CREATETABLE[dbo].[szmBank]( [Id][bigint]IDENTITY(1,1)NOTNULL, [Balance][decimal](18,0)NOTNULL
添加一些测试数据:
Id Balance
————— —————-
1 1000
2 2000
3 3000
③准备工作2:封装C#代码中的SQL辅助类SqlHelper
注意封装的时候要有一个CommandType参数,决定是执行SQL语句还是存储过程,
CommandType是一个枚举类型,其中Text值为执行SQL语句,StoreProcedure为执行存储过程
具体封装细节这里就不详述了。
找到了2年前我封装的一个SqlHelper.cs,常规使用没有任何问题,仅供参考:
#region //=============================================================================== //ProjectName: //ProjectDescription: //=============================================================================== //ClassName:SqlHelper //ClassVersion:v1.0.0.0 //ClassDescription:SQL语句辅助类 //CLR:4.0.30319.18408 //Author:shanzm //CreateTime:2018-8-1418:22:59 //UpdateTime:2018-8-1418:22:59 //=============================================================================== //Copyright©SHANZM-PC2018.Allrightsreserved. //=============================================================================== #endregion usingSystem.Configuration; usingSystem.Data; usingSystem.Data.SqlClient; namespace_16StoreProcedure { publicclassSqlHelper { privatestaticreadonlystringconnStr= ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; ///<summary> ///返回查询结果的的表 ///</summary> ///<paramname="sql">SQL语句或存储过程</param> ///<paramname="type">执行类型</param> ///<paramname="param">参数</param> ///<returns></returns> publicstaticDataTableGetDataTable(stringsql,CommandTypetype,paramsSqlParameter[]param) { using(SqlConnectionconn=newSqlConnection(connStr)) { using(SqlDataAdapteradapter=newSqlDataAdapter(sql,conn)) { if(param!=null) { adapter.SelectCommand.Parameters.AddRange(param); } adapter.SelectCommand.CommandType=type; DataTableda=newDataTable(); adapter.Fill(da); returnda; } } } ///<summary> ///返回影响行数 ///</summary> ///<paramname="sql">SQL语句或存储过程</param> ///<paramname="type">执行类型</param> ///<paramname="param">参数</param> ///<returns></returns> publicstaticintExecuteNonquery(stringsql,CommandTypetype,paramsSqlParameter[]param) { using(SqlConnectionconn=newSqlConnection(connStr)) { using(SqlCommandcmd=newSqlCommand(sql,conn)) { if(param!=null) { cmd.Parameters.AddRange(param); } cmd.CommandType=type; conn.Open(); returncmd.ExecuteNonQuery(); } } } ///<summary> ///返回查询结果的第一行第一个单元格的数据 ///</summary> ///<paramname="sql">SQL语句或存储过程</param> ///<paramname="type">执行类型</param> ///<paramname="param">参数</param> ///<returns></returns> publicstaticobjectExecuteScalar(stringsql,CommandTypetype,paramsSqlParameter[]param) { using(SqlConnectionconn=newSqlConnection(connStr)) { using(SqlCommandcmd=newSqlCommand(sql,conn)) { if(param!=null) { cmd.Parameters.AddRange(param); } cmd.CommandType=type; conn.Open(); returncmd.ExecuteScalar(); } } } } }
④编写存储过程:
在数据库中:指定数据库–>可编程性–>存储过程–>右键:新建–>存储过程:
SQLServer中编写的SQL语句没有默认的格式化,所有代码排版按照我自己习惯进行Tab缩进
建议放到编辑器中查看下面的存储过程,会好看一些!
SQL大小写不敏感,我习惯小写,方便阅读!
--============================================= --Author:shanzm --Createdate:2020年5月2日19:56:51 --Description:模拟账户之间转账 --============================================= createprocedurepro_transfer_szmbank @frombigint, @tobigint, @balancedecimal(18,0), @returnNumintoutput--(1表示转账成功,2表示失败,3表示余额不足) as begin --判断转出账户是否有足够的金额 declare@moneydecimal(18,0) select@money=Balancefromdbo.szmBankwhereId=@from; if@money-@balance>=0.1 --开始转账 begin begintransaction declare@sumint=0 --转出账户扣钱 updateszmBanksetbalance=balance-@balancewhereid=@from set@sum=@sum+@@error --转入账户加钱 updateszmBanksetbalance=balance+@balancewhereid=@to set@sum=@sum+@@error --判断是否成功 if@sum<>0 begin set@returnNum=2--转账失败 rollback end else begin set@returnNum=1--转账成功 commit end end else begin set@returnNum=3--余额不足 end end go
在数据库中执行测试(F5):
--执行测试: declare@retint executepro_transfer_szmbank @from='1', @to='2', @balance='10', @returnNum=@retoutput--注意输出参数在执行语句中也是要表明"output" print@ret--结果是打印:1,即存储过程实现成功
【注意】:
我们需要查看某个存储过程,则可以使用数据中自带的存储过程查看: sp_helptextpro_transfer_szmBank 修改现有的存储过程,右键存储过程–>修改:显示的存储过程只是把创建存储过程中的create变为了alert 可以在SQLServer的SQL窗口选中某些SQL语句,点击执行,即执行选中的SQL语句
⑤控制台中测试
新建一个控制台项目,在配置文件中添加连接字符串
因为封装的SqlHelper中需要从配置文件中读取数据库连接字符串,所以添加引用:System.Configuration
staticvoidMain(string[]args) { //转出账户的Id intfrom=1; //转入账户的Id intto=2; //转账金额 decimalbalance=10; SqlParameter[]param= { newSqlParameter("@from",from), newSqlParameter("@to",to), newSqlParameter("@balance",balance), //-------------------------------注意:这里设置为输出参数 newSqlParameter("@returnNum",System.Data.SqlDbType.Int{Direction=System.Data.ParameterDirection.Output} }; //------------------------设置CommonType为StorProcedure类型 SqlHelper.ExecuteNonquery("pro_transfer_szmbank",System.Data.CommandType.StoredProcedure,param); //------------------------获取输出参数 //根据输出参数判断转账结果 intoutPutparam=Convert.ToInt16(param[3].Value); switch(outPutparam) { case1:Console.WriteLine($"success:从Id:{from}转账{balance}元到Id:{to}");break; case2:Console.WriteLine("error");break; case3:Console.WriteLine("余额不足");break; } Console.ReadKey(); }
测试结果:
success:从Id:1转账10元到Id:2
3.示例2:测试返回DataTable
①存储过程:
createprocedure[dbo].[pro_ReturnDataTable] as begin selectIdas用户ID,Balanceas余额fromszmBank; end go
②数据库中测试:
executepro_ReturnDataTable
测试结果:即显示szmBank中的所有数据
③控制台中测试:
staticvoidMain(string[]args) { DataTabledt=SqlHelper.GetDataTable("pro_ReturnDataTable",CommandType.StoredProcedure); foreach(DataRowrowindt.Rows) { Console.WriteLine(row["用户ID"].ToString()+":"+row["余额"].ToString()); } Console.ReadKey(); //TransferAccounts(); ReturnDataTable(); }
测试结果:即打印szmBank中的所有数据
4.源代码下载
C#中使用存储过程-源代码下载
所需要的数据库表在示例中已说明,可以直接使用建表语句创建!
存储过程的SQL语句在示例中完整的展示了,可以直接复制!
上述就是C#学习教程:SQL Server存储过程在C#中调用的简单实现方法分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/cdevelopment/903900.html