处理 SMO 事件

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Microsoft Fabric SQL 数据库

某些服务器事件类型可以通过使用事件处理程序和 ServerConnection 对象来进行订阅。

SQL Server 管理对象（SMO）中的许多实例类可以在服务器上执行某些操作时触发事件。

通过设置事件处理程序并订阅相关事件，可以以编程方式对这些事件进行处理。 这种事件处理只是暂时性的，因为当 SMO 客户端程序退出时会删除所有的订阅。

ConnectionContext 事件处理

ServerConnection 对象支持若干种事件类型。 事件属性必须设置为相应事件处理程序的实例，且必须将事件处理程序对象定义为用于处理事件的受保护函数。

事件订阅

您可以通过以下方法处理事件：编写事件处理程序类，创建该类的实例，将事件处理程序分配给父对象，然后订阅事件。

必须编写事件处理程序类才能处理事件。 事件处理程序类可以包含多个事件处理程序函数，且只有在安装事件处理程序类后才能处理事件。 事件处理程序函数从 ServerEventNotificatificationArgs 参数接收有关事件的信息，该参数可用于报告有关事件的信息。

可以处理的数据库和服务器事件的类型在类和ServerEventSet类中DatabaseEventSet列出。

示例

若要使用提供的任何代码示例，必须选择要在其中创建应用程序的编程环境、编程模板和编程语言。 有关详细信息，请参阅 如何在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 .NET 中注册事件处理程序并订阅事件处理

以下代码示例演示如何在 .NET 中设置事件处理程序以及如何订阅数据库事件。

Visual Basic

Visual Basic

此代码示例演示如何设置事件处理程序以及如何订阅数据库事件。

'Create an event handler subroutine that runs when a table is created.
Private Sub MyCreateEventHandler(ByVal sender As Object, ByVal e As ServerEventArgs)
    Console.WriteLine("A table has just been added to the AdventureWorks2022 database.")
End Sub

'Create an event handler subroutine that runs when a table is deleted.
Private Sub MyDropEventHandler(ByVal sender As Object, ByVal e As ServerEventArgs)
    Console.WriteLine("A table has just been dropped from the AdventureWorks2022 database.")
End Sub

Sub Main()
    'Connect to the local, default instance of SQL Server.

    Dim srv As Server
    srv = New Server

    'Reference the AdventureWorks2022 database.
    Dim db As Database
    db = srv.Databases("AdventureWorks2022")

    'Create a database event set that contains the CreateTable event only.
    Dim databaseCreateEventSet As New DatabaseEventSet
    databaseCreateEventSet.CreateTable = True

    'Create a server event handler and set it to the first event handler subroutine.
    Dim serverCreateEventHandler As ServerEventHandler
    serverCreateEventHandler = New ServerEventHandler(AddressOf MyCreateEventHandler)

    'Subscribe to the first server event handler when a CreateTable event occurs.
    db.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler)

    'Create a database event set that contains the DropTable event only.
    Dim databaseDropEventSet As New DatabaseEventSet
    databaseDropEventSet.DropTable = True

    'Create a server event handler and set it to the second event handler subroutine.
    Dim serverDropEventHandler As ServerEventHandler
    serverDropEventHandler = New ServerEventHandler(AddressOf MyDropEventHandler)

    'Subscribe to the second server event handler when a DropTable event occurs.
    db.Events.SubscribeToEvents(databaseDropEventSet, serverDropEventHandler)

    'Start event handling.
    db.Events.StartEvents()

    'Create a table on the database.
    Dim tb As Table
    tb = New Table(db, "Test_Table")

    Dim mycol1 As Column
    mycol1 = New Column(tb, "Name", DataType.NChar(50))
    mycol1.Collation = "Latin1_General_CI_AS"
    mycol1.Nullable = True

    tb.Columns.Add(mycol1)
    tb.Create()

    'Remove the table.
    tb.Drop()

    'Wait until the events have occurred.
    Dim x As Integer
    Dim y As Integer
    For x = 1 To 1000000000
        y = x*2
    Next

    'Stop event handling.
    db.Events.StopEvents()
End Sub

C#

C#

此代码示例演示如何设置事件处理程序以及如何订阅数据库事件。

//Create an event handler subroutine that runs when a table is created.
private void MyCreateEventHandler(object sender, ServerEventArgs e)
{
    Console.WriteLine("A table has just been added to the AdventureWorks2022 database.");
}

//Create an event handler subroutine that runs when a table is deleted.
private void MyDropEventHandler(object sender, ServerEventArgs e)
{
    Console.WriteLine("A table has just been dropped from the AdventureWorks2022 database.");
}

public void Main()
{
    //Connect to the local, default instance of SQL Server.
    var srv = new Server();

    //Reference the AdventureWorks2022 database.
    Database db = srv.Databases["AdventureWorks2022"];

    //Create a database event set that contains the CreateTable event only.
    var databaseCreateEventSet = new DatabaseEventSet
    {
        CreateTable = true
    };

    //Create a server event handler and set it to the first event handler subroutine.
    var serverCreateEventHandler = new ServerEventHandler(MyCreateEventHandler);

    //Subscribe to the first server event handler when a CreateTable event occurs.
    db.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler);

    //Create a database event set that contains the DropTable event only.
    var databaseDropEventSet = new DatabaseEventSet
    {
        DropTable = true
    };

    //Create a server event handler and set it to the second event handler subroutine.
    var serverDropEventHandler = new ServerEventHandler(MyDropEventHandler);

    //Subscribe to the second server event handler when a DropTable event occurs.
    db.Events.SubscribeToEvents(databaseDropEventSet, serverDropEventHandler);
    
    //Start event handling.
    db.Events.StartEvents();

    //Create a table on the database.
    var tb = new Table(db, "Test_Table");

    var myCol1 = new Column(tb, "Name", DataType.NChar(50))
    {
        Collation = "Latin1_General_CI_AS",
        Nullable = true
    };

    tb.Columns.Add(myCol1);
    tb.Create();

    //Remove the table.
    tb.Drop();
    
    //Wait until the events have occurred.
    int x;
    int y;
    for (x = 1; x <= 1000000000; x++)
    {
        y = x * 2;
    }

    //Stop event handling.
    db.Events.StopEvents();
}