.NET DLL aus einem Trigger aufrufen?

[Michael Berg]

Hallo zusammen,

besteht irgendwie die Möglichkeit Funktionen bzw. Proceduren aus einer .NET DLL (Assembly) aus einem Datenbank Trigger (MS SQL 2005) aufzurufen?

Wenn ja kann mir jemand grundlegen zeigen wie ich das anstellen kann?
Vielleicht mit einem kleinen Codeschnipsel.

Hintergrund der Übung ist, dass bei einem Datenbankereignis (INSERT) Funktionen aus einem .NET Programm aufgerufen werden sollen. Die DLL kapselt alle Funktionen aus dem Programm.

Vielen Dank schon mal im Voraus.

Beste Grüße

MiCHA

[Andreas Kosch]

Hallo,

warum der Umweg? Im Fall des MS SQL Server 2005 kann doch ein Trigger direkt in SQLCLR (also in Form einer .NET-Assembly) implementiert werden.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "CLRTriggerDemo", 
       Target = "ClrTriggerTbl", Event = "FOR INSERT, UPDATE")]
    public static void CLRTriggerDemo()
    {
        SqlTriggerContext aTC = SqlContext.TriggerContext;
        if (aTC.TriggerAction == TriggerAction.Insert)
        {
            using (SqlConnection aCon = new SqlConnection("Context connection=true"))
            {
                aCon.Open();
                SqlCommand aCmd = new SqlCommand("SELECT * FROM INSERTED", aCon);
                SqlContext.Pipe.ExecuteAndSend(aCmd); 
            }
        }
        if (aTC.TriggerAction == TriggerAction.Update)
        {
            if (aTC.IsUpdatedColumn(1))
            {
                SqlContext.Pipe.Send("Spalte Wert wurde geändert");
            }
        }
    }
}

[Michael Berg]

Hallo Herr Kosch,

vielen Dank für die schnelle Antwort. Verstehe ich das jetzt richtig, dass man nicht mehr "nur" den Trigger auf Tabellen- bzw. Datenbankebene zu betrachten hat, sondern weit darüber hinaus?

Das ist ja der Hammer!!!

Die Definition des Triggers mit Create Trigger usw. würde dann wegfallen bzw. mit Ihrem Code ersetzt werden bzw. wie implementiere ich dann Ihren Trigger, der ja eigentlich aus einer gekapselten Quellcodedatei besteht?

Vielen Dank schon mal im Voraus für die konstruktive Hilfe

M.Berg

[Andreas Kosch]

Hallo,

Das ist ja der Hammer!!!

in der Tat, denn mit SQLCLR kann man in eigener Regie den in T-SQL erreichbaren Funktionsumfang erweitern.

Die Definition des Triggers mit Create Trigger usw. würde dann wegfallen ..

Nicht ganz, denn CREATE TRIGGER registriert über AS EXTERNAL NAME nur noch den Namen der .NET-Assembly, in der die Funktionalität des Triggers implementiert wird:

CREATE TRIGGER [dbo].[CLRTriggerDemo]  
  ON [dbo].[ClrTriggerTbl]  
  AFTER  INSERT, UPDATE 
AS 
  EXTERNAL NAME [CLRTrigger].[Triggers].[CLRTriggerDemo];

Die einzelnen Aktionen des Triggers werden kann von der .NET-Klassenbibliothek gekapselt, wobei dort der Funktionsumfang der Klassen aus dem .NET Framework zur Verfügung steht. Da beim MS SQL Server 2005 die Sicherheit (inklusive der Robustheit im praktischen Betrieb) an erster Stelle steht, gibt es in der Voreinstellung eine Beschränkung der nutzbaren .NET-Klassen (siehe den Visual Studio 2005-Dialog Add References mit der zusätzlichen Registerseite SQL Server). Wenn die Voreinstellung geändert wird, lassen sich auch weiteren .NET Framework-Assemblies einbinden (siehe Abbildung).

[Michael Berg]

Hallo Herr Kosch,

vielen Dank für die super Unterstützung.
Dies ist genau das was ich gesucht habe.

Besten Dank nochmal, es ist wirklcih ein klasse Forum!!!

Michael Berg

[Michael Berg]

Hallo Herr Kosch,

leider habe ich doch noch ein Anliegen und benötige Hilfe.
Beim Ausführen bekomme ich immer eine Fehlermeldung im SQL Server.

Ich habe schon probiert die CLR Integration anzuschalten, aber die war schon aktiv. Auch die Verweise habe ich dahingehend angepasst, sodass die Assembly eigentlich bekannt sein sollte.

