机房收费——sqlhelper

  关于sqlHelper，这是一个很囧的事。敲完三层，我师父问我：sqlHelper用的怎么样，我说啊，什么，数据库没问题啊。

直到敲第二遍七层，我才知道，啊，原来我还不知道他是什么。

这个sqlHelper是一个直接封装在D层的类，目的嘛，就是少写代码。下面把它让你们看看吧。

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Public Class SqlHelper
    ‘获得数据库连接字符串
    Private ReadOnly strConn As String = ConfigurationManager.AppSettings("Connstr")
    ‘新建连接
    Dim connSQL As SqlConnection = New SqlConnection(strConn)
    ‘定义命令
    Dim cmdSQL As New SqlCommand
    ‘‘‘ <summary>
    ‘‘‘ 运行增删改，有參，确认是否运行成功
    ‘‘‘ </summary>
    ‘‘‘ <param name="cmdSQLText">须要运行的数据库语句</param>
    ‘‘‘ <param name="cmdSQLType">数据语句的类型，可能是sql类型，也可能是存储过程等</param>
    ‘‘‘ <param name="sqlParams">參数数组，无法确定有多少參数</param>
    ‘‘‘ <returns>返回受影响行数</returns>
    ‘‘‘ <remarks></remarks>
    Public Function ExecuteAddDelUpdate(ByVal cmdSQLText As String, ByVal cmdSQLType As CommandType, ByVal sqlParams As SqlParameter()) As Integer
        cmdSQL.Parameters.AddRange(sqlParams) ‘将參数传入
        cmdSQL.CommandType = cmdSQLType ‘确定语句类型，是sql还有oracle还是其它类型
        cmdSQL.CommandText = cmdSQLText ‘将sql语句传给cmdSQL
        cmdSQL.Connection = connSQL ‘设置连接，全局变量

        ‘開始运行查询
        Try
            connSQL.Open() ‘打开连接
            Return cmdSQL.ExecuteNonQuery() ‘运行查询
            cmdSQL.Parameters.Clear() ‘清除參数
        Catch ex As Exception
            Return 0
        Finally
            Call CloseConnection(connSQL)
            Call CloseSQLCommand(cmdSQL)
        End Try

    End Function
    ‘‘‘ <summary>
    ‘‘‘ 运行增删改，有參，确认是否运行成功
    ‘‘‘ </summary>
    ‘‘‘ <param name="cmdText">须要运行的数据库语句</param>
    ‘‘‘ <param name="cmdType">数据语句的类型，一般为Sql语句，不是存储过程</param>
    ‘‘‘ <returns>返回受影响的行数</returns>
    ‘‘‘ <remarks></remarks>
    Public Function ExecuteAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
        cmdSQL.CommandType = cmdType ‘确定语句类型，是sql还有oracle还是其它类型
        cmdSQL.CommandText = cmdText ‘将sql语句传给cmdSQL
        cmdSQL.Connection = connSQL ‘设置连接，全局变量

        ‘開始运行查询
        Try
            connSQL.Open() ‘打开连接
            Return cmdSQL.ExecuteNonQuery() ‘运行查询
            cmdSQL.Parameters.Clear() ‘清除參数
        Catch ex As Exception
            Return 0
        Finally
            Call CloseConnection(connSQL)
            Call CloseSQLCommand(cmdSQL)
        End Try

    End Function
    ‘‘‘ <summary>
    ‘‘‘ 运行查询操作，有參，返回datatable类型
    ‘‘‘ </summary>
    ‘‘‘ <param name="cmdText">须要运行的数据库语句，一般SQL语句，也有存储过程</param>
    ‘‘‘ <param name="cmdType">数据语句的类型，一般为Sql语句，不是存储过程</param>
    ‘‘‘ <param name="sqlParams">參数数组，不确定有多少參数数组</param>
    ‘‘‘ <returns>返回datatable类型</returns>
    ‘‘‘ <remarks></remarks>
    Public Function ExecuteSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal sqlParams As SqlParameter()) As DataTable
        Dim sqlAdapter As SqlDataAdapter
        Dim dtSQL As New DataTable
        Dim dsSQL As New DataSet

        cmdSQL.CommandText = cmdText
        cmdSQL.CommandType = cmdType
        cmdSQL.Connection = connSQL
        cmdSQL.Parameters.AddRange(sqlParams)
        sqlAdapter = New SqlDataAdapter(cmdSQL)

        Try
            sqlAdapter.Fill(dsSQL)
            dtSQL = dsSQL.Tables(0) ‘datatable为dataSet的第一个表
            cmdSQL.Parameters.Clear()
        Catch ex As Exception
            MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告")
        Finally
            Call CloseSQLCommand(cmdSQL)
        End Try

        Return dtSQL

    End Function
    ‘‘‘ <summary>
    ‘‘‘ 运行查询操作，无參，返回datatable类型
    ‘‘‘ </summary>
    ‘‘‘ <param name="cmdText">须要运行的数据库语句，一般SQL语句，也有存储过程</param>
    ‘‘‘ <param name="cmdType">数据语句的类型，一般为Sql语句，不是存储过程</param>
    ‘‘‘ <returns>返回datatable类型</returns>
    ‘‘‘ <remarks></remarks>
    Public Function ExecuteSelect(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
        Dim sqlAdapter As SqlDataAdapter
        Dim dtSQL As New DataTable
        Dim dsSQL As New DataSet

        cmdSQL.CommandText = cmdText
        cmdSQL.CommandType = cmdType
        cmdSQL.Connection = connSQL
        sqlAdapter = New SqlDataAdapter(cmdSQL) ‘实例化adapter

        Try
            sqlAdapter.Fill(dsSQL) ‘用dsSQL填充sqlAdapter
            dtSQL = dsSQL.Tables(0)
        Catch ex As Exception
            Call CloseSQLCommand(cmdSQL)
        End Try

        Return dtSQL

    End Function
    ‘‘‘ <summary>
    ‘‘‘ 关闭连接
    ‘‘‘ </summary>
    ‘‘‘ <param name="connSQL">须要关闭连接</param>
    ‘‘‘ <remarks></remarks>
    Public Sub CloseConnection(ByVal connSQL As SqlConnection)
        If (connSQL.State <> ConnectionState.Closed) Then ‘假设没有关闭
            connSQL.Close() ‘关闭连接
            connSQL = Nothing ‘不指向原对象
        End If
    End Sub
    ‘‘‘ <summary>
    ‘‘‘ 关闭命令
    ‘‘‘ </summary>
    ‘‘‘ <param name="cmdSQL">须要关闭命令</param>
    ‘‘‘ <remarks></remarks>
    Public Sub CloseSQLCommand(ByVal cmdSQL As SqlCommand)
        If Not IsNothing(cmdSQL) Then ‘假设存在命令
            cmdSQL.Dispose() ‘将命令销毁
            cmdSQL = Nothing

        End If
    End Sub
End Class

           SQLHelper将数据库操作语句分为了两类，一类是增删改、一类是查，这两类又分别有有參和无參两种，这些在D层有多个类须要操作数据库时，我们仅仅须要调用这里的类，假设有參，仅仅须要把參数穿进去即可了，以下看一看在D层我们是怎么用它的：

Imports System.Data
Imports System.Data.SqlClient
Imports DALHelper
Imports IDAL
Imports Entity
Public Class LoginDAL : Implements IDAL.ILogin
    Public Function SelectUser(user As UserInfoEntity) As DataTable Implements ILogin.SelectUser
        Dim cmdText As String
        Dim sqlhelper As New SqlHelper
        Dim dtSQL As DataTable
        Dim sqlParams As SqlParameter()

        cmdText = "select * from T_UserInfo where UserName=@UserName and Password=@Password"
        sqlParams = {New SqlParameter("@UserName", user.UserName), New SqlParameter("@Password", user.Password)}
        dtSQL = sqlhelper.ExecuteSelect(cmdText, CommandType.Text, sqlParams)

        Return dtSQL

    End Function
End Class

代码就是这样，虽然我也不太懂，但也就将就用吧、