2 Comments

(Puedes ver este artículo en español aquí)

In a previous post Salesforce Workflow Outbound Messages Handled in .Net with WCF I explained how to create a .Net WCF service to handle workflow outbound messages from Salesforce. In this article I will add a twist to it by making the web service call back to Salesforce to retrieve additional data.

The Requirements

Allow me to explain the concepts by using a concrete example: one of our client required to integrate their Salesforce org with their ERP. The integration is based on the following rule:

  • Accounts (creations and modifications) are maintained in Salesforce
  • Accounts will be created in the ERP only when an opportunity associated with the account is won

The above rules translate to the following

  • We can create a workflow on opportunity that triggers only when the stage of the opportunity is “Closed Won”
  • The workflow will have an outbound message that calls a web service in the ERP
  • Since the outbound message can only send fields of the object it is based on, we cannot send account fields using an outbound message for the opportunity. We can send the account Id (a field in the opportunity) and then have the web service in the ERP to connect back to Salesforce and retrieve the account fields using the account Id.

Let’s dive into code!

The Workflow Outbound Message

Let’s start with the workflow definition. I won’t provide too many details on how to create the workflow since I already talked about this in the article Salesforce Workflow Outbound Messages Handled in .Net with WCF (please read this first if you need more details).

In Salesforce go to “Worflow & Approvals” and create a new workflow rule based on the Opportunity object. Give it a name (I called CreateAccountOnERP) and make sure that the evaluation criteria is set to “created, and any time it’s edited to subsequently meet criteria”. We want this option since we only want to call the web service if the opportunity stage is “Closed Won”. Now specify the rule criteria and make sure that both “Opportunity : Closed” and “Opportunity : Won” fields are true. Your workflow rule should  look like this:

Opportunity workflow rule

Now, add an outbound message action to the workflow. When creating the outbound message, make sure you mark “Send Session Id” (I will explain this later), and also to include the AccountId field as the fields to send. We still don’t have the web service url so put anything in the “Endpoint URL” field:

Opportunity outbound message

Web Service in Visual Studio (using WCF)

Now, get the WSDL for the outbound message and save it locally on disk (I named the file opportunityWorkflowOutboundMessage.wsdl), we will use this later to create our service definition inside Visual Studio.

In Visual Studio, create a new empty ASP.Net Web Application and name it WorkflowNotificationServices (the following steps are basically the same explained in the article Salesforce Workflow Outbound Messages Handled in .Net with WCF). Add the WSDL to the project. Add a new “WCF Service” to the project and name it OpportunityNotificationService.svc. Visual Studio will add three files to your project: IOpportunityNotificationService.cs, OpportunityNotificationService.svc and the implementation OpportunityNotificationService.svc.cs.

Now open a command prompt (if you have the Productivity Power Tools extension you can right click on the project in Solution Explorer and choose “Power Commands->Open Command Prompt”) and type the following:

svcutil /noconfig /out:IOpportunityNotificationService.cs opportunityWorkflowOutboundMessage.wsdl

Open the IOpportunityNotificationService.cs file and make sure you make these changes:

  • Put the whole class inside the namespace of the project (in my case is the name of the project WorkflowNotificationServices)
  • Change the name of the interface from NotificationPort to IOpportunityNotificationService.
  • Change the ConfigurationName parameter of the ServiceContractAttribute attribute from NotificationPort to OpportunityNotificationService.
  • Remove the parameter ReplyAction=”*” from the OperationContractAttribute attribute.
  • At the end of the file, remove the interface NotificationPortChannel and the class NotificationPortClient (we don’t need these since they are used by a client consuming the web service).

The interface should now look as the following (highlighted lines are the one that changed):

namespace WorkflowNotificationServices
{
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "4.0.0.0")]
    [System.ServiceModel.ServiceContractAttribute(Namespace = "http://soap.sforce.com/2005/09/outbound", ConfigurationName = "OpportunityNotificationService")]
    public interface IOpportunityNotificationService
    {
        [System.ServiceModel.OperationContractAttribute(Action = "")]
        [System.ServiceModel.XmlSerializerFormatAttribute()]
        [System.ServiceModel.ServiceKnownTypeAttribute(typeof(sObject))]
        notificationsResponse1 notifications(notificationsRequest request);
    }

    /// rest of the code below
}

Next, open the file OpportunityNotificationService.svc.cs, remove the DoWork method and implement the IOpportunityNotificationService interface (place the cursor on text for the name of the interface and press Crtl+. and choose “Implement interface IOpportunityNotificationService”).

And finally, edit the web.config and replace it with the following:

<configuration>
    <connectionStrings>
        <add name="ERP" connectionString="Data Source=localhost;Initial Catalog=Hiperantena;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
    <system.web>
        <compilation debug="true" targetFramework="4.5" />
        <httpRuntime targetFramework="4.5" />
        <webServices>
            <protocols>
                <clear/>
                <add name="HttpSoap" />
                <add name="Documentation"/>
            </protocols>
        </webServices>
    </system.web>
    <system.serviceModel>
        <services>
            <service name="WorkflowNotificationServices.OpportunityNotificationService">
                <endpoint binding="basicHttpBinding" contract="OpportunityNotificationService"/>
            </service>
        </services>
        <behaviors>
            <serviceBehaviors>
                <behavior name="">
                    <serviceMetadata httpGetEnabled="true" httpsGetEnabled="true" />
                    <serviceDebug includeExceptionDetailInFaults="false" />
                </behavior>
            </serviceBehaviors>
        </behaviors>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />
    </system.serviceModel>
</configuration>

The project should compile at this point and if we run it we should be able to get the WSDL for this web service.

The Call Back to Salesforce

The outbound message we defined earlier for the opportunity will send two important pieces of information: the id of the account, and the session id. We will use these to connect to Salesforce and get the data for the account that is required in the ERP. For this we will use the Salesforce SOAP API.

Go back to Salesforce, and go to “Setup->Build->Develop->API”, you will get the API WSDL screen:

Salesforce API

We need to get the Enterprise WSDL (for a difference between Enterprise and Partner WSDL see this article). Save the WSDL on disk (I named it enterprise.wsdl) and add it to your Visual Studio project. We will create a proxy for this WSDL using Visual Studio.

In the Solution Explorer, right click the References node and choose “Add Service Reference”. Specify the full path to the enterprise WSDL and click Go. Make sure you specify Salesforce as the namespace:

Add service reference

Click OK. Visual Studio will create the proxy to call the Salesforce SOAP API and make the required modifications in web.config. Open the OpportunityNotificationService.cs file and replace the code with the following:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using WorkflowNotificationServices.Salesforce;

namespace WorkflowNotificationServices
{
    public class OpportunityNotificationService : IOpportunityNotificationService
    {

        public notificationsResponse1 notifications(notificationsRequest request)
        {
            bool result = true;

            notifications notifications1 = request.notifications;

            string sessionId = notifications1.SessionId;
            string url = notifications1.EnterpriseUrl;

            OpportunityNotification[] opportunityNotifications = notifications1.Notification;
            foreach (OpportunityNotification opportunityNotification in opportunityNotifications)
            {
                WorkflowNotificationServices.Opportunity opportunity = (WorkflowNotificationServices.Opportunity)opportunityNotification.sObject;

                try
                {
                    if (!CreateAccount(url, sessionId, opportunity))
                        result = false;
                }
                catch (Exception e)
                {
                    Trace.TraceError(e.Message);
                    result = false;
                }
            }

            notificationsResponse response = new notificationsResponse();
            response.Ack = result;

            return new notificationsResponse1() { notificationsResponse = response };
        }

