检查数据库中是否存在表或字段

引言

这是我用 VB.NET 和 ADO.NET 编写的两个函数，用于检查数据库中是否存在表或字段。它可以与 MS Access、SQL Server 或任何其他 OLE 数据库一起工作。

背景

检查 Access 数据库中的表或字段是否存在应该是一项非常简单的任务，但使用 ADO.NET 可能会变得非常复杂。使用 DAO 或 VB 6.0 中的 ADO，这是一项极其简单的任务。使用过它的人都会同意。因此，我发布这些函数是为了帮助其他程序员。希望对大家有所帮助。

使用代码

以下是这些函数

''' <summary>
''' Checks to see if a table exists in Database or not.
''' </summary>
''' <param name="tblName">Table name to check</param>
''' <param name="cnnStr">Connection String to connect to</param>
''' <returns>Works with Access or SQL</returns>
''' <remarks></remarks>

Public Function DoesTableExist(ByVal tblName As String, ByVal cnnStr As String) As Boolean
    ' For Access Connection String,
    ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    ' accessFilePathAndName

    ' Open connection to the database
    Dim dbConn As New OleDbConnection(cnnStr)
    dbConn.Open()
    
    ' Specify restriction to get table definition schema
    ' For reference on GetSchema see:
    ' http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx

    Dim restrictions(3) As String
    restrictions(2) = tblName
    Dim dbTbl As DataTable = dbConn.GetSchema("Tables", restrictions)

    If dbTbl.Rows.Count = 0 Then
        'Table does not exist
        DoesTableExist = False
    Else
        'Table exists
        DoesTableExist = True
    End If

    dbTbl.Dispose()
    dbConn.Close()
    dbConn.Dispose()
End Function


''' <summary>
''' Checks to see if a field exists in table or not.
''' </summary>
''' <param name="tblName">Table name to check in</param>
''' <param name="fldName">Field name to check</param>
''' <param name="cnnStr">Connection String to connect to</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function DoesFieldExist(ByVal tblName As String, _
                               ByVal fldName As String, _
                               ByVal cnnStr As String) As Boolean
    ' For Access Connection String,
    ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    ' accessFilePathAndName

    ' Open connection to the database
    Dim dbConn As New OleDbConnection(cnnStr)
    dbConn.Open()
    Dim dbTbl As New DataTable

    ' Get the table definition loaded in a table adapter
    Dim strSql As String = "Select TOP 1 * from " & tblName
    Dim dbAdapater As New OleDbDataAdapter(strSql, dbConn)
    dbAdapater.Fill(dbTbl)

    ' Get the index of the field name
    Dim i As Integer = dbTbl.Columns.IndexOf(fldName)

    If i = -1 Then
        'Field is missing
        DoesFieldExist = False
    Else
        'Field is there
        DoesFieldExist = True
    End If

    dbTbl.Dispose()
    dbConn.Close()
    dbConn.Dispose()
End Function