SQL CE guide for Windows Phone 7.1 – part 2 of 2

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";
Data entry

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.

Data selection

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();
Data deletion

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.

Data manipulation

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.

SQL CE guide for Windows Phone 7.1 – part 1 of 2

Until recently, we were forced to memorize our data in XML files, through serialization. With the coming  of SQL Server CE on Windows Phones 7.1, everything became much easier and much more efficient thanks to the potentiality of a relational database.

Configuration and installation of the tools that make life easier when creating  a database and its context.

On Codeplex, a tool named SQL Server Compact Toolbox, which integrates into Visual Studio and offers a series of features to work with the SQL server CE databases, is available. One of these features, recently introduced, is the automatic generation of Datacontext for projects like Windows Phone.

The add-in for Visual Studio is downloadable from the Visual Studio Gallery.  Once the procedure is finished, let’s open it and we’ll find the item “SQL Server Compact Toolbox” in the Tools Menu. When opening it, we’ll be a shown a window where all the available connections will be listed: this window will be empty, unless we’ve formerly configured some connections to a SQL Server CE database, by using the Visual Studio Server Explorer native tool.

Database creation

This tool gives a chance not only to connect to existing databases, but also to create them: we won’t be able to take advantage of this feature, though, because the tool only allows us to create SQL CE 4.0 databases, while Windows Phone 7.1 is based on the 3.5 version. If we created one based on the 4.0 version, the option to create the DataContext for Windows Phones wouldn’t be enabled.

The tool is nevertheless able to use the Explorer Server: the trick is therefore to create a database with the Visual Studio native tool and, afterwards, to “hook “ on SQL Server Compact Toolbox in order to create our DataContext.

The Trick

Let’s therefore open the Explorer Server (we’ll find it in the View Menu of Visual Studio), let’s click with the right button on Data Connections and let’s choose the Add Connection.

Let’s push the Change button on the Data Source item e let’s make sure that we select the “Microsoft SQL Server Compact 3.5” typology. Afterwards, in the Connection Properties section, let’s click on the Create button in order to choose the position in which the SDF file containing the real database is going to be created. In fact, this file is never going to be used in our application, as the Code-First approach is to have the database automatically generated at the first start of our application. However, we’re going to use it as a “temporary storage” in order to define the structure of our database, to be converted in a DataContext.

Once the database is created, we’ll find our connection in the Explorer Server: if we now go back to the SQL Server Compact Toolbox and push the Refresh button, the list of connections will get updated and the name of our database will appear.

Table creation

The time has come to define entities, or rather the table of our database: let’s therefore go back to the Explorer Server, expand the database just created, click on “Tables” with the right mouse button and select “Create Table”.

Once the Table and its columns was created, if the pattern is to have relations, let’s also define the relationship between tables.

By pushing with the right button on the table, let’s select “Table Properties”. Let’s go to the Add Relations section, let’s give a name to the relation and, in both dropdowns, Primary Key Table Column and Foreign Key Table Column, let’s have the primary key of Table 1, namely Table1Id, and the external key of Table 2, namely always Table2Id, overlap. Let’s push the Add Colomns button: the relation will appear on the panel below. At this point, let’s push the Add Relation button in order to save everything: now the two tables are connected through a one to many relation (in this case).

DataContext Generation

Now that the database has been created, we are ready to “transform” it in an entity and to have LINQ to SQL create the database in the Isolated Storage, through the DataContext.

In order to do this, we need the tool SQL CE Toolbox, which we can recall with the last button in the Explorer Server or from the Visual Studio Tools menu.

Let’s click on the Refresh button (the first one) and our database, which will be recognized as a SQL Sever CE 3.5, will appear. We only have to click with the right button to select “Add Windows Phone DataContext to current Project (beta)” and there you are!

The properties of the different classes will be created with full support at the biding of Silverlight, thanks to the  INotifyPropertyChanged interface implementation. This means that all the checks that are in biding with these properties will be automatically updated at every change in order to reflect the change automatically. At this stage, we’re ready to do some basic operations: insertion,  deletion, change and selection.

SQL CE guide for Windows Phone 7.1 – part 2 of 2

How to prevent “Saving changes is not permitted” on SQL Server 2008

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the Database Management Studio will not allow you to save the changes.
This is caused by a configuration setting that will result by default in the following dialog:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

To be able to make a change in your table structure, you’ve just to follow these steps:

  • Tools > Options
  • Select the tab Designers and choose Tables and designers
  • uncheck the option: “Prevent saving changes that require table re-creation”

Now you can save changes.