        private bool CreateAccount(string url, string sessionId, WorkflowNotificationServices.Opportunity opportunity)
        {
            int recordsAffected = 0;

            EndpointAddress address = new EndpointAddress(url);
            SoapClient soapClient = new SoapClient("Soap", address);
            SessionHeader session = new SessionHeader() { sessionId = sessionId };

            string query = String.Format("select Id, Name, AccountNumber, BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry from account where id = '{0}'", opportunity.AccountId);

            QueryResult result;
            soapClient.query(session, null, null, null, query, out result);

            if (result.size > 0)
            {
                Account account = result.records[0] as Account;

                ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["ERP"];
                using (SqlConnection cn = new SqlConnection(connectionString.ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("salesforce_createAccount", cn))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add("@idSalesforce", SqlDbType.VarChar).Value = account.Id;
                        command.Parameters.Add("@name", SqlDbType.VarChar).Value = account.Name;
                        command.Parameters.Add("@number", SqlDbType.VarChar).Value = (object)account.AccountNumber ?? DBNull.Value;
                        command.Parameters.Add("@address", SqlDbType.VarChar).Value = (object)account.BillingStreet ?? DBNull.Value;
                        command.Parameters.Add("@city", SqlDbType.VarChar).Value = (object)account.BillingCity ?? DBNull.Value;
                        command.Parameters.Add("@state", SqlDbType.VarChar).Value = (object)account.BillingState ?? DBNull.Value;
                        command.Parameters.Add("@postalCode", SqlDbType.VarChar).Value = (object)account.BillingPostalCode ?? DBNull.Value;
                        command.Parameters.Add("@country", SqlDbType.VarChar).Value = (object)account.BillingCountry ?? DBNull.Value;

                        cn.Open();
                        recordsAffected = command.ExecuteNonQuery();
                    }
                }

            }

            return recordsAffected > 0;
        }
    }
}

There are a few important things here to notice: In lines 24 and 25 we get the session id and the url that comes from the outbound message from Salesforce. These two parameters are needed to connect back to Salesforce. Remember that when we created the outbound message in Salesforce we marked the “Send Session ID” field. We use this information in lines 54-56 to create a SessionHeader object. In line 58 we build a SOQL query to get the information from account and we use the AccountId field sent in the outbound message. We then use the session header in line 61 to send the query to Salesforce. The rest of the code just process the information we got back from Salesforce and we send this to the ERP (in this example I’m calling a SQLServer stored procedure to simulate the ERP).

Testing the Call Back

We need to publish our web service and make it available on the internet. The publishing is outside of the scope of this article. In my case I published it on an IIS server in our DMZ, and the public url is http://www.grupolanka.com/Salesforce/WorkflowNotificationServices/OpportunityNotificationService.svc (don’t try it, it won’t work).

Now we need to go back to Salesforce and change the url of the outbound message we created earlier. Edit the outbound message definition (in the example is SendOportunityToERP) and edit the “Endpoint URL” field with the URL.

Now, create a new account and create an opportunity. Change the stage of the opportunity to “Closed Won” and save it. Salesforce will trigger the workflow and call the web service we defined, and this will call back to Salesforce to retrieve the information from the account.

You can get the sample project here:

Everyone is excited about the new Universal Apps. I can now have a new type of project, called a Shared project, where I can put all the common files shared between a Windows 8.1 and Windows Phone 8.1 project. This is a better way around the “Add as a Link” option we had before. But universal apps is more than just a shared project. It seems that the APIs used by both Windows and Windows Phone are converging into one. Granted there still a lot of work to do and I’m not sure if there will be a 100% compatibility between the two frameworks, but this is a great step.

I’m going to talk about how to localize a universal app. In my article Internationalization of a Cross Platform Application I explain how to use the Multilingual App Toolkit in a PCL project to localize an application targeted for multiple platforms (Windows, Windows Phone, Android and iOS). But when I tried to apply the steps in the article to a Universal App I run into some problems. I will still base my localization on resource files, the thing is where do I put these? Should I put them in the new Shared project? or should I keep using PCL projects? Let’s first review our main goals:

  • To have a single place where we can specify strings and have these strings shared by all the platform projects.
  • Strings can be used in the UI as well as programmatically
  • Use the Multilingual App Toolkit to handle all the translations


Resource files in Shared project

When I knew about the new shared project type I thought “great, now I can put my resource files in the shared project”. Well, it turns out you can, but this is not the best approach. To explain why let’s see how our goals stand when we use resource files in shared projects.

As mentioned before, universal apps are basically three projects: a project for Windows 8.1, a project for Windows Phone 8.1 and a Shared project. By putting resource files in the shared project they will be shared by both the Windows and Windows Phone projects. Our first goal is ok.

The convergence of the frameworks between Windows and Windows Phone is also applied to the use of resources. Windows apps (aka Windows Store or WinRT apps) use the .resw resource type. On the other hand, Windows Phone Silverlight apps (the “Silverlight” suffix was added to differentiate them from the new Windows Phone apps which use the WinPRT framework) use .resx resource files. Now, both Windows 8.1 and Windows Phone 8.1 use .resw files..resw files can be used in XAML by specifying the x:Uid attribute in XAML components (this was not possible with .resx files), but these resource files do not have a strong type associated to them (as in the case of .resx resource files). This means that we cannot use resource strings programmatically. You can create a strong type associated to .resw files by using ResW File Code Generator, but it turns out that this extension doesn’t work if the resource is in the shared project. So, our second goal doesn’t stand.

The Multilingual App Toolkit cannot be enabled for shared projects. You have to enable MAT in both Windows and Windows Phone projects. It turns out that the decision of the MAT team regarding universal app was that they “decided to enable MAT based on the specific project instead of the shared project” (see this blog post for details). This means that I would have to do translations twice, one for each project. When you enable MAT in both projects you’re solution will be something like this:

Universal app with MAT

.xlf files are duplicated. So, yes we can use MAT on universal app (our third goal is ok) but since we can’t use it on the shared project our first goal doesn’t stand.

Finally, our goals stand as this:

  • To have a single place where we can specify strings and have these strings shared by all the platform projects: No
  • Strings can be used in the UI as well as programmatically: No
  • Use the Multilingual App Toolkit to handle all the translations: Yes

In summary, having a resource file in the shared project means that I will not be able to use resource strings programmatically and also that I will have to deal with duplicate translations and manual steps to keep these translations up to date. So, resource files in shared projects: not the best approach.


Resource files in PCL project

Our second option is to put resource files in a PCL project. Let’s see how this options stands with our goals. You can perfectly add a PCL project to a universal app solution:

PCL for universal app

When you do this Visual Studio automatically sets the targets for the PCL project to “Windows 8.1” and “Windows Phone 8.1”:

PCL project properties

I suggest to change the “Default namespace” of the PCL project to match the default namespace of the other projects as well. In the PCL project, create a folder structure that is compatible with the project’s default language, in my case “en-US”. Add a new resource (.resw) file to the folder. You should have something like this:

PCL folder structure

(note: you can change the PCL default language by editing the PCL project file. Look for the node DefaultLanguage)

It is required for the resource file to be inside a folder with a name that matches the project default language, otherwise MAT will not find any strings and the .xlf files will not be generated.

