Fingerübungen - MS SQL Pivot

Nach drei Jahren MS SQL und ich finde mal wieder etwas, dass ich noch nicht kenne und ich auch nicht in der Schulung hatte. 

Bis vor heute, war mir nicht bewusst, dass man auch Pivot Ergebnisse in MS SQL schreiben kann.

Beispiel: Ich habe in der einen Spalte, mehrere verschiedene Themen, die ich hier Unkreativ "Sache 1" bis "Sache 3" genannt habe. Diese lassen sich am Ende so raus führen, dass am Ende daraus drei Spalten werden. Hätte ich das vorher gewusst, hätte ich viel bessere Abfragen schreiben können.

 DECLARE @myTable TABLE (Dinge VARCHAR(10), Menge INT)  
 INSERT INTO @myTable  
 (  
   Dinge,  
   Menge  
 )  
 VALUES  
 ( 'Sache 1', 10 ),  
 ( 'Sache 1', 2 ),  
 ( 'Sache 1', 5 ),  
 ( 'Sache 1', 77 ),  
 ( 'Sache 2', 10 ),  
 ( 'Sache 2', 1 ),  
 ( 'Sache 2', 2 ),  
 ( 'Sache 2', 3 ),  
 ( 'Sache 3', 10 ),  
 ( 'Sache 3', 10 ),  
 ( 'Sache 3', 10 )  
 SELECT [pvt].[Sache 1], pvt.[Sache 2], pvt.[Sache 3]  
 FROM @myTable t  
 PIVOT  
 (  
 SUM(Menge)  
 FOR Dinge IN([Sache 1], [Sache 2], [Sache 3])  
 ) AS pvt  

Fingerübungen - MS SQL Schnipselübung

Heute mal was sehr Kleines, das mehr als Fingerübung dient und auch die Aktivität meines Blogs zu halten. Ich bin mal wieder durch meine Notizen durch und suchte etwas, was ich an kleinen Sachen, die ich bereits angefangen habe. Und sehe da, ich habe hier noch ein paar SQL-Schnipsel.

In meinem aktuellen Projekt ist die Verwendung von SQL nicht weg zu denken und meine Skills haben sich inzwischen von Grundkenntnissen in Fortgeschritten gewandelt. Vom Experten bin ich noch weit weg, auch wenn andere der Meinung sind, daß man das auch nach wenigen Monaten sein kann. Aber die Anzahl der Jahre und Auseinandersetzen macht einen unterschied, wie sehr man in der Tiefe mit der Technologie vertraut ist und auch wie sehr die eine und andere Sprache sich im Verstand eingeübt hat.

Nichts für ungut, kommen wir zu einer kleinen Übungsaufgabe.

Tools

MS SQL Management Studio, Jetbrains DataGrip oder einem Tool für SQL

Übungsziel

Als Variable soll eine Tabelle angelegt werden, in der ein paar Einträge getätigt werden , Verarbeitet und am Ende Ausgeben. Hierbei soll keine Tabelle in der Datenbank angelegt werden. Praktisch bleiben die Daten nur für das Fertig Script in der Ausführung und werden nicht gespeichert.

Variable anlegen

Statt nur einen Wert in einer Variable zu hinterlegen, sollen gleich ein paar mehr Werte hinterlegt werden. Hier eignet sich die Variable als Tabelle anzulegen. Für die Eindeutigkeit eines Datensatz erhält die Tabelle eine Spalte für den Identifier oder auch Kurz ID, die mit der Einstellung IDENTITY wird von eins beginnend gezählt und mit jedem neuen Datensatz um einen hochzählt.

 DECLARE @myTable TABLE(  
      id INT IDENTITY(1, 1),  
      Hinweis VARCHAR(1024)  
 ); 

 

Werte eintragen

Das eintragen wird mit INSERT INTO ermöglicht.

 INSERT @myTable  
 (  
   Hinweis  
 )  
 VALUES  
 ('Dies ist der vorläufige text!'),  
 ('ersetzen'),  
 ('Dies ist was anderes!')  

 

Eintrag ändern

Nun soll Text hinzugefügt werden, die im Feld Hinweis mit 'Dies ist' im Anfang stehen.

 UPDATE mt  
 SET mt.Hinweis = mt.Hinweis + ' Hinzugefügter text'  
 FROM @myTable mt  
 WHERE mt.Hinweis LIKE 'Dies ist%'  

