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.

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)

Reimers Google Maps .NET Control

Ich spiele gerade ein wenig mit Google Maps herum – genauer gesagt mit dem Google Maps .NET Control von Reimers. Mit diesem Control ist es sehr einfach eine Google Map auf die eigene Seite zu bringen.

Kleines Beispiel:

<Reimers:GoogleMap ID="myGMap" width="100%" Height="100%" runat="server">
</Reimers:GoogleMap>

…dieses Markup genügt um eine Map auf der Seite anzuzeigen. Einen Center-Punkt und Zoom im Markup festzulegen ist auch kein Problem:

<Reimers:GoogleMap ID="myGMap" width="100%" Height="100%" runat="server" Zoom="7">
<Center Latitude="50" Longitude="10" />
</Reimers:GoogleMap>

Hinzufügen von Markern im Code-Behind funktioniert folgendermaßen. Der Code fügt einen Marker an Position 50, 10 ein. Außerdem wird ein InfoWindow definiert, welches sich beim Klick auf den Marker öffnet und das eingefügte HTML anzeigt.

GoogleMarker gm1 = new GoogleMarker("mrkClick", 50, 10);
gm1.ClientSideHandlers.OnClick = gm1.OpenInfoWindowHTML(myGMap, "<b>Hallo vom SQL-ASP-Blog!!!</b>");
myGMap.Overlays.Add(gm1);

…das sieht dann so aus:

marker

Das Control beherrscht auch eigene Callbacks, so dass man auf bestimmte Events reagieren kann. So können zum Beispiel Marker nur hinzugefügt werden, wenn eine bestimmte Zoomstufe erreicht ist etc. – alles ohne Neu laden der Seite.

Wie gesagt – ich spiele erst einmal ein wenig damit herum. Aufgefallen ist mir bisher, dass es anscheinend keine einfache Möglichkeit gibt das Control und gleichzeitig UpdatePanels auf einer Seite zu verwenden. Finde ich etwas schade…

Trotzdem ist das Control auf jeden Fall einen Blick wert, wenn man mal einfach eine Google Map auf eine Seite bringen möchte.

Zu finden gibt es das Google Maps .NET Control hier: http://www.reimers.dk/. Für den Download ist eine Anmeldung notwendig.

ASP .NET Bild Upload in SQL Server speichern

Hier noch einmal ein Beitrag, wie es möglich ist Bilder (oder auch andere Dateien) von einer ASP .NET Seite aus im SQL zu speichern.

Eigentlich habe ich das mit diesem (hier) Beitrag schon beschrieben, aber hier nochmal konzentriert nur auf das Thema “Bild hochladen und im SQL Server speichern”. Ob es nun Sinn macht die Datei als solches zu speichern oder nur einen Verweis (als Pfad) soll hier nicht diskutiert werden – die Antwort darauf ist sowieso meist: “Es kommt drauf an…” (außerdem gibt es für solche Zwecke zumindest ab SQL Server 2008 auch den FILESTREAM Datentyp)

Also los. Wie kann ein hochgeladenes Bild im SQL Server gespeichert und wieder abgerufen werden? Ganz einfach! In nur drei einfachen Schritten :-)

1. Webseite

Wir brauchen die entsprechende Webseite. Also erstelle ich eine neue im Visual Studio und fülle die Seite mit einem FileUpload Control und einem Button zum Upload (FileUpload1 und Button1).

file_upload_1

2. Datenbank

In der Datenbank brauchen wir eine Tabelle zum Speichern der Bilder

CREATE TABLE pictures
(
picture VARBINARY(MAX)
,Name VARCHAR(255) 
,AddedAt DATETIME
)

Die Spalte “picture” wird das eigentliche Bild enthalten, daher der Datentyp VARBINARY(MAX).

3. Datei speichern

