学步园

     这是第三次做机房收费系统时的代码记录，DAL层配上SQLHelper后使得代码更加优化，下面记录机房收费系统的用户表（userinfo）DAL层的代码

    DAL层的代码如下：

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''  DalUserInfoSql.vb
''  Implementation of the Class DalUserInfoSql
''  Generated by Enterprise Architect
''  Created on:      06-五月-2011 15:39:54
''  Original author: 黄爱岗
'' 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''  Modification history:
'' 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit On
Option Strict On

Imports Entity
Imports System.Data.SqlClient
Imports DalInterface

'Namespace ComputerSys.Dal
''' <summary>
''' 用户信息
''' </summary>
Public Class DalUserInfoSql
    'Inherits ComputerSys.Dal.SQLHelper
    Implements DalInterface.IUserInfo

    ''' <summary>
    ''' 添加用户
    ''' </summary>
    ''' <param name="entityUserInfo">用户信息</param>
    Public Function AddUser(ByVal entityUserInfo As EntityUserInfo) As Boolean Implements IUserInfo.AddUser
        Dim SqlStr As String = "insert into UserInfo(UserID,UserPwd,UserLevel,UserRegister,UserName,UseState) values (@UserID,@UserPwd,@UserLevel,@UserRegister,@UserName,@UseState)"
        Dim Sqlparams(5) As SqlParameter   '定义数组
        Dim SqlHelper As New SQLHelper     '实例化SQLHelper

        '添加数据到数组
        Sqlparams(0) = New SqlParameter("@UserID", entityUserInfo.UserID)
        Sqlparams(1) = New SqlParameter("@UserPwd", entityUserInfo.UserPwd)
        Sqlparams(2) = New SqlParameter("@UserLevel", entityUserInfo.UserLevel)
        Sqlparams(3) = New SqlParameter("@UserRegister", entityUserInfo.UserRegister)
        Sqlparams(4) = New SqlParameter("@UserName", entityUserInfo.UserName)
        Sqlparams(5) = New SqlParameter("@UseState", entityUserInfo.UseState)

        '调用添加方法，返回Boolean值
        Return SqlHelper.ExecuteAddDeleteUpdate(SqlStr, Sqlparams)

    End Function

    ''' <summary>
    ''' 根据用户ID更新用户的使用状态
    ''' </summary>
    ''' <param name="entityUserInfo">用户信息</param>
    Public Function ModifyUserState(ByVal entityUserInfo As EntityUserInfo) As Boolean Implements IUserInfo.ModifyUserState
        Dim SqlStr As String = "update UserInfo set UseState=@UseState where UserID=@UserID"
        Dim Sqlparams(1) As SqlParameter   '定义数组
        Dim SqlHelper As New SQLHelper     '实例化SQLHelper

        '添加数据到数组
        Sqlparams(0) = New SqlParameter("@UserID", entityUserInfo.UserID)
        Sqlparams(1) = New SqlParameter("@UseState", entityUserInfo.UseState)

        '调用更新方法，返回Boolean值
        Return SqlHelper.ExecuteAddDeleteUpdate(SqlStr, Sqlparams)

    End Function

    ''' <summary>
    ''' 根据用户级别查询所有用户信息
    ''' </summary>
    ''' <param name="entityUserLevel">用户信息中的用户级别</param>
    Public Function QueryAllUser(ByVal entityUserLevel As EntityUserInfo) As DataSet Implements IUserInfo.QueryAllUser
        Dim SqlStr As String = "select * from UserInfo where UserLevel=@UserLevel"
        Dim Sqlparams(0) As SqlParameter  '定义数组
        Dim SqlHelper As New SQLHelper    '定义并实例化SQLHelper

        '添加数组元素
        Sqlparams(0) = New SqlParameter("@UserLevel", entityUserLevel.UserLevel)

        '调用查询方法，返回DataSet
        Return SqlHelper.ExecuteQuery(SqlStr, Sqlparams)

    End Function

    ''' <summary>
    ''' 根据用户ID获得用户信息
    ''' </summary>
    ''' <param name="entityUserInfo">用户信息中的用户ID</param>
    Public Function QueryByID(ByVal entityUserInfo As EntityUserInfo) As DataSet Implements IUserInfo.QueryByID
        Dim SqlStr As String = "select * from UserInfo where UserID=@UserID"
        Dim Sqlparams(0) As SqlParameter   '定义数组
        Dim SqlHelper As New SQLHelper    '定义并实例化SQLHelper

        '添加数组元素
        Sqlparams(0) = New SqlParameter("@UserID", entityUserInfo.UserID)

        '调用查询方法，返回DataSet
        Return SqlHelper.ExecuteQuery(SqlStr, Sqlparams)

    End Function

    ''' <summary>
    ''' 根据用户ID修改用户密码
    ''' </summary>
    ''' <param name="entityUserInfo">用户信息</param>
    Public Function ModifyUserPwd(ByVal entityUserInfo As Entity.EntityUserInfo) As Boolean Implements IUserInfo.ModifyUserPwd
        Dim SqlStr As String = "update UserInfo set UserPwd=@UserPwd where UserID=@UserID"
        Dim Sqlparams(1) As SqlParameter   '定义数组
        Dim SqlHelper As New SQLHelper     '实例化SQLHelper

        '添加数据到数组
        Sqlparams(0) = New SqlParameter("@UserID", entityUserInfo.UserID)
        Sqlparams(1) = New SqlParameter("@UserPwd", entityUserInfo.UserPwd)

        '调用更新方法，返回Boolean值
        Return SqlHelper.ExecuteAddDeleteUpdate(SqlStr, Sqlparams)

    End Function

End Class

---

     SQLHelper的代码如下：

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''  SQLHelper.vb
''  Implementation of the Class SQLHelper
''  Generated by Enterprise Architect
''  Created on:      06-五月-2011 15:40:07
''  Original author: 黄爱岗
'' 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''  Modification history:
'' 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit On
Option Strict On

Imports System.Data.SqlClient
Imports System.Reflection

'Namespace ComputerSys.Dal
''' <summary>
''' 提炼出查询时重复用的代码，供所有DAL层的模块调用
''' </summary>
Public Class SQLHelper

    ''' <summary>
    ''' 连接数据库的字符串
    ''' </summary>
    Private connStr As String = System.Configuration.ConfigurationSettings.AppSettings("ConnStr")
    Dim conn As SqlConnection = New SqlConnection(connStr)   '定义连接

    ' ''' <summary>
    ' ''' 连接数据库
    ' ''' </summary>
    ' ''' <param name="SqlStr"></param>
    'Public Function Connection(ByVal SqlStr As String) As Boolean
    '    Connection = False
    'End Function

    ''' <summary>
    ''' 执行增删改语句，返回boolean类型
    ''' </summary>
    ''' <param name="SqlStr"></param>
    Public Function ExecuteAddDeleteUpdate(ByVal SqlStr As String, ByVal SqlParameter As SqlParameter()) As Boolean
        Dim cmd As SqlCommand = New SqlCommand(SqlStr, conn)

        '循环添加sql语句
        For i = 0 To SqlParameter.Length - 1
            cmd.Parameters.Add(SqlParameter(i))
        Next
        conn.Open()  '打开连接
        Return cmd.ExecuteNonQuery() > 0
        conn.Close()   '关闭连接
        conn = Nothing  '清空
        cmd.Dispose()  '关闭Cmd
        cmd = Nothing  '清空

    End Function

    ''' <summary>
    ''' 执行查询语句，返回DataSet
    ''' </summary>
    ''' <param name="SqlStr">Sql语句</param>
    Public Function ExecuteQuery(ByVal SqlStr As String, ByVal SqlParameter As SqlParameter()) As DataSet
        Dim cmd As SqlCommand = New SqlCommand(SqlStr, conn)

        '循环添加Sql语句
        For i = 0 To SqlParameter.Length - 1
            cmd.Parameters.Add(SqlParameter(i))
        Next

        Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)    '定义DataAdapter
        Dim ds As New DataSet    '定义DataSet

        Try
            dap.Fill(ds)  '向DataSet添加数据
            Return ds     '返回DataSet
        Catch ex As Exception
            Return Nothing
        End Try

        conn = Nothing  '清空
        cmd.Dispose()   '关闭Cmd
        cmd = Nothing   '清空

    End Function

    ''' <summary>
    ''' 执行查询语句，返回DataSet
    ''' </summary>
    ''' <param name="SqlStr">Sql语句</param>
    Public Function ExecuteQuery(ByVal SqlStr As String) As DataSet
        Dim cmd As SqlCommand = New SqlCommand(SqlStr, conn)   '添加执行命令
        Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)    '定义DataAdapter
        Dim ds As New DataSet    '定义DataSet

        Try
            conn.Open()   '打开连接     (注意：这句可以不加)
            dap.Fill(ds)  '向DataSet添加数据
            Return ds     '返回DataSet
        Catch ex As Exception
            Return Nothing
        End Try

        conn.Close()    '关闭连接   (注意：这句可以不加)   
        conn = Nothing  '清空
        cmd.Dispose()   '关闭Cmd
        cmd = Nothing   '清空

    End Function
End Class

 

第二次的DAL层代码请点连接