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...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:
csharp-sqlite.wp.zip
UPDATE: Use the updated version at http://sviluppomobile.blogspot.com/2010/07/wp7-sqliteclient-with-transaction.html
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 :-)
ReplyDeleteGood 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.
ReplyDeleteThis 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
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 :)
ReplyDeleteWow, where did you come from? 4 posts and 3 of them rock! I'll excuse the first one :) Keep up the good work.
ReplyDeleteWhy you had to do that? Silverlight port of application was already there in trunk...
ReplyDeleteIt was not working. You can try it
ReplyDeleteHi,
ReplyDeletewill 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 :)
You already have it attached to the post. It's a project compiled with the CTP version.
ReplyDeleteSeems like the table information is gone when I restart the emulator. Any hints on how to maintain the data?
ReplyDeleteI didn't dig much into the SQLite3 library code (looks horrible!), so a quick hint would help.
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...
ReplyDeleteHummm, 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 :)
ReplyDeleteIs 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.
C# Sqlite is not an official one.. I'm wondering if you get all features of Sqlite in C# version..
ReplyDeleteGuys from Sqlite told me once that it's not possible to port SQLite to Silverlight version because Silverlight can't have pInvoke.
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
ReplyDeleteFeatured... http://agilemobility.net
ReplyDeleteGood work!
how can read data from table using your test project?
ReplyDeleteHi, thanks for sharing, i like very much.
ReplyDeletewww.itsolusenz.com
Did you guys even try this code?
ReplyDeleteThe attached source code doesn't even compile. Too many debug errors. WTF?
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.
ReplyDeleteFeels very slow for me too on select.
ReplyDelete@Dan Do you have any sample code on how to read data?
Thanks
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.
ReplyDeleteFeeling 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.
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
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGreat 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
ReplyDeleteWow 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
ReplyDeleteEver since I have been programming, I think this is such a concerning topic!
ReplyDeleteIn fact, I would say that this is a interesting as Generic Viagra due to the usage it can have.
Thanks for sharing information
scrub m65 kamagra attorney lawyer body scrub field jacket lovegra weed attorney injury lawyer
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteNice. 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?
ReplyDeleteHey,
ReplyDeleteLoving 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
I have same issue as Geoff Hudik does.
ReplyDeleteHave a look at how to deploy a db with you project
ReplyDeleteThanks for the nice information. Its really very good stuff. Awesome post.
ReplyDeleteMobile Application Development
Good Job Dan. Are you planning on putting this on sourceforge?
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeletehow can we use an existsing sqlite database with this app?
ReplyDeletehow can we use an existsing sqlite database with this app?
ReplyDeleteI 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..?
You can deploy or download you database to the IsolatedStorage and they use it from there.
ReplyDeleteThe project was published on codeplex:
http://sqlitewindowsphone.codeplex.com/
Could you provide an example on how to deploy an existing sqlite database to the isolated storage and then use it ?
ReplyDeleteGreat 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.
ReplyDeletemobile application development
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!!
ReplyDeletehttp://passwordmgr.codeplex.com/wikipage?title=Sqlite
Good job,
ReplyDeleteCan 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)
HI,I have got a problem.
ReplyDeleteWhen 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
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
ReplyDeleteAdibu
ReplyDeleteI 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();
}
}
}
}
}
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
DeleteSame here adibu, I have the same "Unable to open database" with the codeplex sources.
ReplyDeleteThe 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?
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.
ReplyDeleteYou can find my email In About me
I'm having the same error.
ReplyDeleteAny hints so far?
Hi, do you run this "Sqlite for WP 7" in the windows phone 7 device?
ReplyDeleteThank You!
I have run CSharpSqlite.TestProject on HTC T8788 without any problem. Here is the result got in debug mode:
ReplyDeleteItems | 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
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).
ReplyDeleteMy 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
Hi,
ReplyDeleteIs 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.
sqlite3_open() ( from http://www.sqlite.org/c3ref/open.html ):
ReplyDeleteint 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?
I have seen SQLCipher in crypto.cs file in comments section. Any plan to provide encryption using SQLCipher?
ReplyDeleteHello guys! Nice work!
ReplyDeleteIm 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?
Hi Dan,
ReplyDeletegreat 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...
Hi Dan,
ReplyDeletefirst 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
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
ReplyDeleteReally an appreciable blog, great work with best efforts U have released simple blog here.Thanks for this blog share here.
ReplyDeletebest web design company
I really admire this, It's really looks interesting! Very nice write up. Anyways, its a Great post.
ReplyDeletekamagra
This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! Keep up the good work.
ReplyDeleteUltrabook Notebook Tipis Harga Murah Terbaik :: Harga Notebook :: Mivo Tv :: Cerita Lucu :: Zodiak :: Software Akuntansi Laporan Keuangan Terbaik :: Ultrabook Notebook Tipis Harga Murah Terbaik :: Puisi Cinta :: Kata-Kata Bijak :: Cell Phone Wallpapers :: Ultrabook Acer :: Cerita Humor :: Cell Phone Reviews :: Humor Sufi :: Ultrabook Notebook Tipis Harga Murah Terbaik :: Kata Bijak :: Kata Cinta :: Kata Mutiara :: Ultrabook Notebook Tipis :: Harga Notebook Acer :: Ultrabook Notebook Tipis Harga Murah Terbaik :: Ultrabook Notebook Tipis Harga Murah Terbaik :: Software Akuntansi Laporan Keuangan Terbaik :: Raja Gombal Kata Rayuan Gombal :: Kata-Kata Mutiara Bijak Mario Teguh :: Contoh Surat Lamaran Kerja :: SEPEDA MOTOR INJEKSI IRIT HARGA TERBAIK CUMA HONDA :: Contoh Surat Lamaran Kerja :: Pantun Jenaka Kata-Kata Indah :: Puisi Untuk Ibu :: Ultrabook Notebook Tipis Harga Murah Terbaik
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.
ReplyDeleteEcommerce Cart
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.
ReplyDeleteAlex Wright
generic viagra 100mg | super p force
Kata Mutiara
ReplyDeleteKata Cinta
Kata Bijak
Zodiak
SMS CInta SMS Lucu
Ramalan Bintang
Kata Bijak Lucu
Harga Emas
Jadwal Liga Inggris
Cara Memakai Jilbab
Cara Menghilangkan Ketombe
Cara Meninggikan Badan Secara Alami
This comment has been removed by the author.
ReplyDeleteDownload.. thanks brother Click Here, Kata Lucu
ReplyDeleteI'm always look for people to check out my site. Please stop by and leave a comment sometime!
ReplyDeleteWebsite development bangalore
Windows Phone 7 series is really admiring...It is something interesting to here that there is no local database support..
ReplyDeleteWeb Designing Company
Lagutren
ReplyDeletenics
How to Use SQLite for WP7 Using Phonegap
ReplyDelete