使用擷取模式

SMO 程式可以擷取與記錄程式所發出的相等 Transact-SQL 陳述式來取代程式所執行的陳述式，或是加上程式所執行的陳述式。您可以使用 ServerConnection 物件，或使用 Server 物件的 ConnectionContext 屬性來啟用擷取模式。

範例

如果要使用所提供的任何程式碼範例，您必須選擇建立應用程式用的程式設計環境、程式設計範本，及程式設計語言。如需詳細資訊，請參閱《SQL Server 線上叢書》中的＜如何：在 Visual Studio .NET 中建立 Visual Basic SMO 專案＞(英文) 或＜如何：在 Visual Studio .NET 中建立 Visual C# SMO 專案＞(英文)。

在 Visual Basic 中啟用擷取模式

此程式碼範例會啟用擷取模式，然後顯示擷取緩衝區中保留的 Transact-SQL 命令。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Set the execution mode to CaptureSql for the connection.
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql
'Make a modification to the server that is to be captured.
srv.UserOptions.AnsiNulls = True
srv.Alter()
'Iterate through the strings in the capture buffer and display the captured statements.
Dim s As String
For Each s In srv.ConnectionContext.CapturedSql.Text
    Console.WriteLine(s)
Next
'Execute the captured statements.
srv.ConnectionContext.ExecuteNonQuery(srv.ConnectionContext.CapturedSql.Text)
'Revert to immediate execution mode. 
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql

在 Visual C# 中啟用擷取模式

此程式碼範例會啟用擷取模式，然後顯示擷取緩衝區中保留的 Transact-SQL 命令。

{ 
//Connect to the local, default instance of SQL Server. 
Server srv; 
srv = new Server(); 
//Set the execution mode to CaptureSql for the connection. 
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; 
//Make a modification to the server that is to be captured. 
srv.UserOptions.AnsiNulls = true; 
srv.Alter(); 
//Iterate through the strings in the capture buffer and display the captured statements. 
string s; 
foreach ( s in srv.ConnectionContext.CapturedSql.Text) { 
   Console.WriteLine(s); 
} 
//Execute the captured statements. 
srv.ConnectionContext.ExecuteNonQuery(srv.ConnectionContext.CapturedSql.Text); 
//Revert to immediate execution mode. 
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql; 
}