机房重构——三层添加删除用户

  过了很长时间了，终于完成了另外的一条线，一条线还是犯了以前的老毛病，三层传递的类型不统一。不过还是先来介绍说一下我的这条线。

1，有了登录的例子，我一般形成了这个套路，开始先敲实体层。因为我使用的是和登录那条线的实体，本来是想贴出来的，后来想一想大家不会犯这种低级的错误就不贴出来了，因为我就是实体层出问题了所以链接数据库的时候总是链接不上，以至于我在三层中，敲了一遍用一遍，所以我估计会对实体层总结一遍。不过对大多数人老说很简单，就是因为简单而自信不会出问题而出问题了。这是通病。

2，然后开始D。所以我们在敲的时候可以考虑一下，我在D层的时候还是用着T_UserInfoDLL,和登陆使用的D

层是一样。感觉没有必要在重新在建一层了。

主要是用几个功能：

（1），通过用户级别读取表中具有相同级别的用户信息.

（2），添加新用户。

（3），删除选中的用户信息。

D层代码：（不知道为什么我的图片和代码一直不能正常插入。格式可能有点恶心。）

D层 ——————————————————————————————————————————————————
  '自定义连接字符串
    Dim coon As New SqlClient.SqlConnection("server=.;DataBase=Charge;;user id =sa;password=123456;")

    ''' <summary> 
    ''' 通过用户级别读取表中具有相同级别的用户信息
    ''' </summary>  
    '''  <param name="UserLevel">字符串</param>
    ''' <returns>返回dtQuery（表格）</returns> 
    ''' <remarks>同过结果的行数判断表是否为空</remarks>
    '''
    Public Function SelectUserAllInfo(ByRef UserLevel As String) As DataTable
        Dim cmdQuery As New SqlCommand
        Dim dtAdapter As New SqlDataAdapter
        Dim dstQuery As New DataSet
        Dim dtQuery As New DataTable
        Dim strSQL As String = "select *  from T_UserInfo where userLevel=@level"
        cmdQuery = New SqlCommand(strSQL, coon)
        cmdQuery.Parameters.Add(New SqlParameter("@level", UserLevel))

        Try
            dtAdapter.SelectCommand = cmdQuery
            dtAdapter.Fill(dstQuery, "T_UserInfo")
            dtQuery = dstQuery.Tables("T_UserInfo")
            '判断数据是否为空
            'Dim rowll As DataRow
            'Dim i As Integer
            'For i = 1 To 10000
            '    rowll = dtQuery.Rows(i)
            '    If (rowll Is Nothing) Then
            '        MsgBox("改时间段内无数据，请检查输入时间", vbOK + vbExclamation, "警告")
            '    End If
            'Next i
            If dtQuery.Rows.Count() = 0 Then
                MsgBox("数据库查询内容为空", vbOK + vbExclamation, "警告")

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            coon.Close()
        End Try
        Return dtQuery
    End Function

    ''' <summary> 
    ''' 添加新用户
    ''' </summary>  
    '''  <param name="AddUserInfo">实体类</param>
    ''' <returns>返回True or False</returns> 
    ''' <remarks>添加成功会有提示</remarks>

    Public Function InsertUserInfo(ByVal AddUserInfo As ChargeEntity.T_UserInfoEntity) As Boolean
        '定义一个SqlCommand命令
        Dim cmdInsert As New SqlCommand
        '定义连接字符串
        cmdInsert.Connection = coon
        '开始连接数据库
        cmdInsert.CommandText = "insert into T_UserInfo(userID,userName,userPassword,userLevel,operatorID) values (@UserID,@UserName,@UserPassword,@UserLevel,@OperatorID)"
        '获得从U层传入的参数
        'comm.Parameters.Add()添加参数到参数集,add里面的第一个参数是要添加的参数名,第二个参数是参数的数据类型
        'Parameters的作用就是把存储过程执行结束后得到的参数传到程序里()

        cmdInsert.Parameters.Add(New SqlParameter("@UserID", AddUserInfo.userId))           '输入参数
        cmdInsert.Parameters.Add(New SqlParameter("@UserName", AddUserInfo.userName))       '输入参数
        cmdInsert.Parameters.Add(New SqlParameter("@PassWord", AddUserInfo.userPassword))   '输入参数
        cmdInsert.Parameters.Add(New SqlParameter("@Level", AddUserInfo.userLevel))         '输入参数
        cmdInsert.Parameters.Add(New SqlParameter("@OperatorID", AddUserInfo.operatorId))   '输入参数
        '定义一个count用来存放返回受影响的行数，flag用户返回执行结果的Boolean值
        Dim count As Integer
        Dim flag As Boolean
        Try
            coon.Open()
            count = cmdInsert.ExecuteNonQuery() 'ExecuteNonQuery执行一个SqlCommand返回操作影响的行数，这个多半是判断操作是否成功的，例如插入删除和更新操作，如果影响行数为0的话，则证明操作是不成功的。大于0证明操作成功
            '判断数据插入是否正常
            If count = 0 Then
                flag = False
            Else
                flag = True
            End If
        Catch ex As Exception '异常捕获的，try里面有可能发生一些未处理的错误，如果发生，则跳到catch处进行处理，
            MsgBox(ex.Message) '这里是直接显示的错误描述。
        Finally
            coon.Close()
            cmdInsert = Nothing
        End Try
        Return flag
    End Function

    ''' <summary> 
    ''' 删除选中的用户信息
    ''' </summary>  
    '''  <param name="entityDelUser">shiti</param>
    ''' <returns>返回True or False</returns> 
    ''' <remarks>添加成功会有提示</remarks>

    Public Function DeleteSeleteUserInfo(ByVal entityDelUser As ChargeEntity.T_UserInfoEntity) As Boolean
        '定义cmd为SqlCommand
        Dim cmdDelete As SqlCommand = coon.CreateCommand()
        Dim flag As Boolean
        '定义删除语句
        cmdDelete.CommandText = "DELETE from T_UserInfo where  userId=@selectID"
        cmdDelete.Parameters.Add(New SqlParameter("@selectID", entityDelUser.userId))
        Try
            coon.Open()
            Dim count As Integer
            count = cmdDelete.ExecuteNonQuery()
            If count = 0 Then
                MsgBox("数据删除异常，请联系管理员！", vbOK + vbExclamation, "警告")
                flag = False
            Else
                flag = True
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            coon.Close()
            cmdDelete = Nothing
        End Try
        Return flag
    End Function

D层 ——————————————————————————————————————————————————

3之后开始是B层的代码，B层是业务逻辑层，怎样的一个业务逻辑层呢。就是业务处理，调用数据访问，事务等等的操纵。

主要功能是

（1）,通过用户级别读取表中具有相同级别的用户信息。

（2），添加新用户信息。

（3），删除选中的用户信息。

（4），删除用户。

B层 ——————————————————————————————————————————————————
'/*********************************************************************
'类 名 称：AddDeleteUserInfoBLL
'命名空间：BLL
'创建时间：2014年11月17日11:57:39
'作    者：童斯婧
'小    组：
'修改时间：
'修 改 人：
'版 本 号：v1.0.0
'*********************************************************************/

'添加删除用户，为UI准备
Public Class AddDeleteUserInfoBLL
    ''' <summary>
    ''' 通过用户级别读取表中具有相同级别的用户信息
    ''' </summary>
    ''' <param name=" UserLevel"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    '''
    Public Function SelectUserAllInfo(ByVal UserLevel As String) As DataTable
        Dim QueryInfo As New ChargeDAL.T_UserInfoDAL
        Dim dtQuery As DataTable = QueryInfo.SelectUserAllInfo(UserLevel)
        ' Dim dtQuery As DataTable = QueryInfo.SelectUser(UserLevel) 第一次我使用登录时候敲的SelectUsesr（）
        Return dtQuery

    End Function

    ''' <summary>
    ''' 添加新用户信息
    ''' </summary>
    ''' <param name=" entityAddDeleteUersInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    '''
    Public Function InsertNewUserInfo(ByVal entityAddDeleteUersInfo As ChargeEntity.T_UserInfoEntity)

        '从D层中获取数据
        Dim insertInfo As New ChargeDAL.T_UserInfoDAL
        Dim flag As Boolean = insertInfo.InsertUserInfo(entityAddDeleteUersInfo)

        '定义一个flagX存放返回信息
        Dim flagX As Boolean
        Try
            '判断数据库数据是否插入成功
            If flag = True Then
                MsgBox("用户信息添加成功！", , "提示")
                flagX = True
            Else
                MsgBox("用户信息添加失败，请重新添加", vbOK + vbExclamation, "警告")
                flagX = False
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return flagX    '函数返回值

    End Function

    ''' <summary> 
    ''' 删除选中的用户信息
    ''' </summary>  
    '''  <param name="entityDelUser">字符串</param>
    ''' <returns>返回True or False</returns> 
    ''' <remarks>添加成功会有提示</remarks>
    Public Function DeleteSelectUserInfo(ByVal entityDelUser As ChargeEntity.T_UserInfoEntity) As Boolean
        Dim deleteInfo As New ChargeDAL.T_UserInfoDAL
        Dim flag As Boolean = deleteInfo.DeleteSeleteUserInfo(entityDelUser)
        If flag = True Then
            Return True
        Else
            Return False
        End If
    End Function
    ''' <summary>
    ''' 删除用户
    ''' </summary>
    ''' <param name="entityDelUser"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteSeleteUserInfo(ByVal entityDelUser As ChargeEntity.T_UserInfoEntity) As Boolean
        '从D层中获取数据
        Dim deleteInfo As New ChargeDAL.T_UserInfoDAL
        Dim flag As Boolean = deleteInfo.DeleteSeleteUserInfo(entityDelUser)
        '判断flag存放返回信息
        If flag = True Then
            Return True
        Else
            Return False
        End If
    End Function