Since the targets for the PCL are “Windows 8.1” and “Windows Phone 8.1” the resource files you can add are of type .resw. We already explained that .resw resources do not have an associated strong type and we cannot use strings programmatically. To work around this we will use ResW File Code Generator: in the .resw file properties we will specify values for the properties “Custom Tool” and “Custom Tool Namespace” to be “ReswFileCodeGenerator” and the namespace of our project, respectively:

resw file properties

This will generate a strong type associated with the resource file at compile time.

You can follow the approach explained in How to use the Multilingual App Toolkit (MAT) in a Portable Class Library (PCL) project to see how to use MAT on a PCL:

  • Enable MAT for the PCL project
  • Add translation languages to the project (in my case I’m adding “es” and “it”)

Your PCL project should now look as this:

PCL with MAT

At this point, when you compile the PCL project, all the strings in the default resource file will be copied to the .xlf files by MAT. You can now start translating using MAT.

There are some steps we need to do in order to finish this:

  • Make sure you add the PCL project as a reference to both Windows and Windows Phone projects
  • In the shared folder, create a structure such as the following:

Shared folder structure

Basically you need to create a resource file for each language you support and this resource file needs to be inside a folder with the name of the locale. The .resw files in this shared folder structure can be empty. This is a requirement of both Windows and Windows Phone projects in order to know that the application supports all those languages. At this point you can start using resource strings in the UI as well as programmatically (see more details about how to use PCL resources in cross platform apps by reading the article Internationalization of a Cross Platform Application)

Let’s review if we have accomplished our goals:

  • To have a single place where we can specify strings and have these strings shared by all the platform projects: Yes
  • Strings can be used in the UI as well as programmatically: Yes
  • Use the Multilingual App Toolkit to handle all the translations: Yes


Sample App

You can find a sample VS project here:


Conclusion

In summary, having our resources in a PCL project is still the best option. Shared projects is an excellent idea, but it still lacks support for some other features, as in this case, to be able to handle resource files using MAT and to have .resw files generate a strong type. Let’s hope that the guys in the MAT team provide a way to use MAT in shared projects in the future. In the meantime, let them know that we need this by voting on the user voice idea.

8 Comments

The use of PCL projects and paradigms such as MVVM allow for a great code sharing between all the different platform projects. The recently announced shared projects in Visual Studio 2013 Update 2 and the support of shared projects by Xamarin improve the code sharing when developing for multiple platforms. I’ve been using Xamarin tools for Visual Studio to create cross platform applications for mobile devices, but this is not enough. If you truly want to use MVVM, data binding, etc. then you need to resort to complementing frameworks. I’ve been using MvvmCross, as it is one of the most complete and mostly adopted frameworks for using MVVM in non-windows platform developments (such as in iOS and Android) with Xamarin.

MvvmCross is an excellent framework and library. It provides nuget packages for the core and a set of plugins for platform specific functionality. It’s very easy to adopt and understand, thanks to the great tutorials published by its author, Stuart Lodge, in its N+1 series. In all, is a must if you want to create cross platform applications. However, there is one thing I don’t like about MvvmCross: internationalization support.

Internationalization of applications using MvvmCross (explained in this N+1 series video) in my opinion has the following shortcomings:

  • Looks like a proprietary (non-standard) way of implementing internationalization
  • It is based on platform specific plugins
  • Localization resources are defined in the platform projects and not in the PCL project. This means that you will have to repeat the strings and localization on each platform.
  • It is based on JSON (not that I don’t like JSON, but I don’t like it when used in something that it is not standard)
  • There are no tools that will allow to easily translate the strings

Stuart, at the end of the video, suggests that there might be other ways to do internationalization, by using resource files. Resource files are supported by Visual Studio and also by Xamarin. However, resource files are not supported in the platform specifics. At the end of the video Stuart points at this url for an alternative approach to internationalization using resource files. After reading the article I was not satisfied, the article was not clear and I had a difficult time following it. I thought that there must be a better approach to it.

I wanted a solution to internationalization that complies with the following guidelines:

  • It must be based on resource files
  • Resource files should be maintained in only one project
  • I should be able to use the excellent Multilingual App Toolkit tool to maintain the translation
  • It should use the platform specific way of implementing internationalization

Finding a solution to comply with all of the above guidelines was not easy, so I wanted to share the findings so other people will save time digging in the internet for a better solution to internationalization.

I will use a solution such as the one used in the article A sample cross platform application using MVVM, but you can use any existing solution that has a PCL project and platform specific projects for WinRT, Windows Phone, iOS and/or Android.


Using the Multilingual App Toolkit in a PCL project

I found this article on the internet that explains how to use the Multilingual App Toolkit in a PCL project. The article explained what I wanted to accomplish, but it didn’t explain all that needs to be done in order to make it work. It lacks some important steps to actually have it all working. The article also shows part of the whole solution: it only explains the internationalization for WinRT and Windows Phone platform and doesn’t explain how to extend this to iOS and Android projects. I suggest you read the article since it provides a good explanation of the difference of resource files between PCL, WinRT and Windows Phone. Following is a more detailed explanation of how the use the Multilingual App Toolkit in a PCL project

I’m assuming that you have already installed the Multilingual App Toolkit extension in your Visual Studio. If you haven’t please do install it before continuing.

We will use the Windows Phone project as the main project for our internationalization work. The reason is because Windows Phone and PCL projects use the same .resx files for resources, and also because it is much easier to use the Multilingual App Toolkit from a Windows Phone project.

Resource file created as part of the WP8 projectWhen you create a Windows Phone project, the Visual Studio template includes a Resources\AppResources.resx resource file. We will add a copy of this resource file in the PCL project since this will need the resource file as part of its file structure. We will then remove the Windows Phone resource file and add a link to the resource file defined in the PCL.

Follow these steps:

  • In the PCL project, create a Resources folder
  • Add a copy of the AppResources.resx file located in the Windows Phone Resources folder
  • Make sure that the custom tool used for this added file is PublicResXFileCodeGenerator, and that the resource file has also a designer file associated with it.
  • Delete the AppResources.resx file from the Resources folder of the Windows Phone project
  • In the Windows Phone project, in the Resources folder, add a link to the AppResources.resx file of the PCL project
  • Make sure that the custom tool used for this linked file is empty
  • In the Windows Phone project, delete the file called LocalizedStrings.cs

Your solution structure should now look as this:

Solution structure after performing the steps

Now, follow these steps:

  • With the Windows Phone project selected, in Visual Studio menu go to “Tools” and then select “Enable Multilingual App Toolkit”. This will add the pseudo language to the project (which you can delete if you want to).
  • Right click on the Windows Phone project and from the menu select the “Add Translation Language…" option. This will open the Multilingual App Toolkit dialog to add languages to your project.
  • Add as many languages as you need. In my case, and for this example, I’m adding Spanish and Italian (no culture specific)
  • In the PCL project, select the option to “Show All Files” on the Solution Explorer
  • In the PCL project, refresh the Resources folder. You should see a .resx file for each of the added languages.
  • Select all then .resx files and then right click and choose “Include in Project”. This will add those resources files to the PCL project.

Your solution structure should now look as this:

Solution structure after adding the new languages

Now, we need to add a service to our PCL project to provide translated strings. Follow these steps:

  • In the PCL project, open the default resource file, Resources\AppResources.resx, and change the access modifier from “Internal” to “Public”