Nun kommt die eigentliche “Arbeit”: Beim Klick auf “Upload” soll das Bild mit dem Dateinamen und dem Datum in der Datenbank gespeichert werden. Im Event Button1.Click füge ich folgenden Code hinzu:

protected void Button1_Click(object sender, EventArgs e)
{
//die Datei aus dem FileUpload Control 
Byte[] myFile;
myFile = FileUpload1.FileBytes;
//der Dateiname ohne Pfad
string myFileName;            
myFileName = System.IO.Path.GetFileName(FileUpload1.FileName);
//connection to sql server
SqlConnection con = new SqlConnection("Data Source=192.168.1.13;Initial Catalog=TestDB;User ID=sa;Password=blah");
//Insert Statement mit den entsprechenden Parametern
SqlCommand cmd = new SqlCommand("INSERT INTO pictures VALUES (@picture, @name, GETDATE())", con);
//Parameter für das Bild an sich
SqlParameter paramPicture = new SqlParameter("@picture", System.Data.SqlDbType.VarBinary);
paramPicture.Value = myFile;
cmd.Parameters.Add(paramPicture);
//Parameter für den Dateinamen
SqlParameter paramFilename = new SqlParameter("@name", System.Data.SqlDbType.VarChar);
paramFilename.Value = myFileName;
cmd.Parameters.Add(paramFilename);
//Verbindung öffnen und INSERT ausführen (Achtung, kein Exception-Handling)
con.Open();
cmd.ExecuteNonQuery();
con.Close();     
}

Ich denke der Code ist so verständlich, dass ich diesen hier nicht näher erläutern muss. Fragen natürlich immer gern :-)

Ruft man nun die Webseite auf, wählt eine Datei und klickt auf Upload wird diese im SQL Server gespeichert, was dann so aussieht:

picture_2

Nun ist die Datei erst einmal im SQL Server. Wie man diese dann auch wieder da heraus bringt um bspw. das Bild anzuzeigen schreibe ich später nochmal. (das habe ich inzwischen  [hier] getan.)

SQL Server Messages empfangen (ExecuteNonQuery)

Im SQL Server kann per Skript eine Nachricht ausgegeben werden. Ganz einfach per:

PRINT 'HALLO'

Um jetzt diese Nachricht in einer SQLConnection abzufangen gibt es nun eine einfache Möglichkeit. Zunächst wird eine Methode benötigt, die die Nachricht empfängt und weiterverarbeitet. Hier am Beispiel _con_InfoMessage(…).

private void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);                
}

Nun muss noch der EventHandler für das Ereignis festgelegt werden:

_con.InfoMessage += new SqlInfoMessageEventHandler(_con_InfoMessage);

Mit dieser Einstellung wird nach einem ExecuteNonQuery() die Nachricht, die vom SQL Server empfangen wird in der Methode _con_InfoMessage verarbeitet (und hier in diesem Fall einfach in die Konsole geschrieben).

Für das SQL Server Database Copy Tool wollte ich anzeigen, wie weit der Server bereits mit dem BACKUP bzw. dem RESTORE ist. Mit der obigen Einstellung wird wie gesagt das Event nur einmal am Ende von ExecuteNonQuery() ausgeführt. Für eine Fortschrittsanzeige soll es allerdings jedes Mal auftreten, wenn eine Nachricht empfangen wird. Dafür gibt es die Eigenschaft (für SQLConnection):

_con.FireInfoMessageEventOnUserErrors = true;

Diese Eigenschaft legt fest, dass das Ereignis jedes Mal ausgeführt wird. Wie am Namen “OnUserErrors” zu erkennen gilt dies nicht nur für Nachrichten, die mit PRINT erzeugt werden. Vielmehr werden Fehler in der Ausführung bis zu einem Schweregrad von 16 an die Methode weiter gegeben (normalerweise würde eine Exception bei ExecuteNonQuery() entstehen. Um nun an die richtigen Exceptions (also Fehler) ran zu kommen habe ich mir noch Folgendes gebastelt:

private void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if (e.Errors.Count > 0)
{
if (e.Errors[0].Class > 0)
{
//save error for use
_internalError += e.Message + " ";
}
}
Console.WriteLine(e.Message);             
}

