自己做项目的时候学着写的
Imports System.Data
Imports System.Data.SqlClient
''' -----------------------------------------------------------------------------
''' Project  : ComClass
''' Class  : SqlHelp
'''
''' -----------------------------------------------------------------------------
''' <summary>
''' 数据访问抽象基础类
''' </summary>
''' <remarks>
''' </remarks>
''' <history>
'''  [skyapplezhao] 2009-4-23 Created
''' </history>
''' -----------------------------------------------------------------------------
Public NotInheritable Class SqlHelp
  
    Private Shared connectingstrings As String = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString").ToString.Trim
#Region "ExecNonSql执行SQL语句,无返回"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    '''  ExecNonSql
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <param name="sqlParms"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
'''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Overloads Shared Function ExecNonSql(ByVal SQLString As String)
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand(SQLString, myConn)
            myConn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
    End Function
   
    Public Overloads Shared Function ExecNonSql(ByVal SQLString As String, ByVal cmdtype As CommandType, ByVal sqlParms As SqlParameter())
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand
            FillSqlCommand(cmd, myConn, Nothing, cmdtype, SQLString, sqlParms)
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
    End Function
#End Region
#Region "ExecuteSqlTran执行SQL语句，实现数据库事务"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' ExecuteSqlTran
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <param name="sqlnum">SQL语句的行数(0或者正确的行数)</param>
    ''' <returns>true/false</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Shared Function ExecuteSqlTran(ByVal SQLString As String, ByVal sqlnum As String)
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        Dim errorStr As String
        Dim reInt As String
        Dim rebol As Boolean
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand(SQLString, myConn)
            myConn.Open()
            myTrans = myConn.BeginTransaction
            cmd.Transaction = myTrans
            reInt = cmd.ExecuteNonQuery()
            If sqlnum = 0 Then
                myTrans.Commit()
                rebol = True
            Else
                If reInt = sqlnum Then
                    myTrans.Commit()
                    rebol = True
                Else
                    reInt = 0
                    myTrans.Rollback()
                    rebol = False
                End If
            End If
        Catch ex As Exception
            errorStr = ex.ToString
            reInt = 0
            myTrans.Rollback()
            rebol = False
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
        Return rebol
    End Function
#End Region
#Region "ExecuteSql// ExecuteSql执行SQL语句，返回影响的记录数"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' ExecuteSql// ExecuteSql
    ''' </summary>
    ''' <param name="SQLString">sql语句</param>
    ''' <returns>返回影响的记录数</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Overloads Shared Function ExecuteSql(ByVal SQLString As String) As Int16
        Dim i As Int16 = 0
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand(SQLString, myConn)
            myConn.Open()
            i = cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
        Return i
    End Function
    Public Overloads Shared Function ExecuteSql(ByVal SQLString As String, ByVal cmdtype As CommandType, ByVal sqlParms As SqlParameter()) As Int16
        Dim r As Int16 = 0
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand
            '''cmd.CommandType = cmdtype
            FillSqlCommand(cmd, myConn, Nothing, cmdtype, SQLString, sqlParms)
            r = cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
        Return r
    End Function
#End Region
#Region "ExecuteScalarSql // ExecuteScalarS 执行SQL语句，返回查询结果"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' ExecuteScalarSql // ExecuteScalarS
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <returns>返回第一行第一列</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Shared Function ExecuteScalarSql(ByVal SQLString As String) As Object
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Dim obj As Object = Nothing
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand(SQLString, myConn)
            myConn.Open()
            obj = cmd.ExecuteScalar()
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
    End Function
    Public Shared Function ExecuteScalarS(ByVal SQLString As String, ByVal cmdtype As CommandType, ByVal sqlParms As SqlParameter()) As Object
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Dim obj As Object = Nothing
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand
            FillSqlCommand(cmd, myConn, Nothing, cmdtype, SQLString, sqlParms)
            obj = cmd.ExecuteScalar()
            cmd.Parameters.Clear()
            Return obj
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myConn.Dispose()
        End Try
    End Function
#End Region
#Region "ExecuteReader//ExecuteReadSql执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' ExecuteReader//ExecuteReadSql
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>返回SqlDataReader</returns>
    ''' <remarks>
    ''' 使用该方法切记要手工关闭SqlDataReader和连接
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Shared Function ExecuteReader(ByVal SQLString As String) As SqlDataReader
        Dim myReader As SqlDataReader = Nothing
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand(SQLString, myConn)
            myConn.Open()
            myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Return myReader
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Shared Function ExecuteReadSql(ByVal SQLString As String, ByVal cmdtype As CommandType, ByVal sqlParms As SqlParameter()) As SqlDataReader
        Dim myReader As SqlDataReader = Nothing
        Dim myConn As SqlConnection
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand
            FillSqlCommand(cmd, myConn, Nothing, cmdtype, SQLString, sqlParms)
            myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            cmd.Parameters.Clear()
            Return myReader
        Catch ex As Exception
            Throw ex
        End Try
    End Function