Datensatz überschreiben

Zweites Beispiel wie ein Feld überschrieben werden kann.

 UPDATE mt  
 SET mt.Hinweis = 'Ich bin ein neuer Satz'  
 FROM @myTable mt  
 WHERE mt.Hinweis = 'ersetzen'  

Ergebnis

Kommen wir zum Ende. Mit einem Select auf die Tabelle, kann nun das Ergebnis aus den verwendeten Vorgängen wiederspiegeln.

 SELECT *   
 FROM @myTable  

 

Zur Vollständigkeit

Nun das Ganze in einem Rutsch. Eine kleine Übung mit MS SQL, jedoch wurden hier nur die Grundbefehle verwendet, so daß dieses Beispiel auch mit anderen Datenbanken funktionieren sollte.

 -- Tabellen Variable anlegen  
 DECLARE @myTable TABLE(  
      id INT IDENTITY(1, 1),  
      Hinweis VARCHAR(1024)  
 );  
 
 -- Datensätze hinzufügen  
 INSERT @myTable  
 (  
   Hinweis  
 )  
 VALUES  
 ('Dies ist der vorläufige text!'),  
 ('ersetzen'),  
 ('Dies ist was anderes!')  
 
 -- Zwischen Stand ausgeben.  
 SELECT * FROM @myTable  
 
 -- Bedingt Test Update Text ergänzen. Dahinter anhängen  
 UPDATE mt  
 SET mt.Hinweis = mt.Hinweis + ' Hinzugefügter text'  
 FROM @myTable mt  
 WHERE mt.Hinweis LIKE 'Dies ist%'  
 
 -- Überschreiben  
 UPDATE mt  
 SET mt.Hinweis = 'Ich bin ein neuer Satz'  
 FROM @myTable mt  
 WHERE mt.Hinweis = 'ersetzen'  
 
 -- Ergebnis ausgeben  
 SELECT *   
 FROM @myTable  

Error Text ausgeben bei einer Stored Procedure in C# Anwendung

Vielleicht ist das, worauf ich diesmal eingehe selbstverständlich, aber ich habe zu dem Thema ein wenig gesucht und mal wieder nichts gefunden. Wenn ich in meiner Anwendung eine Stored Procedure ausführe, dann würde bei einem Fehler keine Rückmeldung von der Ausgeführten Prozedur kommen und der Eindruck entsteht, dass die gespeicherte Prozedur normal durchgelaufen ist. Nun kann man auch einen Output Wert einrichten und erwarten, das ein entsprechender Wert herauskomm. Käme nun ein Standard Wert heraus, könnte daraus geschlossen werden, dass etwas in der Prozedur nicht gemacht macht wurde. Ohne Detail Information, kann man alles vermute. Fehler im Script, keine Ausreichende Berechtigung vergeben oder Datensatz hat einen Fehler. Was auch immer. Schöner wäre einen Fehlertext zu erhalten.

 

Benötigt

• MS SQL Datenbank
• Visual Studio 2019

 

Ziel

Das Beispiel soll ein Wert zurück geben, wenn die Stored Procedure ohne Fehler ausgeführt wurde. Bei einem Ausnahmefehler soll wiederum der Fehler Text Ausgeben werden in der Anwendung.

 

Beispiel Prozedur anlegen

Die Folgende Prozedur kann in ihrer Ausführung normal durchlaufen und den erwarteten Ausgangs Wert in 'myGetStatusMessage' ausgeben. Wird der Parameter 'showMeErrorMessage' auf 1 gesetzt, dann wird eine Ausnahme geworfen.

 USE [example-data]   
 GO  
 SET Ansi_Nulls ON  
 GO  
 CREATE PROCEDURE [dbo].[myStoredProcedure]    
   @showMeErrorMessage AS BIT,   
   @myGetStatusMessage AS NVARCHAR(20) OUTPUT,     
   @myStoredProcedureErrorMessage NVARCHAR(100) OUTPUT   
 AS  
 Begin Try  
   IF @showMeErrorMessage = 1  
   Begin  
     THROW 123, 'Eine Ausnahme wurde geworfen', 1;   
   end  
   SELECT @myGetStatusMessage = 'Alles in Ordnung!'   
 end try  
 begin catch  
   SELECT @myStoredProcedureErrorMessage = ERROR_MESSAGE();  
 end catch  

 

