为了开发省力一点-基本数据操作

 Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Namespace BaseDataOperations
    Public Class BaseDataOperation
        Dim _ConnString As String = ""
        Sub New(ByVal value As String)
            _ConnString = value
        End Sub
        Public Property ConnString() As String
            Get
                Return _ConnString
            End Get
            Set(ByVal Value As String)
                _ConnString = Value
            End Set
        End Property
#Region "返回DataReader"
        Public Overloads Function GetDataReader(ByVal Fstring As String) As SqlDataReader
            If _ConnString = "" Then
                Return (Nothing)
            End If
            Dim sqlcn As New SqlConnection(_ConnString)
            Dim sqlcmd As New SqlCommand(Fstring, sqlcn)
            Dim sqlrd As SqlDataReader
            Try
                sqlcn.Open()
                sqlrd = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception

            End Try
            sqlcmd.Dispose()
            Return (sqlrd)
        End Function
        Public Overloads Function GetDataReader(ByVal sqlcmd As SqlCommand) As SqlDataReader
            If sqlcmd.Connection.State = ConnectionState.Closed Then
                sqlcmd.Connection.Open()
            End If
            Dim sqlrd As SqlDataReader
            sqlrd = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
            Return (sqlrd)
        End Function
        Public Overloads Function GetOleReader(ByVal CnString As String, ByVal Fstring As String) As OleDbDataReader
            Dim olecn As New OleDbConnection(CnString)
            Dim olecmd As New OleDbCommand(Fstring, olecn)
            Dim olerd As OleDbDataReader
            Try
                olecn.Open()
                olerd = olecmd.ExecuteReader(CommandBehavior.CloseConnection)

            Catch ex As Exception

            End Try
            olecmd.Dispose()
            Return (olerd)
        End Function
        Public Overloads Function GetOleReader(ByVal olecmd As OleDbCommand) As OleDbDataReader
            If olecmd.Connection.State = ConnectionState.Closed Then
                olecmd.Connection.Open()
            End If
            Dim olerd As OleDbDataReader
            olerd = olecmd.ExecuteReader(CommandBehavior.CloseConnection)
            Return (olerd)
        End Function
#End Region
#Region "返回DataTable(ReadOnly)"
        Public Overloads Function GetDataTable(ByVal Fstring As String) As DataTable
            Dim sqlcn As New SqlConnection(_ConnString)
            Dim sqlcmd As New SqlCommand(Fstring, sqlcn)
            Dim sqlapa As New SqlDataAdapter
            sqlapa.SelectCommand = sqlcmd
            Dim ds As New DataSet
            sqlapa.Fill(ds)
            Return (ds.Tables(0))
        End Function
        Public Overloads Function GetDataTable(ByVal sqlcmd As SqlCommand) As DataTable
            Dim sqlapa As New SqlDataAdapter
            sqlapa.SelectCommand = sqlcmd
            Dim ds As New DataSet
            sqlapa.Fill(ds)
            Return (ds.Tables(0))
        End Function
        Public Overloads Function GetDataTable(ByVal olecmd As OleDbCommand) As DataTable

        End Function
        Public Overloads Function GetDataTable(ByVal CnString As String, ByVal Fstring As String) As DataTable

        End Function
#End Region
#Region "Excute SQL"
        Public Overloads Function ExecuteSql(ByVal value As String) As Boolean
            Dim sqlcn As New SqlConnection(_ConnString)
            Dim sqlcmd As New SqlCommand(value, sqlcn)
            Dim ReturnValue As Boolean = False
            Try
                sqlcn.Open()
                sqlcmd.ExecuteNonQuery()
                ReturnValue = True
                sqlcn.Close()
            Catch ex As Exception
            End Try
            sqlcmd.Dispose()
            sqlcn.Dispose()
            Return (ReturnValue)
        End Function
        Public Overloads Function ExecuteSql(ByVal cn As String, ByVal value As String) As Boolean
            Dim olecn As New OleDbConnection(cn)
            Dim olecmd As New OleDbCommand(value, olecn)
            Dim ReturnValue As Boolean = False
            Try
                olecn.Open()
                olecmd.ExecuteNonQuery()
                olecn.Close()
                ReturnValue = True
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.OKOnly, "错误")
            End Try
            olecmd.Dispose()
            olecn.Dispose()
            Return (ReturnValue)
        End Function
        Public Overloads Function ExecuteSql(ByVal value1 As String, ByVal value2 As String, ByVal isRollBack As Boolean) As Boolean
            Dim ReturnValue As Boolean = False
            Dim sqlcn As New SqlConnection(_ConnString)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = sqlcn
            If isRollBack Then
                Dim sqltran As SqlTransaction
                Try
                    sqlcn.Open()
                    sqltran = sqlcn.BeginTransaction
                    sqlcmd.Transaction = sqltran
                    sqlcmd.CommandText = value1
                    sqlcmd.ExecuteNonQuery()
                    sqlcmd.CommandText = value2
                    sqlcmd.ExecuteNonQuery()
                    sqltran.Commit()
                    ReturnValue = True
                    sqlcn.Close()
                Catch ex As Exception
                    If sqlcn.State = ConnectionState.Open Then
                        sqltran.Rollback()
                        sqlcn.Close()
                    End If
                End Try
                sqltran.Dispose()
            Else
                Try
                    sqlcn.Open()
                    sqlcmd.CommandText = value1
                    sqlcmd.ExecuteNonQuery()
                    sqlcmd.CommandText = value2
                    sqlcmd.ExecuteNonQuery()
                    ReturnValue = True
                    sqlcn.Close()
                Catch ex As Exception

                End Try
            End If
            sqlcmd.Dispose()
            sqlcn.Dispose()
            Return (ReturnValue)
        End Function
#End Region
#Region "返回唯一值"
        Public Overridable Function GetScalar(ByVal value As String) As String
            Dim sqlcn As New SqlConnection(_ConnString)
            Dim sqlcmd As New SqlCommand(value, sqlcn)
            Dim ReturnValue As String = ""
            Try
                sqlcn.Open()
                ReturnValue = sqlcmd.ExecuteScalar()
                sqlcn.Close()
            Catch ex As Exception

            End Try
            sqlcmd.Dispose()
            sqlcn.Dispose()
            Return (ReturnValue)
        End Function

        Public Shared Function getValue(ByVal table As String, ByVal keyColumn As String, ByVal KeyValue As String, ByVal tarColumn As String, ByVal Conn As String) As String
            Dim sqlcn As New SqlConnection(Conn)
            Dim sqlcmd As New SqlCommand
            sqlcmd.Connection = sqlcn
            Dim sqlstring As String = "select " & tarColumn & " from " & table & " where " & keyColumn & "='" & KeyValue & "'"
            sqlcmd.CommandText = sqlstring
            Dim r As String = ""
            Try
                sqlcn.Open()
                r = sqlcmd.ExecuteScalar
            Catch ex As Exception
            Finally
                sqlcn.Close()
            End Try
            Return (r)
        End Function
#End Region
#Region "Fill Table"
        Public Overridable Overloads Function RefDataTable(ByVal Fstring As String, ByVal table As DataTable) As Boolean
            Dim sqlcn As New SqlConnection(_ConnString)
            Dim sqlcmd As New SqlCommand(Fstring, sqlcn)
            Dim sqlapa As New SqlDataAdapter
            sqlapa.SelectCommand = sqlcmd
            Dim rValue As Boolean = False
            Try
                table.Rows.Clear()
                sqlapa.Fill(table)
                rValue = True
            Catch ex As Exception

            End Try
            Return (rValue)
        End Function
#End Region
    End Class
End Namespace