#End Region
#Region "ExecuteDataSet // ExecuteDataSet执行查询语句，返回DataSet"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' ExecuteDataSet // ExecuteDataSet
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>返回DataSet</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Overloads Shared Function ExecuteDataSet(ByVal SQLString As String) As DataSet
        Dim myConn As SqlConnection
        Dim myAdapter As SqlDataAdapter
        Dim ds As DataSet = Nothing
        Try
            myConn = New SqlConnection(connectingstrings)
            myConn.Open()
            myAdapter = New SqlDataAdapter(SQLString, myConn)
            ds = New DataSet
            myAdapter.Fill(ds, "ds")
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            myAdapter.Dispose()
            myConn.Dispose()
        End Try
        Return ds
    End Function
    Public Overloads Shared Function ExecuteDataSet(ByVal SQLString As String, ByVal cmdtype As CommandType, ByVal sqlParms As SqlParameter()) As DataSet
        Dim myConn As SqlConnection
        Dim myAdapter As SqlDataAdapter
        Dim ds As DataSet = Nothing
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand
            FillSqlCommand(cmd, myConn, Nothing, cmdtype, SQLString, sqlParms)
            myAdapter = New SqlDataAdapter(cmd)
            ds = New DataSet
            myAdapter.Fill(ds)
            cmd.Parameters.Clear()
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            cmd.Dispose()
            myAdapter.Dispose()
            myConn.Dispose()
        End Try
        Return ds
    End Function
#End Region
#Region "ExecuteDataTableSql执行查询语句,返回DataTable"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' ExecuteDataTableSql
    ''' </summary>
    ''' <param name="SQLString">查询语句</param>
    ''' <returns>返回DataTable</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Overloads Shared Function ExecuteDataTableSql(ByVal SQLString As String) As DataTable
        Dim myConn As SqlConnection
        Dim myAdapter As SqlDataAdapter
        Dim myTable As DataTable = Nothing
        Try
            myConn = New SqlConnection(connectingstrings)
            myConn.Open()
            myAdapter = New SqlDataAdapter(SQLString, myConn)
            myAdapter.Fill(myTable)
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            myAdapter.Dispose()
            myConn.Dispose()
        End Try
        Return myTable
    End Function
    Public Overloads Shared Function ExecuteDataTableSql(ByVal SQLString As String, ByVal cmdtype As CommandType, ByVal sqlParms As SqlParameter()) As DataTable
        Dim myConn As SqlConnection
        Dim myAdapter As SqlDataAdapter
        Dim myTable As DataTable = Nothing
        Dim cmd As SqlCommand
        Try
            myConn = New SqlConnection(connectingstrings)
            cmd = New SqlCommand
            FillSqlCommand(cmd, myConn, Nothing, cmdtype, SQLString, sqlParms)
            myAdapter = New SqlDataAdapter(cmd)
            myAdapter.Fill(myTable)
        Catch ex As Exception
            Throw ex
        Finally
            myConn.Close()
            myAdapter.Dispose()
            cmd.Dispose()
            myConn.Dispose()
        End Try
        Return myTable
    End Function
#End Region
#Region "填充SqlCommand,Private域"
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' FillSqlCommand
    ''' </summary>
    ''' <param name="cmd">SqlCommand</param>
    ''' <param name="conn">SqlConnection</param>
    ''' <param name="trans">SqlTransaction</param>
    ''' <param name="cmdType">CommandType</param>
    ''' <param name="cmdText">语句</param>
    ''' <param name="sqlParms">SqlParameter</param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    '''  [skyapplezhao] 2009-4-23 Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Private Shared Sub FillSqlCommand(ByVal cmd As SqlCommand, ByVal conn As SqlConnection, ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal sqlParms As SqlParameter())
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        cmd.Connection = conn
        cmd.CommandType = cmdType
        cmd.CommandText = cmdText
        If Not (trans Is Nothing) Then
            cmd.Transaction = trans
        End If
        If Not (sqlParms Is Nothing) Then
            For Each sqlParm As SqlParameter In sqlParms
                If (sqlParm.Direction = ParameterDirection.Input Or sqlParm.Direction = ParameterDirection.InputOutput) And sqlParm.Value Is Nothing Then
                    sqlParm.Value = DBNull.Value
                End If
                cmd.Parameters.Add(sqlParm)
            Next
        End If
    End Sub
#End Region
End Class