Zufalls-Datensätze direkt per SQL auslesen

Manchmal benötigt man schnell und einfach einen einzelnen, beliebigen Datensatz aus einer Datenbank/Tabelle. Wo mir dieses bislang sehr nützlich erschien, war zum Beispiel beim Einlesen von Zufallsbildern auf mobilen Geräten. Um hier keinen unnötigen Overhead wie das Erstellen von Objekt-Id-Listen zu erzeugen, ist es manchmal hilfreich einfach nur einen Zufallswert direkt aus der Datenbank geliefert zu bekommen. Dieses kann man ganz einfach wie folgt realisieren.

MS-SQL-Server:

SELECT TOP 1 * FROM Testtabelle

ORDER BY NEWID();

SQLite:

SELECT * FROM Testtabelle ORDER BY RANDOM() LIMIT 1; 

SQLite BulkInsert-Alternative - Kleiner Code, große Wirkung

Heute bin ich auf ein Performance-Problem beim Füllen einer SQLite Datenbank gestoßen. Und zwar nutze ich für eine mobile Anwendung eine Datenbank, welche sich täglich komplett  inhaltlich verändern kann. Diese Datenbank wird dann per REST Webservice aus einem ASP.Net Web heruntergeladen.

Die Datenbank beinhaltet zwei Tabellen mit jeweils zehn Spalten. Da es bei SQLite kein Bulk-Insert wie z.B. beim SQL-Server gibt, habe ich zuerst versucht die ca. 40.000 Datensätze nacheinander ganz einfach per INSERT einzufügen. Dieses war mir aber deutlich zu langsam, so dass ich nach optimaleren Alternativen gesucht habe. Und ich bin auch recht schnell fündig geworden. Hierfür konnte ich sogar meine bestehende Programmierung fast komplett behalten. Ich musste lediglich einen kleinen Teil umstellen und um ein paar Zeilen Code erweitern.

Hier ist erst einmal zur Übersicht der Aufbau der ersten Tabelle.

CREATE TABLE Artikel(Artikelnummer VARCHAR(255) PRIMARY KEY ,EAN 
VARCHAR(255),Artikelbezeichnung1 VARCHAR(255),Artikelbezeichnung2 
VARCHAR(255),Hersteller VARCHAR(255),EmpfVK FLOAT,Verfuegbarkeit INTEGER, 
Gewicht FLOAT, Status_NichtVerfuegbar BOOLEAN,Artikelinfo 
TEXT);

Das Befüllen der Tabelle sieht dann in meine Programmierung wie folgt aus.

   1:  

   2: SQLiteConnection connection = new 

   3: SQLiteConnection();

   4:      

   5: SQLiteCommand command = new 

   6: SQLiteCommand(connection);

   7: command.CommandText = "INSERT INTO Artikel 

   8: VALUES 

   9: (@Artikelnummer,@EAN,@Artikelbezeichnung1,@Artikelbezeichnung2,@Hersteller,@EmpfVK,@Verfuegbarkeit,@Gewicht,@Status_NichtVerfuegbar,@Artikelinfo);";

  10: command.Prepare();

  11:  

  12:  

  13: SQLiteTransaction tr = connection.BeginTransaction();

  14: command.Transaction = tr;

  15:  

  16:  

  17: for (int 

  18: i = 0; i < listArtikel.Count; i++)

  19: {

  20:   command.Parameters.Add(new SQLiteParameter("@Artikelnummer", listArtikel[i].Artikelnummer));

  21:   command.Parameters.Add(new SQLiteParameter("@EAN", 

  22: listArtikel[i].EAN));

  23:   command.Parameters.Add(new SQLiteParameter("@Artikelbezeichnung1", 

  24: listArtikel[i].Artikelbezeichnung1));

  25:   command.Parameters.Add(new SQLiteParameter("@Artikelbezeichnung2", 

  26: listArtikel[i].Artikelbezeichnung2));

  27:   command.Parameters.Add(new SQLiteParameter("@Hersteller", 

  28: listArtikel[i].Hersteller));

  29:   command.Parameters.Add(new SQLiteParameter("@EmpfVK", 

  30: listArtikel[i].EmpfVK));

  31:   command.Parameters.Add(new SQLiteParameter("@Verfuegbarkeit", listArtikel[i].Verfuegbarkeit));

  32:   command.Parameters.Add(new SQLiteParameter("@Gewicht", 

  33: listArtikel[i].Gewicht));

  34:   command.Parameters.Add(new SQLiteParameter("@Status_NichtVerfuegbar", 

  35: listArtikel[i].Status_NichtVerfuegbar));

  36:   command.Parameters.Add(new SQLiteParameter("@Artikelinfo", 

  37: listArtikel[i].Artikelinfo));

  38:  

  39:  

  40:   command.ExecuteNonQuery();

  41:  

  42:  

  43:   command.Parameters.Clear();

  44: }

  45:  

  46:  

  47: tr.Commit();

  48: command.Dispose();

