最近几天设计模式放了放,看了一套关于三层的视频,关于三层架构在VS中怎么实现登录,自己做了一下,下面是注意事项,以及步骤和代码。
第一步:
搭建环境
创建窗体
搭建三层:
新建解决方案
模板层 数据访问层 业务逻辑层
在解决方案中新建三个类库:编译成为dll文件
建立模板层:
模板层的作用:与数据库表字段一一对应的cs文件。
建立数据访问层:
注意事项:1、实际做工程中尽量用存储过程。
2、可以使用构造参数的方式。
3、拼接字符串---不可取
代码实现:
首先建立模板层:
模板层的主要作用是建立与数据库表字段一一对应的cs文件。
新建类m_userInfo
Public Class m_userInfo
Private _userID As Integer
Private _userName As String
Private _userPwd As String
Private _email As String
Private _qq As String
'''
''' 用户ID
'''
'''
'''
'''
Public Property userID() As Integer
Get
Return _userID
End Get
Set(ByVal value As Integer)
_userID = value
End Set
End Property
'''
''' 用户姓名
'''
'''
'''
'''
Public Property userName() As String
Get
Return _userName
End Get
Set(ByVal value As String)
_userName = value
End Set
End Property
'''
''' 用户密码
'''
'''
'''
'''
Public Property userPwd() As String
Get
Return _userPwd
End Get
Set(ByVal value As String)
_userPwd = value
End Set
End Property
'''
''' 用户Email
'''
'''
'''
'''
Public Property email() As String
Get
Return _email
End Get
Set(ByVal value As String)
_email = value
End Set
End Property
'''
''' 腾讯QQ
'''
'''
'''
'''
Public Property qq() As String
Get
Return _qq
End Get
Set(ByVal value As String)
_qq = value
End Set
End Property
End Class
数据访问层(DAL)
Imports System.Data.SqlClient '注意引用
Public Class da_userInfo
Private connStr As String = "server=127.0.0.1;database=dakui;uid = sa;pwd=123"
'''
''' 获得一个用户信息
'''
'''
用户ID
'''
一个用户对象
'''
2010-12-13 20:50 By Zsk
Public Function GetObject(ByVal userId As Integer) As Model.m_userInfo
Dim sql As String = "select userName,userPwd,from,email,qq userInfo where userId = @userId"
Dim conn As SqlConnection = New SqlConnection(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As New SqlParameter("@userId", SqlDbType.Int)
sqlParam.Value = userId
Dim sdr As SqlDataReader = Nothing
Dim modelUserInfo As New Model.m_userInfo
Try
sdr = cmd.ExecuteReader()
While sdr.Read
modelUserInfo.userID = userId
modelUserInfo.userName = sdr.GetString(0)
modelUserInfo.userPwd = sdr.GetString(1)
modelUserInfo.email = sdr.GetString(2)
modelUserInfo.qq = sdr.GetString(3)
End While
Return modelUserInfo
Catch ex As Exception
Return Nothing
Finally
If Not IsNothing(sdr) Then
sdr.Close()
sdr = Nothing
End If
If Not IsNothing(conn) Then
conn.Close()
conn = Nothing
End If
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Function
'''
''' 获得多有用户
'''
'''
所有用户的DataSet集合
'''
2010-12-13 20:45 By Zsk
Public Function SelectAll() As DataSet
Dim sql As String = "select * from userInfo"
Dim conn As SqlConnection = New SqlConnection(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As New DataSet
Try
conn.Open()
dap.Fill(ds)
Return ds
Catch ex As Exception
Return Nothing
Finally
If Not IsNothing(conn) Then
conn.Close()
conn = Nothing
End If
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Function
'''
''' 删除一条用户信息
'''
'''
一个用户对象
'''
是否删除成功
'''
2010-12-13 20:41 By Zsk
Public Function Del(ByVal modelUserInfo As Model.m_userInfo) As Boolean
Dim sql As String = "delete from userInfo where userId = @userId"
Dim conn As SqlConnection = New SqlConnection(connStr) '注意引用
Dim cmd As New SqlCommand(sql, conn)
Dim sqlParam As New SqlParameter("@userId", SqlDbType.Int)
sqlParam.Value = modelUserInfo.userID
Try
conn.Open()
Return cmd.ExecuteNonQuery() > 0
Catch ex As Exception
Return False
Finally
If Not IsNothing(conn) Then
conn.Close()
conn = Nothing
End If
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Function
'''
''' 更新一条用户信息
'''
'''
一个用户对象
'''
更新是否成功
'''
2010-12-13 20:40 By Zsk
Public Function Update(ByVal modelUserInfo As Model.m_userInfo) As Boolean
Dim sql As String = "update userInfo set userName = @userName,userPwd=@userPwd,email=@email,qq=@qq where userId=@userId"
Dim conn As SqlConnection = New SqlConnection(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As SqlParameter
sqlParam = New SqlParameter("@userName", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.userID
cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@userPwd", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.userPwd
cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@email", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.email
cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@qq", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.qq
cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@userId", SqlDbType.Int)
sqlParam.Value = modelUserInfo.userID
cmd.Parameters.Add(sqlParam)
Try
conn.Open()
Return cmd.ExecuteNonQuery() > 0
Catch ex As Exception
Return False
Finally
If Not IsNothing(conn) Then
conn.Close()
conn = Nothing
End If
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Function
'''
''' 插入一条注册信息
'''
'''
一个用户信息表的对象
'''
是否添加成功
'''
2010-12-13 20:40 By Zsk
Public Function Insert(ByVal modelUserInfo As Model.m_userInfo) As Boolean
Dim sql As String = "insert into userInfo(userName,userPwd,email,qq) values(@userName,@userPwd,@email,@qq)" '不能用拼接字符串的方式
Dim conn As SqlConnection = New SqlConnection(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As SqlParameter
sqlParam = New SqlParameter("@userName", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.userName
cmd.Parameters.Add(sqlParam)
'sqlParam = New SqlParameter("@userID", SqlDbType.VarChar)
'sqlParam.Value = modelUserInfo.userID
'cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@userPwd", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.userPwd
cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@email", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.email
cmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@qq", SqlDbType.VarChar)
sqlParam.Value = modelUserInfo.qq
cmd.Parameters.Add(sqlParam)
Try
conn.Open()
Return cmd.ExecuteNonQuery() > 0
Catch ex As Exception
Return False
Finally
If Not IsNothing(conn) Then
conn.Close()
conn = Nothing
End If
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Function
End Class
业务逻辑层(BLL)
主要是实现数据访问层中的方法,开始感觉有些多余,但是维护程序的时候一般数据访问层是不修改的,改主要是改业务逻辑层。
Public Class b_userInfo
Private accessUserInfo As New AccessDB.da_userInfo
'''
''' 获得一个用户信息
'''
'''
用户ID
'''
一个用户对象
'''
2010-12-13 21:00 By Zsk
Public Function GetObject(ByVal userId As Integer) As Model.m_userInfo
Return accessUserInfo.GetObject(userId)
End Function
'''
''' 获得多有用户
'''
'''
所有用户的DataSet集合
'''
2010-12-13 21:05 By Zsk
Public Function SelectAll() As DataSet
Return accessUserInfo.SelectAll()
End Function
'''
''' 删除一条用户信息
'''
'''
一个用户对象
'''
是否删除成功
'''
2010-12-13 21:10 By Zsk
Public Function Del(ByVal modelUserInfo As Model.m_userInfo) As Boolean
Return accessUserInfo.Update(modelUserInfo)
End Function
'''
''' 插入一条注册信息
'''
'''
一个用户信息表的对象
'''
是否添加成功
'''
2010-12-13 21:15 By Zsk
Public Function Insert(ByVal modelUserInfo As Model.m_userInfo) As Boolean
Return accessUserInfo.Insert(modelUserInfo)
End Function
'''
''' 更新一条用户信息
'''
'''
一个用户对象
'''
更新是否成功
'''
2010-12-13 21:20 By Zsk
Public Function Update(ByVal modelUserInfo As Model.m_userInfo) As Boolean
Return accessUserInfo.Update(modelUserInfo)
End Function
End Class
窗体内代码
Public Class frmExplame
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim modelUserInfo As New Model.m_userInfo
Dim bllUserInfo As New BLL.b_userInfo
modelUserInfo.userName = Me.txtUserName.Text
modelUserInfo.userPwd = Me.txtUserPwd.Text
modelUserInfo.email = Me.txtEmail.Text
modelUserInfo.qq = Me.txtQQ.Text
If bllUserInfo.Insert(modelUserInfo) Then
labelMsg.Text = "添加成功"
Else
labelMsg.Text = "添加失败"
End If
End Sub
End Class