.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 )
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>
- ''' <span style="color:#006600;">判断学号是否存在</span>
- ''' </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>
- ''' <span style="color:#006600;">学生注册
- </span> ''' </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类:
- <span style="font-size:18px;"> </span><p>Imports System
- Imports System.Collections.Generic
- Imports System.Linq
- Imports System.Text
- Imports System.Data
- Imports System.Data.SqlClient
- Imports System.Configuration '必须要在管理器中添加引用</p><p> </p><p>Public Class SqlHelper</p><p>''' <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</p><p> </p><p> ''' <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</p><p> End Sub</p><p>
- ''' <summary>
- '''
- ''' 关闭command命令
- ''' </summary>
- ''' <param name="cmd">需要关闭的命令</param>
- ''' <remarks></remarks>
- Public Sub CloseCmd(ByVal cmd As SqlCommand)</p><p> If Not IsNothing(cmd) Then '若cmd命令存在
- cmd.Dispose() '销毁
- cmd = Nothing
- End If
- End Sub</p><p>End Class
-
- </p>
(注:不要忘记在UI层添加引用System.configuration
)
|