Before reading this article read SQL CE guide for Windows Phone 7.1 – part 1 of 2.
The first thing to do is to create our database, in case the application was started for the first time. It’s a very simple operation, natively managed by the DataContext, that offers us a way to discover if the database already exists (DatabaseExists(), a Boolean is returned) and another one to create the database (CreateDatabase()).
Actually, the DataContext generated by SQL CE Toolbox already contains a method called CreateIfNotExists() that does everything by itself: it checks if the database already exists and if it doesn’t, creates it. Moreover, it gives us the opportunity to import an already existing SQL CE Server: with Windows Phone 7.1, it is in fact possible to include a .sdf file in the project, which will be available in read-only, though. The method CreateIfNotExists() is able to find out if the XAP includes a database with a name specified in the DatContext (for example, Orders.sdf) and, if it does, is able to copy it in the Isolated Storage, in order to grand us full access read/write.
When starting the application for the first time, we’ll then have to:
using (DataContext context = new DataContext (DataContext .ConnectionString))
We can notice two things:
- The use of “using”, as the DataContext is a class that implements the IDisposable Interface. This means that once we’ve finished using it, we have to Dispose it, otherwise the connection to the database is left open, wasting resources. The using statement makes life easier for us, by automatically Disposing the class once the operations inside the block are finished (successfully or with mistakes).
- The DataContext generated by SQL CE Toolbox includes a static property called ConnectionString, with the path in which the file is going to be saved on the Isolated Storage. This property has to be used, as in the example, when creating a DataContext instance:
public static string ConnectionString = "Data Source=isostore:/MyDataBase.sdf";
To save our objects inside the database, we have to use the DataContext.
using (DataContext context = new DataContext(DataContext.ConnectionString))
As we can notice, we only have to call the InsertOnSubmit method, displayed by the property MyObjects, and to pass the object we want to save in the database.
For the entry, the method InsertAllOnSubmit is available too and it allows us to save several objects at once in the database : it indeed accepts a collection of the IEnumerable<T> type as input (where T is the type we want to save).
Once all the database operations are finished (in our case, just the entry), we have to call the SubmitChanges method, displayed by the DataContext, which generates and runs the SQL code needed. If this method isn’t called, the database operations simply join the queue but are never run.
Selecting and searching for elements in the table is very easy, thanks to LINQ, to the lamba expressions and to the properties displayed by our DataContext.
List orders = context.MyObject.Where(x => x.Property > 100).ToList();
In order to erase one or more elements from a table, the methods DeleteOnSubmit and DeleteAllOnSubmit are at our disposal.
The first one is used for removing a specific element and accepts the object to be removed as a parameter.
If you want to remove several elements at the same time instead, the DeleteAllOnSubmit method, which accepts a collection of the IEnumerable type as input parameter, comes to our help.
To update an already-existing record, simply retrieve it from the collection (as we did in the section on Data selection), make the changes and afterwards, call the SubmitChanges method.