Access Modifier

  • In the Services folder, add a new class called ResourceService.cs and replace the code with the following:
using SampleApp.Resources;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public class ResourceService
    {
        public static AppResources Resources;

        public AppResources LocalizedResources
        {
            get
            {
                return Resources ?? (Resources = new AppResources());
            }
        }
    }
}
  • On the Windows Phone project, open App.xaml and replace the following line:
<local:LocalizedStrings xmlns:local="clr-namespace:SampleApp" x:Key="LocalizedStrings"/>

with this line:

<services:ResourceService x:Key="LocalizedStrings" />

make sure you define the services namespace to point to the PCL assembly.

  • On the WinRT project, open App.xaml and add the following resource:
<services:ResourceService x:Key="LocalizedStrings" />

make sure you define the services namespace to point to the PCL assembly.

Now, we need to configure the projects. Follow these steps:

  • In the Windows Phone project, right click and choose “Properties” to see the project properties. In the “Application” tab, make sure that the section “Supported Cultures” list all the languages available for the application.
  • In the WinRT project, open the manifest file, Package.appxmanifest, and in the “Application” tab make sure that “Default language” is set to the same language used as your default language in the Windows Phone project.

Now you can start using the Multilingual App Toolkit to translate your resources. As an example, let’s create a new string:

  • In the Windows Phone project, double click the Resources\AppResources.resx file
  • Add a new string (in this example “users”) and its translation to the default language (in this example “en”)

Resource file

  • Compile the solution
  • The Multilingual App Toolkit will update the .xlf files, and also update the .resx files, which are located in the PCL project file structure.
  • On the Windows Phone project, double click any of the .xlf files to open the Multilingual Editor and start doing the translation of the new strings

Multilingual editor

At this point we have the following:

  • All resource files are in the PCL project
  • The main resource file is edited from the Windows Phone project
  • We can use the Multilingual App Toolkit, from the Windows Phone project, to edit translations
  • We have a service in the PCL project that will provide translated strings for all projects

Let’s see now how can we use this to translate the user interface of a WinRT and a Windows Phone project (iOS and Android will be explained later)


Translation in the PCL

The PCL has no user interface, so we need to worry only about how to get translated strings from code. Resource files automatically generate a strongly typed class with all the resources (see the associated file AppResources.Designer.cs file). We can use this class to get access to the strings:

string label = AppResources.users;


Translation in Windows Phone

Remember, from all the previous steps, that we have a ResourceService class in our PCL project to provide translated strings. We have created a static resource in the App.xaml that creates an instance of this service.

We can get translated strings in XAML by using data binding against the service. Here is an example for the string “users” created before:

<TextBlock Text="{Binding LocalizedResources.users, Source={StaticResource LocalizedStrings}}" />

Notice the use of the static resource defined in App.xaml, which we named LocalizedStrings. From code, we can use the same service, as follows:

label.Text = AppResources.users;


Translation in WinRT

We use a similar approach to the translation in Windows Phone. We have also created the static resource in App.xaml. In XAML you get a translated string as follows:

<TextBlock Text="{Binding LocalizedResources.users, Source={StaticResource LocalizedStrings}}" />

Notice the use of the static resource defined in App.xaml, which we named LocalizedStrings. From code, we can use the same service, as follows:

label.Text = AppResources.users;

There is one drawback to doing translation this way in WinRT: we don’t get to use the x:Uid way of translating UI elements. But this is not a problem since this way only applies to WinRT and we want to do translations once for all platforms.


Translation in Other Platforms

Let’s see how we are on our goals:

  • It must be based on resource files: Yes! We use resource files to declare our strings
  • Resource files should be maintained in only one project: Yes! We use the Windows Phone project to maintain the resources
  • I should be able to use the Multilingual App Toolkit tool to maintain the translation: Yes! We use the Multilingual App Toolkit from the Windows Phone project to translate our strings.
  • It should use the platform specific way of implementing internationalization: Yes for Windows Phone and WinRT, no yet for iOS and Android.

iOS and Android do not use resource files, they use their own method of declaring internationalization strings. So far our solutions doesn’t work for these two platforms.

There is a very good article by Chris Miller in Code Magazine called Cross-Platform Localization for Mobile Apps where the author provides a very good explanation of how internationalization should be done in iOS and Android.

Resources in Android

In Android, resources are specified in XML files, and are contained in folder structure inside the Resources folder, where each folder is specified as Values-<languageCode>:

Android resources

The contents of a resource file is as follows:

Android resources XML

Resources in iOS

In iOS, resources are specified as text files, and are contained in a folder structure in the root of the project, where each folder is specified as <languageCode>.lproj:

iOS resources

Each folder has a Localizable.strings file containing the following structure:

iOS resources strings


A Partial Solution

In the Code Magazine article, the author solves the gap between resource files and the iOS/Android method using a very clever approach based on T4 templates. The author provides the T4 templates in the article, but I thought these could be improved. The ones provided by the article require some manual modifications before you can use them. Also, in the case of iOS you have to use string literals in order to reference the strings, and we don’t like to use string literals, we would rather use an auto-generated class with all the strings as constants. So I think this is a partial solution to the problem.

As I did in the case of the resources files for windows, I thought I could give a better approach to the one provided in Code Magazine. So, here we go.


An Improved T4 Template to Generate iOS/Android String Resources

For this we will need a couple of extensions from the Visual Studio gallery. We need the T4 Toolbox, and optionally if we want to edit the T4 templates, a T4 editor such as the Tangible T4 Editor. Once the extensions are installed, restart Visual Studio and reopen your solution.

We will create the T4 templates in our PCL project. In the PCL project, under the Resources folder, add a new T4 template (select a “Text Template” item from the “Add New Item…” dialog box in VS). Call the template Resx2OthersTemplate.tt, and replace its content with the following:

<#@ include file="T4Toolbox.tt" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Xml.Linq" #>
<#@ Assembly Name="System.Windows.Forms" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Collections" #>
<#@ import namespace="System.Collections.Generic" #> 
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#+ 

public static class ResxUtilities
{
    public static Dictionary<string, string> GenerateResources(string resourceName)
    {
        Dictionary<string, string> items = new Dictionary<string, string>();
        string locale = GetLocale(resourceName);

        if (locale != "")
        {
            locale = "_" + locale;
        }

        // Read in the .resx file and collect the data elements
        if (File.Exists(resourceName))
        {
            XDocument document = XDocument.Parse(File.ReadAllText(resourceName));

            foreach(var item in document.Element("root").Elements("data")) 
            { 
                string Name = EscapeName(item);
                string Value = EscapeValue(item);

                items.Add(Name, Value);
            }
        }

        return items;
    }

    public static string GetNameSpace(string filename)
    {
        string [] words = filename.Replace(".\\", "").Split(new char[] {'.'});
        return words[0];
    }

    public static string GetLocale(string filename)
    {
        filename = Path.GetFileName(filename);
        string [] words = filename.Replace(".\\", "").Split(new char[] {'.'});

        if (words.Length > 2)
        {
            return words[1];
        }
        else
        {
            return "";
        }

    }

    public static string EscapeName(XElement item)
    {
        string name = item.Attribute("name").Value;
        return Regex.Replace(name, "[^a-zA-Z0-9_]{1,1}", "_");
    }

    public static string EscapeValue(XElement item)
    {
        XElement vitem = item.Descendants().FirstOrDefault();
        string name = vitem.Value;
        name = name.Replace("'", "\\'");
        return name;
    }

