SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

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.)

Rangfolge mit DENSE_RANK() und RANK() feststellen

Vor kurzem habe ich einiges über die Funktion ROW_NUMBER() beschrieben (hier und hier). Es gibt weitere nützliche Funktionen um Ergebnisse eines SELECTs zu nummerieren: DENSE_RANK() und RANK(). Damit können Ergebnisse nach Werten nummeriert werden. Was genau heißt das? Nehmen wir an, wir haben eine Tabelle mit Mitarbeitern und deren Gehalt.

CREATE TABLE employees
(
emp_no int primary key identity(1,1),
emp_name varchar(100),
salary money
)

…und auch ein paar Datensätze.

INSERT INTO employees VALUES ('Hans', 1000)  
INSERT INTO employees VALUES ('Peter', 5000)
INSERT INTO employees VALUES ('Marie', 5000)
INSERT INTO employees VALUES ('Claudia', 2000)
INSERT INTO employees VALUES ('Peer', 3000)
INSERT INTO employees VALUES ('Sandra', 8000)
INSERT INTO employees VALUES ('Maria', 4000)

Um nun die Personen nach dem Gehalt zu “sortieren” kann DENSE_RANK() oder RANK() (oder natürlich auch beides :-)) verwendet werden.

SELECT emp_name
, salary
, DENSE_RANK() OVER (ORDER BY salary) RangOhneLuecke
, RANK() OVER (ORDER BY salary) RangMitLuecke
FROM employees

Liefert folgendes Ergebnis (die Funktion der OVER-Klausel habe ich hier beschrieben):

dense_rank

Wie man sieht sind die Mitarbeiter aufsteigend nach Gehalt sortiert mit einem “Rang” versehen. Dabei fällt auch auf, dass Peter und Marie, da sie das gleiche Gehalt haben den gleichen Rang haben.

Ebenso wird hier auch gleich der Unterschied zwischen DENSE_RANK() und RANK() deutlich. DENSE_RANK() nummeriert die Abfolge einfach durch, “erlaubt” doppelte Nennungen und zählt dann einfach weiter, während RANK() bei Doppelnennungen danach die nächstgrößere Zahl für die Zählung verwendet.

 

Anderes Beispiel: Möchte man bei einem Sportwettbewerb zulassen, dass es mehrere erste Plätze und danach trotzdem noch den zweiten und dritten Platz gibt sollte man DENSE_RANK() verwenden. Soll es maximal drei “Beste” geben, dann sollte RANK() verwendet werden. Sind hier drei Personen gleich gut bekommen alle den ersten Platz, der nächste geht allerdings leer aus. :-(

Wie immer mehr auch hier und hier.

 

Report ohne Toolbar anzeigen - Report einbetten

SSRS Berichte lassen sich bspw. über einen IFRAME in HTML-Seiten einbinden. Dabei stört jedoch, wenn man nur die Daten (bei festen Parametern) sehen will, dass ständig die Toolbar mit angezeigt wird.

Möchte man das nicht kann man an den aufrufenden URL einfach ein “&rc:Toolbar=false” anhängen – und die Toolbar ist verschwunden.

Ein vollständiger URL sieht dann bspw. so aus:

“http://yourserver/reportserver?%2fReportFolder%2fReportName&rs:Command=Render&rc:Toolbar=false”

Mehr Informationen über URL-Parameter für Reports gibt es hier bei Microsoft: http://msdn.microsoft.com/en-us/library/ms152835.aspx

Vergleich innerhalb einer Tabelle mit ROW_NUMBER()

Ich musste neulich innerhalb einer Tabelle vergleichen, wie “weit” Datumsangaben voneinander entfernt sind. Solche Fragen kann man auch mit ROW_NUMBER() beantworten. Einen Beitrag über die grundsätzliche Funktion von ROW_NUMBER() habe ich bereits hier geschrieben: Klick mich

Hier noch einmal das Erstellungsskript für die Tabelle mit den Geburtsdaten:

CREATE TABLE tBirthdates
(  
Name VARCHAR(50),
Gender bit, -- 0 = male, 1 = female
Birthdate DATETIME
)
GO
INSERT INTO tBirthdates VALUES ('Peter', 0, '19800502')
INSERT INTO tBirthdates VALUES ('Mary', 1, '19810603')
INSERT INTO tBirthdates VALUES ('Hans', 0, '19840830')
INSERT INTO tBirthdates VALUES ('Pedro', 0, '19720502')
INSERT INTO tBirthdates VALUES ('Mario', 0, '19651002')
INSERT INTO tBirthdates VALUES ('Maria', 1, '19850221')
INSERT INTO tBirthdates VALUES ('Lena', 1, '19601231')
GO

Um nun die Abstände der Geburten herauszufinden, sortiert nach dem Geburtsdatum kann wieder ROW_NUMBER() verwendet werden.

SELECT a.Name, b.Name, a.Birthdate, b.Birthdate, DATEDIFF(d, a.Birthdate, b.Birthdate) Diff FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Birthdate) Nummer
, Name
, Birthdate
FROM tBirthdates
) a
INNER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY Birthdate) - 1 Nummer 
, Name
, Birthdate
FROM tBirthdates
) b ON a.Nummer = b.Nummer

