.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
)