三层架构——存储过程（在ADO.net中调用存储过程）例子

 

.net版机房收费系统时，希望在各方面有一些革新，用一些新东西（当然，这也是这次的要求）.做学生注册时,一个学生注册后,需要更新三个表——StudentInfo表、CardInfo表、Recharge表

初步设计的表,随着系统的深入可能会有改动

 

CardInfo表
StuID	CardID	Cash	Status	IsCheck	Date	Time	UserName
学号	卡号	金额	使用状态	是否结账	注册日期	注册时间	办理人

 

StudentInfo表
StuID	StuName	Sex	Department	Grade	Class	Explian	UserName
学号	姓名	性别	系别	年级	班级	备注	办理人

 

Recharge表
CardID	ChargeCash	ChargeDate	ChargeTime	UserName	IsCheck
卡号	充值金额	充值日期	充值时间	办理人	是否结账

(注:注册时,要在学生信息表(StudentInfo)添加学生信息，在卡信息表(CardInfo)添加办理的卡的信息，注册办卡时，要充值，在充值信息表(Recharge)中添加充值记录)

 

        这样的话,一个注册功能需要三条sql语句,此时想到了存储过程.我的代码是这样的.

       以下代码不仅仅展示了存储过程的一个应用,更是一个三层架构的实际应用——例子

 

(初步代码)

存储过程:

Create procedure [dbo].[pro_StuRegister] 

@StuID varchar(20),@StuName varchar(20),@Sex varchar(20),@Department varchar(20),@Grade varchar(20),@Classes varchar(20),@Explian varchar(100),@CardID varchar(20),@Cash varchar(20),@Status varchar(20),@IsCheck varchar(20),@Date varchar(20),@Time varchar(20),
@ChargeCash numeric(10,2),@ChargeDate varchar(20),@ChargeTime varchar(20),@UserName varchar(20)

as
   insert into studentInfo(StuID,StuName,Sex,Department,Grade,Class,Explian) values(@StuID,@StuName,@Sex,@Department,@Grade,@Classes,@Explian)
   

   insert into CardInfo(StuID , CardID,Cash,Status ,IsCheck ,Date ,Time ) values(@StuID , @CardID ,@Cash,@Status ,@IsCheck ,@Date ,@Time) 
   

   insert into  Recharge(CardID,ChargeCash ,ChargeDate ,ChargeTime ,UserName ,Ischeck ) values(@CardID ,@ChargeCash ,@ChargeDate ,@ChargeTime ,@UserName ,@IsCheck )

go   

 

DAL层:

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Public Class student_DA
    ''' <summary>
    ''' 判断学号是否存在
    ''' </summary>
    ''' <param name="student"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsStuIDExist_DA(ByVal student As Model.student) As Boolean

        Dim sql As String = "select count(*) from studentInfo where StuID=@StuID"   'sql语句
        Dim parameter As SqlParameter = New SqlParameter("@StuID", student.StuID)  '设置参数
        Dim bool As Boolean                                            '定义Boolean类型变量，接收此方法返回值

        '调用SqlHelper类中的ExcuteScalar()方法，通过返回值设置bool的值
        If (CInt(New SqlHelper.SqlHelper().Executescalar(sql, CommandType.Text, parameter) > 0)) Then
            bool = True    '有记录，返回True
        Else
            bool = False     '无记录，返回False
        End If

        Return bool
    End Function
    ''' <summary>
    ''' 学生注册
    ''' </summary>
    ''' <param name="student"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function StuRegister_DA(ByVal student As Model.student, ByVal card As Model.card, ByVal recharge As Model.recharge) As Integer

        '设置参数数组
        Dim parameters() As SqlParameter =
               {
                New SqlParameter("@StuID", student.StuID),
                New SqlParameter("@StuName", student.StuName),
                New SqlParameter("@Sex", student.Sex),
                New SqlParameter("@Department", student.Department),
                New SqlParameter("@Grade", student.Grade),
                New SqlParameter("@Classes", student.Classes),
                New SqlParameter("@Explian", student.Explian),
                New SqlParameter("@CardID", card.CarID),
                New SqlParameter("@Cash", card.Cash),
                New SqlParameter("@Status", card.Status),
                New SqlParameter("@IsCheck", card.IsCheck),
                New SqlParameter("@Date", card.Dates),
                New SqlParameter("@Time", card.Time),
                New SqlParameter("@ChargeCash",recharge.ChargeCash ),
                New SqlParameter ("@ChargeDate",recharge .ChargeDate ),
                New SqlParameter ("@ChargeTime",recharge .ChargeTime ),
                New SqlParameter ("@UserName",recharge .UserName )
                }

        Dim result As Integer     '定义Integer型变量，接收SqlHelper类中的ExecuteNone()方法的返回值,同时作为此方法的返回值

        '将ExecuteNone()方法返回值赋给变量result
        result = New SqlHelper.SqlHelper().ExecuteNone("Pro_StuRegister", CommandType.StoredProcedure, parameters)

        Return result
       
    End Function
End Class

 

BLL层:

