Wednesday, March 17, 2010

Sqlite for WP 7 Series - Proof of concept

I was really excited a month ago when they announced Windows Phone 7 Series, but also disappointed because it looked unfinished. Yesterday when they released a CTP for Windows Phone development I was surprised that there is no local database support (in my opinion a step backward from Windows Mobile 6.5 and an essential feature for developing business applications).
What I've developed is a proof of concept application that implements Sqlite db engine inside Windows Phone 7 series. I've used csharp-sqlite developed by noah.hart and modified it to work with IsolatedStorage and WP7 Phone Tools CTP.
After some initial struggle with the library I wrote a simple test program:



Hope you will find it useful...
csharp-sqlite.wp.zip


76 comments:

  1. Good work! I wasn't really one of the guys worrying about lack of SQL Compact edition support, since I don't find that piece of software particulary good, but this should stop the ranting on the lack of DB support :-)

    ReplyDelete
  2. Good job. I was starting exactly the same yesterday. Next thing is SQLiteClient - talking directly to SQLite is really ugly. But wait - WTF they don't have System.Data in the Windows Phone 7 SDK.

    This thing sucks you can't imagine how anoid I am at moment.

    Here is a guy who was porting it to the iphone:
    http://www.jprl.com/Blog/archive/development/mono/MonoTouch/

    And here are the sources for system.data in mono:
    http://anonsvn.mono-project.com/viewvc/branches/mono-2-6/mcs/class/System.Data/System.Data/?sortby=date

    ReplyDelete
  3. This is awesome. Are you planning to integrate this code changes upstream to http://code.google.com/p/csharp-sqlite/? Hope this proof of concept app graduates as a real world app :)

    ReplyDelete
  4. Wow, where did you come from? 4 posts and 3 of them rock! I'll excuse the first one :) Keep up the good work.

    ReplyDelete
  5. Why you had to do that? Silverlight port of application was already there in trunk...

    ReplyDelete
  6. It was not working. You can try it

    ReplyDelete
  7. Hi,
    will you post your ported version of the library?
    I struggled a bit to get it running/compiling. The SL project was not able to be imported into the CTP.
    Would be great :)

    ReplyDelete
  8. You already have it attached to the post. It's a project compiled with the CTP version.

    ReplyDelete
  9. Seems like the table information is gone when I restart the emulator. Any hints on how to maintain the data?
    I didn't dig much into the SQLite3 library code (looks horrible!), so a quick hint would help.

    ReplyDelete
  10. The emulator has absolutely no perseistency store. So if you restart the emu - it will clear everything you entered. Nothing wrong with c#Sqllite here...

    ReplyDelete
  11. Hummm, so you cannot have a consistent storage of data? For example a list of contact persons? Makes no sense to me when you restart the emulator and the contacts are gone :)
    Is there a way I can manipulate the code to expose the data on external file? I can see a fileName = "Test.DB" in the code, isn't this what is meant for sustaining the data? I can't find a way to make that Test.DB physically exist on hard drive.

    ReplyDelete
  12. C# Sqlite is not an official one.. I'm wondering if you get all features of Sqlite in C# version..

    Guys from Sqlite told me once that it's not possible to port SQLite to Silverlight version because Silverlight can't have pInvoke.

    ReplyDelete
  13. Michael C# Sqlite is a full port of Sqlite to C# and it needs no P/Invoke . Work with Silverlight but you have to modify the os_win file to use IsolatedStorage instead of FileStream. On WP7 doesn't make any sense to adapt this version because IsolatedStorage is very slow so you would have a slow db engine

    ReplyDelete
  14. Featured... http://agilemobility.net
    Good work!

    ReplyDelete
  15. how can read data from table using your test project?

    ReplyDelete
  16. Did you guys even try this code?
    The attached source code doesn't even compile. Too many debug errors. WTF?

    ReplyDelete
  17. Is it only for me that the code is _extremely_ slow? Selects take ~0.2-0.5 secounds per _row_, which is unacceptable if you will have a database with a few hundered rows.

    ReplyDelete
  18. Feels very slow for me too on select.
    @Dan Do you have any sample code on how to read data?

    Thanks

    ReplyDelete
  19. I was playing around with this to see if the sqlite library could be optimized for the WP7 environment. I noticed that I was able to take a select statement that returned 19 rows down from 7 seconds of execution down to about 1.5 seconds by moving the IsolatedStorageFile to a static variable and only retrieving it one time during the execution of the application.

    Feeling optimistic I decided to dig a little further and discovered that for my one select statement the winaccess method of os_win_c.cs was being executed 23 times and verifying the existence of the database file each time. For a quick test I assume the file is there and set the pResOut value to 0. The execution of my select statement then got knocked down to ~150ms.

    I guess what I'm getting at is this. Do you know of any efforts to architect a more efficient SQLite library for WP7? I'm beginning to think that SQLite might be feasible from IsolatedStorage if accessed efficiently.

    Thanks.

    ReplyDelete
  20. Nice work Ruprect. At the moment I don't know if anybody is optimizind os_win_c.cs that is the only problem. You could try it yourself. I would like to try to optimize it myself but it's been a really busy period. When I will have some spare time I will have a look at it

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Great webpage! I dont imagine Ive seen every one of the angles of this theme the way in which youve pointed them out. Youre a accurate star, a rock star guy. Vending Machine Locators Youve got a great deal to say and know so much about the subject that i think you ought to just teach a class about it. Vending Machine Locator

    ReplyDelete
  23. Wow I am so stoked...my 4yrold son and I along w/ my mother want to see the show so bad...I am convinced that my little boy Dylan is BEP Biggest fan...some kids pretend to be firemen,Doctors and Police Officers...NOT my Boy HE pretends to be Apple, Will or Taboo and lucky me...he always makes me Fergie! nike dunks

    ReplyDelete
  24. Ever since I have been programming, I think this is such a concerning topic!
    In fact, I would say that this is a interesting as Generic Viagra due to the usage it can have.
    Thanks for sharing information

    ReplyDelete
  25. This comment has been removed by a blog administrator.

    ReplyDelete
  26. This comment has been removed by a blog administrator.

    ReplyDelete
  27. Nice. Unfortunately so far I cannot successfully open an existing database in a WP7 app. Your sample appears to create one on the fly but in my case I have an existing sqlite db I want to read from. I don't get an error when it opens the database probably because it is "hard-coded" to create the database if it doesn't exist. Later when I select from a valid table the error is the table does not exist and the DB shows no tables. I've tried placing the .db file in multiple locations with different build actions and different paths/filename when opening the DB to no avail. Any ideas?

    ReplyDelete
  28. Hey,
    Loving your blog, awesome tips on this you have here. I
    would just like to ask you some questions privately, mind
    contacting me at ,info@seo services
    Thanks,
    Mark
    seo company

    ReplyDelete
  29. I have same issue as Geoff Hudik does.

    ReplyDelete
  30. Have a look at how to deploy a db with you project

    ReplyDelete
  31. Thanks for the nice information. Its really very good stuff. Awesome post.

    Mobile Application Development

    ReplyDelete
  32. Good Job Dan. Are you planning on putting this on sourceforge?

    ReplyDelete
  33. This comment has been removed by a blog administrator.

    ReplyDelete
  34. This comment has been removed by a blog administrator.

    ReplyDelete
  35. This comment has been removed by a blog administrator.

    ReplyDelete
  36. This comment has been removed by a blog administrator.

    ReplyDelete
  37. This comment has been removed by a blog administrator.

    ReplyDelete
  38. This comment has been removed by a blog administrator.

    ReplyDelete
  39. how can we use an existsing sqlite database with this app?

    ReplyDelete
  40. how can we use an existsing sqlite database with this app?

    I have saved my db as Test.DB into IsolatedStorage, and try to read from my table named as Test,then I got an error message as "Unable to open database file"..Any ideas..?

    ReplyDelete
  41. You can deploy or download you database to the IsolatedStorage and they use it from there.

    The project was published on codeplex:
    http://sqlitewindowsphone.codeplex.com/

    ReplyDelete
  42. Could you provide an example on how to deploy an existing sqlite database to the isolated storage and then use it ?

    ReplyDelete
  43. Great information towards mobile development. As more and more smart phone devices and tablet computers are coming in the market, the requirements for developing applications also increasing.

    mobile application development

    ReplyDelete
  44. Created a wrapper and unit test for your work. Using it in my multi-targeting (single code base for Desktop, Silverlight and Windows Phone) Password Manager application. Gave credit where credit is due! Thanks!!

    http://passwordmgr.codeplex.com/wikipage?title=Sqlite

    ReplyDelete
  45. Good job,
    Can we use this wrapper in Desktop version?

    I tried to use this wrapper in Desktop version, just to make a connection to the database, but it can't.

    It fails in this step
    Sqlite3.sqlite3_open(Database, ref _db)

    ReplyDelete
  46. HI,I have got a problem.
    When I stop windows phone 7 simulator for debug , then I run simulator again. All the data in the sqLite are lost.

    Everytime , I need to re-insert all the data to the database. Would you tell me the solution.
    THANKS

    ReplyDelete
  47. The solution would be to backup your isolated storage and the restore it when you restart the emulator. Look on my blog and you will find a post on how to backup and restore your IsolatedStorage

    ReplyDelete
  48. Adibu
    I am trying to access a SQLite from isolated storage and I had error message
    Unable to open database.
    When page load it works, I see the records in list and textblock, but when I click the button
    I have the error. Please see the code:
    ...
    using Community.CsharpSqlite;

    namespace SQLiteFood
    {
    public partial class MainPage : PhoneApplicationPage
    {
    private SqliteConnection conn;
    private SqliteCommand cmd;
    private SqliteDataReader reader;
    private IsolatedStorageFile isf;


    private SqliteConnection db = null;
    // Constructor
    public MainPage()
    {
    InitializeComponent();
    }

    private void PhoneApplicationPage_Loaded(object sender, RoutedEventArgs e)
    {

    isf = IsolatedStorageFile.GetUserStoreForApplication();

    isf.DeleteFile("CustomerDB.db");


    using (conn = new SqliteConnection("Version=3,uri=file:CustomerDB.db"))
    {
    conn.Open();

    using (cmd = conn.CreateCommand())
    {
    cmd.CommandText = "CREATE TABLE Customers ( [_id] INTEGER PRIMARY KEY, [First] TEXT, [Last] TEXT)";

    cmd.ExecuteNonQuery();

    cmd.Transaction = conn.BeginTransaction();
    cmd.CommandText = "INSERT INTO Customers(First, Last) VALUES(@First, @Last);SELECT last_insert_rowid();";

    cmd.Parameters.Add("@First", null);
    cmd.Parameters.Add("@Last", null);

    */
    DateTime start = DateTime.Now;
    this.lstResult.Items.Add("First Name, Last Name");

    cmd.Parameters["@First"].Value = "Adrian";
    cmd.Parameters["@Last"].Value = "Dibu";

    object s = cmd.ExecuteScalar();
    cmd.Transaction.Commit();
    cmd.Transaction = null;
    this.lstResult.Items.Add("Time taken :" + DateTime.Now.Subtract( start ).TotalMilliseconds + " ms.");

    cmd.CommandText = "SELECT * FROM Customers";

    using (reader = cmd.ExecuteReader())
    {
    while (reader.Read())
    {

    this.lstResult.Items.Add(string.Format("{0},{1},{2}",
    reader.GetInt32(0),
    reader.GetString(1),
    reader.GetString(2)

    ));

    textBox1.Text = reader.GetString(2);
    }
    }
    }
    }

    }

    private void button1_Click(object sender, RoutedEventArgs e)
    {
    isf = IsolatedStorageFile.GetUserStoreForApplication();
    using (conn = new SqliteConnection("Version=3,uri=file:CustomerDB.db"))
    {
    conn.Open();

    using (cmd = conn.CreateCommand())
    {

    cmd.CommandText = "SELECT Last FROM Customers";
    // here error that Database can’t be opened
    object s = cmd.ExecuteScalar();

    while (reader.Read())
    {
    textBox1.Text = reader.GetString(2);

    }

    // conn.Close();
    db.Close();
    }


    }
    }
    }
    }

    ReplyDelete
    Replies
    1. In my mobile App,I want to use ExecuteReader but it is not there in my dll or DBHelper.Can u provide me the link with appropriate dll and DBHelper for SQLITE

      Delete
  49. Same here adibu, I have the same "Unable to open database" with the codeplex sources.
    The funny thing is that the code from http://sviluppomobile.blogspot.com/2010/09/wp7-deploy-db-as-content-file.html works just fine :-)

    Maybe Dan can take a look at it?

    ReplyDelete
  50. Adi send me a test project on my email and I will have a look. I've implemented Sqlite in my Boxfiles solution and everything works ok.

    You can find my email In About me

    ReplyDelete
  51. I'm having the same error.
    Any hints so far?

    ReplyDelete
  52. Hi, do you run this "Sqlite for WP 7" in the windows phone 7 device?
    Thank You!

    ReplyDelete
  53. I have run CSharpSqlite.TestProject on HTC T8788 without any problem. Here is the result got in debug mode:
    Items | Insert(s) | Select(s)
    50 | 0.98 | 0.041
    100 | 1.714 | 0.076
    200 | 3.695 | 0.161
    300 | 5.779 | 0.252
    400 | 7.71 | 0.33
    500 | 10.585 | 0.435
    1000 | 23.14 | 0.912
    1500 | 37.087 | 1.496
    2000 | 58.811 | 2.752

    Dan,

    Any plan to implement the SetPassword() and ChangePassword() of the sqlite3 db? I need an encrypted db.

    Thanks,

    William

    ReplyDelete
  54. is there a way to know if database exists already. Upon app launch I want to check if dadatbase exists and if not then create it (since it must be first time app is running).
    My code looks like:
    IsolatedStorageFile directory = IsolatedStorageFile.GetUserStoreForApplication();
    if (directory.FileExists(test.db))
    {
    db.Open();
    MessageBox.Show("existing database opened: ");
    }
    else
    {
    MessageBox.Show("existing database not found: " );
    }
    this never finds the file

    ReplyDelete
  55. Hi,

    Is it possble to connect with this client to an online database?

    I want to do some synchronazation with and online database, but I dont know kow to do that.

    ReplyDelete
  56. sqlite3_open() ( from http://www.sqlite.org/c3ref/open.html ):

    int sqlite3_open(
    const char *filename, /* Database filename (UTF-8) */
    sqlite3 **ppDb /* OUT: SQLite db handle */
    );

    A database connection handle is usually returned in *ppDb, even if an error occurs. The only exception is that if SQLite is unable to allocate memory to hold the sqlite3 object, a NULL will be written into *ppDb instead of a pointer to the sqlite3 object.

    My Sqlite3.sqlite3 object becomes NULL after the open call ( sqlite3_open() ) - any hints about why this is happening?

    ReplyDelete
  57. I have seen SQLCipher in crypto.cs file in comments section. Any plan to provide encryption using SQLCipher?

    ReplyDelete
  58. Hello guys! Nice work!
    Im using your api in a WP7 project and Im experiencing a performance issue in my select querys. I found that what is taking too much time is the ToList() method that is shown in your example.
    var result = cmd.ExecuteQuery("select * from table ");
    result.ToList();

    Any ideas on what is going on?

    ReplyDelete
  59. Hi Dan,
    great work man.. I am trying to download my .db file to isolatedstorage from the webserver and access that in my app.. I have no clue how to do that way. coz u r creating a temp.db in the app it self. In my case I just want to read the existing database which can be downloadable to storage. Can you please help me...

    ReplyDelete
  60. Hi Dan,
    first thx for the code, works perfectly for me. Can I use it in an commercial project, under what kind of licence did you publish it ?
    Best regards
    Klaus

    ReplyDelete
  61. I appreciate you for your thoughtful gift of having written this short article. The message seems to be forwarded to me specifically. My son also had a lot to learn from it – though he was the individual that came upon your site first. Most of us can’t imagine a more amazing present than a monetary gift to encourage you to definitely do more. law personal statement

    ReplyDelete
  62. Really an appreciable blog, great work with best efforts U have released simple blog here.Thanks for this blog share here.
    best web design company

    ReplyDelete
  63. I really admire this, It's really looks interesting! Very nice write up. Anyways, its a Great post.


    kamagra

    ReplyDelete
  64. Mobile development it has grown to this high ever no other industry has this scale every from a high school student to a 80 year old person has a mobile phone it has to be some 75% of wolrd population has mobiles mostly in urben areas.
    Ecommerce Cart

    ReplyDelete
  65. Windows Phone 7 Series is really awesome. I like to use all kinds of product from windows from os to phone. Amazing company really awesome.


    Alex Wright
    generic viagra 100mg | super p force

    ReplyDelete
  66. This comment has been removed by the author.

    ReplyDelete
  67. I'm always look for people to check out my site. Please stop by and leave a comment sometime!
    Website development bangalore

    ReplyDelete
  68. Windows Phone 7 series is really admiring...It is something interesting to here that there is no local database support..


    Web Designing Company

    ReplyDelete
  69. How to Use SQLite for WP7 Using Phonegap

    ReplyDelete