End Class

4.因为窗体提前设计后了，所以就可以直接到U层的代码了，显示层一般是在干什么的，是针对窗口功能设计的，引用了B层和实体层。看着窗体来设计代码。

  ' ''' <summary>
    ' ''' 单击空白，MDI窗口最小化
    ' ''' </summary>
    ' ''' <param name="sender"></param>
    ' ''' <param name="e"></param>
    ' ''' <remarks></remarks>
    ' '''
    'Private Sub frmAddDeleteUser_Deactivate(sender As Object, e As EventArgs) Handles Me.Deactivate '当一个对象不再是活动窗口时发生
    '    If Me.WindowState <> 1 Then
    '        Me.WindowState = 1 '最小化（最小化为一个图标）
    '    End If
    'End Sub

    ''' <summary>
    ''' 窗体加载
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    '''

    Private Sub frmAddDeleteUser_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cmbLevel.Items.Add("一般用户")
        cmbLevel.Items.Add("管理员")
        cmbLevel.Items.Add("操作员")
    End Sub

    Private Sub cmbLevel_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbLevel.SelectedIndexChanged
        '定义传入参数
        Dim UserLevel As String
        UserLevel = cmbLevel.Text.Trim()

        Try
            '通过B层获得返回值
            Dim queryInfo As New ChargeBLL.AddDeleteUserInfoBLL
            Dim dtQuery As DataTable = queryInfo.SelectUserAllInfo(UserLevel)
            '在DataGridView1中显示数据
            '显示查询数据
            DataGridView.AutoGenerateColumns = True '自动创建列
            If dtQuery.Rows.Count() = 0 Then
                DataGridView.DataSource = vbNull
            Else
                DataGridView.DataSource = dtQuery
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 添加
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
        frmAddUser.Show()
    End Sub

    ''' <summary>
    ''' 登录的用户名是否正确的全局变量
    ''' </summary>
    ''' <remarks></remarks>
    Public Class GlobalVariable
        '登录的用户名
        Public Shared user As String = frmUserLogin.txtUsername.Text.Trim
        'Public Shared level As String = frmLogin.userLevel
    End Class

    ''' <summary>
    ''' 自定义的删除Datagridview记录的方法
    ''' </summary>
    ''' <remarks></remarks>
    '''
    Public Sub DelUser()
        'Dim HangRow As Integer = DataGridView.SelectedRows.Count '获取用户选取行的集合
        Dim HangRow As New Integer
        Dim entityDelUser As New ChargeEntity.T_UserInfoEntity
        Dim bllDelUser As New ChargeBLL.AddDeleteUserInfoBLL

        HangRow = DataGridView.SelectedRows.Count

        If DataGridView.Rows.Count > 0 Then 'DataGridView控件中是否选中行
            '从下往上删
            For i As Integer = HangRow To 1 Step -1
                '获取第N行的第一列数据
                Dim user As String = DataGridView.SelectedRows(i - 1).Cells("userID").Value.ToString()
                '判断该用户是否为当前登录用户
                If GlobalVariable.user = user Then
                    MsgBox("该用户为当前登录用户，不可删除！", vbOKOnly + vbInformation, "提示")
                End If

                entityDelUser.userId = user     '将该用户名数据赋给实体entityDelUser
                entityDelUser.userLevel = cmbLevel.Text '获取当前删除行用户的级别

                If bllDelUser.DeleteSeleteUserInfo(entityDelUser) Then
                    MsgBox("成功删除用户", vbOKOnly + vbInformation, "提示")
                Else
                    MsgBox("删除失败！", vbOKOnly + vbInformation, "提示")

                End If
            Next
            '重新返回数据库信息
            Dim bllUpdateUser As New ChargeBLL.AddDeleteUserInfoBLL
            DataGridView.DataSource = bllUpdateUser.SelectUserAllInfo(entityDelUser.ToString)
        End If

    End Sub

    ''' <summary>
    ''' 删除
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        '判断是否选中行
        If DataGridView.SelectedRows.Count > 0 Then
            If MessageBox.Show("确定要删除用户么？删除后不可恢复！", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) = Windows.Forms.DialogResult.Yes Then
                DelUser()
            End If
        Else
            MessageBox.Show("请选择要删除的用户", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)

        End If
        '第二种方法()
        ''定义要传入的参数
        ''Dim selectUserID As String = Convert.ToString(DataGridView.CurrentRow.Cells(0).Value)         '选取某行特定单元格的值
        'Dim entityDelUser As New ChargeEntity.T_UserInfoEntity
        'entityDelUser = DataGridView.CurrentRow.Cells(0).Value '选取某行特定单元格的值
        'DataGridView.Rows.Remove(DataGridView.CurrentRow)
        'Try
        '    Dim deleteInfo As New ChargeBLL.AddDeleteUserInfoBLL
        '    Dim flag As Boolean = deleteInfo.DeleteSelectUserInfo(entityDelUser)
        '    'Dim flag As Boolean
        '    'flag = deleteInfo.DeleteSelectUserInfo(selectUserID)
        '    If flag = False Then
        '        Exit Sub
        '    End If
        'Catch ex As Exception
        '    MsgBox(ex.Message)
        'End Try
    End Sub

    ''' <summary>
    ''' 退出按钮
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    '''

    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        '显示主窗体，关闭本窗体
        frmMain.Show()
        Me.Close()
    End Sub

B层 ——————————————————————————————————————————————————

4.因为窗体提前设计后了，所以就可以直接到U层的代码了，显示层一般是在干什么的，是针对窗口功能设计的，引用了B层和实体层。看着窗体来设计代码。

 ' ''' <summary>
    ' ''' 单击空白，MDI窗口最小化
    ' ''' </summary>
    ' ''' <param name="sender"></param>
    ' ''' <param name="e"></param>
    ' ''' <remarks></remarks>
    ' '''
    'Private Sub frmAddDeleteUser_Deactivate(sender As Object, e As EventArgs) Handles Me.Deactivate '当一个对象不再是活动窗口时发生
    '    If Me.WindowState <> 1 Then
    '        Me.WindowState = 1 '最小化（最小化为一个图标）
    '    End If
    'End Sub

    ''' <summary>
    ''' 窗体加载
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    '''

    Private Sub frmAddDeleteUser_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cmbLevel.Items.Add("一般用户")
        cmbLevel.Items.Add("管理员")
        cmbLevel.Items.Add("操作员")
    End Sub

    Private Sub cmbLevel_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbLevel.SelectedIndexChanged
        '定义传入参数
        Dim UserLevel As String
        UserLevel = cmbLevel.Text.Trim()

        Try
            '通过B层获得返回值
            Dim queryInfo As New ChargeBLL.AddDeleteUserInfoBLL
            Dim dtQuery As DataTable = queryInfo.SelectUserAllInfo(UserLevel)
            '在DataGridView1中显示数据
            '显示查询数据
            DataGridView.AutoGenerateColumns = True '自动创建列
            If dtQuery.Rows.Count() = 0 Then
                DataGridView.DataSource = vbNull
            Else
                DataGridView.DataSource = dtQuery
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 添加
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
        frmAddUser.Show()
    End Sub

    ''' <summary>
    ''' 登录的用户名是否正确的全局变量
    ''' </summary>
    ''' <remarks></remarks>
    Public Class GlobalVariable
        '登录的用户名
        Public Shared user As String = frmUserLogin.txtUsername.Text.Trim
        'Public Shared level As String = frmLogin.userLevel
    End Class

    ''' <summary>
    ''' 自定义的删除Datagridview记录的方法
    ''' </summary>
    ''' <remarks></remarks>
    '''
    Public Sub DelUser()
        'Dim HangRow As Integer = DataGridView.SelectedRows.Count '获取用户选取行的集合
        Dim HangRow As New Integer
        Dim entityDelUser As New ChargeEntity.T_UserInfoEntity
        Dim bllDelUser As New ChargeBLL.AddDeleteUserInfoBLL

        HangRow = DataGridView.SelectedRows.Count

        If DataGridView.Rows.Count > 0 Then 'DataGridView控件中是否选中行
            '从下往上删
            For i As Integer = HangRow To 1 Step -1
                '获取第N行的第一列数据
                Dim user As String = DataGridView.SelectedRows(i - 1).Cells("userID").Value.ToString()
                '判断该用户是否为当前登录用户
                If GlobalVariable.user = user Then
                    MsgBox("该用户为当前登录用户，不可删除！", vbOKOnly + vbInformation, "提示")
                End If

                entityDelUser.userId = user     '将该用户名数据赋给实体entityDelUser
                entityDelUser.userLevel = cmbLevel.Text '获取当前删除行用户的级别

                If bllDelUser.DeleteSeleteUserInfo(entityDelUser) Then
                    MsgBox("成功删除用户", vbOKOnly + vbInformation, "提示")
                Else
                    MsgBox("删除失败！", vbOKOnly + vbInformation, "提示")

                End If
            Next
            '重新返回数据库信息
            Dim bllUpdateUser As New ChargeBLL.AddDeleteUserInfoBLL
            DataGridView.DataSource = bllUpdateUser.SelectUserAllInfo(entityDelUser.ToString)
        End If

    End Sub

    ''' <summary>
    ''' 删除
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        '判断是否选中行
        If DataGridView.SelectedRows.Count > 0 Then
            If MessageBox.Show("确定要删除用户么？删除后不可恢复！", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) = Windows.Forms.DialogResult.Yes Then
                DelUser()
            End If
        Else
            MessageBox.Show("请选择要删除的用户", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)

        End If
        '第二种方法()
        ''定义要传入的参数
        ''Dim selectUserID As String = Convert.ToString(DataGridView.CurrentRow.Cells(0).Value)         '选取某行特定单元格的值
        'Dim entityDelUser As New ChargeEntity.T_UserInfoEntity
        'entityDelUser = DataGridView.CurrentRow.Cells(0).Value '选取某行特定单元格的值
        'DataGridView.Rows.Remove(DataGridView.CurrentRow)
        'Try
        '    Dim deleteInfo As New ChargeBLL.AddDeleteUserInfoBLL
        '    Dim flag As Boolean = deleteInfo.DeleteSelectUserInfo(entityDelUser)
        '    'Dim flag As Boolean
        '    'flag = deleteInfo.DeleteSelectUserInfo(selectUserID)
        '    If flag = False Then
        '        Exit Sub
        '    End If
        'Catch ex As Exception
        '    MsgBox(ex.Message)
        'End Try
    End Sub

    ''' <summary>
    ''' 退出按钮
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    '''

    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        '显示主窗体，关闭本窗体
        frmMain.Show()
        Me.Close()
    End Sub
 U层——————————————————————————————————————————————————————————————————

小结：

经过这个功能的实现，我开始接触传递集合的方式，也比前一个登录功能完成的顺利。估计是sql语句比较单一吧，在B层的判断运用的不是很完善。