SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

SQL CLR Integration – Test

Eine kurze Erweiterung zum letzten Artikel: siehe [hier]

Möchte ich die erstellte Funktion testen und debuggen, dann kann ich das auch direkt aus dem Visual Studio heraus. Dazu gibt es im erstellten Projekt eine bereits vorhandene Test.sql, die dazu dienen soll eigene Funktionen etc. auszuführen, zu testen und zu debuggen.

image

Mit Rechtsklick auf die Test.sql und “Set as default debug script” wird dieses Skript immer beim Debuggen ausgeführt.

image

Nun kann ich im Skript meine Funktion ausführen und ein paar Tests durchführen. Beispiel:

IF dbo.fDNSCheck('MyDNSName') = 1
PRINT 'OK'
IF dbo.fDNSCheck('NotMyDNSName') = 0
PRINT 'OK'  

Dazu muss auf dem SQL Server allerdings der Remote Debugger installiert sein (msvsmon.exe). Weitere Informationen dazu gibt es [hier]Außerdem müssen einige Berechtigungen als Voraussetzung vorhanden sein (siehe ebenso der Link). Sind alle Voraussetzungen vorhanden, dann kann per F5 das Skript gestartet werden, es können Haltepunkte im .NET Code gesetzt werden etc.

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.

yUML – UML Diagramme tippen statt zeichnen

Habe soeben den Dienst “yUML” entdeckt => http://yuml.me/. Auf dieser Seite kann man mit einer einfachen Beschreibungssprache UML Klassendiagramme, Aktivitätsdiagramme und Use Case Diagramme zeichnen.

Aus dem Text:

[Fortbewegungsmittel|VMax;AnzahlPassagiere|Fortbewegen()]^[Auto],[Fortbewegungsmittel]^[Fahrrad],[Fortbewegungsmittel]^[Fuß]

…wird beispielsweise dieses Diagramm:

image

und aus:

[BlogLeser]-(Kommentieren)
[Admin]-(Beitrag schreiben)
[Admin]-(Login).jpg

wird:

image

Ziemlich cool!

Reporting Services – Reports auf einen anderen Server übertragen

Ich habe hier zig Reports und möchte diese auf einen anderen Server umziehen. Dazu muss ich aber nicht jedes Report-Projekt öffnen und neu bereitstellen. Nein! Der Reporting Services Scripter http://www.sqldbatips.com/showarticle.asp?ID=62 kann mir diese Arbeit zum Glück abnehmen. Einfach den Quellserver, den Zielserver und die jeweiligen Pfade zur RS.exe konfigurieren – zu übertragende Reports auswählen und schon ist der neue Server gefüllt.

image

Visual Studio (C#) – Fehlende Using-Direktiven (halb)automatisch einfügen

Ein kleiner Tipp. Teilweise tippt man munter drauf los, ohne vorher die benötigten “using” Direktiven anzugeben. Visual Studio merkt das und unterstreicht die verwendete Klasse rot.

sqlcon

Nun kann man – entweder die using-Direktive manuell eingeben, oder – man drückt [STRG] + [.] und ein Kontextmenü klappt auf und die Direktive kann direkt automatisch eingebunden werden.

sqlcon2

Funktioniert mit Visual Studio 2008 und 2010 (ob es in 2005 schon ging, habe ich nicht getestet).

ASP .NET - Bild aus Datenbank abrufen und anzeigen

In einem anderem Beitrag [hier]habe ich bereits beschrieben, wie einfach es ist, ein Bild auf einer Webseite hochzuladen und mit wenigen Zeilen C#-Code in einer SQL Server Datenbank zu speichern. Interessant ist natürlich auch der Rückweg: Also wie kommt das Bild wieder aus der Datenbank heraus und kann im Browser angezeigt werden?

Auch das geht wieder in einfachen Schritten.

Schritt 1 – Eine ASPX-Seite zur Ausgabe eines Bildes

Um ein Bild aus der Datenbank zu lesen und im Browser anzuzeigen, erstelle ich eine neue Seite “GetPicture.aspx”. Diese Seite hat keinen eigentlichen Inhalt, sondern nur den Zweck ein Bild anhand des Namens (oder eines beliebigen anderen Parameters, z. B. einer ID) aus der Datenbank zu holen und an den Browser zu senden.

protected void Page_Load(object sender, EventArgs e)
{
//Variable für die Bilddaten
Byte[] myFile;
//der Dateiname aus dem QueryString
string myFileName = Request.QueryString["FileName"];
if (myFileName != null)
{
//Verbindung zum Server
SqlConnection con = 
new SqlConnection("Data Source=192.168.1.13;Initial Catalog=BildDB;User ID=xxx;Password=xxx");
//Insert Statement mit den entsprechenden Parametern
SqlCommand cmd = new SqlCommand("SELECT picture FROM pictures WHERE name = @name", con);
//Parameter für den Bildnamen
SqlParameter paramName = new SqlParameter("@name", System.Data.SqlDbType.VarChar);
paramName.Value = myFileName;
cmd.Parameters.Add(paramName);
//Daten lesen
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() ) //im Beispiel interessiert nur der erste Satz
{
myFile = (Byte[])reader["picture"];
Response.ContentType = "image/jpeg"; //ContentType setzen
Response.BinaryWrite(myFile);        //Bild ausgeben
}
con.Close();
}      

Ruft man nun diese Seite mit einem in der Datenbank vorhandenen Bildnamen auf, dann wird das Bild direkt im Browser angezeigt.

image

Schritt 2 – Bilder in einem Gridview anzeigen

Um nun mehrere Bilder aus der Datenbank anzuzeigen kann bspw. ein GridView verwendet werden. Auf einer zusätzlichen Seite “Anzeigen.aspx” habe ich eine neue SQLDatasource und ein GridView erstellt.

        <!-- Die Datenquelle -->
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:BildDBConnectionString %>" 
SelectCommand="SELECT picture, name, addedat FROM [pictures]">
</asp:SqlDataSource>
<!-- Anzeige im Gridview -->
<asp:GridView ID="GridView1" runat="server" 
DataSourceID="SqlDataSource1" 
AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
<asp:BoundField DataField="addedat" HeaderText="addedat" 
SortExpression="addedat" />
<asp:ImageField DataImageUrlField="name" 
DataImageUrlFormatString="~/GetPicture.aspx?Filename={0}">
</asp:ImageField>
</Columns>
</asp:GridView>

Das “Besondere” an diesem GridView ist nun, dass es ein ImageField hat, welches direkt die in Schritt 1 erstellte ASPX-Seite aufruft und den aus der Datenbank gelieferten Dateinamen übergibt.

Das Endergebnis sieht dann so aus:

image 

Das Projekt (Visual Studio 2010) hängt zum Anschauen direkt hier am Beitrag. Fragen sind natürlich immer gern gesehen ;)

