VB.NET应用更新时如何对比SQL Server数据库架构并自动更新？

不用像之前那样逐个编写列存在性判断代码，推荐采用版本化迁移脚本的方式，既能减少重复工作量，又能清晰管理所有架构变更，实现自动更新。

步骤1：创建数据库版本记录表 #

先在数据库中建立一张表，专门记录已执行的迁移脚本版本，避免重复执行：

CREATE TABLE DB_Migration_History (
    Migration_ID INT PRIMARY KEY IDENTITY(1,1),
    Version_Number VARCHAR(50) NOT NULL UNIQUE,
    Execute_Date DATETIME DEFAULT GETDATE(),
    Script_Description VARCHAR(255)
)

步骤2：按版本编写迁移脚本 #

给每个版本的架构修改编写独立SQL脚本，每个脚本对应唯一版本号，同时保证脚本幂等性（重复执行不报错）：

• v1.1_Table1_Add_3Columns.sql：给Table1新增3列

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1' AND COLUMN_NAME='Col1')
ALTER TABLE Table1 ADD Col1 VARCHAR(50) NULL;

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1' AND COLUMN_NAME='Col2')
ALTER TABLE Table1 ADD Col2 INT NULL;

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1' AND COLUMN_NAME='Col3')
ALTER TABLE Table1 ADD Col3 DATETIME NULL;

• v1.2_Table2_Add_1Column.sql：给Table2新增1列

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table2' AND COLUMN_NAME='NewCol')
ALTER TABLE Table2 ADD NewCol NVARCHAR(100) NULL;

步骤3：VB.NET实现自动更新逻辑 #

在应用启动阶段执行以下流程：

1. 检查版本记录表是否存在，不存在则自动创建
2. 查询已执行的所有版本号
3. 读取应用内嵌的迁移脚本（将脚本作为项目资源嵌入，避免客户端丢失文件）
4. 仅执行未在历史记录中的脚本
5. 执行完成后，将版本号写入历史表

示例VB.NET代码：

Imports System.Data.SqlClient
Imports System.IO
Imports System.Reflection

Public Sub AutoUpdateDatabaseSchema()
    Dim connStr As String = "你的数据库连接字符串"
    Dim historyTableName As String = "DB_Migration_History"

    Using con As New SqlConnection(connStr)
        con.Open()

        ' 检查迁移记录表是否存在，不存在则创建
        Dim checkTableSql As String = $"IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = '{historyTableName}') BEGIN {GetMigrationTableCreateScript()} END"
        New SqlCommand(checkTableSql, con).ExecuteNonQuery()

        ' 获取已执行的版本列表
        Dim executedVersions As New List(Of String)()
        Using reader As SqlDataReader = New SqlCommand($"SELECT Version_Number FROM {historyTableName}", con).ExecuteReader()
            While reader.Read()
                executedVersions.Add(reader("Version_Number").ToString().Trim())
            End While
        End Using

        ' 读取内嵌的迁移脚本（假设脚本嵌入在项目Resources中，命名格式为Migration_vX.X.sql）
        Dim assembly As Assembly = Assembly.GetExecutingAssembly()
        Dim resourceNames As String() = assembly.GetManifestResourceNames()

        For Each resName As String In resourceNames
            If resName.StartsWith("你的项目命名空间.Migration_") AndAlso resName.EndsWith(".sql") Then
                ' 提取版本号，比如从Migration_v1.1.sql中拿到v1.1
                Dim version As String = resName.Split("_v")(1).Replace(".sql", "")
                
                If Not executedVersions.Contains(version) Then
                    ' 读取脚本内容
                    Using stream As Stream = assembly.GetManifestResourceStream(resName)
                        Using sr As New StreamReader(stream)
                            Dim sqlScript As String = sr.ReadToEnd()

                            ' 执行迁移脚本
                            New SqlCommand(sqlScript, con).ExecuteNonQuery()

                            ' 记录到历史表
                            Dim insertHistorySql As String = $"INSERT INTO {historyTableName} (Version_Number, Script_Description) VALUES (@Version, @Desc)"
                            Dim cmd As New SqlCommand(insertHistorySql, con)
                            cmd.Parameters.AddWithValue("@Version", version)
                            cmd.Parameters.AddWithValue("@Desc", $"执行脚本：{resName}")
                            cmd.ExecuteNonQuery()
                        End Using
                    End Using
                End If
            End If
        Next

        con.Close()
    End Using
End Sub

Private Function GetMigrationTableCreateScript() As String
    Return "CREATE TABLE DB_Migration_History (
                Migration_ID INT PRIMARY KEY IDENTITY(1,1),
                Version_Number VARCHAR(50) NOT NULL UNIQUE,
                Execute_Date DATETIME DEFAULT GETDATE(),
                Script_Description VARCHAR(255)
            )"
End Function

额外优化建议 #

• 版本号按顺序递增：比如v1.1、v1.2、v2.0，避免乱序导致执行顺序出错
• 工具生成脚本：架构变更较多时，可使用SQL Server Data Tools (SSDT)对比新旧数据库，自动生成迁移脚本后按版本管理
• 添加异常处理：在VB.NET代码中加入Try-Catch块，捕获脚本执行错误并记录日志，方便排查问题

内容的提问来源于stack exchange，提问作者Johan