Quick Start Guide for Windows Phone 7 Database

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.

46 Comments

  1. 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

  2. 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.

  3. @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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. @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…

  9. 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,
      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
      ().Save();

  10. 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.

  11. 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

  12. 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?

  13. 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 πŸ˜‰

  14. 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

  15. 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.

  16. hi seargy,

    iam inserting ,retrieving data on sqlite database in windows phone7 emulator is working,but my problem is after i close the phone emulator ,again i run the phone emulator retrieving data from already stored data on sqlite database is not appear directly only i opened connection.where is the problem behind that emulator .how to get past inserted data on present time.iam download sqlite.dll,i add that reference to my phone application using silverlight i have an error i.e your sqlite.dll is not built with windows phone run time,hot it solved,send to me good sites,please give raply to me as early as possible

  17. hi seargy,

    iam inserting ,retrieving data on sqlite database in windows phone7 emulator is working,but my problem is after i close the phone emulator ,again i run the phone emulator retrieving data from already stored data on sqlite database is not appear directly only i opened connection.where is the problem behind that emulator .how to get past inserted data on present time.iam download sqlite.dll,i add that reference to my phone application using silverlight i have an error i.e your sqlite.dll is not built with windows phone run time,hot it solved,send to me good sites,please give raply to me as early as possible

  18. @gopi
    This is a known behavior of the emulator. There is no way around it. If you redeploy (doing a rebuild), it will loose all isolated storage data, not just database. There is no workaround for that. You need to test on the actual phone or you can include the data in your app and upon start up write some test data down.

  19. hi sergey,

    thank u for your valuable solution for me,but i run windows phone emulator open the connection insert image path on sqlitedatabase is working.after i close emulator again i open the connection click retrieve button for getting stored image path on emulator iam getting null reference exception problem why its happening please give suggetions to me.iam stored image path based on selection of photo album photos on windows phone emulator.

  20. hi sergey,

    i have another problem,i.e i want sqlite.dll for windows phone7 version.where it is download send for me.i download sqlite.dll but i add that reference i got an error that is dll reference is not built with windows phone run time.i want dll for all sqllite components thay are sqlitedatareader,sqliteconnction,sqlitecommand etc…….

  21. @gopikrishna
    You should not use sqlite on the phone. It already has a database, SQL CE. You should use it. In any case, the data is stored in isolated storage and will not persist in emulator. You have to find a different way to test.

  22. hi sergey,

    i dont know sqlce database for windows phone 7,for basic and better understanding purpose is there any website for me.i want to try my database based on sqlce,is there any example for me for understand purpose……………

  23. hi sergey,

    iam searching so many websites for this sqlce database,i got so many examples ,but in this examples when windows phone emulator run insert some data on sqlce database ,retrieve that inserted data is working fine good,but when i close windows phone 7 emulator ,run emulator when i click retrieve button that inserted data is not appear what’s the problem on sqlce database.i want to release my application on markets this database is not fine for me.i want to store data permenantly on windows phone 7 sqlce database ,what i do please provide complete solution for me ………………………………

  24. Hello…i have developed Windows 7 mobile app and have integrated database successfully, but the problem is when i restart the emulator or phone, database is removing automatically.please help me..and tell me if there is some other alternative..

  25. @ mjtannaMaulik
    This is how the emulator behaves, if you rebuild the app, the emulator will wipe isolated storage. You can use storage explorer I think to copy the files from the emulator to your hard drive, then copy them back after the deployment,.

  26. The code which i have done is given below,
    If i comment allDone.Waitone() and allDone.Set(),Its working fine.
    But I have to click 2-3 times the button.

    Please find a solution….

    public class HttpHandler
    {
    public static byte[] LVData { get; set; }
    public static string[] ResponseData { get; set; }

    private static ManualResetEvent allDone = new ManualResetEvent(false);

    public static string[] GetDataFromServer()
    {

    Uri address = new Uri(AppResources.URL);
    HttpWebRequest request = WebRequest.Create(address) as HttpWebRequest;
    request.Method = “POST”;
    request.ContentType = “application/x-www-form-urlencoded”;
    // request.AllowWriteStreamBuffering = true;

    request.BeginGetRequestStream(new AsyncCallback(GetRequestStreamCallback), request);
    allDone.WaitOne();

    return ResponseData;

    }

    private static void GetRequestStreamCallback(IAsyncResult asynchronousResult)
    {
    HttpWebRequest request = (HttpWebRequest)asynchronousResult.AsyncState;
    //byte[][] encryptedData = new byte[][] { UTF8Encoding.UTF8.GetBytes(Common.ToCurrentDateTimeInMillis().ToString()), UTF8Encoding.UTF8.GetBytes(“110”), UTF8Encoding.UTF8.GetBytes(“1.0.0.1”), UTF8Encoding.UTF8.GetBytes(“2222”) };
    //byte[] encryptedLVData = TLV.getLVArrayFromMultipleData(encryptedData);
    //byte[][] requestData = new byte[][] { UTF8Encoding.UTF8.GetBytes(“201”), UTF8Encoding.UTF8.GetBytes(“1fcb3416af524d5f”), encryptedLVData };
    //byte[] requestedLVData = TLV.getLVArrayFromMultipleData(requestData);

    Stream postStream = request.EndGetRequestStream(asynchronousResult);
    postStream.Write(LVData, 0, LVData.Length);
    postStream.Close();
    request.BeginGetResponse(new AsyncCallback(GetResponseCallback), request);
    // Start the asynchronous operation to get the response

    }
    private static void GetResponseCallback(IAsyncResult asynchronousResult)
    {
    try
    {
    HttpWebRequest request = (HttpWebRequest)asynchronousResult.AsyncState;
    // End the operation
    HttpWebResponse response = (HttpWebResponse)request.EndGetResponse(asynchronousResult);
    Stream streamResponse = response.GetResponseStream();
    BinaryReader br = new BinaryReader(streamResponse);
    MemoryStream ms = new MemoryStream();
    while (true)
    {
    try
    {

    ms.WriteByte(br.ReadByte());
    }
    catch (EndOfStreamException e)
    {
    break;
    }

    }
    byte[] msByte = ms.ToArray();
    streamResponse.Close();
    br.Close();
    ms.Close();
    allDone.Set();
    byte[][] responseData = TLV.getAllDataFromLVArray(msByte, 0);
    List lstData = new List();
    // To check the status
    if ((UTF8Encoding.UTF8.GetString(responseData[0], 0, responseData[0].Length)) == “0”)
    {
    byte[][] result = TLV.getAllDataFromLVArray(responseData[1], 0);
    foreach (byte[] b in result)
    {
    lstData.Add(UTF8Encoding.UTF8.GetString(b, 0, b.Length));
    }
    }
    else
    {
    lstData.Add(“false”);
    lstData.Add(UTF8Encoding.UTF8.GetString(responseData[1], 0, responseData[1].Length));
    }

    // Release the HttpWebResponse
    //response.Close();
    //allDone.Set();
    ResponseData = lstData.ToArray();
    }
    catch (WebException Ex)
    {
    ResponseData = new string[] {“-1”, Ex.Message };
    }

    }

Leave a Reply

Your email address will not be published. Required fields are marked *