Tritt also ein richtiger Fehler auf (Schweregrad > 0 [PRINT = Schweregrad 0]) wird dieser in _internalError gespeichert und ich kann diesen später nach ExecuteNonQuery() behandeln.

SQL Server Database Copy Tool – Start per Kommandozeile

Heute habe ich dem SQL Server Database Copy Tool noch eine, meiner Meinung nach, wichtige Funktion hinzugefügt. Jetzt kann eine SQL Server Datenbank auch per Kommandozeile kopiert werden. Nützlich wenn man Datenbanken öfter kopieren möchte (bspw. eine “frische” Version auf den Testserver) oder mehrere Datenbanken per Batch kopiert.

So sieht das dann aus:

db_copy_tool_cmd

Gestartet wird das Tool über die Kommandozeile per: dbcopytool FROM_SERVER TO_SERVER FROM_DB TO_DB. (startet man das Tool ohne Parameter wird es wie "normal", also mit Form gestartet)

Hierbei ist wichtig, dass die angegebenen Server mit genau dem Namen in der Konfiguration enthalten sind, da das Tool die Verzeichnisse für Backup und Restore daraus erhält.

SQL Server Database Copy Tool – neue Version auf CodePlex

Ich habe soeben eine neue Version des Database Copy Tool for SQL Server auf [Codeplex] hochgeladen. Was hat sich geändert:

  1. Ich habe es mit dem SQL Server 2005 und dem SQL Server 2008 getestet
  2. Kopieren von Dateien wird nun nicht mehr mit der Methode Microsoft.VisualBasic.FileIO.FileSystem.CopyFile durchgeführt. Stattdessen verwende ich einen Aufruf der CopyFileEx aus der Kernel32.dll. Praktisch: Damit kann ich den Fortschritt des Kopierens in meiner eigenen Form anzeigen, statt den Standard-Windows-Kopierdialog zu verwenden
  3. Ein wenig im Code aufgeräumt (aber nur ein Wenig :-))
  4. Ein neuer Dialog um einen Server in der Konfiguration hinzuzufügen (statt der Standard-InputBox)

Hier nochmal ein paar Screenshots

02_settings

03_addserver

Wie ich schon zwei Mal geschrieben hab – Was fehlt noch?

  • Anzeige des Fortschritts des BACKUP und des RESTORE – muss also irgendwie an die Meldungen, die sonst im Management Studio zu sehen sind rankommen
  • Kommentare im Code…;-)

Weiteres dazu in meinen vorherigen Posts [hier]und [hier]

DB Copy Tool für SQL Server – auf Codeplex

Das im Letzten Post erwähnte DB Copy Tool ist nun auch auf Codeplex zu haben: Link zu Codeplex.

Inzwischen hat sich hier auch noch einiges getan:

db_copy_tool_1

Die zu verwaltenden Server können nun über den Servers-Tab verwaltet werden.

db_copy_tool_2

Hier können Server manuell hinzugefügt werden, oder automatisch aus dem Netzwerk erkannt werden (Danke an volleyknallerfür die Tipps). Für den ausgewählten Server kann hier auch das eingestellte Datenverzeichnis und das Backupverzeichnis ausgelesen oder manuell gespeichert werden. Der Button "Add servers from network" fügt alle automatisch erkannten SQL Server im Netzwerk hinzu (ohne User und Passwort). Das funktioniert (und dauert auch so lange) wie im SQL Server Management Studio :-)

Was fehlt jetzt noch:

  • einiges Exception-Handling
  • Support für Server mit mehreren SQL-Server Instanzen
  • Test mit dem SQL Server 2005
  • eine Menge Kommentare und Verschönerungen im Code :-)