使用 VB.NET 在运行时动态添加 Access 数据库列

Rob Culhane

5.00/5 (13投票s)

2013 年 4 月 21 日

CPOL

5分钟阅读

64520

2606

如何使用 VB.NET 在运行时动态添加 Access 数据库列

下载源代码 - 72.6 KB

引言

本项目将演示如何使用 VB.NET 动态构建和更新 Access 数据库。一旦这里的结构就位，只需一行代码即可轻松添加新的数据库列和数据。该示例使用 VB.NET 2008 和 Access 2010 构建和测试。

背景

正如公司所面临的，我们需要特定的方法来测试我们的产品，并从一套已使用约 3 年的测试软件中保存数据。最初，一小组测试参数以 .csv 格式保存。只要所有被测试单元保存的数据量和类型相同，这对于小数据集来说是可以的。随着时间的推移，需要为各种测试保存更多不同类型的数据，并能够从这些数据中报告有用信息，使用 .csv 文件格式变得越来越难以维护。Access 数据库似乎是显而易见的解决方案，但如何在 VB.NET 中按照我们期望的方式实现它仍然是个谜。

由于每种测试的产品可能只包含完整数据集的一部分，因此通过代码添加新列是最方便的方法。随着新测试的出现，数据库可以自动更新。为每种产品测试手动设置数据库字段似乎有点麻烦。

Using the Code

首先，关于设置此项目的一些注意事项，如果您不注意，很容易遇到问题。您很可能会因为您的

将您的 VB.NET 项目设置 - > 编译 - > 高级编译选项… 目标 CPU… 设置为“x86”
您需要在开发系统上以及您打算部署程序的任何系统上安装 Microsoft “Access Engine 2012”。
http://www.microsoft.com/en-us/download/details.aspx?id=13255

OleDB 用于连接到我们的数据库。TestDataTableRow DataRow 用于在整个代码中保存测试数据。需要 DataTable 和 DataSet 来完成与稍后匹配的 Access 数据库的结构。

Imports System.Data.OleDb 
Public Class Form1
        Dim DB_TableName As String = "TestDataTable"
        Dim DB_TestDataTableRow As DataRow
        Dim DB_DataSet As DataSet   
    Dim DB_DataTable As DataTable

现在来创建数据结构并将它们链接起来…

