使用擷取模式

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse Analytics

SMO 程式可以擷取和記錄程式所發出的對等 Transact-SQL 語句，或除了程式所執行的語句之外。 您可以使用 物件，或使用 ConnectionContext 物件的 屬性 Server 來啟用擷取模式 ServerConnection 。

範例

若要使用提供的任何程式碼範例，您必須選擇程式設計環境、程式設計範本，以及用來建立應用程式的程式設計語言。 如需詳細資訊，請參閱 在 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 ( String p_s in srv.ConnectionContext.CapturedSql.Text ) {   
   Console.WriteLine(p_s);   
}   
// Execute the captured statements.   
srv.ConnectionContext.ExecuteNonQuery(srv.ConnectionContext.CapturedSql.Text);   
// Revert to immediate execution mode.   
srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;   
}