SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

SQL CLR Integration - .NET Code im SQL Server

Der SQL Server bietet die Möglichkeit CLR-Code direkt auszuführen und somit erweiterte Funktionen zu erfüllen. Ein kleines Beispiel: Ich möchte prüfen, ob mein SQL Server auf einen bestimmten DNS-Namen im Netzwerk hört. Dazu ist mir mit Bordmitteln keine Möglichkeit bekannt.

Zunächst einmal muss die CLR-Integration aktiviert werden.

sp_configure 'clr_enable', 1
RECONFIGURE

Ergebnis ist:

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jetzt können wir loslegen. Ich erstelle im Visual Studio ein neues Datenbankprojekt und nenne es DNSChecker:

image

Im darauf folgenden Dialog muss eine Verbindung zu einer Datenbank hergestellt werden (die Datenbank, in der die Assembly letztendlich liegen soll und aus der sie ausgeführt wird).

image

Nachdem die Verbindung eingetragen ist, habe ich über einen Rechtsklick auf das Projekt und “Add” die Möglichkeit verschiedene Objekte zu erstellen:

image

Die einzelnen Möglichkeiten sind:

  • User-Defined Function – Eine Funktion mit Eingabeparametern und einem Ausgabeparameter
  • Stored Procedure – Ähnlich einer T-SQL Prozedur
  • Aggregate – Eine Aggregatfunktion, wie SUM(), AVG() etc.
  • Trigger – Ein CLR Trigger
  • User-Defined Type – Ein eigener “Datentyp”

Für das Beispiel beschränke ich mich auf das Erstellen einer Funktion, die mir nach Eingabe eines DNS-Namens zurückgibt, ob mein Server auf diesen Namen hört, oder nicht. Also => User-Defined Function

Nach Erstellen der Funktion erhalte ich einen bereits funktionsfähigen Rumpf mit einer Art “Hello World”.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fDNSCheck()
{
// Put your code here
return new SqlString("Hello");
}
};

Würde ich die Funktion nun aufrufen würde ich ganz einfach “Hello” als Ergebnis erhalten. Ich möchte allerdings “wahr” oder “falsch” als Bit-Wert zurückgeben und benötige als Eingabeparameter noch einen string (den DNS-Namen)

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
// Put your code here
return new SqlBoolean(false);
}
};

 

 

Nun fehlt noch die eigentliche Überprüfung, ob der Server auf den Namen hört.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
bool result = false;
try
{
//Get all ips for DNS-Name
IPHostEntry ip = Dns.GetHostEntry(sDnsName);
IPAddress[] IpA = ip.AddressList;
//check if a local ip for this name exists
foreach (IPAddress ipDNS in IpA)
{
foreach (IPAddress ipLocal in Dns.GetHostEntry(Dns.GetHostName()).AddressList)
{
if (ipDNS.ToString() == ipLocal.ToString())
{
result = true;
}
}
}
}
catch (Exception ex)
{
result = false;
}
return new SqlBoolean(result);
}

Dazu iteriere ich über alle für den DNS-Namen gefundenen IP-Adressen und vergleiche mit allen auf dem Server gefundenen IP-Adressen. Wird eine Übereinstimmung gefunden wird “true” zurück gegeben. Nun ist die Funktion fertig und könnte arbeiten. Da die Funktion allerdings die Berechtigungsstufe “unsafe” benötigt (näheres siehe unter http://technet.microsoft.com/de-de/library/ms131071.aspx) muss die Datenbank in der diese laufen soll die Eigenschaft “TRUSTWORTHY ON” haben. Also…

ALTER DATABASE CLRTestDB SET TRUSTWORTHY ON

…und für die Assembly muss eingestellt werden, dass der Zugriff in der Stufe “unsafe” erfolgen soll (in den Projekteigenschaften).

image

Nun kann ich die Assembly per “Deploy” auf dem Server installieren. Führe ich die Funktion nun aus und mein Server hört z.B. auf den Namen “MyDNSName”, dann gibt die Funktion “1” für “wahr” zurück.

image

Dieses kleine Beispiel hat gezeigt, wie einfach es ist den SQL Server mit eigenen .NET Funktionen zu erweitern.

blog comments powered by Disqus