    public static string GetLanguage(string filename)
    {
        string lang = null;
        var f = Path.GetFileName(filename);
        var foo = f.Split('.');
    
        if (foo.Count() > 2)
        {
            if (foo[1].Length == 2)
            {
                lang = foo[1];
            }
        }
        if (foo.Count() == 2)
        {
            lang = "en";
        }
    
        return lang;
    }
}

public class Resx2AndroidTemplate : Template
{
    public string ResxFileName {get; set;}

    public override string TransformText()
    {
        string fullname = this.Context.Host.ResolvePath(ResxFileName);
        Dictionary<string, string> items = ResxUtilities.GenerateResources(fullname);

        int l = items.Count;

        WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
        WriteLine("<resources>");

        foreach(KeyValuePair<string, string> pair in items)
        {
            Write("  <string name=\"");
            Write(pair.Key);
            Write("\">");
            Write(pair.Value);
            WriteLine("</string>");
        }
        WriteLine("</resources>");

        return this.GenerationEnvironment.ToString();
    }
}

public class Resx2iOSTemplate : Template
{
    public string ResxFileName {get; set;}

    public override string TransformText()
    {
        string fullname = this.Context.Host.ResolvePath(ResxFileName);
        Dictionary<string, string> items = ResxUtilities.GenerateResources(fullname);

        int l = items.Count;

        foreach(KeyValuePair<string, string> pair in items)
        {
            WriteLine(String.Format("\"{0}\"=\"{1}\";", pair.Key, pair.Value));
        }

        return this.GenerationEnvironment.ToString();
    }    
}

public class Resx2ClassTemplate : CSharpTemplate
{
    public string ResxFileName {get; set;}

    public override string TransformText()
    {
        #>
namespace <#= DefaultNamespace #>
{
    public static class Strings
    {
<#+
        string fullname = this.Context.Host.ResolvePath(ResxFileName);
        Dictionary<string, string> items = ResxUtilities.GenerateResources(fullname);

        foreach(KeyValuePair<string, string> pair in items)
        {
#>
        public static string <#= Identifier(pair.Key) #> = "<#= pair.Key #>";
<#+
        }
#>
    }
}
<#+
        return this.GenerationEnvironment.ToString();
    }    
}
#>

In the same folder add another T4 template and call it Resx2Others.tt, and replace its content with the following:

<#@ template debug="true" hostSpecific="true" #>
<#@ output extension="log" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.IO" #>
<#@ assembly name="EnvDTE" #>
<#@ include file="Resx2OthersTemplate.tt" #>
<#
    // Create instances of the templates for iOS and Android
    Resx2AndroidTemplate androidTemplate = null;
    Resx2iOSTemplate iosTemplate = null;
    Resx2ClassTemplate classTemplate = new Resx2ClassTemplate();

    var hostServiceProvider = (IServiceProvider)Host;
    var dte = (EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE));        

    foreach(EnvDTE.Project project in dte.Solution.Projects)
    {
        string projectName = project.Name.ToLower();
        if(projectName.Contains("ios") || projectName.Contains("touch"))
        {
            iosTemplate = new Resx2iOSTemplate();
            iosTemplate.Output.Project = project.FullName;
            iosTemplate.Output.ItemType = "Content";
        }
        else if(projectName.Contains("droid"))
        {
            androidTemplate = new Resx2AndroidTemplate();
            androidTemplate.Output.Project = project.FullName;
            androidTemplate.Output.ItemType = "AndroidResource";
        }
        
    }
    
    // Set the current directory to the .tt folder
    Directory.SetCurrentDirectory(Path.GetDirectoryName(Host.TemplateFile));

    // Set the file mask for the resx files to read from
    var files = Directory.GetFiles(".", "AppResources*.resx");

    foreach(var resxFile in files)
    {
        WriteLine("Processing file {0}", resxFile);

        // Fix up the file name
        string resxFileName = resxFile.Replace(".\\","");
                
        string locale = ResxUtilities.GetLocale(resxFile);

        if (!(locale.Equals("qps-ploc", StringComparison.CurrentCultureIgnoreCase)))
        {
            if (!string.IsNullOrWhiteSpace(locale))
            {
                locale = "-" + locale.Replace("-", "-r");
            }

            // Android
            if(androidTemplate != null)
            {
                androidTemplate.ResxFileName = resxFileName;
                string androidStringsFolder = @"Resources\Values" + locale;

                // Set the destination filename and path and transform the resource
                androidTemplate.Output.File = Path.Combine(androidStringsFolder, Path.GetFileName(Path.ChangeExtension(ResxUtilities.GetNameSpace(resxFile), ".xml")));
                androidTemplate.Output.Encoding = Encoding.UTF8;
                androidTemplate.Render();
            }

            // iOS
            if(iosTemplate != null)
            {
                iosTemplate.ResxFileName = resxFileName;
                // Don't need the locale, just the language
                var lang = ResxUtilities.GetLanguage(iosTemplate.ResxFileName);

                if (lang != null) 
                {
                    iosTemplate.Output.File = Path.Combine(lang + ".lproj",  "Localizable.strings");
                    iosTemplate.Output.Encoding = Encoding.UTF8;
                    iosTemplate.Render();
                }
            }

            // generate a class file with constants only for the main resource file (which doesn't specify a locale)
            if(String.IsNullOrWhiteSpace(locale))
            {
                classTemplate.Output.File = "Strings.cs";
                classTemplate.ResxFileName = resxFileName;
                classTemplate.Render();
            }
        }
    }
#>

This is the main T4 template, the one that needs to run. Notice that this template includes the Resx2OthersTemplate.tt template. These templates are just a rewrite of the templates found in the Code Magazine article, so the credit should go to Chris Miller for coming out with this solution. All I did was to improve the idea, adding some automations to remove constants and also to create a strong typed class with all the string names to be used in code. This is a summary of the improvements I made:

  • The template automatically detects if a iOS and/or Android project is part of the solution. If an iOS/Android is not part of the solution then it skips the creation of strings for that platform.
  • The template automatically finds out the name of the projects, so there is no need to specify these in the template
  • The template automatically generates a Strings.cs file in the PCL project providing constants to be used instead of string literals in the code.
  • The template sets the appropriate content type for iOS to “Content” and for Android to “AndroidResource”
  • The code was changed to be more modular

T4 templates do not run automatically on build (unless you have the pro version of the Tangible T4 editor). There are some solutions in the internet to have T4 templates run on build, but I will not mention them here since this article is already too long. For now, remember to run the T4 template (right click on Resx2Others.tt and choose from the menu the option “Run Custom Tool”) every time you modify your translations. Of course you don’t need to do this on every build, I guess that the way you translate an app is to start with one (e.g. Windows Phone) and once you have all the app translated, you use the Multilingual App Toolkit to translate strings, and then you run the T4 template to update the translation strings for both iOS and Android.


Translation in Android

When you run the T4 template you will automatically generate the Values-<languageCode> folders and the AppResources.xml files with all the strings. To translate elements of the UI you will use the following:

<TextView
    android:text="@string/users"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:id="@+id/textView1" />

In code you will use the following:

label.Text = Resources.GetString(Resource.String.users);


Translation in iOS

When you run the T4 template you will automatically generate the <languageCode>.lproj folders and the Localizable.strings files with all the strings. To translate in code you will use:

label.Text = NSBundle.MainBundle.LocalizedString(Strings.users, Strings.users);

