SQL Server 資料庫物件動態建立：結合 DMO 與 Transact-SQL 實現自動化部署

背景動機

軟體發行版本無法手工建立每個客戶的資料庫結構。需將建立資料庫裝置、資料庫、資料表、索引、鍵值等邏輯固化在程式中，安裝時自動在 SQL Server 建立。核心技術：DMO (Distributed Management Objects) 提供物件導向管理介面，Transact-SQL 執行 DDL/DML。

開發環境與準備

• Visual Basic 5.0 / 6.0
• 參考 Microsoft SQLDMO Object Library (sqldmo.dll)
• 參考 Microsoft Windows Common Controls 5.0 (comctl32.ocx)

核心程式碼結構

1. 模組層級宣告連線物件

Global oServer As New SQLOLE.SQLServer

2. 建立連線

oServer.Connect ServerName:="MyServer", Login:="sa", Password:="pwd"

3. 動態建立資料庫裝置與資料庫 (Transact-SQL 批次)

Dim strSQL As String
strSQL = "USE MASTER " & _
         "DISK INIT NAME='Device_1', PHYSNAME='D:\SQLData\device1.dat', VDEVNO=10, SIZE=102400 " & _
         "DISK INIT NAME='Device_2', PHYSNAME='D:\SQLData\device2.dat', VDEVNO=11, SIZE=204800 " & _
         "CREATE DATABASE Db_1 ON Device_1 = 100 " & _
         "CREATE DATABASE Db_2 ON Device_2 = 100 "
oServer.ExecuteImmediate Command:=strSQL, ExecType:=SQLOLEExec_Default

4. 建立資料表、主鍵、索引

' Db_1: T_1 (ID_Code PK, Name 索引)
strSQL = "USE Db_1 " & _
         "CREATE TABLE T_1 (Name CHAR(8) NULL, Age NUMERIC(2) NULL, " & _
         "Sex SMALLINT NULL, ID_Code CHAR(16) NOT NULL, " & _
         "CONSTRAINT PK_T_1 PRIMARY KEY CLUSTERED (ID_Code)) " & _
         "CREATE INDEX IX_T_1_Name ON T_1 (Name)"
oServer.ExecuteImmediate strSQL

' Db_2: T_2 (Department, No 複合 PK)
strSQL = "USE Db_2 " & _
         "CREATE TABLE T_2 (Name CHAR(8) NULL, Age NUMERIC(2) NULL, " & _
         "Sex SMALLINT NULL, Department CHAR(16) NOT NULL, No CHAR(4) NOT NULL, " & _
         "CONSTRAINT PK_T_2 PRIMARY KEY CLUSTERED (Department, No))"
oServer.ExecuteImmediate strSQL

5. 擴充裝置容量 (範例)

strSQL = "DISK RESIZE NAME='Device_1', SIZE=102400" ' 增加 100MB
oServer.ExecuteImmediate strSQL

6. 修改/刪除物件

使用 ALTER DATABASE, ALTER TABLE, DROP DATABASE, DROP TABLE 等 T-SQL 陳述式搭配 ExecuteImmediate。

服務啟動自動化

Shell "net start mssqlserver"
Shell "net start sqlexecutive"
' 或顯示服務管理器
Shell "sqlmgr.exe", 1

後續資料存取建議

結構建立完成後，應用程式資料存取建議使用 ADO / RDO / ODBC，DMO 僅用於管理操作。

優勢總結

• 安裝程式零手工干預，適合大量部署。
• 版本升級只需更新腳本邏輯，保證結構一致性。
• VB 開發效率高，易於整合至安裝精靈 (InstallShield / Wise 等)。