Der einfachste Weg verwalteten Code (Managed Code – .NET Runtime – CLR) und SQL Server zu „verheiraten“ ist die Entwicklungsumgebung Visual Studio (2005 – 2010) zu verwenden. Visual Studio vereinfacht die Entwicklung drastisch, z.B. durch automatisierte Deployment Prozesse. Trotzdem werden wir den Prozess hier „per Hand“ erklären. Natürlich funktioniert dieser Weg auch mit jeder beliebigen .NET Entwicklungsumgebung.
Für jedes unterstützte Stück „Managed Code“ existieren im Namespace Microsoft.SqlServer.Server entsprechende Attribute welche Methoden für den SQL Server verfügbar machen. Dies sind im folgenden:
SqlProcedure SqlFunction SqlUserDefinedAggregate SqlUserDefinedType SqlMethod
Auf die zwei häufigsten dieser Attribute möchte ich im folgenden nun kurz eingehen.
SqlProcedure
Die am meisten verwendeten – und nützlichsten – Features in eigenen Projekten sind wahrscheinlich Stored Procedures. Da diese aber meist in T-SQL entwickelt werden lassen sich damit meist nur schwierig komplexere Aufgaben abbilden. Zum Glück lassen sich Stored Procedures auch mit C# (oder einer anderen .NET kompatiblen Sprache) entwickeln. Jedoch gibt es gewisse Voraussetzungen für das entwickeln von „Managed Stored Procedures“.
- Die enthaltende Klasse muss public sein
- Die entsprechende Methode muss public static sein
Das sind alle Anforderungen. Klingt doch gar nicht mal so schlimm, oder? 😉 Um das ganze noch einmal zu verdeutlichen, hier ein Beispiel:
using System;
using Microsoft.SqlServer.Server;
namespace abapguru.SqlServer {
public class SqlServerIntegration {
[SqlProcedure("spDoSomething")]
public static void DoSomething() {
// Hier passiert was...
}
}
}
Hier gibt es also zu „normalen“ statischen Methoden kaum unterschiede. Das SqlProcedure Attribute macht die Methode DoSomething für den SQL Server zugreifbar. Der einzige unterstützte Parameter ist ein String der einen alternativen Namen für die Stored Procedure angibt. Im Normalfall ist dieser immer gleich dem Namen der Methode.
SqlFunction
Funktionen für den SQL Server zu erstellen ist fast so einfach wie Stored Procedures, mit dem einen unterschied dass die Methode einen Rückgabewert benötigt.
using System;
using Microsoft.SqlServer.Server;
namespace abapguru.SqlServer {
public class SqlServerIntegration {
[SqlFunction()]
public static int CalculateSomething(int x, int y) {
return (x + y);
}
}
}
Das Attribute SqlFunction unterstützt folgende Parameter:
- DataAccess
Bestimmt, ob für die Berechnung auf Daten zugegriffen werden muss oder ob die Berechnung autonom durchgeführt werden kann - IsDeterministic
Bestimmt, ob die Funktion immer den selben Wert zurückgibt, unabhängig vom Status des SQL Servers - IsPrecise
Bestimmt, ob das Ergebnis „wissenschaftlich präzise“ ist (z.B. für mehrdimensionale Berechnungen nötig) - Name
Bestimmt den Namen der Funktion innerhalb des SQL Servers
Assembly in SQL Server einbinden
Angenommen wir haben nun eine fertige Assembly. Um diese innerhalb des SQL Servers verwenden zu können musse diese nur noch installiert eingebunden werden. Dies wird über einen T-SQL Befehl bewerkstelligt.
CREATE ASSEMBLY <NAME> FROM <PATH>
Beispiel
CREATE ASSEMBLY abapguru.SqlServer FROM 'C:\Assemblies\abapguru.SqlServer.dll'
Ich denke die Erklärung der Syntax kann ich mir sparen – das ist jedermann logisch oder 🙂
Eine Anmerkung noch: Standardmäßig wird die Assembly mit der „Berechtigung“ installiert, nur „safe Code“ auszuführen. Die CLR Integration hat die Notation von drei verschiedenen Ausführungsberechtigungen; diese werden auf Assemblyebene gesetzt.
- SAFE
Nur CLR ist ausführbar. Kein Zugriff auf externe Resourcen - EXTERNAL
Zugriff auf externe Daten ist erlaubt. Darunter fallen z.B. Dateisysteme, Eventlog, Netzwerk. Kein Zugriff auf „unsafe Code“ or „interop code“. - UNSAFE
Keine Limitation, somit Zugriff auf alle Resourcen erlaubt
Diese Ausführungsberechtigungen können beim Importieren der Assembly mit Hilfe des WITH PERMISSION_SET Zusatzes angegeben werden.
CREATE ASSEMBLY <NAME> FROM <PATH> WITH PERMISSION_SET=(SAFE|EXTERNAL|UNSAFE)
Ähnlich wie alle DDL Befehle gibt es analog zu CREATE natürlich auch DROP und ALTER. Hier verweise ich allerdings auf die MSDN Dokumentation 😉
Managed Code auf T-SQL mappen
Nachdem die Assembly nun dem SQL Server bekannt ist benötigen wir nur mehr einen letzten, aber wichtigen Schritt. Die in CLR Codierten Funktionen und Stored Procedures müssen nun noch auf äquivalente T-SQL Befehle „gemappt“ werden. Dazu benötigen wir (schon wieder) einige DDL Befehle. Einige Teile davon dürften aber durchaus dem ein oder anderen bekannt sein, der schon seine Erfahrungen mit Stored Procedures gemacht hat.
CREATE PROCEDURE <NAME> AS EXTERNAL NAME <ASSEMBLY>.<TYPE>.<METHOD>
Beispiel
CREATE PROCEDURE spDoSomething AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.DoSomething
Dieses kleine Stück T-SQL erstellt eine neue Stored Procedure, allerdings mit Referenz auf das Coding innerhalb unserer eigens erstellten Assembly. Nun können wir mittels EXEC die Stored Procedure aufrufen.
EXEC spDoSomething
Sollte die Stored Procedure parametrisiert sein, können die Parameter (wie gewohnt) in T-SQL spezifizert werden. Achtung! Die Signatur muss hier exakt identisch sein. Beispiel:
CREATE PROCEDURE spDoSomethingWithParams @NUMBER INT AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.DoSomethingWithParams
Ähnlich funktionerit das ganze auch mit Funktionen
CREATE FUNCTION CalculateSomething ( @X INT, @Y INT ) RETURNS INT AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.CalculateSomething
Auch hier kann, nach absetzen des obigen Codes die Funktion über SELECT aufgerufen werden.
SELECT CalculateSomething(5, 5)
Fehler 6506 vorbeugen
Solltet Ihr den Fehler mit der Nummer 6506 (Could not find Type in Assembly) beim mappen der Funktionen und SPs bekommen, hilft mit ziemlicher sicherheit folgender Trick. Als erstes sollte sichergestellt werden dass die CLR Integration prinzipiell aktiviert ist. Wenn nicht, bitte mit folgendem Befehl aktivieren.
EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE
Wenn dieser Schritt erledigt ist und der Fehler euch immer noch nervt hilft folgendes. Aus einem (mir) unerklärlichen Grund muss der Assemblyname im Klassennamen noch einmal auftauchen. Beispiel:
Liefert den Fehler 6506
AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.CalculateSomething
Läuft ohne Fehler durch
AS EXTERNAL NAME [abapguru.SqlServer].[abapguru.SqlServer.SqlIntegration].CalculateSomething
Geschafft!
Eine Anmerkung noch am Ende: Bitte extremste Sorgfalt beim Entwickeln von CLR Code welcher in SQL Server integriert wird. Sollte z.B. eine unbehandelte Ausnahme innerhalb des Codings auftreten kann es sein dass der SQL Server die komplette AppDomain entladen muss, was zu enormen Performanceproblemen führen kann.
Bei gelegenheit werde ich noch einen Post zum Thema Performance tippen – mal sehen wie es die Zeit zulässt.