vb.net 自定义数据库链接类(Access)

自定义数据库链接类(Access)是链接Access数据库时使用的类。

补足：Application.StartupPath.ToString是指定生成.EXE文件的路径，把数据库和执行文件存放到一起时，不管安装后在什么路径都无需特定指定。

 

Imports System.Data.OleDb

 

 #Region "全局变量"

 

     Private mTimeout As Integer

     Private mDbConn As OleDbConnection

     Private mSqlConn As OleDbCommand                 'SQL执行

     Private sTblNm As String                         '存放数据的表名

     Private mbOpen As Boolean                        'DB链接判断用

     Private mDbReader As OleDbDataReader

     Private mDbTrans As OleDbTransaction

     Private mbTran As Boolean = False                'Transaction开始判断用

     Private sSqlStr As String                        'SQL文

     Private mDbResult As DB_RESULT                   'EXECUTE方法的处理结果

     Private mDbConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath.ToString & _

                                    "/数据库名.mdb;Persist Security Info=False;Jet OLEDB:Database Password =密码"

 

     'DB返回值

     Public Enum DB_RESULT As Integer

         DB_OK

         DB_OPEN_NG

         DB_TRAN_NG

         DB_UNIQUE_NG

         DB_LOCK_NG

         DB_OTHER_NG

     End Enum

 

 #End Region

 

 #Region "属性"

     ''' <summary>

     ''' SQL文

     ''' </summary>

     ''' <value>SQL文</value>

     ''' <remarks></remarks>

     Public WriteOnly Property SqlCmd() As String

         Set(ByVal value As String)

             Me.sSqlStr = value

         End Set

     End Property

 

     ''' <summary>

     ''' 表名

     ''' </summary>

     ''' <value>表名</value>

     ''' <remarks></remarks>

     Public WriteOnly Property TblNm() As String

         Set(ByVal value As String)

             Me.sTblNm = value

         End Set

     End Property

 

 #End Region

 

     ''' <summary>

     ''' 数据库链接

     ''' </summary>

     ''' <remarks></remarks>

     Public Function OpenDB() As Boolean

 

         Try

             If Me.mbOpen Then

                 Return True

             End If

 

             If Me.mDbConn Is Nothing Then

                 Me.mDbConn = New System.Data.OleDb.OleDbConnection

                 Me.mDbConn.ConnectionString = Me.mDbConnStr

             End If

 

             '数据库链接开始

             mDbConn.Open()

             Me.mbOpen = True

 

             CommLog.WriteLine("数据库链接成功")

 

             Return True

         Catch ex As Exception

             mDbConnStr = ""

             If Not mDbConn Is Nothing Then

                 mDbConn = Nothing

             End If

             CommLog.WriteLine("数据库链接失败")

             CloseDB()

             Return False

         End Try

     End Function

 

     ''' <summary>

     ''' Transaction开始

     ''' </summary>

     ''' <returns></returns>

     ''' <remarks></remarks>

     Public Function BeginTran() As Boolean

 

         Try

            

             If Me.mbTran Then

                 Return True

             End If

 

             Me.mDbTrans = Me.mDbConn.BeginTransaction

             Me.mbTran = True

 

             CommLog.WriteLine("Transaction开始")

 

             Return True

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

             Return False

         End Try

     End Function

 

     ''' <summary>

     ''' Commit

     ''' </summary>

     ''' <returns></returns>

     ''' <remarks></remarks>

     Public Function CommitTran() As Boolean

         Try

 

             If Not mbOpen OrElse Not mbTran Then

                 Return False

             End If

 

             mDbTrans.Commit()

             mDbTrans.Dispose()

             mDbTrans = Nothing

             mbTran = False

 

             CommLog.WriteLine("Commit")

 

             Return True

 

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

             Return False

         End Try

     End Function

 

     ''' <summary>

     ''' Rollback

     ''' </summary>

     ''' <returns></returns>

     ''' <remarks></remarks>

     Public Function RollbackTran() As Boolean

         Try

 

             If Not mbOpen OrElse Not mbTran Then

                 Return False

             End If

 

             mDbTrans.Rollback()

             mDbTrans.Dispose()

             mDbTrans = Nothing

             mbTran = False

 

             CommLog.WriteLine("Rollback")

 

             Return True

 

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

             Return False

         End Try

     End Function

 

     ''' <summary>

     ''' 数据库断开

     ''' </summary>

     ''' <remarks></remarks>

     Public Sub CloseDB()

 

         Try

             If Not mbOpen Then

                 Return

             End If

 

             If mbOpen AndAlso mbTran Then

                 mDbTrans.Rollback()

                 mDbTrans.Dispose()

                 mDbTrans = Nothing

                 mbTran = False

             End If

 

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

         End Try

 

         Try

             If mDbConn.State = ConnectionState.Open Then

                 mDbConn.Close()

             End If

 

             CommLog.WriteLine("数据库断开")

 

             mbOpen = False

 

             mDbConn.Dispose()

             mDbConn = Nothing

 

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

         End Try

 

     End Sub

 

 #Region "SQL文执行"

 

     ''' <summary>

     ''' Execute

     ''' </summary>

     ''' <returns>执行的行数</returns>

     ''' <remarks></remarks>

     Public Function Execute() As Integer

         Dim iAffected As Integer

 

         Try

 

             iAffected = -1

 

             Me.mDbResult = DB_RESULT.DB_OK

 

             mSqlConn = Me.mDbConn.CreateCommand

             mSqlConn.Transaction = mDbTrans

             mSqlConn.CommandText = Me.sSqlStr

 

             CommLog.WriteLine(Me.sSqlStr)

 

             iAffected = mSqlConn.ExecuteNonQuery()

 

             Return iAffected

 

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

            Return -1

         Finally

             If Not mSqlConn Is Nothing Then

                 mSqlConn.Dispose()

                 mSqlConn = Nothing

             End If

         End Try

     End Function

 

     ''' <summary>

     ''' SELECT文执行(DataSet)

     ''' </summary>

     ''' <returns>检索结果</returns>

     ''' <remarks></remarks>

     Public Function GetDataSet() As DataSet

         Dim ds As New DataSet

         Dim objODA As OleDbDataAdapter

 

         Try

             mSqlConn = Me.mDbConn.CreateCommand

             mSqlConn.CommandText = sSqlStr

             objODA = New OleDbDataAdapter(mSqlConn)

             CommLog.WriteLine(sSqlStr)

 

             objODA.Fill(ds, Me.sTblNm)

 

             mSqlConn.Dispose()

         Catch ex As Exception

             CommLog.WriteLine(ex.ToString)

         End Try

         Return ds

     End Function

 #End Region