Beispiel C# Anwendung

Nun zu einer Consolen Anwendung, die den Aufruf einer gespeicherten Prozedure ausführen kann und auch unter Ziel Bedingungen den Fehlertext ausgeben kann, dass durch den Catch Block der Prozedur ausgefüllt wurde.

 

 using System;  
 using System.Data.SqlClient;  
 Console.WriteLine("Ein Beispiel wie man eine Error Meldung aus einer Store Procedure ausgegeben werden kann");  
 Console.WriteLine("Store Procedure wird ausgeführt ohne Ausnahmefehler!");  
 RunStoreProcedure(false);  
 Console.WriteLine("\r\nStore Procedure wird ausgeführt mit Ausnahmefehler!");  
 RunStoreProcedure(true);  
 Console.WriteLine("\r\nPress enter to close application");  
 Console.ReadLine();  
 static void RunStoreProcedure(bool throwError)  
 {  
   var connection = new SqlConnection(  
     @"server=127.0.0.1,1433;User Id=CustomUser;Password=passwort;Database=example-data;");  
   connection.Open();  
   var query = "DECLARE @StatusOutput AS NVARCHAR(20), @ErrorOutput AS NVARCHAR(100);" +  
         $"EXEC [dbo].[myStoreProcedure] {(throwError ? 1 : 0)}, " +  
         "@StatusOutput OUTPUT, " +  
         "@ErrorOutput OUTPUT; " +  
         "SELECT @StatusOutput AS Status, @ErrorOutput AS Error";  
   var command = new SqlCommand(  
     query,   
     connection);  
   var reader = command.ExecuteReader();  
   while (reader.Read())  
   {  
     Console.Write("Ergebnis: ");  
     for (var iColumn = 0; iColumn < reader.GetColumnSchema().Count; iColumn++)  
     {  
       Console.Write($"{reader.GetValue(iColumn)}, ");  
     }  
     Console.WriteLine("");  
   }  
   connection.Close();  
 } 

 

That's it

Der Umfang ist übersichtlich und kann in den meisten Fällen für eine Anwendung in der Form eine Store Procedure ausführen. Was nicht so einfach ist, wenn eine Tabelle zurück gegeben wird und dort die Möglichkeit statt der Ergebnis Tabelle, wiederum einen Fehlertext auszugeben.

 

GitHub - ExampleStoreProcedureErrorOutput

SQLite oder CSV auf Raspberry Pi 2/3 mit Win 10 IoT

Irgendwann kommt der Punkt, da möchte man seine Daten auch speichern. Bei dem Einsatz von vielen Daten kann auf die Klassische Art in einer CSV im IsolatedStorage gespeichert werden. Ist einfach zu lesen führt aber zu redundante Dateninhalte. Mit SQLite lassen sich relationale Dateninhalte zusammenstellen. Aber man muss zusätzliche Referenzen hinzufügen und sich mit SQL auseinandersetzen (allerdings nur ein wenig). Beide Varianten funktionieren auf PC, Tablet, Windows Phone und natürlich auf Raspberry Pi 2 und 3.

Was nehme ich?

Vorweg sollte man sich fragen, was wird mein Ziel. Das hängt immer von der eigenen Anwendung ab. Soll in der Stunde ein Durchschnittswert errechnet werden der dann angezeigt werden soll, dann reicht sicherlich ein Array.

Möchte ich Benutzereinstellungen Speichern? Dann könnte der folgende Programmschnipsel reichen der den IsolatedStorage verwendet.

ApplicationData.Current.LocalSettings.Values["MyKey"] = MyValue; if (ApplicationData.Current.LocalSettings.Values.ContainsKey("MyKey"))     MyValue = ApplicationData.Current.LocalSettings.Values["MyKey"]; Aus <https://stackoverflow.com/questions/42750736/uwp-how-to-use-isolated-storage>

Für diesen Bleiben wir zunächst bei einfachen Datensätzen, die einfach hintereinander gespeichert und in einer Tabelle abgebildet werden können. Der Teil für Relationale Datenhaltung wird auf den nächsten Post eingegangen.

Datenabruf

Der Vorteil einer Datenbank Abfrage ergibt sich dann beim Abrufen der Daten. Wurden z.B. so viele Daten gespeichert, dass es in Summe ca. 20 Megabyte groß ist, dann könnte sich das Öffnen der CSV Datei etwas lang werden. Und wenn man nur zu einem Bestimmten Zeitraum oder einen Bereich haben möchte, dann ist das gesamte einlesen einer Datei und das Parsen der Inhalte relative Zeitintensiv.

Performance Vergleich?

Hierzu muss man sagen, dass der IsolatedStorage nicht gedacht ist, schnell einzelne Daten zu speichern. In der Demo Anwendung sind zwei provisorische Klasse mit IsolatedStorage angelegt. Diese sind nur soweit geschrieben, das damit Lesen, Speichern und zurück setzen ermöglicht. (siehe am Ende Link zum Github Repository). Letzten Endes sollte anhand der Ergebnisse zeigen, was für den einen oder anderen die bessere Entscheidung sein könnte und unabhängig von weiteren Kriterien betrachtet werden sollte.

Test Vorgang

Als erstes wird festgelegt, wie viele Daten geschrieben werden. Vorbelegt als Default Anzahl sind zehn Datensätze zu erstellen. Die Werte sind in allen Datensätzen dieselben, außer der letzte wird abwechselnd zu gewiesen (hier Wohnzimmer und Flur).

Jeder Vorgang wird zehnmal ausgeführt und gemessen. Daraus ergibt sich dann die Durschnittszeit.

• Bestehende Daten löschen
• Zehn Mal die Anzahl Daten schreiben. Bei jeden neu lauf, werden die Daten wieder gelöscht.
• Die Zehn Daten, zehnmal lesen. Daten werden immer neu geladen.
• Datenabrufen und auf "Wohnzimmer" filtern. Auch hier werden die Daten immer neu geladen

 Nach dem Durchlauf werden die Ergebnisse der einzelnen Durchläufe sowie der Durchschnittswert angezeigt.

Beispiel Anwendung

Die Beispiel UWP Anwendung ist nur Zweckmäßig für den Test aufgebaut. Oben Links kann über die Textbox ein Wert ab 1 eingetragen werden und setzt damit die Anzahl Daten, die geschrieben werden sollen. Mit dem Button "Run" wird der Test Ausgeführt. Sobald dieser durchlaufen ist, steht unter dem Button "Finish".

Rechts sind zwei Textboxen die wiederum zwei Beispiele zu SQLite und IsolateStorage abbilden, die im Programmcode im Einzelnen betrachtet werden können. (Die Ergebnisse im Bild können von PC zu PC abweichen)

Ergebnisse

Ziel Systeme sind natürlich der Raspberry Pi 2 und 3. Warum das Schreiben mit SQLite auf dem Raspberry Pi3 langsamer ist, konnte ich mit dem Testaufbau noch nicht ermittelt. Zudem kommt, dass nach dem Aufräumen der Methoden Inhalte, sich die Zeiten verschlechtert haben. Warum dies ist, werde ich auf einen späteren Post eingehen.

Ergebnisse in dem der Programmcode einfach runter geschrieben wurde

Ergebnisse nach dem Aufräumen

Fazit

Der Aufbau der Methoden kann sicherlich besser gelöst werden. Die Methoden Inhalte in 'Func<T>' auszulagern ließ die Ergebnisse deutlich mehr schwanken. Die Gemessene Zeit stieg um das Sieben- bis Zehnfache an beim Lesen mit 'Where' Abfragen. Der Test mit Schreiben in die SQLite Datenbank ist dahingegen weniger abweichend, dafür aber beim IsolatedStorage ist die Durschnittszeit auf ca. das zwanzigfache angestiegen.

Beide haben Vor- und Nachteile. Mit diesen Beispiel Test wäre IsolatedStorage gut fürs schnelle Speichern und für das schnelle Lesen die SQLite Datenbank.

Die Auslegung der Tests ist sehr spärlich und behandelt noch nicht das Verwenden von Relationalen Daten. Das kommt dann mit den nächsten Posts.

Offene Punkte:

• Speichern von Relationalen Daten
• Abruf von Relationalen Daten
• Probleme mit Code Optimierung

Github - ExampleSQLiteAndIsolatedStorage

Referenzen

https://stackoverflow.com/questions/42750736/uwp-how-to-use-isolated-storage

https://docs.microsoft.com/de-de/windows/uwp/files/quickstart-reading-and-writing-files

http://blog.chrisbriggsy.com/Using-SQLITE-in-Windows-10-IoT-Core-Insider-Preview/