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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s