Notice that we are using a class named Strings instead of the string literals. This class was generated as part of the T4 template and is included in the PCL project. This class is generated as follows:

public static class Strings
{
    public static string ResourceFlowDirection = "ResourceFlowDirection";
    public static string ResourceLanguage = "ResourceLanguage";
    public static string ApplicationTitle = "ApplicationTitle";
    public static string AppBarButtonText = "AppBarButtonText";
    public static string AppBarMenuItemText = "AppBarMenuItemText";
    public static string users = "users";
}

With this auto-generated class we avoid the use of string literals in our projects.


Sample App

You can find a sample VS project here:


Conclusion

Let’s review again our goals:

  • It must be based on resource files: Yes! We use resource files to declare our strings
  • Resource files should be maintained in only one project: Yes! We use the Windows Phone project to maintain the resources
  • I should be able to use the Multilingual App Toolkit tool to maintain the translation: Yes! We use the Multilingual App Toolkit from the Windows Phone project to translate our strings.
  • It should use the platform specific way of implementing internationalization: Yes! for Windows Phone, WinRT, iOS and Android.

I think that this approach is a much better one than the one used in MvvmCross. Let me know what you think, leave a comment!

3 Comments

When I design a new application I always design it with localization in mind. For cross platform applications my strategy is based on the use of resource files in a Portable Class Library (PCL) project. See my previous article on Internationalization of a Cross Platform Application for details on details about how to do localization in a cross platform application.

For localization I use the Multilingual App Toolkit (MAT). MAT was created to easy the task of localizing Windows Store and Windows Phone applications and since version 3.0 it could also be used in desktop applications (Asp.Net MVC, WinForms and WPF). But what about PCLs? Can MAT be used in PCLs? Well, if you’re using version 3.1 then yes, you can use MAT in PCLs. If you’re using version 3.0 or lower then yes, you can enable a PCL project for translation with MAT, and no, it doesn’t work as it is supposed to. Let’s show how to have MAT work in a PCL project and how to solve the compilation problem you get if your project was enabled with MAT 3.0.

Create a PCL project and enable MAT on it

(note: I’m using Visual Studio 2013 Update 2 and Multilingual App Toolkit 3.1)

Start Visual Studio and create a new “Class Library (Portable)” project and call it “Portable” (or whatever you want).

New PCL project

In the target frameworks dialog, add the targets you need. I will add the newest ones: “Windows Phone 8.1”, “Windows 8.1” and “.Net Framework 4.5”.

Choose target frameworks

The combination of target frameworks you choose will determine the kind of resources files you can add to the PCL project. There are basically two types of resource files:

  • .resx: This type of resource has a strong type associated to it which allows to use strings in code. It is used by Windows Phone Silverlight 8/8.1 and .Net Framework 4.5 targets.
  • .resw: This type does not have a strong type associated (although you can create one by using ResW File Code Generator). It is used by Windows 8/8.1 and by Windows Phone 8.1 targets.

When any of the selected targets uses .resx files then the PCL project will use .resx files. Otherwise the PCL project will use .resw files. In my case, since I chose “.Net Framework 4.5” as one of the targets my PCL project will use .resx resources. Having a strong type associated with my resource is very important later when using resources in the UI and app logic. (See my article on How to use Multilingual App Toolkit in Universal Apps for more information)

Now, delete the file Class1.cs generated by the Visual Studio wizard. The first thing you will be tempted to do is to enable MAT on the project. Select the project (not the solution) in Solution Explorer and from the Tools menu select “Enable Multilingual App Toolkit”. You will get the following message:

matNoResource

The message is pretty clear: we need to add a resource file before we can enable MAT. Let’s put our resource files in a folder called Resources. Right click on the project in Solution Explorer and from the popup menu select “Add” and then “New Folder” and call it Resources. New let’s add a resource file, right click on the newly created folder and from the popup menu select “Add” and then “New Item…”. The Visual Studio “Add New Item” dialog appears:

New resource

Select “Resource File” from the list of templates and name it Resources.resx. Add some strings to your resource file and make sure to that the “Access Modifier” for the resource is marked “Public” (cause we want to access to the strong type generated from other projects):

resource

Compile your project. Now we are ready to enable MAT on the project. Select the project (not the solution) in Solution Explorer and from the Tools menu select “Enable Multilingual App Toolkit”. When MAT is enabled by default the Pseudo Language file is added automatically to the project. You can remove this file (Resources.qps-ploc.xlf) if you want. Let’s add a new language.

Right click on the project in solution explorer and from the popup menu select “Add translation languages…”. MAT will open the “Translation Languages” dialog where you can select the languages your app will support. In my case I will add Spanish (es) and Italian (it). Click OK to close the dialog. MAT will add .xlf files to your project for each of the languages you chose. Your solution now should look something like this:

solution

Now, compile the project.

Solving the compilation error if using Multilingual App Toolkit prior to 3.1

If you’re not using the latest version of MAT (at the time of this writing is 3.1) then you will probably get the following error:

Error

If you enabled MAT and added languages to your project with a version of MAT prior to 3.1 then you will probably get the error above. If you installed MAT 3.1 new projects will be created fine, but old projects will still present the error. To fix this you will need to edit your project file. If you take a closer look to the build output you will find the following message: “No XLIFF language files were found.  The app will not contain any localized resources.” This means that MAT is not able to find the files with extension .xlf in the project. Right click on the project in Solution Explorer and from the popup menu choose “Unload Project”, and then again right click on the (now unloaded) project and select “Edit Portable.cproj”. Visual Studio will open the project file. Locate the following lines:

    <None Include="Resources\Resources.es.xlf">
      <Generator>XliffResxGenerator</Generator>
      <LastGenOutput>Resources.es.resx</LastGenOutput>
    </None>
    <None Include="Resources\Resources.it.xlf">
      <Generator>XliffResxGenerator</Generator>
      <LastGenOutput>Resources.it.resx</LastGenOutput>
    </None>

And change the node name from None to XliffResource:

    <XliffResource Include="Resources\Resources.es.xlf">
      <Generator>XliffResxGenerator</Generator>
      <LastGenOutput>Resources.es.resx</LastGenOutput>
    </XliffResource>
    <XliffResource Include="Resources\Resources.it.xlf">
      <Generator>XliffResxGenerator</Generator>
      <LastGenOutput>Resources.it.resx</LastGenOutput>
    </XliffResource>

Now, reload the project: right click on the project in Solution Explorer and from the popup menu select “Reload Project”. Now you should be able to compile the project.

Now I have my resource files in a PCL project, and since I’m using resx files I also have a public resources type that I can use in MVVM and databinding. See more details on how to do this in these two articles:

1 Comments

There are many options to manage data in Windows RT and Windows Phone. You can store data locally on the device using XML o JSON files, you can store data on the Cloud using Azure Mobile Services, you can use local databases, etc. Each one of these solutions have their advantages and disadvantages. In this article I will focus on the use of local databases, specifically using the SQLite database. You can find more information about SQLite on their web site www.sqlite.com. The good thing about SQLite is that it works on almost any platform you can think of, including of course Windows RT and Windows Phone. If you want to effectively use PCL and have most of the logic of the app shared between Windows RT and Windows Phone, then your best option (at least, at the time I write this article) is to use SQLite.

sqliteSQLite has SDK packages for both Windows RT (8.0 and 8.1) and Windows Phone (8) that can be installed as a .vsix package into Visual Studio. These are just wrappers to the sqlite library (written in C++) and their syntax is not what we would expect if our project is using C#/XAML. Besides, both SDKs are not suitable for a PCL project and we can’t have all the data access logic in our PCL project. Nick Randolph’s blog  has a very good 4-article series on how to use SQLite in a PCL library. He basically sets four goals: the project has to be blendable, has to use SQLite, all the data access logic needs to be in a PCL project, and the data access needs to be done using objects (LINQ style). I highly recommend reading the articles since it gives a step-by-step guide on how to install SQLite SDKs, and a bunch of nuget packages to achieve the goal. You will see that it is not easy to have it all configured, and having to do that every time you start a new project might be cumbersome. But, hey! there was no other alternative at the time he wrote the article, so kudos to him.

Fortunately, there is now a nuget package that will make things easier for you: SQLite.Net PCL. This is actually a set of packages you need to install (depending on the platform) in order to use SQLite. The thing is that setting it up is not trivial, you need to take the information from a bunch of blog posts, github docs, and even some source files, and there are some gotchas you need to be aware of. In this article I will summarize my experience in setting things up, hopefully to save time to those who would like to use the package. This is a step-by-step guide, so grab a cup of coffee and be ready to spend some time reading since it is a long article.

As with Nick’s articles, we will have the same 4 goals in mind (blendable, SQLite, PCL and LINQ). Of course, if we want to effectively achieve these goals we need to architecture our projects to use MVVM. I’m a big fan of MVVM Light, and this is what I will use in this guide.

Project structureLet’s start by creating a project structure such as the one we have in the figure. We have three projects, and the suffix for the project name describes it, but lets explain it in detail:

  • SampleApp.PCL: this is where all the logic for the app, including the data access layer, will be developed.
  • SampleApp.WinRT: this is the WinRT (in this case Windows 8.1) application. Notice that we have included a reference to the SampleApp.PCL project
  • SampleApp.WP: this is the Windows Phone (in this case, Windows Phone 8) application. Notice that we have included a reference to the SampleApp.PCL project

Notice the project structure is already organized to work with MVVM (notice the folder structure) and services and view models are already wired up with the IoC container. You can create your own project structure, or you can follow the structure I described in this set of articles:

The last article presents a sample application that uses device isolated storage for the data access layer. I will use the code in that article to change the data layer to SQLite. You can download the sample project from here.

Project configuration: the platform

Before continuing you need to change the platform of the project configuration. By default, the platform is AnyCPU. If you leave as this you will get errors when trying to install the nuget packages (and later, to run the project). This is because the SQLite’s SDKs are not built for AnyCPU, they must target a specific platform. The platform you choose should be based on the following:

  • For Windows Phone:
    • ARM: if you run on the device and to package the app for the Windows Store
    • x86: to debug and run on the emulator
  • For WinRT
    • x86: you can use this to debug and run on the simulator. You also need this to package the app for the Windows Store
    • x64: to package the app for the Windows Store

In the case of WinRT, when submitting the app in the Windows Store, you need to upload both x86 and x64 versions of your app.

For now we will use x86 on both the WinRT and WP projects. On VS, right click the solution and choose properties. On the configuration properties node change the platform from AnyCPU to x86 for both the WinRT and WP project:

platformConfiguration

SQLite.Net nuget package on the PCL project

SQLite.Net is actually a set of nuget packages. There is a common package for the PCL project, and then there is a package for each of the platforms you want to target. Let’s us start with our PCL project. On the PCL project, using the nuget package manager, install both SQLite.Net PCL (SQLite.Net-PCL) and SQLite.Net.Async PCL (SQLite.Net.Async-PCL) packages.

SQLite.Net nuget package on the WinRT project

For the WinRT project we need to install the same packages, plus the device specific package. The device specific package is the SQLite.Net PCL - WinRT Platform (SQLite.Net.Platform.WinRT). This package depends on a package called SQLite for Windows Runtime (Windows 8.1) – Redistribution (SQLite.WinRT.redist), which installs the SQLite SDK for the WinRT platform. The problem with this package (at the time of this writing) is that it targets a version of SQLite that is not the current one (when I write this article the latest SQLite SDK is version 3.8.4.3, and the version used by the nuget package is 3.8.4.1), so if you have already installed SQLite SDKs you will get an error when installing the nuget package. To workaround this, first uninstall the version of the SQLite SDK for the WinRT platform, and then add the nuget package.

If you have a version of the SQLite SDK that is equal or lower than the one targeted by the SQLite.WinRT.redist then you’re fine. If your installed version is greater then follow these steps (again, only do this if your version of the SQLite SDK is greater than the one targeted by SQLite.WinRT.redist):

  • Open Visual Studio, go to “Tools” menu, and then select “Extensions and Updates…”.
  • On the dialog box, on the tree that is displayed on the left, select “Installed –> SDK”.
  • Locate the extension called “SQLite for Windows Runtime (Windows 8.1)” and click Uninstall.
  • Restart Visual Studio

Now you’re ready to install the nuget packages. On the WinRT project, install the SQLite.Net.Platform.WinRT package. This will attempt to install first SQLite.WinRT.redist and you will notice that a .vsix package for the SQLite SDK is installed. Unfortunately the package will fail to install the first time, I guess because VS and the nuget package manager are not aware of the newly installed SDK. The workaround is to restart VS and then attempt to install the nuget package again. This time it should install without any problems.

Now, if you’re like me, who likes to use the latest version whenever possible, you would like to use the latest version of the SQLite SDK. These are the steps you need to use the latest version:

  • Uninstall the version of the SQLite SDK that was installed with the SQLite.WinRT.redist package using the steps outlined above. Close VS.
  • Download the latest .vsix package from the SQLite download page. At the time of this writing is version 8.3.4.3. You will probably download a .zip file, that’s ok, just change the file extension to .vsix. Install this package.
  • Now reopen your solution in VS. On the WinRT project expand the References node. You will see that the reference to the SQLite SDK has a warning: SQLite reference warning
  • Remove this reference.
  • Add a reference to the new SQLite SDK. You will find this reference on the node for Windows –>Extensions SQLite WinRT reference
  • Compile to make sure everything works

winrtReferencesOf course, if you do this, you need to repeat all the above every time you start a new project that uses SQLite. I whish that the SQLite.Net package would not depend on the SQLite.WinRT.redist, because this whole process is a pain in the a..., but anyway… let’s move on.

The SQLite.Net.Platform.WinRT will also install the base SQLite.Net-PCL package. For consistency, let’s add the SQLite.Net.Async-PCL as well. The figure shows how the WinRT project references should be at this point.


SQLite.Net nuget package on the WP project

If you haven’t done so before, install the SQLite SDK for the Windows Phone platform first. Download the latest .vsix package from the SQLite download page. At the time of this writing is version 8.3.4.3. You will probably download a .zip file, that’s ok, just change the file extension to .vsix and then install this package. Make sure you restart VS.

wpReferencesNow, on your WP project use the nuget package manager to install the the SQL.Net PCL – WindowsPhone8 Platform (SQLite.Net.Platform.WindowsPhone8) package.  The SQLite.Net.Platform.WindowsPhone8 will also install the base SQLite.Net-PCL package. For consistency, let’s add the SQLite.Net.Async-PCL as well.

Next, add a reference to the SQLite SDK. On the References node, choose “Add Reference..” and on the “Windows Phone –> Extensions” node select the SQLite SDK SQLite WP reference