Das Wichtigste an dieser Programmierung ist, dass sich die Parameter-Zuweisung sowie die Ausführung (ExecuteNonQuery) innerhalb der gestarteten Transaktion liegt. Außerdem sollte das eigentliche SQL-Statement (Command.Text) außerhalb der Schleife und nicht bei jedem Durchlauf zugewiesen werden.

Ein weiterer wichtiger Punkt ist das Aufbereiten des SQL-Statements mit command.Prepare().

Mir hat die Implementierung der Transaktion eine Zeitersparnis von fast 90% gebracht. Ich denke das ist ein sehr erstaunliches Ergebnis.

Zugriff aus .Net auf SQLite Datenbanken

In meinem letzten Beitrag habe ich mich ja mit einem universellen Datenbank-Browser für Mac OS und Windows beschäftigt. Dieses war mir auch sehr wichtig, da man so recht schnell Datenbanken erstellen, mit ersten Testdaten füllen und später die richtigen Daten gut prüfen kann.

Jetzt möchte ich aber den eigentlich interessanteren Teil ansprechen. Den Datenzugriff aus .Net auf eine SQLite-Datenbank.

In folgendem Beispiel möchte ich zeigen, wie man per C#-Code eine SQLite-Datenbank erstellt, eine Tabelle anlegt und diese mit Daten befüllt.

// Datenbanknamen angeben

string dataBaseName = "Maschinen.db";

// Connection-Objekt instanziieren

SQLiteConnection connection = new SQLiteConnection();

// Datenbankdatei erstellen (inkl. Standarddatenbank "main")

SQLiteConnection.CreateFile(dataBaseName);

// Connection-String für die Verbindung erstellen

connection.ConnectionString = "Data Source=" + dataBaseName;

// Verbindung zur Datenbank erstellen

connection.Open();

// Command-Objekt instanziieren um SQL-Statements auszuführen

SQLiteCommand command = new SQLiteCommand(connection);

// SQL-Statement ohne Parameter erstellen und ausführen

command.CommandText = "CREATE TABLE Maschinen(Id INTEGER PRIMARY KEY, Name VARCHAR(255), Typ VARCHAR(255));";

command.ExecuteNonQuery();

// SQL-Statement mit Parametern erstellen

command.CommandText = "INSERT INTO Maschinen VALUES (@Id, @Name, @Typ);";

// Command-Objekt mit Parametern füllen

command.Parameters.Add(new SQLiteParameter("@Id", 1));

command.Parameters.Add(new SQLiteParameter("@Name", "Maschine1"));

command.Parameters.Add(new SQLiteParameter("@Typ", "Typ1"));

// SQL-Statement mit Parametern ausführen

command.ExecuteNonQuery();

// Parameter aus Command-Objekt wieder entfernen

command.Parameters.Clear();

// Command-Objekt wieder freigeben

command.Dispose();

// Verbindung zur Datenbank schliessen

connection.Close();

SQLite Datenbank-Browser für Mac und Windows

Da ich momentan viel parallel mit Windows und Mac OS arbeite und auch bei kleineren Projekten oft SQLite-Datenbanken verwende, habe ich einen Datenbank-Browser gesucht, der unter beiden Plattformen läuft.

Bei der Suche bin ich über interessante Clients gestolpert, die auch wirklich gut waren. Viele sind aber entweder für Mac OS oder für Windows gewesen. Letztendlich bin ich bei einen Open Source Tool hängen geblieben. Dieses trägt den Namen "SQLite Database Browser" und ist zu finden unter folgendem Link.
http://sqlitebrowser.sourceforge.net/

SQLite Database Browser unter Mac OS

Dieses Tool ist zwar grafisch nicht sehr anspruchsvoll, aber das muss ein Datenbank-Browser meiner Meinung nach auch nicht sein. Man kann mit ihm alle Grundfunktionalitäten abdecken wie zum Beispiel:
- Neue Datenbanken erstellen
- Bestehende Datenbanken einlesen (Daten sowie Struktur anzeigen)
- Strukturen verändern (Tabellen und Indizes erstellen, ändern und löschen)
- SQL-Abfragen ausführen