DateTime-Spalten in SQLite und DataSet update

  • C#
  • .NET (FX) 4.5–4.8

Es gibt 12 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    DateTime-Spalten in SQLite und DataSet update

    Hallo zusammen,

    gibt es eine Möglichkeit nur DateTime Spalten speziell formatieren zu können für einen DataTable Update Command?

    Um etwas Schreibarbeit und Fehleranfälligkeit (neue Columns etc.) habe ich den SQLiteCommandBuilder genutzt um schell alle CRUDs einer Tabelle an der Hand zu haben. Funktioniert super. Nur leider formatiert er die DateTime-Spalten nicht im richtigen Format für SQLite.

    Folgendes Beispiel zur Verdeutlichung:

    C#-Quellcode

    1. var command = new SQLiteCommand(string.Format("SELECT * FROM {0}", dataTable.TableName), this.connection);
    2. SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
    3. SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
    4. adapter.Update(dataTable);


    DateTime sieht so aus mit direktem Update command:

    C#-Quellcode

    1. 01.01.2020 00:00:00


    SQLite benötigt es aber so:

    C#-Quellcode

    1. 2020-01-01 00:00:00


    Das DataSet interpretiert den String beim Zurückladen zwar richtig, werden aber bei direkten SQL-Queries nicht mehr richtig erkannt.

    Klar kann ich alle Commands per Hand schreiben und dem Adapter geben, die DateTime-Spalte so formatieren wie sie SQLite braucht, aber mit dem SQLiteCommandBuilder ginge das viel einfacher und weniger Fehleranfällig.

    Kann ich die DateTime-Spalte irgendwie anders überschreiben ohne alle Queries per Hand zu schreiben? Oder mache ich etwas grundlegend falsch?

    Danke!

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „asuryan“ ()

    asuryan schrieb:

    Das DataSet interpretiert den String beim Zurückladen zwar richtig, werden aber bei direkten SQL-Queries nicht mehr richtig erkannt.
    Ähm - was für ein String? Ein DateTime ist kein String.
    Was meinst du mit "direkte Query"? Doch nicht etwa das Werte-Einfrickeln in den CommandText?
    Solch sollte verboten werden.
    Werte immer immer immer nur mit DbParametern an eine Query übergeben!!

    Gugge Must-Know: Sql-Injection , warum
    Ähm - was für ein String? Ein DateTime ist kein String.


    Ja, das ist schon klar. ;) Aber in SQLite gibt es keinen DateTime Datentyp. Siehe hier:
    sqlitetutorial.net/sqlite-date/
    Ein Datum kann nur als TEXT, REAL oder INTEGER gespreichert werden. Ich habe mich für den TEXT entschieden. Der DataAdapter schreibt mit update aber das falsche Format. Das ist das Problem.

    Mit "direktem Query" meine ich sowas (als Beispiel... bei User-Eingaben, die nicht hardcoded sind, wird das schon als DBParameter übergeben):

    C#-Quellcode

    1. SELECT * FROM Table WHERE date>'01.01.2018'


    Doch nicht etwa das Werte-Einfrickeln in den CommandText?

    Hm. Ich befürchte doch. Gerade wegen dem Datum-Problem.

    Eine Zeile bzw.Tabelle OHNE Datum update ich so:

    C#-Quellcode

    1. var command = new SQLiteCommand(string.Format("SELECT * FROM {0}", dataTable.TableName), this.connection);
    2. SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
    3. SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
    4. adapter.Update(dataTable);


    Eine Zeile MIT Datum _leider_ so, weil ich das Datum explizit formatieren will/muss (das will ich ja loswerden):

    C#-Quellcode

    1. public SCSLiteResult insertInvoiceEntry(DSSCBusinessAccounter.InvoiceRow invoice)
    2. {
    3. String sql = "INSERT INTO Invoice (status,number,identifier,date,description) VALUES (@status,@number,@identifier,@date,@description)";
    4. SQLiteCommand command = new SQLiteCommand(sql);
    5. this.addInvoiceParameters(invoice, command);
    6. SCSLiteResult result = this.sqlite.executeCommand(command);
    7. this.db.AcceptChanges();
    8. return result;
    9. }
    10. public void addInvoiceParameters(DSSCBusinessAccounter.InvoiceRow invoice, SQLiteCommand command)
    11. {
    12. command.Parameters.Add(new SQLiteParameter("@status", invoice.status));
    13. command.Parameters.Add(new SQLiteParameter("@number", invoice.number));
    14. command.Parameters.Add(new SQLiteParameter("@identifier", invoice.identifier));
    15. command.Parameters.Add(new SQLiteParameter("@date", SCSQLiteDB.convertDateTimeToDB(invoice.date)));
    16. command.Parameters.Add(new SQLiteParameter("@description", invoice.description));
    17. }


    Weißt Du was ich meine?

    Danke!

    Dieser Beitrag wurde bereits 10 mal editiert, zuletzt von „asuryan“ ()

    Klingt schaurig in meinen Ohren - eine Db ohne Zeitwert-Typen!

    Dann täte ich empfehlen, einen anderen Ersatz-Typ zu wählen als Text. Weil in vielen Cultures notiert man Zeit-Texte immer anders, und je nach verwendeter Culture kann derselbe Text sehr unterschiedliche Datumse bedeuten oder auch garnet lesbar sein.
    Also wenn SqLite Zeiten als REAL speichern kann, zuzüglich der dafür nötigen Konvertierungen, täte ich das empfehlen.
    SQLite verwendet intern ISO8601-Format.
    Deswegen macht es Sinn, dieses Format auch nach außen beizubehalten.
    sqlite.org/lang_datefunc.html
    Das ist nicht kulturabhängig und kann auch solche Dinge wie Zeitzonen speichern (ähnlich DateTimeOffset).

    Ich habe mir dafür extra mal ein paar Extensions geschrieben, die von ISO8601 in DateTimeOffset umrechnen und umgekehrt.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Wenn man mit DotNet arbeitet, hat das DateTime das Ticks. Das ist absolut Neutral, und kann problemlos in SQLite als Integer (so viel es mir recht ist, gibt es auch 8Byte Integer) gespeichert werden. Beim Anfordern kann es dann wieder ins DateTime konvertiert werden. Korrigiert mich, wenn's nicht so wäre.

    Freundliche Grüsse

    exc-jdbi
    Will man SQLite nur als Datentopf nehmen ohne die Datenbankfunktionen zu verwenden, kann man die Timestamps speichern wie man möchte.
    Das einzig Vernünftige ist aber die Verwendung eines SQLite-unterstützten Time Strings.
    So wie hier gelistet:
    sqlite.org/lang_datefunc.html

    Es gibt noch die Varianten INTEGER für das Unix-Zeitformat und REAL für julianisches Tagesformat.
    Aber das sind beides Exoten.

    Im übrigen empfehle ich für die Speicherung grundsätzlich das Zeitzonem-Offset ebenfalls zu speichern.
    Oder wenigstens alles in UTC zu speichern.
    Wer schon mal Zeitreihen für Tage mit DST-Switch angelegt hat, wird verstehen was ich meine.
    DST = Daylight Saving Time
    DST-Switch = Umschaltung Sommerzeit/Winterzeit und umgekehrt
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Danke euch für eure Antworten!

    Kann ich bei SQLite das manuelle INSERT, UPDATE etc mit nem SQLCommand aber irgendwie loswerden das ich ja wegen den Datum-Columns ja machen muss?

    Ich würd gern einfach ein DataTable.Update machen können... kann ich wegen der manuellen Formatierung der Datums-Columns aber nicht.
    Die Update-Funktion ist in SQLite sehr easy gelöst. Schau dir das hier an.
    stackoverflow.com/a/16486163

    Sollte funktionieren. Was die Richtigkeit des Strings anbelangt, musst du dir schon eventuell was anderes einfallen lassen. Tips wurden hier ja genug abgegeben.

    Freundliche Grüsse

    exc-jdbi
    ... und im UpdateCommand um.


    Das mache ich ja. Aber dafür brauche ich ja immer eine konkrete Instanz einer Row (siehe meinen Code unter Post #3). Auf die ich ja dann die Umwandlung ausführen kann.

    C#-Quellcode

    1. command.Parameters.Add(new SQLiteParameter("@date", SCSQLiteDB.convertDateTimeToDB(invoice.date)));


    Bei DataTable.Update hab ich das ja aber nicht?! Da führt er ja alle notwendigen SQLs auf alle Rows aus, die das benötigen... Also insert, update, etc. Das funktioniert ja auch ganz prächtig wenn ich eben keine DateTime Columns hab. Wie schreibt Ihr denn DateTime vom DataSet in SQLite?

    Wie verallgemeinere ich das denn?

    C#-Quellcode

    1. SQLiteDataAdapter tableAdapter = new SQLiteDataAdapter();
    2. String insertSQL = "INSERT INTO Entry (dateBooked, description) VALUES (@dateBooked, @description)";
    3. SQLiteCommand insertSQLCommand = new SQLiteCommand(insertSQL);
    4. tableAdapter.InsertCommand = insertSQLCommand;
    5. String updateSQL = "UPDATE Entry SET dateBooked=@dateBooked, description=@description WHERE id=@id";
    6. SQLiteCommand updateSQLCommand = new SQLiteCommand(updateSQL);
    7. tableAdapter.UpdateCommand = updateSQLCommand;


    Hier muss ich ja an dateBooked einer einzelnen Instanz ran!?


    Wie schreibt Ihr denn DateTime vom DataSet in SQLite UND wollt direkte SQL queries auf die Tabelle schicken?

    Weil ohne Konvertierung beim Insert wird SQLite einen Query wie "SELECT * FROM Entry WHERE dateBooked > '31.12.2019 00:00:00'" 0 Ergebnisse liefern auch wenn es 100 gibt. Da das Format ja "2019-12-31 00:00:00" in SQLite sein muss. Das DataSet wird es per Update aber immer im falschen Format in SQLLite schreiben.

    Kann ich das Verhalten des TableAdapters mit DateTime Spalten irgendwie überschreiben?!

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „asuryan“ ()

    Ich würde DateTime komplett rauslassen aus Datenmodell und BusinessLogik.
    Was man mit DateTime rechnen kann, kann man besser noch mit Double rechnen.
    Dazu ein Converter Double <-> DateTime, der immer zwischengeschaltet wird, wenn was angezeigt wird bzw. wenn Eingaben verarbeitet werden.

    Also in all deim Code, und auch auf der DB sind Datum-Werte vom Typ Double - feddich.
    Insbes. in Databinding-Szenarien fügt sich das ganz gut ein, Databinding hat nämlich mit Formatting und Parse genau dafür Mechanismen vorgesehen.

    ErfinderDesRades schrieb:

    Dazu ein Converter Double <-> DateTime, der immer zwischengeschaltet wird,
    Das entspricht dem Speicherformat JulianDate in SQLite.
    Dazu bietet die Datenbank sogar Konvertierungsfunktionen, die bei der Abfrage verwendet werden können.

    ErfinderDesRades schrieb:

    Was man mit DateTime rechnen kann, kann man besser noch mit Double rechnen.
    In Double speichern kann man halt nur UTC-Zeit.
    Das erfordert ggf. grundsätzlich eine Konvertierung in Lokalzeit.
    Gut, schreibst du ja, dass du einen Konverter dazwischen schalten musst.
    Wenn du Lokalzeit anzeigen willst, solltest du das berücksichtigen, dass deine berechnete Zeit UTC ist und du ggf. ein .ToLocalTime bzw ein .ToUniversalTime anhängen musst.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --