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(); }
Hello,
ReplyDeleteThis is great post, can help in adding the SQLite trace using C#,
Thanks,
Hanna