Dieses SELECT-Statement erzeugt folgendes Ergebnis:

row_number

Daraus lässt sich sortiert in der Spalte Diff erkennen, wie weit die Geburten jeweils auseinander lagen. Das Beispiel ist zwar etwas konstruiert, allerdings gibt es tatsächlich Anwendungsfälle, bei denen so etwas nützlich sein kann :-)

IIS Log für SQL Server konfigurieren und analysieren

Hier auf dieser Seite findet sich eine Beschreibung, wie das Logging des IIS auf den SQL Server umgestellt werden kann. Das Log wird dann in eine Tabelle geschrieben aus der jeder einzelne Zugriff auf den IIS ersichtlich ist.

Link zum Artikel:  http://www.databasejournal.com/features/mssql/article.php/3646171/Using-SQL-for-IIS-Web-Logs-Part-1.htm

Nun suche ich noch ein Tool, mit dem das Log möglichst einfach und grafisch ausgewertet werden kann, also Anzahl Zugriffe, Benutzer etc...bisher habe ich leider nichts gefunden, was das direkt kann. 

SQL Server Tools

Hier eine Liste von Tools für den SQL Server und rings herum, die sich bei mir im Laufe der Zeit angesammelt haben. Ich werde die Liste im Laufe der Zeit erweitern…

SQL Server Database Copy Tool
Das beste ;-) Tool um Datenbanken von einem SQL Server zu einem anderen zu kopieren. Mehr dazu habe ich in diversen Posts hier geschrieben: http://www.sql-asp-blog.de/category/SQL-Server-Database-Copy-Tool.aspx

Download hier: http://dbcopytool.codeplex.com/

SSMS Toolpack
Nützliches Add-In für das Management Studio. Tools wie Query Execution History etc. Mehr dazu habe ich hier geschrieben: http://www.sql-asp-blog.de/post/SSMS-Toolpack.aspx

Download hier: http://www.ssmstoolspack.com/

SQL Inform
JAVA-Applikation um SQL Code (nicht nur T-SQL) mit etlichen Optionen zu formatieren. Läuft direkt im Browser oder Stand-Alone.

Download hier: http://www.sqlinform.com/

Idera SQL check
kostenloses Tool um einige wichtige Performance Counter zu überwachen.

Download hier: http://www.idera.com/Products/Free-Tools/SQL-check/

Idera SQL job manager
Dieses Tool bietet eine grafische Übersicht (wie ein Outlook-Kalender) mit den gelaufenen und geplanten Agent-Jobs. Außerdem können damit Zeitpläne bearbeitet werden etc.

Download hier: http://www.idera.com/Products/Free-Tools/SQL-job-manager/

jDiskReport
Java-Anwendung. Durchsucht ein definiertes Verzeichnis und zeigt die Größenverteilung der einzelnen Verzeichnisse und Dateien an. Klasse um große Dateien zu finden oder einfach nur um aufzuräumen.

Download hier: http://www.jgoodies.com/freeware/jdiskreport/

Notepad++
Erweitertes Notepad mit Tabs, Syntax-Highlighting etc.. Braucht jeder :-)

Download hier: http://notepad-plus.sourceforge.net/de/site.htm

SQL Server Performance Dashboard
In SSMS integrierte Reports, die – wie der Name schon sagt – Aufschluss über die momentanen Aktivitäten auf dem SQL Server geben. Einen super Artikel über die Reports gibt es hier: http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

