机房收费系统(VB.NET)——存储过程实战

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/details/38170191

    最初接触存储过程是在耿建玲老师的视频里,当初只是草草过了一遍,只是有了个印象,知道了这个名词;大二时也有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语句执行,需要多次连接数据库,或者需要对多张表进行处理时,可以将这些操作封装在一起,即创建存储过程,以后每次需要的时候直接调用执行,即可执行所有的操作,避免了多次打开、关闭数据库连接。

       ★ 当涉及到比较复杂的需求时(比如机房收费系统中的下机结算消费金额就可以采用存储过程),比如排序、计算等等,可以把数据直接传到存储过程,一系列操作在数据库服务器里进行,减小了客户端与服务器之间的数据流量,同时还保证了系统的安全性。

       到了这里大家脑子里可能会有个问题:既然让一个存储过程执行多个任务,那么万一在执行的过程中,这些任务中的一个或者几个任务没有完成,该如何是好?这时事务就派上用场了…快去实践吧…

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏信安之路

HCTF2017的三个WriteUp

解决方法就是先 undefine 掉函数,再右键选择 Code,最后 Create function 就可以正常反编译了。

1090
来自专栏分布式系统进阶

ReplicaManager源码解析1-消息同步线程管理

基本上就是作三件事: 构造FetchRequest, 同步发送FetchRequest并接收FetchResponse, 处理FetchResponse, 这三...

1502
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第三十天 Activiti工作流【悟空教程】

工作流(Workflow),就是“业务过程的部分或整体在计算机应用环境下的自动化”,它主要解决的是“使在多个参与者之间按照某种预定义的规则传递文档、信息或任务的...

8143
来自专栏ml

------------数据库的加锁操作(上)

       从事一个项目,需要考虑数据的安全性,之前对于数据库这部分的数据操作学习的比较零散,由于手头的项目,于是系统的 学习了下数据库操作加锁的知识: --...

48810
来自专栏分布式系统进阶

Influxdb 数据写入流程

因此对写入请求的处理就在函数 func (h *Handler) serveWrite(w http.ResponseWriter, r *http.Reque...

1823
来自专栏Java学习网

Java开发之使用Java 8 Streams 对数据库进行 CRUD 操作

Speedment 是一个开放源代码的工具集,它可以被用来生成 Java 实体,并且能将我们同数据库的通信过程管理起来。你可以利用一个图形工具连接到数据库并生成...

1093
来自专栏程序员的SOD蜜

PDF.NET(PWMIS数据开发框架)之SQL-MAP目标和规范

SQL-MAP的目标: 集中管理SQL语句,所有SQL语句放在专门的配置文件中进行管理; 通过替换SQL配置文件,达到平滑切换数据库到另外一个数据库,比如从O...

2618
来自专栏技术小讲堂

Entity Framework4.3 Code-First基于代码的数据迁移讲解1.建立一个最初的模型和数据库   2.启动Migration(数据迁移)3.第一个数据迁移4.订制的数据迁移4.动态

前段时间一直在研究Entity Framework4,但是苦于没有找到我特别中意的教程,要么就是千篇一律的文章,而且写的特别简单,可以说,糟践了微软这么牛埃克斯...

3448
来自专栏技术小讲堂

ASP.NET AJAX(1)__Microsoft AJAX LibraryASP.NET AJAX(1)__Microsoft AJAX Library

ASP.NET AJAX(1)__Microsoft AJAX Library Microsoft AJAX Library为我们提供的客户端框架,提供的内容大...

2854
来自专栏岑玉海

hbase源码系列(六)HMaster启动过程

  这一章是server端开始的第一章,有兴趣的朋友先去看一下hbase的架构图,我专门从网上弄下来的。   按照HMaster的run方法的注释,我们可以了解...

5629

扫码关注云+社区

领取腾讯云代金券