prettify

baseline

sqlite c# wrapper usage

I wanted to use sqlite from C# and I created a wrapper. The wrapper was published in the previous post:Sqlite c# wrapper.
I found some nice projects doing almost the same thing but I wanted to be able to use the latest version of sqlite just as it comes out of the pipeline.
I hate it when I have to wait for months for the latest version to be ported to the environment I use for development. The usage of the wrapper classes is:
string sDb = "Vocabulary.sqlite";
private void CreateDb() {
    string SQL = "PRAGMA legacy_file_format=0;" +
        "PRAGMA foreign_keys=1;" +
        "PRAGMA user_version=5;" +
        "CREATE TABLE Words (" +
            " nkWrd INTEGER PRIMARY KEY" +
            ",nfWrdBat INTEGER" +
            ",sWrd TEXT NOT NULL UNIQUE" +
            ",sWrdNative TEXT NOT NULL" +
            ",FOREIGN KEY(nfWrdBat) REFERENCES Batches(nkBat)" +
        ");" +
        "CREATE TABLE Batches (" +
            " nkBat INTEGER PRIMARY KEY" +
            ",sBatNm TEXT NOT NULL UNIQUE" +
            ",dBat TEXT NOT NULL" +
        ");";
    //if (File.Exists(sDb)) File.Delete(sDb);
    sqlite db = new sqlite(sDb);
    db.OpenDatabase();
    db.Exec(SQL);

    string sQry = "INSERT INTO Batches" +
        " (sBatNm,dBat)" +
        " VALUES " +
        "(@sBatNm,@dBat);";
    sqliteStm stm = db.Prepare(sQry);
    string sBatNm;
    DateTime dBat;
    db.Exec("BEGIN");
    try {
        sBatNm = "abc Αυτό είναι abc το πρώτο βάτς abc";
        dBat = DateTime.Now;
        stm.BindText(1, sBatNm);
        stm.BindDate(2, dBat);
        stm.Step();
        txtResults.Text += db.LastInsertRowid().ToString() + "\n"; ;

        sBatNm = "Αυτό είναι το δεύτερο";
        dBat = DateTime.Now.AddDays(1);
        stm.Reset().ClearBindings();
        stm.BindText(1, sBatNm);
        stm.BindDate(2, dBat);
        stm.Step();
        db.Exec("COMMIT");
    } catch (SQLiteException) {
        db.Exec("ROLLBACK");
    } 
    stm.FinalizeStm();
    db.CloseDatabase();
}
private void ReadData() {
    string SQL = "SELECT nkBat, dBat AS 'ημερομηνία', sBatNm || 'α' FROM Batches";
    sqlite db = new sqlite(sDb);

    sqliteStm stm = db.Prepare(SQL);
    while (stm.Read()) {
        txtResults.Text +=
            stm.Fields["nkBat"].INTEGER.ToString() + "\t" +
            stm.Fields["ημερομηνία"].DATETIME.ToString("yyyy/MM/dd HH:mm:ss") + "\t" +
            stm.Fields["sBatNm || 'α'"].TEXT + "\n";
    }
    db.CloseDatabase();
}       

1 comment:

  1. Hello,

    This is great post, can help in adding the SQLite trace using C#,
    Thanks,
    Hanna

    ReplyDelete