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