Public Class student_BLL
 ''' <summary>
    ''' 学生注册
    ''' </summary>
    ''' <param name="student"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function StuRegiste_BLL(ByVal student As Model.student, ByVal card As Model.card, ByVal recharge As Model.recharge) As Boolean

        Dim student_da As DAL.student_DA = New DAL.student_DA()    '实例化DAL层student_DA类
        Dim card_bll As BLL.card_BLL = New BLL.card_BLL()          '实例化BLL层card_BLL类
        Dim result As Integer                                    '定义Integer型变量，存放DAL层StuRegister_DAL()方法的返回值
        Dim bool As Boolean                                       '定义Boolean型变量，存放此方法的返回值

        '判断卡号是否存在
        If card_bll.IsCardIDExist(card) Then
            Throw New Exception("此卡号已存在！")
            Exit Function

        End If

        '判断学号是否存在
        If student_da.IsStuIDExist_DA(student) Then             '

            Throw New Exception("此学号已存在！")
            Exit Function

        End If

        '判断添加信息是否成功
        result = student_da.StuRegister_DA(student, card, recharge)    '接收DAL层StuRegister_DAL()方法的返回值（调用DAL层StuRegister_DA()方法，判断是否注册成功）

        If result > 0 Then
            bool = True          '学生注册（添加学生信息）成功，返回值为True
            'Else
            '    bool = False           '学生注册（添加学生信息）未成功，返回值为False
        Else
            Throw New Exception("注册失败！")
        End If

        Return bool          '返回Boolean值，True为注册成功，False为注册失败

    End Function
End Class

UI层:

Public Class FrmStuRegister
 '点击“注册”按钮
    Private Sub btStuRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btStuRegister.Click
        Dim student_bll As BLL.student_BLL = New BLL.student_BLL()   '实例化BLL层student_BLL
        Dim card_bll As BLL.card_BLL = New BLL.card_BLL()            '实例化BLL层card_BLL
        Dim student As Model.student = New Model.student()             '实例化实体类student
        Dim card As Model.card = New Model.card()
        Dim recharge As Model.recharge = New Model.recharge()

        student.StuID = txtStuID.Text.Trim()                          '给属性赋值
        student.StuName = txtStuName.Text.Trim()
        student.Sex = CmbSex.Text.Trim()
        student.Department = txtDepartment.Text.Trim()
        student.Grade = txtGrade.Text.Trim()
        student.Classes = txtClass.Text.Trim()
        student.Explian = lstExplian.Text

        card.StuID = student.StuID
        card.CarID = txtCardID.Text.Trim()
        card.Cash = txtChargeCash.Text.Trim()
        card.Status = "使用"
        card.IsCheck = "未结账"
        card.Dates = Now
        card.Time = Now

        recharge.CardID = card.CarID
        recharge.ChargeCash = card.Cash
        recharge.ChargeDate = card.Dates
        recharge.ChargeTime = card.Time

        '判断输入是否有空值
        Dim txt As Control     '定义类型为控件的变量
        For Each txt In Me.Controls   '遍历此界面中所有此类型控件

            '当控件类型为TextBox，并且有内容为空时，弹出提示框
            If (txt.GetType().Name = "TextBox" And txt.Text = "") Then         '我的这个方法有缺陷（要改正）
                MessageBox.Show("输入信息不能为空！请将信息输入完整！")

            End If
        Next

        '判断输入格式是否正确
        If Not IsNumeric(txtStuID.Text.Trim()) Then
            MessageBox.Show("学号请输入数字！")
            Exit Sub

        End If

        If Not IsNumeric(txtCardID.Text.Trim()) Then
            MessageBox.Show("卡号请输入数字！")
            Exit Sub

        End If

        If Not IsNumeric(txtGrade.Text.Trim()) Then
            MessageBox.Show("年级请输入数字!")
            Exit Sub

        End If

        If Not IsNumeric(txtClass.Text.Trim()) Then
            MessageBox.Show("班级请输入数字！")
            Exit Sub

        End If

        If Not CmbSex.Text = "女" Or CmbSex.Text = "男" Then
            MessageBox.Show("请正确输入性别！")
        End If

        '以上判断完成后，开始注册
        Try
            If student_bll.StuRegiste_BLL(student, card, recharge) Then
                MessageBox.Show("注册成功！")
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
End Class

 

SqlHelper类:

 
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration  '必须要在管理器中添加引用
 
Public Class SqlHelper
''' <summary>
    ''' 执行增删改三个操作,(无参)
    ''' </summary>
    ''' <param name="cmdText">需要执行语句——Sql语句、存储过程</param>
    ''' <param name="cmdType">判断Sql语句的类型</param>
    ''' <returns>Interger,受影响的行数</returns>
    ''' <remarks></remarks>
    Public Function ExecuteNone(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
        '为要执行的命令cmd赋值
        cmd.CommandText = cmdText       'sql语句
        cmd.CommandType = cmdType       '设置执行语句的类型（sql语句、存储过程？）
        cmd.Connection = conn           '设置连接

        '执行操作
        Try
            conn.Open()
            Return cmd.ExecuteNonQuery()
        Catch ex As Exception
            Return 0
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
    End Function
 
    ''' <summary>
    ''' 关闭connection连接
    ''' </summary>
    ''' <param name="conn">需要关闭的连接</param>
    ''' <remarks></remarks>
    Public Sub CloseConn(ByVal conn As SqlConnection)
        If (conn.State <> ConnectionState.Closed) Then  '如果没有关闭
            conn.Close()                    '关闭连接
            conn = Nothing                            
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' 关闭command命令
    ''' </summary>
    ''' <param name="cmd">需要关闭的命令</param>
    ''' <remarks></remarks>
    Public Sub CloseCmd(ByVal cmd As SqlCommand)
      If Not IsNothing(cmd) Then          '若cmd命令存在
            cmd.Dispose()                   '销毁
            cmd = Nothing  
        End If
    End Sub
End Class

(注:不要忘记在UI层添加引用System.configuration

)