Meldung 6522, Ebene 16, Status 1, Prozedur CLRTriggerDemo, Zeile 1
.NET Framework-Fehler beim Ausführen der benutzerdefinierten Routine oder des benutzerdefinierten Aggregats "CLRTriggerDemo":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: UI

System.Security.HostProtectionException:
at System.Security.CodeAccessSecurityEngine.ThrowSecu rityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecu rityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetH elper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetH elper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at SqlServerProject_Trigger.Triggers.Trigger_Test()
.
Weiterhin bekomme ich dann beim Erstellen des Triggers direkt im Management Studio die Meldung

Meldung 6528, Ebene 16, Status 1, Prozedur CLRTriggerDemo, Zeile 1
Die 'CLRTrigger'-Assembly wurde im SQL-Katalog der 'mylibrary'-Datenbank nicht gefunden.

Können Sie mir nochmal helfen???

Vielen Dank im Voraus!!!

Michael Berg

[Andreas Kosch]

Hallo,

Meldung 6528, Ebene 16, Status 1, Prozedur CLRTriggerDemo, Zeile 1
Die 'CLRTrigger'-Assembly wurde im SQL-Katalog der 'mylibrary'-Datenbank nicht gefunden

die Installation einer Assembly für SQLCLR läuft in 2 Schritten ab:

1. Die Assembly-DLL wird über den CREATE ASSEMBLY-Aufruf direkt im SQL Server gespeichert, so dass zur Laufzeit der Zugriff auf die Datei nicht mehr notwendig ist. Außerdem wird dabei die Sicherheitsebene definiert.
2. Der Alias-Name jeder Methode der Assembly wird über die Anweisungen CREATE PROCEDURE bzw. CREATE TRIGGER definiert, so dass diese Methoden in T-SQL "sichtbar" sind.

Wenn der Deploy-Vorgang direkt aus Visual Studio 2005 heraus gestartet wird, kümmert sich die IDE um alle diese Details. Wenn die Assembly von Hand installiert werden soll, muss man die Schritte nacheinander ausführen:

USE mylibrary
GO

CREATE ASSEMBLY [CLRTrigger] 
  AUTHORIZATION [dbo]
  FROM 'C:\Programme\CLRTrigger.dll' 
  WITH PERMISSION_SET = SAFE
GO

CREATE TRIGGER [dbo].[CLRTriggerDemo]  
  ON [dbo].[ClrTriggerTbl]  
  AFTER  INSERT, UPDATE 
AS 
  EXTERNAL NAME [CLRTrigger].[Triggers].[CLRTriggerDemo];
GO

Wenn die im SQL Server 2005 installierte Assembly auch im Debugger schrittweise untersucht werden soll, muss zusätzlich über ALTER ASSEMBLY auch die .pdb-Datei mit den .NET Debugging Symbols mit in die sys.assembly_files-Tabelle kopiert werden. Dieser Schritt passiert immer dann automatisch, wenn die Installation direkt aus Visual Studio 2005 heraus über den Deploy-Menüeintrag des Projekts erfolgt.

[SCREAMI]

Hallo Leute...

mir ist das mit dem SQLCLR auch neu. Deshalb nochmal eine Verständnisfrage...

Mein Vorhaben: Ich möchte auf einem Rechner eine WinForm Applikation (.NET Framework 2.0) installieren. Diese soll Daten in einem Diagramm darstellen. Das soll geschehen, sobald in die SQL DB 2005 ein Datensatz geschrieben wurde. Das heißt:

- Ich schreibe einen Trigger, der auf das Assembly verweist
- Ich mache aus meiner Klasse, in der ich den Trigger beschrieben habe, eine .DLL
- Erstelle ein neues Assembly im DBMS und verweise auf die .DLL


Nun sollte immer, wenn ein Datensatz geschrieben wurde, es eine Rückmeldung auf meine Klasse geben, dessen Ergebnis ich dann verwenden kann.

Habeich dasso im Groben richtig verstanden ?

gruß,
screami

[Andreas Kosch]

Hallo,

für die beschriebene Aufgabe sind die Alternativen

• SQL Server Query Notification
• SQL Server 2005 Notification Services

geeignet, aber nicht die SQLCLR-Implementierung. Die Aufgabe wird über die Query Notification am einfachsten zu realisieren sein, da im .NET Framework dafür die Klasse SqlDependency einsatzfertig vorliegt.