NOTE: This project was a solution long before Windows Phone have built-in Database Support. You should be using Local Database support provided by Windows Phone itself!

 

Project Description
C# Sqlite Port for Windows phone 7 and possibly Silverlight 3, 4. The core engine was slightly modified to be used with IsolatedStorage and SqliteClient were ported by using missing codes from Mono project in order to maximize usability and portability from desktop.

Based on latest source of http://code.google.com/p/csharp-sqlite/ (revision 78fcda0bf7)

Summary of Changes:

  • Conversion from ArrayList, HashTable to List and Dictionary
  • Removal of functions that use DataTable
  • Filled in Missing ADO.NET classes, enums from Project Mono Source
  • Filled in Missing ASCII encoding class from http://www.hardcodet.net/2010/03/silverlight-text-encoding-class-generator
  • Conversion from normal file IO to IsolatedStorage
    (NOTE: it will not work with files added as resource/content in the application – you need to copy it to IsolatedStorage first)
  • Fixed Issues of core engine which does not close the file properly and prevent deletion of journal


image

General Usage pattern follows normal ADO.NET classes, however – SqliteClient did not inheirted or implements any ADO.NET interfaces or classes.

Some Recommendations:

  • Queries are converted to ASCII (ISO-8859-1) which means all international characters will not work. Always use Parameter to store data.
  •  Phones use Flash based storage which works well for large block access – always use transaction. It seems like internally, SQLite would create a journal file every time there is a change to database, using transaction will only create one journal file per transaction. Without transaction, journal file gets created every time.
  • Closing the connection will also close the database and disposal of associated stream, there is no connection pooling.

Further Improvements:

  • Storing Journal using MemoryStream instead of IsolatedStorage file stream. Have to verify whether journal file were used to recover anything.

 

Extension
  1. IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
  2. isf.DeleteFile("test.db");
  3.  
  4. using (SqliteConnection conn = new SqliteConnection("Version=3,uri=file:test.db"))
  5. {
  6.     conn.Open();
  7.  
  8.     using (SqliteCommand cmd = conn.CreateCommand())
  9.     {
  10.         cmd.CommandText = "CREATE TABLE test ( [id] INTEGER PRIMARY KEY, [col] INTEGER UNIQUE, [col2] INTEGER, [col3] REAL, [col4] TEXT, [col5] BLOB)";
  11.         cmd.ExecuteNonQuery();
  12.  
  13.         cmd.Transaction = conn.BeginTransaction();
  14.         cmd.CommandText = "INSERT INTO test(col, col2, col3, col4, col5) VALUES(@col, @col2, @col3, @col4, @col5);SELECT last_insert_rowid();";
  15.         cmd.Parameters.Add("@col", null);
  16.         cmd.Parameters.Add("@col2", null);
  17.         cmd.Parameters.Add("@col3", null);
  18.         cmd.Parameters.Add("@col4", null);
  19.         cmd.Parameters.Add("@col5", null);
  20.  
  21.         DateTime start = DateTime.Now;
  22.         this.lstResult.Items.Add("Inserting 100 Rows with transaction");
  23.  
  24.         for (int i = 0; i < 100; i++)
  25.         {
  26.             cmd.Parameters["@col"].Value = i;
  27.             cmd.Parameters["@col2"].Value = i;
  28.             cmd.Parameters["@col3"].Value = i * 0.515;
  29.             cmd.Parameters["@col4"].Value = "สวัสดี な. あ · か · さ · た · な · は · ま · や · ら · わ. 形容詞 hello " + i;
  30.             cmd.Parameters["@col5"].Value = Encoding.UTF8.GetBytes("สวัสดี");
  31.  
  32.             object s = cmd.ExecuteScalar();
  33.         }
  34.         cmd.Transaction.Commit();
  35.         cmd.Transaction = null;
  36.         this.lstResult.Items.Add("Time taken :" + DateTime.Now.Subtract( start ).TotalMilliseconds + " ms.");
  37.  
  38.         cmd.CommandText = "SELECT * FROM test";
  39.         using (SqliteDataReader reader = cmd.ExecuteReader())
  40.         {
  41.             while (reader.Read())
  42.             {
  43.                 var bytes = (byte[])reader.GetValue(5);
  44.                 this.lstResult.Items.Add(string.Format("{0},{1},{2},{3},{4}, {5}",
  45.                     reader.GetInt32(0),
  46.                     reader.GetInt32(1),
  47.                     reader.GetInt32(2),
  48.                     reader.GetDouble(3),
  49.                     reader.GetString(4),
  50.                     Encoding.UTF8.GetString(bytes, 0, bytes.Length)));
  51.             }
  52.         }
  53.  
  54.         conn.Close();
  55.     }
  56. }

Last edited Apr 23, 2012 at 9:56 PM by nant15, version 3