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

 insert into T_Card(cardNumber ,balance ,type ,stuNumber ,status ,isChecked ) values(@cardNumber ,@balance, @type ,@stuNumber ,@status ,@isChecked )

 insert into 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(varchar,getdate(),120),CONVERT(varchar,GETDATE(),108),'未结账')

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

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

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