机房收费系统（VB.NET）——存储过程实战，收费系统vb.net
最初接触存储过程是在耿建玲老师的视频里，当初只是草草过了一遍，只是有了个印象，知道了这个名词；大二时也有SqlServer数据库这门课，不过老师没讲，自己也没看；真正对存储过程的了解来自于自学考试中的《数据库系统原理》，在考试中，知道存储过程是干嘛的，在纸上怎么写，但从来没有在DBMS中亲手敲过。于是机房收费系统给了我这个机会。
在这里不再过多叙述关于存储过程德基本知识，只写一下在机房收费系统这个小项目中是如何用到存储过程的。
背景：
机房收费系统中有一个注册的功能，原型图如下：
注册时，需要对数据库中的三个表进行更新（向卡表T_Card、学生表T_Student、充值表T_Register中分别新增一条记录），所以，执行时，如果用执行SQL语句的方式，那就需要执行三次SQL语句：
1：向卡表T_Card添加记录
insert into T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )
2：向学生表T_Student添加记录
insert into T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )
3：向充值表T_Register添加记录
insert into chargesystem.dbo.T_Recharge (userID,cardNumber,rechargeCash ,rechargeDate ,rechargeTime ,isChecked )values(@userID,@cardNumber ,@balance, CONVERT(varchar,getdate(),120),CONVERT(varchar,GETDATE(),108),'未结账')
存储过程的使用
对于上面的需求，如果用存储过程，在数据库里建立存储过程之后，在代码里只需负责直接执行这个存储过程即可，而不用连续多次连接、操作数据库。一、建立存储过程
建立存储过程有两种方法（因为系统用的数据库为SqlServer2008，所以这里以此为例）：（1）、手动建立存储过程：
对象资源管理器中：数据库→ChargeSystem(数据库名称)→可编程性→右键“存储过程”→新建存储过程
新建的存储过程可以说是一个已经成型的存储过程德模板，我们只需在上面修改一下存储过程名称、参数、执行语句等代码就OK了。
（2）、SQL语句添加存储过程
直接新建查询，在代码编辑窗口编写存储过程SQL代码，基本语法为：CREATE PROCEDURE PROC_NAME
@[参数名] [类型],@[参数名] [类型]……
AS
BEGIN
[过程体].........
END
用第一种方法建立的存储过程基本上也是这个结构，加入相应的参数和过程体之后，完整的存储过程为：
CREATE PROCEDURE PROC_Register
-- 定义参数
@cardNumber varchar(6),@balance decimal(5,1),@type nvarchar(20),@status nvarchar(50),@isChecked nvarchar(10),
@stuNumber varchar(18),@stuName nvarchar(10),@stuSex varchar(6),@stuMajor nvarchar(30),@stuGrade nvarchar(20),@stuClass nvarchar(20),@comment nvarchar(100),
@userID varchar(18)
AS
BEGIN
--向表中插入数据
insert into ChargeSystem .dbo.T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )
insert into ChargeSystem .dbo.T_Student (stuNumber,stuName,stuSex,stuMajor,stuGrade,stuClass ,comment ) values (@stuNumber ,@stuName ,@stuSex ,@stuMajor ,@stuGrade ,@stuClass,@comment )
insert into chargesystem.dbo.T_Recharge (userID,cardNumber,rechargeCash ,rechargeDate ,rechargeTime ,isChecked )values(@userID,@cardNumber ,@balance, CONVERT(char(10),getdate(),120),CONVERT(varchar,GETDATE(),108),'未结账')
END
注意：无论是用哪种方式建立的存储过程，都需要点击执行来存到服务器里，才可以通过以后的调用来执行，单纯地保存（ctrl+C）只是把这个存储过程文件保存在本地，而不会存入数据库服务器中。
当建立好存储过程时，可以新建查询执行“exec 存储过程名 @参数1=值1,@参数2=值2……”来验证存储过程是否正确。
二、代码中调用存储过程
调用存储过程跟执行SQL语句的方式大同小异，需要注意的是，执行SQL语句时，命令对象Command的CommandType的值为CommandType.Text，而执行存储过程时，CommandType的值为CommandType.StoredProcedure。在本例中具体代码为：
D层代码：
Public Class SqlServerRegisterDAL : Implements IDAL.IRegister
Public Function Insert(ByVal enCard As Entity.CardEntity, ByVal enStudent As Entity.StudentEntity, ByVal userID As String) As Boolean Implements IDAL.IRegister.Insert
Dim sqlHelper As New SqlHelper '定义SqlHelper实例
Dim cmdType As CommandType = CommandType.StoredProcedure '定义数据库命令类型
Dim cmdText As String = "PROC_Register" '数据库执行字符串
Dim parameters As SqlParameter() '定义参数数组，负责向存储过程中的变量传值
'为参数数组中的参数一一赋值
parameters = {New SqlParameter("@cardNumber", enCard.CardNumber), New SqlParameter("@balance", enCard.Balance),
New SqlParameter("@type ", enCard.CardType), New SqlParameter("@status", enCard.Status),
New SqlParameter("@isChecked", enCard.IsChecked), New SqlParameter("@stuNumber", enStudent.StuNumber),
New SqlParameter("@stuName", enStudent.StuName), New SqlParameter("@stuSex", enStudent.StuSex),
New SqlParameter("@stuMajor", enStudent.StuMajor), New SqlParameter("@stuGrade", enStudent.StuGrade),
New SqlParameter("@stuClass", enStudent.StuClass), New SqlParameter("@comment", enStudent.StuComment),
New SqlParameter("@userID", userID)}
'判断是否有查询结果
If sqlHelper.ExecuteNonQuery(cmdText, cmdType, parameters) Then
Return True
Else
Return False
End If
End Function
End Class
SqlHelper代码：
Public Class SqlHelper
'从配置文件中获取连接字符串的值
Dim strConnection As String = ConfigurationSettings.AppSettings("strConnection")
'创建数据库连接对象conn
Dim conn As SqlConnection
'创建数据库操作类cmd
Dim cmd As New SqlCommand
''' <summary>
''' 构造函数，实例化类时就初始化数据库连接对象
''' </summary>
''' <remarks></remarks>
Public Sub New()
conn = New SqlConnection(strConnection)
End Sub
''' <summary>
''' 关闭释放SqlCommand对象
''' </summary>
''' <param name="cmd">需要关闭的SqlCommand对象</param>
''' <remarks>cmd.Dispose()直接释放command资源，不知这么做对系统性能怎么样，先这么着，以后再继续优化</remarks>
Public Sub CloseCommand(ByVal cmd As SqlCommand)
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Sub
''' <summary>
''' 关闭数据库连接
''' </summary>
''' <param name="conn">需要关闭的SqlConnection对象</param>
''' <remarks>关闭数据库连接，但并没有释放，而是存储在连接池中，需要的时候还可以通过Open()方法打开连接</remarks>
Public Sub CloseConnection(ByVal conn As SqlConnection)
If Not IsNothing(conn) Then
conn.Close()
End If
End Sub
''' <summary>
''' 有参数的 增 删 改 操作
''' </summary>
''' <param name="cmdText">需要执行的SQL命令</param>
''' <param name="cmdType">所执行命令的，一般是sql语句、存储过程或表</param>
''' <param name="sqlParameters">参数数组</param>
''' <returns>返回受影响的行数 类型为整型</returns>
''' <remarks></remarks>
Public Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As String, ByVal sqlParameters As SqlParameter()) As Integer
Try
conn.Open() '打开数据库连接
cmd.CommandText = cmdText '设置查询语句
cmd.CommandType = cmdType '设置一个值，解释cmdType(如果值为StoredProcedure时，调用的是存储过程)
cmd.Connection = conn '设置连接
cmd.Parameters.AddRange(sqlParameters) '传入参数
Dim affectedRows As Integer
affectedRows = cmd.ExecuteNonQuery
Return affectedRows '返回执行所受影响行数
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OkOnly, "温馨提示")
Return 0 '如果出错，则返回0
Finally
cmd.Parameters.Clear() '清楚参数
Call CloseCommand(cmd) '关闭并释放Command
Call CloseConnection(conn) '关闭连接conn
End Try
End Function
End Class
为什么要使用存储过程
注册学生卡号在这个系统中不算个大模块，但这个小小的需求，需要与数据库中三个表的数据打交道，而在以往的操作，竟然连了三次数据库，执行了三次SQL语句。
这样频繁地打开与关闭与数据库的连接，需要消耗大量系统资源，降低执行速度。这时就需要考虑用存储过程来代替执行如此之多的SQL语句。
1、一般SQL语句每执行一次就需要编译一次，而存储过程只是在创造时进行编译，以后每次执行都不需要再进行编译。
2、存储过程就是相当于把多个需要执行的SQL语句集合起来，变成一条SQL语句，当然就只需连接和执行一次就可以得到结果。
3、安全性高。可以指定存储过程的使用权，防止SQL注入。
4、系统升级、维护比较方便。
总结：
★ 当涉及到多个SQL语句执行，需要多次连接数据库，或者需要对多张表进行处理时，可以将这些操作封装在一起，即创建存储过程，以后每次需要的时候直接调用执行，即可执行所有的操作，避免了多次打开、关闭数据库连接。
★ 当涉及到比较复杂的需求时（比如机房收费系统中的下机结算消费金额就可以采用存储过程），比如排序、计算等等，可以把数据直接传到存储过程,一系列操作在数据库服务器里进行，减小了客户端与服务器之间的数据流量，同时还保证了系统的安全性。
到了这里大家脑子里可能会有个问题：既然让一个存储过程执行多个任务，那么万一在执行的过程中，这些任务中的一个或者几个任务没有完成，该如何是好？这时事务就派上用场了…快去实践吧…
存储过程把一个或多个T-SQL语句组合到一个逻辑单元中，在SQL Server数据库中保存为一个对象。在存储过程创建后，它的T-SQL定义保存在sys.sql_module系统目录视图中。当存储过程首次执行的时候，SQL Server创建执行计划并把它存储在计划内存缓存中。然后SQL Server就能对这个存储过程的后续执行重用计划。和没有编译且没有准备好的等价即时查询相比，存储过程更加快速可靠。还可以创建使用.net公共语言运行时（CLR）程序集的存储过程。使用存储过程有很多好处，而没什么坏处：帮助在数据层聚集T-SQL代码。帮助大的即时查询减少网络流量。促进代码的可复用性。淡化数据获取的方法。与视图不同，存储过程可以利用刘控制技术、临时表、表变量等。存储过程对查询响应时间的影响比较稳定。存储过程能作为控制层，排除了直接访问SQL Server实例和它的数据库表可能引起的安全隐患。存储过程能用于多种不同的活动，包括简单的SELECT、INSERT、UPDATE、DELETE等。T-SQL活动能混合在单个存储过程中，或者以模块形式创建存储过程，为每一个或一组任务创建多个存储过程。没有参数的存储过程的基本语法如下：CREATE PROCEDURE [Schema_name.] procedure_nameAS { <sql_statement> [...n ] }带参数的存储过程可以接收外部传入参数多达2100个。语法如下：CREATE {PROC|PROCEDURE} [Schema_name.] procedure_name [; number][ { @parameter [ type_schema_name.] data_type } [VARYING ] [=default] [OUT | OUTPUT ] [READONLY] ] [ , ... n][ WITH <procedure_option> [ , ... n] ][FOR REPLICATION ]AS {sql_statement> [;] [ ...n] | <method_specifier> }
Dim cmd As New SqlCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "proc name" '参数名可以任意，但最好与存储过程的return一致 '默认值随便写 Dim ret As New SqlParameter("ret", 0) '返回值类型才有下面一句 ret.Direction = ParameterDirection.ReturnValue cmd.Parameters.Add(ret) '其他的参数和一般的sql语句没区别 '... cmd.ExecuteNonQuery() '得到了ret