Download hier: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc

ROW_NUMBER()

Mit Hilfe von ROW_NUMBER() lassen sich tolle Sachen machen. Die einfachste Anwendung ist die Nummerierung von Datensätzen nach einer festgelegten Sortierreihenfolge.

Ein kleines Beispiel:

Wir haben die Tabelle tBirthdates mit den Geburtsdaten einiger Leute.

CREATE TABLE tBirthdates
(  
Name VARCHAR(50),
Gender bit, -- 0 = male, 1 = female
Birthdate DATETIME
)
GO
INSERT INTO tBirthdates VALUES ('Peter', 0, '19800502')
INSERT INTO tBirthdates VALUES ('Mary', 1, '19810603')
INSERT INTO tBirthdates VALUES ('Hans', 0, '19840830')
INSERT INTO tBirthdates VALUES ('Pedro', 0, '19720502')
INSERT INTO tBirthdates VALUES ('Mario', 0, '19651002')
INSERT INTO tBirthdates VALUES ('Maria', 1, '19850221')
INSERT INTO tBirthdates VALUES ('Lena', 1, '19601231')
GO

Um nun die Geburtstage aller sortiert nach dem Datum zu nummerieren folgendes SQL-Statement:

SELECT Reihenfolge = ROW_NUMBER() OVER (ORDER BY Birthdate)
,*
FROM tBirthdates       

Gibt als Ergebnis die durchnummerierte Reihenfolge des Geburtsdatums.

Row_number_1

Zur Erläuterung: ROW_NUMBER() benötigt immer eine Sortierreihenfolge in der OVER Klausel um entscheiden zu können, wo die Zählung anfängt und wo sie aufhört. Doch ROW_NUMBER() kann noch mehr. Möchte man die Geburtsdaten durchnummeriert nach Geschlecht hilft folgendes SQL-Statement:

SELECT Reihenfolge = ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Birthdate)
,*
FROM tBirthdates       

Hier wird in der OVER Klausel nach Gender, also dem Geschlecht partitioniert. Das kann man sich vorstellen wie eine Gruppierung. ROW_NUMBER() zählt die Einträge pro Gruppe durch (wieder sortiert nach dem Geburtsdatum). Ergebnis siehe Screenshot.

Row_number_2

Das ist es im Grunde auch schon. Mehr Informationen gibt es u.a. hier: http://msdn.microsoft.com/de-de/library/ms186734.aspx

SSF – Redgate SQL Prompt

Eigentlich will ich ja hier keine Werbung machen. Tue ich aber hiermit doch einmal :-) Es gibt ja einige Tools, die die Arbeit mit dem SQL Server einfacher machen sollen. Eins davon – und in meiner persönlichen Hitliste ganz weit oben – ist SQL Prompt von RedGate.

Microsoft hat zwar im SQL Server 2008 bzw. im Management Studio dafür Intellisense integriert um bspw. schneller auf Tabellen, Variablen etc. zuzugreifen, allerdings hat dieses integrierte Intellisense nicht halb so viel Charme wie die Funktionen von SQLPrompt.

Eine Art Intellisense bietet das Tool auch. D.h. wird angefangen ein Tabellenname einzugeben bietet SQLPrompt Vorschläge an…an sich nix besonders tolles. Viel interessanter wird das Ganze bei JOIN Bedingungen. Es genügt “INNER JOIN” oder auch “ij” + [TAB] einzugeben und die Tabelle mit der man joinen möchte, schon schlägt SQL-Prompt anhand der FKs (oder auch Spaltennamen [konfigurierbar]) die richtige Join-Bedingung vor.

Was gibt es noch? Snippets! Jeder schreibt häufiger mal ähnliche oder gleiche Abfragen, weil es sich teilweise gar nicht lohnt diese zu speichern und zu öffnen wenn sie benötigt werden. Mit SQL Prompt sind bereits einige Snippets vorgefertig bspw. “ssf” für “SELECT * FROM”, aber man kann auch eigene definieren. So habe ich für die wichtigsten Datenbanken immer ein paar Snippets mit Kürzel um “Standard-Abfragen” schnell eingeben zu können.

Auf jeden Fall eine Empfehlung und wert es sich mal näher anzuschauen, auch wenn es nicht ganz kostenlos ist :-). => http://www.red-gate.com/products/SQL_Prompt/index.htm