The figure shows how the WP project references should be at this point.

Compile to make sure everything works fine.


Using SQLite

Now for the fun part, let’s change or data access to use SQLite. If you’ve followed along with my project we will make changes to our existing model and configure a new data access to be injected in our IoC container. If you’re not following along with my project, that’s ok, just create the files with the full code below.

The model

Let’s add an id property to our model. This id will be our primary key for the object. On the PCL project, on the “Models” folder, open the file Person.cs and make the following changes:

using GalaSoft.MvvmLight;
using SQLite.Net.Attributes;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Models
{
    public class Person : ObservableObject
    {
        private int id;
        [PrimaryKey, AutoIncrement]
        public int Id 
        {
            get { return id; }
            set { Set(ref id, value); }
        }

        private string name;
        public string Name
        {
            get { return name; }
            set { Set(ref name, value); }
        }
    }
}

Notice the use of the PrimaryKey and AutoIncrement attributes (which are self explanatory).

The storage service

SQLite is just a file in the device storage. We will save this file on the application isolation storage. We need to tell the SQLite API the location of this file on the device. The location of a file on the device is dependent of the device, so this needs to be handled by a service. In our project we already have a service that handles storage specific actions. On the PCL project, on the “Services\Interfaces” folder, open the IStorageService.cs file and add the highlighted code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public interface IStorageService
    {
        Task SaveTextAsync(string file, string text);

        Task<string> LoadTextAsync(string file);

        Task<string> GetPathForFileAsync(string file);
    }
}

The GetPathForFileAsync method will provide SQLite with the full path to the database file. To implement this new method go to the WinRT project and on the “Services” folder open the file StorageService.cs and add the method implementation as follows:

public async Task<string> GetPathForFileAsync(string file)
{
    StorageFile storageFile = await ApplicationData.Current.LocalFolder.CreateFileAsync(file, CreationCollisionOption.OpenIfExists);

    return storageFile.Path;
}

Notice the use of StorageFile API to create the file, and the CreationCollisionOption.OpenIfExists option used. For SQLite to work the file needs to exist. If the file doesn’t exists you will get an error when trying to use the SQLite API. This is one of the gotchas to be aware of. Notice also that the file is created on the application local folder.

The data service

We will create our data service for SQLite now. On the PCL project, on the “Services” folder, add a new class and name it SQLiteDataService.cs:

using SampleApp.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite.Net;
using SQLite.Net.Interop;
using SQLite.Net.Async;
using System.IO;

namespace SampleApp.Services
{
    public class SQLiteDataService : IDataService
    {

        private IStorageService storageService;
        private ISQLitePlatform sqlitePlatform;

        private SQLiteAsyncConnection connection;

        public SQLiteDataService(ISQLitePlatform sqlitePlatform, IStorageService storageService)
        {
            this.sqlitePlatform = sqlitePlatform;
            this.storageService = storageService;

            InitializeAsync();
        }

        private async Task InitializeAsync()
        {
            if (connection == null)
            {
                string databaseFile = await storageService.GetPathForFileAsync("database.sqlite");
                connection = new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(sqlitePlatform, new SQLiteConnectionString(databaseFile, false)));
                await connection.CreateTableAsync<Person>();
            }
        }

        public async Task<IEnumerable<Person>> GetPersonsAsync()
        {
            return await connection.Table<Person>().ToListAsync();
        }

        public async Task AddPersonAsync(Person person)
        {
            await connection.InsertAsync(person);
        }
    }
}

There are a couple of things to notice here. Remember that we are using the PCL version of the SQLite.Net package. This PCL version actually relies on the platform specific implementation of SQLite. All PCL methods are based on the ISQLitePlatform interface. Each platform implements this interface and handles the platform's specific details for SQLite. In our case we are injecting the specific platform implementation using our IoC container. Notice how the constructor expects a ISQLitePlatform object which is assigned to a local property (lines 18 and 24).

The other thing to notice is the initialization of the database. First we get a reference to the file using our storage service (line 34) and then a SQLiteAsyncConnection is instantiated by passing the ISQLitePlatform implementation and the full path to the database file (line 35). I’m using the async implementation of the API, but you can use the synchronous one as well. The key thing is the platform specific object passed on the constructor.

The other parts of the file don’t deserve a details explanation since they just use the SQLite API (for more info refer to the SQLite docs or to the SQLite.Net docs).

We need to tell the IoC container to use this new data service. On the PCL project, on the “Services” folder, open the LocatorService.cs file and make the changes highlighted below:

using GalaSoft.MvvmLight;
using GalaSoft.MvvmLight.Ioc;
using Microsoft.Practices.ServiceLocation;
using SampleApp.ViewModels;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public class LocatorService
    {
        static LocatorService()
        {
            ServiceLocator.SetLocatorProvider(() => SimpleIoc.Default);

            // Services
            if (ViewModelBase.IsInDesignModeStatic)
            {
                SimpleIoc.Default.Register<IDataService, Design.DataService>();
            }
            else
            {
                SimpleIoc.Default.Register<IDataService, Services.SQLiteDataService>();
            }

            // View Models
            SimpleIoc.Default.Register<IMainViewModel, MainViewModel>();
        }

        public IMainViewModel MainViewModel
        {
            get
            {
                return ServiceLocator.Current.GetInstance<IMainViewModel>();
            }
        }

        public static void Cleanup()
        {
        }
    }
}

Also, we need to register with the IoC container the platform specific implementation of the ISQLitePlatform interface on both the WinRT and WP projects. On the WinRT project, on the “Services” folder, open the LocatorService.cs file and make the changes highlighted below:

using GalaSoft.MvvmLight;
using GalaSoft.MvvmLight.Ioc;
using Microsoft.Practices.ServiceLocation;
using SQLite.Net.Interop;
#if NETFX_CORE
using SQLite.Net.Platform.WinRT;
#else
using SQLite.Net.Platform.WindowsPhone8;
#endif
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public class DeviceLocatorService
    {
        static DeviceLocatorService()
        {
            ServiceLocator.SetLocatorProvider(() => SimpleIoc.Default);

            if (ViewModelBase.IsInDesignModeStatic)
            {
            }
            else
            {
            }

            #if NETFX_CORE
            if (!SimpleIoc.Default.IsRegistered<ISQLitePlatform>()) SimpleIoc.Default.Register<ISQLitePlatform, SQLitePlatformWinRT>();
            #else
            if (!SimpleIoc.Default.IsRegistered<ISQLitePlatform>()) SimpleIoc.Default.Register<ISQLitePlatform, SQLitePlatformWP8>();
            #endif

            if (!SimpleIoc.Default.IsRegistered<IStorageService>()) SimpleIoc.Default.Register<IStorageService, StorageService>();
        }

        public static void Cleanup()
        {
        }
    }
}

Notice the use of compiler directives, which is another trick to share files between WP and WinRT projects. In our case the LocatorService.cs file is a shared file (the WP project as a link to it from the WinRT project). We register the appropriate ISQLitePlatform interface: on WinRT we use SQLitePlatformWinRT and on WP we use SQLitePlatformWP8. With this, the IoC container will inject the appropriate SQLite implementation in the PCL data service.

Compile and make sure everything works fine. You should now be able to add objects to your SQLite database on both WinRT and WP, and all the data access logic developed on the PCL project. You can see the contents of your SQLite database using a tool such as sqlitebrowser.

You can download the sample project for this article from here: