Using SqlMetal to generate Windows Phone Local Database classes

Before we begin

Before we begin lets first mention that in this article we will use a previously created SQL Compact database: "Countries.sdf" which has two tables "Country" and "City" with a  relation between them as shown below:

For the purpose of this article we need only a valid sdf database file and nothing more! The database is created in the following path: C:\temp

What is SqlMetal?

According to the official MSDN documentation:

The SqlMetal is a command-line tool which comes with the VisualStudio installation and generates code and mapping for the LINQ to SQL component of the .NET Framework. By applying options that appear later in this topic, you can instruct SqlMetal to perform several different actions that include the following:

• From a database, generate source code and mapping attributes or a mapping file.
• From a database, generate an intermediate database markup language (.dbml) file for customization.
• From a .dbml file, generate code and mapping attributes or a mapping file.

NOTE:The SQLMetal.exe file is included in the Windows SDK that is installed with Visual Studio. By default, the file is located at drive:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin.

Why using SqlMetal?

If you need to use a  local database with Windows Phone 7.1 Mango, this tool can save you many hours of tedious work by generating the code necessary for accessing a local database for you.

How to use SqlMetal?

Actually it is pretty simple. All you need to do is just execute a command in the Visual Studio Command Prompt. To do this follow the steps:

Step1: Press the "Start" button and start typing "Visual Studio Command Prompt". Then select "Visual Studio Command Prompt(2010)" as shown below:

Step2: Here is how the Visual Studio command prompt should look like:

Step3: Write the following command (change any parameters if necessary):

?

1	sqlmetal c:\temp\Countries.sdf /code:"c:\temp\CountriesDataClasses.cs" /language:csharp /namespace:MyNamespace /context:CountriesDataContext /pluralize

• public partial class CountriesDataContext : System.Data.Linq.DataContext

• public partial class City : INotifyPropertyChanging, INotifyPropertyChanged

• public partial class Country : INotifyPropertyChanging, INotifyPropertyChanged

?

1 2 3 4 5

public CountriesDataContext(System.Data.IDbConnection connection) :         base(connection, mappingSource) {     OnCreated(); }

?

1 2 3 4 5

public CountriesDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :         base(connection, mappingSource) {     OnCreated(); }

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="Countries")] public partial class CountriesDataContext : System.Data.Linq.DataContext {           private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();       #region Extensibility Method Definitions partial void OnCreated(); partial void InsertCity(City instance); partial void UpdateCity(City instance); partial void DeleteCity(City instance); partial void InsertCountry(Country instance); partial void UpdateCountry(Country instance); partial void DeleteCountry(Country instance); #endregion           public CountriesDataContext(string connection) :             base(connection, mappingSource)     {         OnCreated();     }           public CountriesDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) :             base(connection, mappingSource)     {         OnCreated();     }                 public System.Data.Linq.Table<City> Cities     {         get         {             return this.GetTable<City>();         }     }           public System.Data.Linq.Table<Country> Countries1     {         get         {             return this.GetTable<Country>();         }     } }

?

1 2 3 4

<StackPanel x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">     <Button Content="1-> Initialize DB" x:Name="btnInitializeDB" Click="btnInitializeDB_Click" />     <Button Content="4-> Select Cities" x:Name="btnSelectCities" Click="btnSelectCities_Click" /> </StackPanel>

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

private const string ConnectionString = @"isostore:/CountryDB.sdf";   // Constructor public MainPage() {     InitializeComponent();     using (CountriesDataContext context = new CountriesDataContext(ConnectionString))     {           if (!context.DatabaseExists())         {             // create database if it does not exist             context.CreateDatabase();         }     } }

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

private void AddCity() {     using (CountriesDataContext context = new CountriesDataContext(ConnectionString))     {         // create a new country and add it to the context         Country country = new Country();         country.Name = "Spain";         // add the new country to the context         context.Countries1.InsertOnSubmit(country);           // create a new city and add it to the context         City city = new City();         city.Name = "Barcelona";         city.Country = country;         // add the new city to the context         context.Cities.InsertOnSubmit(city);           // save changes to the database         context.SubmitChanges();     } }   private void btnInitializeDB_Click(object sender, System.Windows.RoutedEventArgs e) {     this.AddCity();     MessageBox.Show("Added city"); }

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

public IList<City> GetCities() {     IList<City> cityList = null;     using (CountriesDataContext context = new CountriesDataContext(ConnectionString))     {         IQueryable<City> query = from c in context.Cities select c;         cityList = query.ToList();     }       return cityList; }   private void btnSelectCities_Click(object sender, RoutedEventArgs e) {     IList<City> cities = this.GetCities();       StringBuilder messageBuilder = new StringBuilder();     messageBuilder.AppendLine("Cities:");     foreach (City city in cities)     {         messageBuilder.AppendLine(city.Name);     }     MessageBox.Show(messageBuilder.ToString()); }

You can either type the command or paste it by right clicking the mouse and then select "Paste":

Note: The command shown above will generate a new file called "CountriesDataClasses.cs" where you will find all the necessary classes which you will need in order to work with the target database (in our case the database is "Countries.sdf "). This includes:

-  A separate class for each table: each class has properties that correspond to the columns from the respective table. All attributes are automatically applied so that the mapping between the class and the table is configured. What is more all classes implement INotifyPropertyChanging(useful for optimizing the LinqToSQL engine), INotifyPropertyChanged(useful for the data binding) .

- Data context. The data context exposes a Table<TEntity> property  for each table.

Step4: Go to C:\Temp (this is the path where we have generated the CountriesDataClasses.cs file through the command prompt) and open CountriesDataClasses.cs. If you open the file you will see several classes inside:

Step5: Create a new Windows Phone 7.1 Mango project and include the generated CountriesDataClasses.cs file from Step4 above in the project. If you try to build the project you will notice the following errors:

 

Step6:  To fix the problem just add a reference to "System.Data.Linq":

Step7: The code generated by the SqlMetal tool is not completely compatible with Windows Phone 7.1 Mango. However you can fix this just by removing two of the constructors from the DataContext class (in our caseCountriesDataContext). So remove the following constructors:

Here is how the final CountriesDataContext should look like:

Step8: Add the following code in MainPage.xaml

Step9: The first thing we must do is to create the database. Add the following code in MainPage.xaml.cs:

NOTE: For generating the data classes we used another database "Countries.sdf". Note that the connection string is the name under which you write/create your database into Isolated Storage. So you can add whatever name you prefer. In our case we will to create our database in Isolated Storage with the following name: "CountryDB.sdf".

Step10: Next we will populate the database with some data:

Step11: Finally we will add some code that selects all cities from the database. Just add the following in MainPage.xaml.cs:

Step12: That`s it. Just build and run the project.

That was all about  using using SqlMetal to generate Windows Phone 7.1 Mango local database classes.