七层登录——代码篇

使用VB.Net进行机房收费系统个人重构，谨以此记录成长的足迹。

U层：接收用户输入的信息，以及从D层返回的信息。

Imports Facade
Imports Entity

Public Class frmLogin
    Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        '输入框简单逻辑判断：
        If Trim(txtUserName.Text = "") Then
            MsgBox("请输入用户名！", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtUserName.Focus()
            Exit Sub
        End If
        If IsNumeric(txtUserName.Text) = False Then
            MsgBox("用户名请输入数字！", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtUserName.Text = ""
            txtUserName.Focus()
        End If
        If Trim(txtPassword.Text = "") Then
            MsgBox("请输入密码！", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPassword.Focus()
            Exit Sub
        End If

        Try
            '实例化所需的各层的类：
            Dim Facade As New Facade.LoginFacade
            Dim UserInfo As New Entity.UserEntity
            Dim strResult As Boolean

            '将用户输入的用户名和密码赋值给实体类对象
            UserInfo.UserID = txtUserName.Text.Trim
            UserInfo.LoginPassword = txtPassword.Text.Trim

            '为全局变量赋值：
            CommonVariable.UserID = txtUserName.Text.Trim
            CommonVariable.LoginPassword = txtPassword.Text.Trim

            Dim WorklogInfo As New Entity.WorklogEntity
            WorklogInfo.UserID = UserInfo.UserID
            WorklogInfo.LoginDate = DateTime.Now.ToString("yyyy-MM-dd")
            WorklogInfo.LoginTime = DateTime.Now.ToString("HH:mm:ss")
            WorklogInfo.Computer = System.Net.Dns.GetHostName().ToString()
            WorklogInfo.Status = "值班"

            strResult = Facade.Checkuser(UserInfo)
            Dim table As DataTable
            table = Facade.Checkpwd(UserInfo)
            If strResult = True And Trim(txtPassword.Text) = Trim(table.Rows(0).Item(1)) Then
                frmMain.Show()
                Me.Hide()
                txtUserName.Text = ""
                txtPassword.Text = ""
                Facade.InsertWorklog(WorklogInfo)
            End If
        Catch ex As Exception
            MsgBox("用户不存在或密码不正确！")
            txtPassword.Text = ""
            txtUserName.Text = ""
            txtUserName.Select()
            txtUserName.Focus()
        End Try
    End Sub
End Class

Facade层：

Imports BLL
Imports Entity

Public Class LoginFacade
    Public Function Checkuser(ByVal UserInfo As Entity.UserEntity) As Boolean
        Dim IsUserExists As New BLL.LoginBLL
        Dim flag As Boolean
        flag = IsUserExists.CheckUser(UserInfo)
        If flag = True Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Checkpwd(ByVal UserInfo As Entity.UserEntity) As DataTable
        Dim IsPwdExists As New BLL.LoginBLL
        Dim table As DataTable
        table = IsPwdExists.CheckPassword(UserInfo)
        Return table
    End Function

    Public Function InsertWorklog(ByVal WorklogInfo As Entity.WorklogEntity) As Boolean
        Dim workLog As New BLL.InsertWorklogBLL
        Dim flag As Boolean

        flag = workLog.InsertWorklog(WorklogInfo)
        If flag = False Then
            Return False
        Else
            Return True
        End If
    End Function
End Class

BLL层：

Imports IDAL
Imports Entity
Imports Factory

Public Class LoginBLL
    Public Function CheckUser(ByVal UserInfo As Entity.UserEntity) As Boolean
        Dim Factory As New Factory.LoginFactory
        Dim IUser As IDAL.IUserInfoDAL

        '调用创建用户的工厂方法：
        IUser = Factory.CreateIUser()

        Dim table As New DataTable
        table = IUser.SelectUser(UserInfo)

        If table.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    Public Function CheckPassword(ByVal UserInfo As Entity.UserEntity) As DataTable
        Dim Factory As New Factory.LoginFactory
        Dim IUser As IDAL.IUserInfoDAL
        Dim table As DataTable
        IUser = Factory.CreateIUser()
        table = IUser.SelectUser(UserInfo)
        Return table
    End Function

    Public Function InsertWorklog(ByVal WorklogInfo As Entity.WorklogEntity) As Boolean
        Dim factory As New Factory.LoginFactory
        Dim Iworklog As IDAL.IUserInfoDAL
        Dim flag As Boolean

        Iworklog = factory.CreateIUser()
        flag = Iworklog.InsertWorklog(WorklogInfo)

        If flag = False Then
            Return False
        Else
            Return True
        End If
    End Function
End Class

IDAL层：

Imports Entity

Public Interface IUserInfoDAL
    '判断用户是否存在
    Function SelectUser(ByVal UserInfo As Entity.UserEntity) As DataTable
    Function InsertWorklog(ByVal WorklogInfo As Entity.WorklogEntity) As Boolean
End Interface

Factory层：

Imports System.Reflection
Imports System.Configuration
Imports System.Data
Imports IDAL

Public Class LoginFactory
    Dim strDB As String = System.Configuration.ConfigurationSettings.AppSettings("DBString")

    Public Function CreateIUser() As IUserInfoDAL
        Return CType(Assembly.Load("DAL").CreateInstance("DAL" & "." & strDB & "UserDAL"), IUserInfoDAL)
    End Function
End Class

DAL层：

Imports Entity
Imports IDAL
Imports SQLHelper
Imports System.Data.SqlClient

Public Class SQLserverUserDAL : Implements IDAL.IUserInfoDAL   '实现接口中的方法

    '声明实例化SqlHelper类
    Private sqlhelper As SQLHelper.SqlHelper = New SQLHelper.SqlHelper

    Public Function SelectUser(UserInfo As Entity.UserEntity) As DataTable Implements IUserInfoDAL.SelectUser
        Dim sql As String
        Dim table As DataTable  '中间变量用于存储从数据库中查找信息
        '声明并实例化参数数组：
        Dim sqlParams As SqlParameter() = {New SqlParameter("@UserID", UserInfo.UserID),
                                            New SqlParameter("@LoginPassword", UserInfo.LoginPassword)}
        sql = "select * from User_Info where userID=@UserID and loginPassword=@LoginPassword"
        table = sqlhelper.ExecSelect(sql, CommandType.Text, sqlParams)
        Return table
    End Function

    Public Function InsertWorklog(WorklogInfo As WorklogEntity) As Boolean Implements IUserInfoDAL.InsertWorklog
        Dim sql As String
        sql = "INSERT INTO Worklog_Info (userID,loginDate,loginTime,computer,status) VALUES (@UserID,@LoginDate,@LoginTime,@Computer,@Status)"
        Dim SqlParams As SqlParameter() = {New SqlParameter("@UserID", WorklogInfo.UserID),
                                           New SqlParameter("@LoginDate", WorklogInfo.LoginDate),
                                           New SqlParameter("@LoginTime", WorklogInfo.LoginTime),
                                           New SqlParameter("@Computer", WorklogInfo.Computer),
                                           New SqlParameter("@Status", WorklogInfo.Status)}
        Dim cmdType As CommandType = CommandType.Text
        Return sqlhelper.ExecAddDelUpdate(sql, cmdType, SqlParams)
    End Function
End Class

Entity层：

（1）用户实体：

Public Class UserEntity
    '数据库User_Info中对应字段：
    Private _userName As String
    Private _loginPassword As String
    Private _userID As String
    Private _userLevel As String
    Private _head As String

    '定义可读写的属性：
    Public Property UserName() As String
        Get
            Return _userName
        End Get
        Set(value As String)
            _userName = value
        End Set
    End Property

    Public Property LoginPassword() As String
        Get
            Return _loginPassword
        End Get
        Set(value As String)
            _loginPassword = value
        End Set
    End Property

    Public Property UserID() As String
        Get
            Return _userID
        End Get
        Set(value As String)
            _userID = value
        End Set
    End Property

    Public Property UserLevel() As String
        Get
            Return _userLevel
        End Get
        Set(value As String)
            _userLevel = value
        End Set
    End Property

    Public Property Head() As String
        Get
            Return _head
        End Get
        Set(value As String)
            _head = value
        End Set
    End Property

End Class

（2）工作记录实体：

Public Class WorklogEntity
    Private _userID As String
    Private _computer As String
    Private _status As String
    Private _loginDate As String
    Private _loginTime As String
    Private _logoutDate As String
    Private _logoutTime As String

    Public Property UserID() As String
        Get
            Return _userID
        End Get
        Set(value As String)
            _userID = value
        End Set
    End Property

    Public Property Status() As String
        Get
            Return _status
        End Get
        Set(value As String)
            _status = value
        End Set
    End Property

    Public Property Computer() As String
        Get
            Return _computer
        End Get
        Set(value As String)
            _computer = value
        End Set
    End Property

    Public Property LoginDate() As String
        Get
            Return _loginDate
        End Get
        Set(value As String)
            _loginDate = value
        End Set
    End Property

    Public Property LoginTime() As String
        Get
            Return _loginTime
        End Get
        Set(value As String)
            _loginTime = value
        End Set
    End Property

    Public Property LogoutDate() As String
        Get
            Return _logoutDate
        End Get
        Set(value As String)
            _logoutDate = value
        End Set
    End Property

    Public Property LogoutTime() As String
        Get
            Return _logoutTime
        End Get
        Set(value As String)
            _logoutTime = value
        End Set
    End Property
End Class

SQLHelper：

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class SqlHelper
    '数据库连接：
    Dim strConnction As String = "Server=DESKTOP-GUV4MCR;Database=ComputerCharge_sys;User ID =sa;Password=123456"
    Dim conn As New SqlConnection(strConnction)
    Dim cmd As New SqlCommand

    ''' <summary>
    ''' 执行查询操作（有参），返回值是datatable类型，参数不限
    ''' </summary>
    ''' <param name="cmdText">需要执行语句，一般是SQL语句，也有存储过程</param>
    ''' <param name="cmdType"></param>
    ''' <param name="paras">传入的参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
        Dim sqlAdapter As SqlDataAdapter ' 定义数据适配器
        Dim dt As New DataTable
        Dim ds As New DataSet

        '给cmd赋值
        cmd.CommandText = cmdText   'cmdText为所要执行的sql语句
        cmd.CommandType = cmdType    '命令执行的类型
        cmd.Connection = conn
        cmd.Parameters.AddRange(paras)  '参数添加
        sqlAdapter = New SqlDataAdapter(cmd)  '实例化Adapter

        Try
            sqlAdapter.Fill(ds)  '用Adapter将dataset填充
            dt = ds.Tables(0)    'datatable为dataset的第一个表
            cmd.Parameters.Clear()  '清除参数
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")  '如果出错，返回0
        Finally
            Call CloseCmd(cmd)  '销毁cmd命令
        End Try
        Return dt
    End Function

    ''' <summary>
    ''' 执行查询操作，（无参），返回值为datatable类型
    ''' </summary>
    ''' <param name="cmdText">需要执行的语句，一般是SQL语句，也有存储过程</param>
    ''' <param name="cmdType">判断SQL语句的类型，一般都不是存储过程</param>
    ''' <returns>dataTable，查询到的表格</returns>
    ''' <remarks></remarks>
    Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
        Dim sqlAdapter As SqlDataAdapter
        Dim dt As New DataTable
        Dim ds As New DataSet
        '给cmd赋值
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn
        sqlAdapter = New SqlDataAdapter(cmd)  '实例化adapter
        Try
            sqlAdapter.Fill(ds) '用adapter将dataset填充
            dt = ds.Tables(0) 'datatable为dataset的第一个表
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作") '如果出错，返回0
        Finally
            Call CloseCmd(cmd)
        End Try
        Return dt
    End Function


    ''' <summary>
    ''' 执行增删改上操作，（有参）返回值为Boolean类型，确认是否执行成功
    ''' </summary>
    ''' <param name="cmdText">需要执行的语句，一般是SQL语句，也有存储过程</param>
    ''' <param name="cmdType"></param>
    ''' <param name="paras"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Boolean
        '将传入的值分别为cmd的属性赋值
        cmd.Parameters.AddRange(paras)   '将参数传入
        cmd.CommandType = cmdType        '设置一个值，解释cmdText
        cmd.Connection = conn           '设置连接，全局变量
        cmd.CommandText = cmdText        '设置查询的语句

        Try
            conn.Open() '打开连接
            Return cmd.ExecuteNonQuery() '执行增删改操作
            cmd.Parameters.Clear()   '清除参数
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")  '如果出错，返回0
            Return False
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
    End Function

    ''' <summary>
    ''' 执行增删改三个操作，（无参）返回值为boolean类型，确认是否执行成功
    ''' </summary>
    ''' <param name="cmdText">需要执行语句，一般是SQL语句，也有存储过程</param>
    ''' <param name="cmdType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Boolean
        '为要执行的命令cmd赋值
        cmd.CommandText = cmdText  '先是查询的SQL语句
        cmd.CommandType = cmdType   '设置SQL语句如何解释
        cmd.Connection = conn   '设置连接
        '执行操作
        Try
            conn.Open()
            Return cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作") '如果出错，返回0
            Return False
        Finally
            Call CloseConn(conn)
            Call CloseCmd(cmd)
        End Try
    End Function

    ''' <summary>
    ''' 关闭连接
    ''' </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>
    ''' 关闭命令
    ''' </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

另附配置文件：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <appSettings >   
    <add key="DBString"  value="SQLserver" />
  </appSettings>
  <connectionStrings>
    <add name="ConnString" connectionString="Server=DESKTOP-GUV4MCR;Database=ComputerCharge_sys;User ID=sa;Password=123456" />
    <add name="UI.My.MySettings.ComputerCharge_sysConnectionString"
      connectionString="Data Source=DESKTOP-GUV4MCR;Initial Catalog=ComputerCharge_sys;User ID=sa;Password=123456"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

感谢您的阅读~