Menangani peristiwa SMO

Berlaku untuk:SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsSQL database di Microsoft Fabric

Ada jenis peristiwa server yang dapat dilanggani dengan menggunakan penanganan aktivitas dan ServerConnection objek .

Banyak kelas instans di SQL Server Management Objects (SMO) dapat memicu peristiwa ketika tindakan tertentu di server terjadi.

Peristiwa ini dapat ditangani secara terprogram dengan menyiapkan penanganan aktivitas dan berlangganan peristiwa terkait. Jenis penanganan peristiwa ini bersifat sementara karena semua langganan dihapus saat program klien SMO keluar.

Penanganan peristiwa ConnectionContext

Objek ServerConnection mendukung beberapa jenis peristiwa. Properti peristiwa harus diatur ke instans penanganan aktivitas yang sesuai, dan objek penanganan aktivitas harus didefinisikan sebagai fungsi terlindungi yang menangani peristiwa.

Langganan acara

Anda menangani peristiwa dengan menulis kelas penanganan aktivitas, membuat instansnya, menetapkan penanganan aktivitas ke objek induk, lalu berlangganan peristiwa.

Kelas penanganan aktivitas harus ditulis untuk menangani peristiwa. Kelas penanganan aktivitas dapat berisi lebih dari satu fungsi penanganan aktivitas, dan harus diinstal agar peristiwa dapat ditangani. Fungsi penanganan aktivitas menerima informasi tentang peristiwa dari parameter ServerEventNotificatificationArgs yang dapat digunakan untuk melaporkan informasi tentang peristiwa tersebut.

Jenis peristiwa database dan server yang dapat ditangani tercantum di DatabaseEventSet kelas dan ServerEventSetkelas .

Examples

Untuk menggunakan contoh kode apa pun yang disediakan, Anda harus memilih lingkungan pemrograman, templat pemrograman, dan bahasa pemrograman untuk membuat aplikasi Anda. Untuk informasi selengkapnya, lihat Cara Membuat Proyek Visual C# SMO di Visual Studio .NET.

Mendaftarkan penanganan aktivitas dan berlangganan penanganan peristiwa di .NET

Sampel kode berikut menunjukkan cara menyiapkan penanganan aktivitas, dan cara berlangganan peristiwa database, di .NET.

Dasar Visual

Visual Basic

Contoh kode ini memperlihatkan cara menyiapkan penanganan aktivitas, dan cara berlangganan peristiwa database.

'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#

Contoh kode ini memperlihatkan cara menyiapkan penanganan aktivitas, dan cara berlangganan peristiwa database.

//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();
}