Bildverwaltung.zip (32,13 kb)

Server Trigger – DROP DATABASE verhindern

Nur ein kurzer Tipp: Möchte man verhindern, dass Datenbanken auf einem Server gelöscht werden, kann man dies durch einen Trigger auf dem Server tun.

CREATE TRIGGER TR_LoeschenVerhindern 
ON ALL SERVER 
FOR DROP_DATABASE 
AS 
PRINT 'Um eine Datenbank zu löschen muss erst der Trigger TR_LoeschenVerhindern deaktiviert werden.'
ROLLBACK;

Wird nun versucht die Datenbank zu löschen, gibt es folgende Meldung:

Um eine Datenbank zu löschen muss erst der Trigger TR_LoeschenVerhindern deaktiviert werden.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted…
…so kann niemand mehr sagen, die Datenbank ist "ausversehen” abhanden gekommen ;-)

Fehler beim Erstellen des Steuerelements ...

Beim Öffnen einer Website in Visual Studio kann es vorkommen, dass einige Steuerelemente nicht korrekt angezeigt werden können.

Die Fehlermeldung (als Beispiel): "Fehler beim Erstellen des Steuerelements: <Elementname> asp:UpdatePanel konnte nicht für die ContentTemplate-Eigenschaft festgelegt werden." erscheint.

Woher diese Fehlermeldung kommt, weiß ich leider (noch) nicht. Auf jeden Fall kann Sie behoben werden: Einfach das Projekt schließen und alle Dateien in "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files" löschen und danach das Projekt wieder öffnen.

Eine weitere Möglichkeit soll sein diese Verzeichnis zu leeren: C:\Dokumente und Einstellungen\<USERNAME>\Lokale Einstellungen\Anwendungsdaten\Microsoft\VisualStudio\9.0\ProjectAssemblies (hat bei mir nicht funktioniert)

OUTPUT – Gelöschte oder eingefügte Sätze ausgeben

Um zu prüfen, bzw. anzuzeigen welche Sätze nach einem DELETE gelöscht wurden, kann die OUTPUT Klausel verwendet werden. Hier ein kleines Beispiel.

Wir haben eine Tabelle mit einer Identitätsspalte und einem Wert und fügen dort ein paar Werte ein:

CREATE TABLE toDelete
(
id int primary key identity(1,1),
value int
)
insert into todelete values (1)  
insert into todelete values (2)
insert into todelete values (3)
insert into todelete values (4)

Will ich nun alle Datensätze löschen, deren Wert > 2 ist und diese auch noch ausgeben (um diese beispielsweise einem Benutzer anzuzeigen), dann verwende ich die OUTPUT-Klausel:

delete toDelete
OUTPUT DELETED.value
WHERE value > 2

Ergebnis ist folgendes:

output

Das gleiche funktioniert auch mit der Tabelle INSERTED, wenn zum Beispiel neue Werte der Identitätsspalte angezeigt werden sollen.

INSERT INTO toDelete 
OUTPUT INSERTED.*
VALUES (5)

Ergebnis hier ist:

image

Damit kann man sich ein weiteres SELECT auf die Tabelle sparen.