Private Sub Form1_Load(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles MyBase.Load
        DB_DataSet = New DataSet()
        DB_DataTable = New DataTable(DB_TableName)
        DB_DataSet.Tables.Add(DB_DataTable)

现在有三个子例程在这里完成所有工作。首先，最简单的是在每个测试的开始时调用 DB_InitNewTestItem()，它初始化一个新的 DataRow 并清除之前的数据（如果有）。

Sub DB_InitNewTestItem()
        DB_TestDataTableRow = DB_DataTable.NewRow()
End Sub

DB_LogTestValue 负责构建先前创建的 DataRow 以匹配最终 Access 数据库格式的结构。如果 TestItemColumn 名称以前不存在，则会创建该名称，并分配 TestItemValue。这里做了一些“技巧”，因为 VB.NET 不再接受“var”类型的参数。所有值都作为 string 传递，稍后根据 SystemValueType 进行转换。如果您需要更多类型，Access 中有更多类型，只需添加另一个 Case 语句。请注意 Access 可以接受的类型。

另请注意，在我们写入数据库时需要定义 string 长度。如果在 Update 命令期间传递的 string 比定义的长度更大，将会导致错误。这里，默认值设置为“255”，这是我们可以分配的最大“Text”长度值。

Sub DB_LogTestValue(ByVal TestItemColumn As String, ByVal TestItemValue As String, _
Optional ByVal SystemValueType As String = "System.String")
 
Try
        Dim i As Integer = 1
        i = DB_DataSet.Tables(DB_TableName).Columns.IndexOf_
                             (TestItemColumn) 'test if colum exists
        If i = -1 Then 'Column is missing so add it
                Dim column As DataColumn = DB_DataTable.Columns.Add_
                              (TestItemColumn, Type.GetType(SystemValueType))
        End If
 
        Select Case SystemValueType 
               Case "System.Int32"
                       DB_TestDataTableRow.Item(TestItemColumn) = CInt(TestItemValue)
               Case "System.Double"
                       DB_TestDataTableRow.Item(TestItemColumn) = CDbl(TestItemValue)
               Case "System.Boolean"
                       DB_TestDataTableRow.Item(TestItemColumn) = CBool(TestItemValue)
               Case Else '"System.String"
                       If TestItemValue.Length() >= 255 Then
                               TestItemValue = TestItemValue.Substring(0, 254)
                       End If
                       DB_TestDataTableRow.Item(TestItemColumn) = TestItemValue
        End Select
Catch ex As Exception
        'error handler
End Try
End Sub

一旦 DB_LogTestValue() 用有用的数据填充了 DataRow，我们将希望使用 DB_RecordModuleTestToFile() 来保存它。这里将其分解为几个部分进行解释。通常有更好的示例用于连接和写入数据库，因此这里将只解释匹配和传输我们的新 DataRow 到数据库所需的关键细节。

首先，提供程序定义为 Microsoft.ACE.OLEDB.12.0。如果没有安装“Access Engine 2012”，这将导致“提供程序未在本地计算机上注册”的错误。数据源当然是您的 Access 数据库在较新 Access 格式“accdb”下的路径。对于旧的“.mdb”格式，请使用 “Microsoft.Jet.OLEDB.4.0“。

Sub DB_RecordModuleTestToFile()
 
        Dim DB_Provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
        Dim DB_Source As String = "Data Source = " & "C:\DummyDB\DummyDB.accdb"
 
        Dim strSQL As String
        Dim conn As New OleDbConnection Dim cmd As New OleDbCommand
        Dim cmd As New OleDbCommand
        Dim da As New OleDbDataAdapter
        Dim x, i As Integer
        Dim TempColumnName As String = ""
…

将填充的 DataRow 添加到 DataTable 并建立到数据库的连接。强烈建议将数据库连接代码包含在 Try/Catch 块中。

…
        Try
               DB_DataTable.Rows.Add(DB_TestDataTableRow)   
                       conn.ConnectionString = DB_Provider & DB_Source
               conn.Open()
               cmd.Connection = conn
               cmd.CommandType = CommandType.Text
…

现在我们需要构建一个 SQL SELECT 命令 string，以便将数据从数据库拉取到临时的 DataTable 中。此代码示例的目的是写入 Access 数据库，而不一定是将其读回，但我们的本地 DataSet 和目标数据库的结构必须匹配，否则数据库 Update 函数将无法正确写入。

…
        strSQL = "SELECT " & DB_TableName & ".* FROM [" & DB_TableName & "];"       
        cmd.CommandText = strSQL
        da.SelectCommand = cmd
        Dim TempDataSet As New DataSet
        da.Fill(TempDataSet, DB_TableName)
…

现在搜索 TempDataSet 并将任何不存在的列添加到我们的本地 DB_DataSet DataSet。如果您始终读写相同数量的列到数据库，那么这些步骤是不必要的。

…
        For x = 0 To TempDataSet.Tables(DB_TableName).Columns.Count - 1      
               TempColumnName = TempDataSet.Tables_
               (DB_TableName).Columns(x).ColumnName 'pull column names 
                                                    'from the test DataTable
               i = DB_DataSet.Tables(DB_TableName).Columns.IndexOf_
               (TempColumnName) 'returns -1 if column does not exist
               If i = -1 Then 'This Column is missing so add it
                       DB_DataSet.Tables(DB_TableName).Columns.Add(TempColumnName)
               End If
        Next
…

OleDbCommandBuilder 将在后台为我们创建 INSERT、DELETE 和 UPDATE SQL 命令。

…
        Dim dataCommandBuilder As New OleDb.OleDbCommandBuilder(da)
        da.InsertCommand = dataCommandBuilder.GetInsertCommand
        da.DeleteCommand = dataCommandBuilder.GetDeleteCommand
        da.UpdateCommand = dataCommandBuilder.GetUpdateCommand
…

对于每个新的数据列，都需要一个 SQL “ALTER TABLE” 命令，以便我们将新列写入数据库。这里的 datatype 必须是 SQL 兼容的类型。使用 for 循环搜索读取回的数据库列，并构建“ALTER TABLE”命令 string。使用 cmd.ExecuteNonQuery() 将命令发送到数据库以添加列。

…
        For x = 0 To DB_DataSet.Tables(DB_TableName).Columns.Count() - 1
               TempColumnName = DB_DataSet.Tables(DB_TableName).Columns(x).ColumnName
               i = TempDataSet.Tables(DB_TableName).Columns.IndexOf(TempColumnName)
               If i = -1 Then 'This Column is missing so add it
               Dim TempCmdDataType As String = ""
               Select Case DB_TestDataTableRow.Item(x).GetType().ToString
                       Case "System.Int32"
                               TempCmdDataType = "Integer"
                       Case "System.Double"
                               TempCmdDataType = "Double"
                       Case "System.Boolean"
                               TempCmdDataType = "Yes/No"
                       Case "System.String"
                               TempCmdDataType = "Text(255)"
                       Case Else
                               TempCmdDataType = "Text(255)"
               End Select
               cmd.CommandText = "ALTER TABLE " & DB_TableName & " _
               ADD " & TempColumnName & " " & TempCmdDataType
               cmd.ExecuteNonQuery()   'executes the SQL code in cmd without query
                End If
        Next
…

在 Access 数据库更新了新的列信息后，它应该现在可以接受我们本地 DataSet 的数据，并且可以关闭连接。这个“Update”步骤是大多数错误发生的地方，如果之前的任何步骤未能正确同步本地 DataSet 和 Access 数据库。

…
                da.Update(DB_DataSet.Tables(DB_TableName))
                conn.Close()
        Catch ex As Exception
               conn.Close()
               'error handler
        End Try
End Sub

关注点

这段代码在这里 CodeProject 上分享，供其他可能在做类似事情的人参考。我犹豫是否称其为教程，因为这意味着我在数据库方面具有一定的专业知识，超出了这次的经验。经过几周的搜索，我终于找到了完成我正在做的事情的示例，并编写了一个可行的程序。但是，请不要认为这是完成此任务的最佳或唯一方法，因为我可能在无意中违反了一些数据库规则。我希望一些在数据库和 SQL 方面经验更丰富的人能够评论，我欢迎您提出改进的建议。

历史

此示例仅使用 VB.NET 2008 和 Access Engine 2010 构建和测试。其他组合可能有效，但请自行承担风险。如果有人在评论中提供更有效的方法，我会尝试更新示例。