I have a CodePlex project for Isolated Storage based database for Windows phone 7. You can check out this project at http://winphone7db.codeplex.com/
First step is to download the project and build it on your copter. Visit the download page for the latest recommended download. Unzip the source code on your machine, open it up in Visual Studio 2010 and compile to create a DLL.
Next, create new Windows Phone 7 project and add a reference to the DLL from step 1.
Next, in your View Model (or elsewhere in your application) check for database existence and create if it does not exist along with tables. See sample code below
if (!Database.DoesDatabaseExists(DatabaseName))
{
database = Database.CreateDatabase(DatabaseName);
database.CreateTable<Person>();
database.CreateTable<PersonDetail>();
database.Save();
}
else
{
database = Database.OpenDatabase(DatabaseName, string.Empty, true);
}
In the sample above I am testing if database exists, then create it if it does not, Then I am adding two tables to it based on POCO classes. For example, Person class would look like
public class Person
{
public Guid PersonID { get; set; }
public string FirstName { get; set; }
public string LasstName { get; set; }
}
If you would like to add a row to a table, just issue Add command:
db.Table<Person>().Add(NewPerson());
New Person routine just returns Person instance.
To remove, just issue remove command:
db.Table<Person>().Remove(person2);
You can also remove and add a range of items based on condition.
db.Table<Person>().RemoveRange((person) => { return (person.Salary >= 2); });
You can also add a range of items, specifically IEnumerable<T>
db.Table<Person>().AddRange(list);
Make sure to call Save() on either database or a specific table to commit your changes.
You can also look in unit test project that is distributed as part of the source code download for other API samples.
Sergey, Very nice job. Is there a way to index a column to speed up a query. I am performing a query (using linq) on a few thousand items which takes about 15 seconds to return.
Thank you
Charlie Brewer
15 seconds seems excessive, although many thousands of rows would not perform well typically. Feel free to email me the sample – I will take a look.
hi
I have created small windows phone 7 apps.
But i have not idea about where database store in my pc/phone.
I need to check the database then where i need to go ?
Please help me on this.
Hye Harsh,
u cannot get any physical file or location for this Database.
Hye Sergey,
I m working on Windows Phone 7, and i m using this Database.
My app crashes when the data exceeds 9/10 MB.
Can you please tell me is the database saved in Isolated Storage.?
and is there any way to increase the size of the database.
Please reply.
Its really important.
Thanks in advance.
@Harsh – data is stored in isolated storage. Why do you need to know where it is? This information is not really available.
@Deeps – data is stored in Isolation Storage, there is currently no way to store it anywhere else. When you say your app is crashing, what exactly do you mean? Do you get an exception? You can reduce in memory footprint by using lazy loading as well. There is no advertised limit on isolated storage size. There is something else going that is causing the issue.
Also, could you post these issues on the code plex site for the project? If the research provides answers, other users will benefit as well.
thanks for the reply Sergey.
I’ll send you the link of my post.
But the scenario is-
I m working on Windows Phone 7
I have created an app which accepts some user information like name, address and let the user choose any media file like Image or audio recording and add to its account.
Now, when i create an account and add a recording and if it goes to the size more than 7/8 Mb, the app doesn’t save the audio stream and crashes.
and when i again tap on it to start the app again, it doesn’t.
i tried to increase the quota by isolatedstorage.increasequotato() where i have given i long variable of value 5242880 (5MB).
Now it gives System.Exception which says – “An Error Occured while accessing IsolatedStorage”.
Why is it happening.
I m not in a condition to switch to some other database.
Please help.
I m also posting the issue somewhere, and i’ll give u the links.
thanks.
hey Sergey,
I have posted the topic at -
http://forums.create.msdn.com/forums/p/76968/467465.aspx#467465
and
http://www.codeproject.com/Questions/164173/isolated-storage-quota-increase-problem-in-WP7.aspx
Hey Sergey,
I tried to execute the “SilverlightPhoneDatabase.sln” project file but a login comes up asking for username/password to get access on https://tfs.codeplex.com/tfs/TFS09
Where can I get this login information?
@Gooner85. Just click OK, and say Yes to permanently remove binding. Zip file contains TFS information for CodePlex, but you can just ignore that.
Is there a way to create tables with different names? If you had an app that is meant to store data from a class for multiple users, but did not want to store all of the users’ data in the same table, would there be a to create a table with maybe the username being the name of the table?
Also, will the WP7DB be supported by EFCodeFirst soon? Thank you for any assistance you can provide me.
James,
I added feature request to name tables – http://winphone7db.codeplex.com/workitem/6357
Could you please confirm that this is what you are looking for?
I am not making any plans for now to support CodeFirst like features. I am waiting to see if there are any significant announcements made at the MIX 11 that would affect my project in any way.
Thanks
Sergey
Sergey, that is exactly what I would like to see :D
I hope any future annoucements will benefit your project.
Thanks
James
Hi. Thank you for SilverlightPhoneDatabase project and for all the documentation and supports. I wrote an app using this project and released into marketplace. Its been in use for a while. But now I am planning to add more feature to my app which might require an update to one of table. How do you suggest an effective way to add columns without loosing the data the user might have already added.
Hi. Thank you for SilverlightPhoneDatabase project and for all the documentation and supports. I wrote an app using this project and released into marketplace. Its been in use for a while. But now I am planning to add more feature to my app which might require an update to one of table. How do you suggest an effective way to add columns without loosing the data the user might have already added.
@Franklin.
You should be able to just add a property to your class for this table and make it nullable. I would recommend that you test the scenario though. I am on vacation for the next few days, and will not be able to do it myself…
Sergey, I am new to WP7 and really struggling with updating a row using the Silverlight Database.
Scenario is – I have a 10 person records consisting of id, Name and Telephone Number, a list is displayed, the user selects the person (3rd person on the list id = 3) and then changes the telephone number. I want to be able to save that record back to the database.
Can you help please.
Thanks
Hello, Tim,();
();
().Save();
You could take a look at the unit test project that is part of the source code download – it will give you some sample code.
Here is what you would need to do.
If !DoesDatabaseExists(“YourNameHere”)
{
var db = CreateDatabase(“YourNameHere”).
db.Save();
db.CreateTable
}
in view model add property Persons.
Persons = db.Table
ListBox.Items.ItemsSource={Binding Persons}
Once you select a person, navigate to person edit screen.
Bind a textbox to {Binding PhoneNumber}
Add OK button to that screen and fire db.Save(); or db.Table
Sergey
Is it possible to sync this local phone database to a database in the office. If so where do you suggest i look.
Thanks
Neil
No such functioanality out of the box. I wrote something similar as a proof of concept for SL database project http://silverdb.codeplex.com/. if you download the source, you will WCF project that allows you to transfer DB data to wcf service. It is still up to you to process the data on the server.
Thanks
Sergey.
Dear sir,
I want to add the about the person like first name, last name. How can I add? Please help me. And what is meant by db.Table().Add(NewPerson()); how and why used?
Thanks in Advance
I would recomment you take a look at the unit test project that is part of the download of the source code. It will make much more sense. To set names, etc.. Just add
db.Table().Add(NewPerson() {LastName = “Doe”, FIrstName = “Joe”}); It is used to add a new person to the table, and if you call Save() afterwards, those changes will be committed to the storage.
Thanks
Awesome work Sergey. You have helped thousands of WP7 developers by saving their time. I was about to do all this stuff (an API for storing, and querying data from WP7)for a small app that i was about to develop. The efforts of doing so is much higher than what it takes to do the app itself!
Why on earth did MS not come up with a built in solution for this? It is very much unlike of them.
Also, Does your DLL run on WP7 OS 7.0.x and 7.1(Mango) both?
The reason is that “shipping is a feature” Mango release does have SQL CE with an ORM. Yes, it will run on both because Mango upgrade is additive.
Thanks.
And yes,I teach part time for Computer Science undergraduate students, and they are finding your database tool for WP7 very handy. It shields them from dealing directly with XML,stream readers and isolated storage.
Hope to see you come up with a lot more WP7 goodies for developers in future too ;)
Hi Sergey,
I am a student and this has been really helpful. I really new to creating apps for phone and i have no idea on how to start. I just have a few questions.
I need to create a wp7 app for my school project. Can i use this project? I have to dl this project and add it as a reference to my project? is that all?
And do i create the database in this project or on mine? Also i have to deploy it on the wp7, will i be able to do so? Lastly,in my app, one of the features is to allow the user to create a name and image. This info will be saved in the db. And then the user can choose to change the name or the picture and save it again, so my question is, will the db allow me to do so?
Please help, my deadline is approaching soon.
Thank you
Lynette,
You can certainly use this project for your application. Having said that, I would not do so unless you intestinally want to target older (7.0) version of Windows phone. If you are targeting 7.5/7.1 version of the WP, then I would personally use SQL CE which is available there. I just think that that platform is superior to my database. I actually have a blog post to help you get started with that as well. Just search for ‘SQL CE windows phone’ on my blog, and you will find that post.