| Comments

So you got that shiny new Surface device today?  I’m sure you spent the first few hours just opening it up, setting your personal experiences to your desire, re-installing and discovering new apps.

But you are a developer and now you want to see how your app looks on this great device…Here are some tips to get you quickly started.

Setting up the tools

One thing to keep in mind is that Surface is NOT a full ‘desktop’ machine and runs on an ARM processor.  This means to that you cannot install Visual Studio directly on the Surface RT device.  You will need to still ensure that you have a full development environment set up.  But the first thing you will need is to get the remote developer tools for Visual Studio *for your target remote device* architecture.  For Surface RT, this is the ARM tools.

You can get them here: Microsoft Visual Studio Downloads.  Scroll down a bit and look in the “Additional Software” section.  Grab the Remote Tools for Visual Studio 2012 section. 

Remote tools for VS download

You can do this either from the desktop browser on the Surface or from your own desktop and download the remote tools for ARM to a USB key.

On your device, install the remote tools for ARM.  No other Visual Studio installation is required here.  Just run the installer.  When completed you should have a tile on your screen for the tools:

Remote Debugger tile

Now you can get started with your remote debug session!

Configuring the Remote Debugger

After install go ahead and launch the Remote Debugger on your device.  You’ll be presented with (maybe after some firewall questions you need to authorize) the remote debugger now running and in default mode:

Remote Debugger launched

By default, it is set up secure.  This means that in order to attach a remote session you’d need to ensure permissions are correct, etc.  Now since your ARM device isn’t likely on the same domain/workgroup as your developer machine this may be tricky.  Personally, I turn off the authentication options to make my developer experience smoother.  Now of course, you shouldn’t leave your device in this state, but you can close the remote debugger when complete.  Here is the config that I use on my remote debugger:

Remote Debugger config

This allows me to just launch the app on the remote machine without having to use any special authentication tricks since the machine isn’t on my domain, etc.  My remote environment is now set up and ready for me to launch an app and start debugging!

Launching an App on the remote debugger

Now that your remote device is configured and listening, you want to start your app and debug remotely.  Once you have your app in Visual Studio you’ll want to change your launch target to “Remote Device” in the IDE.  This is in the toolbar or in the project properties.  For a C# application it looks like this:

Select Remote Machine target

Once you launch that you’ll be able to select your device.  Now if you are on your home network, with no domains and all on the same subnet, you may just be able to discover your device in the remote debugger connections window.  However you can also just specify the machine name.  Be sure to match the authentication method in this window with what you chose when you set up the remote debugger…in my case “none.”

Remote Debugger connections window

Now that the configuration is there (and selected), when I run (F5) the application it will attempt to deploy it on the remote device.  When you run you’ll notice the remote debugger will show the connections:

Remote debugger connected window

And in your developer workstation you’ll be able to set breakpoints, investigate watch parameters, etc.  All the same stuff you normally do is still available to you.

Summary

Now that you have a Surface (or other Windows RT device) running on an ARM processor, this remote debugging toolset/workflow will be important to you.  The great thing is that once it is set up and you understand the flow, it is very simple and seamless to use.  This presents a great opportunity for you to debug and profile your apps on Windows RT to see any areas that you might be able to optimize for the target device.  And all you need is Visual Studio Express for Windows 8 (free) and the Remote Debugger tools for Visual Studio 2012 (free)

Hope this helps!

| Comments

In some of our internal discussion lists there was some questions about how to host certain content for their application.  Most of the discussion came up from apps needing a privacy policy (Rule 4.1 from the Windows Store App Certification Requirements).  Some folks had apps they just developed, but no “site” or service they were using.  But they needed to host a privacy policy.  Lots of thoughts were floated around and I suggested Azure Free Web Sites as an option.  I originally suggested it as a simple way you could just have a URL to a privacy policy, but…duh, you could easily use it as a very quick marketing site for your app.

Creating a web site in Windows Azure

If you didn’t know, Windows Azure allows you to create a free web site!  It is very quick and simple to set up once you have your Azure account set up.  After doing that go to the portal and choose to create a new Web Site.  I recommend picking from the gallery and choosing WordPress.  There is such a vast ecosystem around WordPress as a CMS system that it is simple to use and set up. 

NOTE: Of course you can use others, even a static web site using the TFS/Git deploy feature even.  Do whatever you want.  I just think WordPress is great and allows you to scale your site features without writing code, etc.

Choose to create a site from the gallery:

Azure Find Apps image

This will walk you through a wizard to pick some names and options.  It is pretty self explanatory.  Once completed you will see your site provisioned in the portal.

Azure web sites image

Just click that link and you will be taken to your site…which for WordPress will be the initial setup page to choose your login.

Choosing a WordPress Theme

Once in the WordPress admin site, choose the Appearance option on the left, then the Themes sub-menu.  Once there, go to the Install Themes tab to pick a theme from the gallery.  Optionally you can grab a premium theme from various sites.  There is one called “Responsive” (search for that term) that is in the built-in gallery, free, and actually serves the basic needs well.  We’ll choose that one:

Responsive theme install image

After you pick it you’ll want to Activate it as the current theme.  Now let’s do some simple configuration.

Configure the WordPress site

In order to serve the goal of making this more of a marketing site and to host our privacy policy we don’t need commenting on pages.  Additionally we want “nice” URLs for our site.  Let’s start with a few simple tweaks.

First (in the WordPress admin site) go to Settings->Permalinks.  Choose the “Post name” option.

WordPress permalink settings image

Next go to Settings->Discussions.  In the top uncheck the “Allow people to post comments on new articles” in the top:

WordPress discussion settings image

This will prevent commenting on content.  Next using the Responsive theme we can configure the home page.  Go to the Appearance->Theme Options section.  You’ll see a “Home Page” option for the Responsive theme.  You can set the main text, tagline and two other options.  This gives you the chance to set the URL for your app that is provided from the Windows Store. 

Here are some posts to help you find URLs for your app: Linking your apps on the web and Connect your web site to your store app.

Here is an example:

Responsive theme home page config image

Now go to Appearance->Widgets to modify some information on the home page for the 3 widget areas.  These could be simple things like a quick blurb about what the app is, maybe some top features, or whatever.  In the admin widget area you’ll see Home Page Widget 1,2,3.  Simply add the “Text” widget to these and you can add the title/text for this:

Home page widget image

You can also remove the theme logo to provide your own logo in the Appearance->Header section to provide your own image.  You can also specify a custom image on the main “hero” section of the home page instead of the default image and specify that.  Once all these quick tweaks are done, your home page is done.  Visit the site to see the quick changes.

Now let’s add our privacy policy so we have a permanent place for our privacy details.

Add a Privacy Policy page

From the WordPress admin site, go to Pages->Add Page.  Name it “Privacy Policy” and then type in your privacy policy text.  You can modify the text using HTML formatting to fit your needs.  Make sure to use the “Full Width Page” template so that it shows the entire page.  Notice the URL for the permalink.  I recommend keeping it simple with “privacy” or “privacy-policy” as the name (which should be default):

Privacy page editing image

Once you Publish the page, your site will now have a link to “Privacy Policy” on the home page and you can use that in your site, for your certification process and other areas you may need it.

You can now provide a link within your app to your privacy policy as well.  Andy has a simple method of adding it to the Settings charm and simply linking to your online site now.  You can visit the site to confirm it looks as you wish:

Privacy page image

Done!  You can see this example at http://timscoolapp.azurewebsites.net

Profit!

Now you have a free site on Azure to host marketing your app.  Of course this helps those getting over the hump with providing a nice place for a simple privacy page, but also enables you to have a way to provide other pages for your app.  You could provide more detail on features, have a form to collect feature requests, whatever.  WordPress is very flexible and the same process you used to create the privacy page can be used for other full-page content.  Or you can explore what WordPress has to offer you.  Again, there are many different ways you can do this and even within WordPress other themes you could choose.  However I think the Responsive one is a simple one to get started with as a base.

You may want to not have “azurewebsites.net” as your site URL as well.  If you wanted you can migrate to a shared instance (not free) and have custom domain name resolution on your Azure site as well.

Hope this helps!

| Comments

I was helping a friend today doing some over-the-shoulder code review and suggestions for his Windows app he’s writing for the store.  In doing this I asked a question about how to perform a certain action.  He indicated that he put those functions in the AppBar and was it not obvious I was supposed to use them.  I looked at the AppBar again and found out why I wasn’t drawn to them.  First, the labels he used weren’t descriptive to me and relied too much on me looking at the actual icon.  Second, however, is that when I hovered over them I received no visual feedback I was expecting and immediately thought they were just disabled (the color was slightly gray as well which probably didn’t help this).

I mentioned these to him and noted he should use the AppBarButtonStyle base definition that comes in the Visual Studio templates as a guide and just set the appropriate content.  He proceeded to let me know that he received the vector data from Syncfusion’s Metro Studio product.  We then began to examine the vector data.  Metro Studio is doing probably too much than it needs to for AppBar button definitions.  To be fair, it serves a goal to get Windows UI style icons for the developers in XAML form.  This is great!  However, if my intention is to use them for AppBar buttons, then it is doing too much.  It led me down a path to see how easy it was to use vector data with the default AppBarButtonStyle definition.  Turns out it wasn’t as simple as I thought.  Let me first explain the core issue then walk through a step on how to do this with vector data.

Fill versus Foreground

The main culprit is Fill vs. Foreground.  The default AppBarButtonStyle has the different visual states for setting the correct default/inverted colors for your icon within the ellipse.  These visual states however set these colors based on changing the Foreground property of the ContentPresenter.  When your content is text – as is the case with the default styles that are commented out – this works fine.  However the Path element in XAML understands Fill and Foreground doesn’t really apply.  So while the vector data could be represented in the icon location, it wasn’t working on the different states because the Foreground value changing had no effect!

Let’s see how we can accomplish this…

Step 1: Get your vector data

I mentioned that we discovered this using Syncfusion’s Metro Studio, but this is one way where you can get vector-based artwork for XAML.  Another is http://xamlproject.com which is similar in how it does things.  Both will give you vector data based on a selected icon.

NOTE: The one thing I don’t like about The XAML Project site is the sizer doesn’t tell me what size I’m using.  Metro Studio gave me the option to set a specific size.

When we launch Metro Studio we can search for different items based on keywords.  Since the Segoe UI Symbol font includes a ton of these for us (all of which are represented in a commented-out style definition in StandardStyles.xaml you can just uncomment and use) I will pick an obscure one called “wash” from Metro Studio.  When I select to edit it I’m presented with a default UI to modify the data:

Metro Studio user interface

Now notice how it gives me options for background shapes, etc.?  For an AppBar button you do not these shapes so you can uncheck that option.  Secondarily the ideal size is 20px with no padding.  The colors (background color and icon color) don’t really matter here as we aren’t going to use them to get the default AppBar button style.  My final options look like this:

Metro Studio user interface

Notice I unchecked the Background Shapes option, chose Custom Size and entered 20 with 0 padding.

Now what I do is click the XAML button and am presented with the full definition.  You don’t need it all.  Just copy only the path Data to your clipboard or wherever you can get it later.

Metro Studio path data export

Now that we have our vector data we need to put it in our button.

Step 2: Creating the path button definition

Because of the Fill versus Foreground issue we can’t just simply use the AppBarButtonStyle as the button style and just set our vector data.  We will not receive the correct state visuals when it is used.  So we first need to re-define a base that we can use.  Since I may use other vector data in the future, I’ll encapsulate this in a new base style I’ll call PathBasedAppBarButtonStyle.  This will allow me to remap Foreground to my Fill property to achieve the desired outcome.  The resulting style is this:

   1: <Style x:Key="PathBasedAppBarButtonStyle" BasedOn="{StaticResource AppBarButtonStyle}" TargetType="ButtonBase">
   2:     <Setter Property="ContentTemplate">
   3:         <Setter.Value>
   4:             <DataTemplate>
   5:                 <Path Width="20" Height="20" 
   6:             Stretch="Uniform" 
   7:             Fill="{Binding Path=Foreground, RelativeSource={RelativeSource Mode=TemplatedParent}}" 
   8:             Data="{Binding Path=Content, RelativeSource={RelativeSource Mode=TemplatedParent}}"/>
   9:             </DataTemplate>
  10:         </Setter.Value>
  11:     </Setter>
  12: </Style>

Notice that for Fill and Data I’m using Binding with the RelativeSource being the TemplatedParent…the actual Button.  This allows me to essentially get at those properties that will be set and put them in my Path element that makes up the container for the vector data.

Step 3: Setting our custom vector data

Using the exported definition from my “wash” icon above, I can now use the style in my AppBar resulting in something like this:

   1: <Page.BottomAppBar>
   2:     <AppBar IsOpen="True">
   3:         <Button Style="{StaticResource PathBasedAppBarButtonStyle}" 
   4:                 AutomationProperties.Name="Wash" AutomationProperties.AutomationId="WashAppBarButton"
   5:                 Content="M9.6803506,4.8474294C10.209365 ... 3.5652149,0z" />
   6:     </AppBar>
   7: </Page.BottomAppBar>

Please note that I trimmed the Path data so as not to take up so much space for your reading.  I set my Button to use the base style, then set my label (via AutomationProperties.Name) and the Content is the exported vector data from the tool.

NOTE: AutomationProperties.Name is used here in the base AppBarButtonStyle to enable us to expose the Content property as the icon and still have a label.  By using this property you also get accessibility for free in using these styles for this particular instance.  Screen readers will read out the name you set when using this.

The resulting visual is correct in the AppBar UI guidelines:

Wash AppBar Icon displayed

Now when my users mouse-hover or press this button the correct visuals and state changes will happen.

Summary

The default AppBarButtonStyle base definition provided from the Visual Studio templates is optimized for the 150+ styles provided in StandardStyles.xaml and font-based vector data.  For most, this is likely more than enough to use the same icons that are used throughout the operating system to give a consistent feel.  If you find one in the 150+ styles provided you should use that.  However if there is something entirely custom or not available in those, you can use your own vector data to provide your AppBar icon.  Using fonts and vectors make it easy to scale up to the different resources without having to provide different images if you used that method.  Defining a new base for vector data isn’t difficult and provides us the flexibility when we need it!

Hope this helps!

| Comments

I’ve previously posted a few things about SQLite including a HOWTO on how to build from their source code.  If you still want to build your own DLL from their source code that is totally fine, but not necessary in most every single case I’ve seen in app usage.  One of the challenges I noted is that since SQLite is a native component and if you are a managed (.NET) app you can’t be architecture neutral anymore (AnyCPU).  What this means is that you have to build your app for each architecture you want to support: x86, x64 and ARM.  The flow of this using SQLite3.dll was that you would have to package, change the DLL, re-package. 

Not anymore.

In working with the great folks on the SQLite team, they’ve packaged the binaries up (for Windows 8 apps) in a nice installer using the Extension SDK format.  What this means is you now add a ‘reference’ to the SQLite binary and based on the architecture being built for your package, it will pull in the right DLL without you having to manage that yourself.  Here’s some step-by-step…

Installing the SQLite for Windows Runtime package

The first thing you want to do is install the package.  You can do this from within Visual Studio itself in all editions.  From the Tools menu, choose Extensions and Updates and then choose the Online section (on the left of the dialog) and search for ‘sqlite’ in the search term.  This will show you the SQLite for Windows Runtime package:

Visual Studio Extensions dialog

Click install my friends.  You will be prompted to restart Visual Studio which you should do.  Go ahead…I’ll wait.

Using the new package in your C#/VB app

Now that you have the SQLite for Windows Runtime package installed in your Visual Studio environment, you want to use it.  In a managed (.NET) app you would do the following steps.

First, create your app (e.g., a Blank XAML app is fine).  Once within your app, use the Add Reference mechanism to get to the next step.  Now you will not be browsing for any DLL directly like you would in a traditional .NET.  What we are adding here is a reference to the Extension SDK…not the library itself, a small but important distinction.  Once in the Add Reference dialog choose the Windows\Extensions view (see on left) and you’ll see SQLite for Windows Runtime.

To correctly use this in a managed app you’ll need to select that *and* the C++ runtime as seen below:

Add Reference dialog

The reason for this is to ensure that your app declares the right dependencies that are needed for the app to run.  The likelihood of the C++ runtime not being on a Windows machine where your app will be installed is extremely rare, but you need to declare this anyway.  Failure to do so will fail your app certification tests.

Now with this involved you can grab a managed wrapper to call the SQLite APIs as I’ve previously described in my HOWTO video.  I personally recommend the sqlite-net library (available via NuGet) to make this easier for you.

NOTE: sqlite-net is available as source in C#.  If you are using a VB app, you would first need to compile the sqlite-net source in a separate DLL so you can just add a reference to that in your VB project.

Using the sqlite-net library you can perform tasks using a model similar to LINQ2SQL where you can have types represent database entities:

   1: public sealed partial class MainPage : Page
   2: {
   3:     public MainPage()
   4:     {
   5:         this.InitializeComponent();
   6:         LoadData();
   7:     }
   8:  
   9:     public void LoadData()
  10:     {
  11:         var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
  12:         using (var db = new SQLite.SQLiteConnection(dbPath))
  13:         {
  14:             db.CreateTable<Person>();
  15:  
  16:             db.RunInTransaction(() =>
  17:                 {
  18:                     db.Insert(new Person() { FirstName = "Tim", LastName = "Heuer" });
  19:                 });
  20:         }
  21:     }
  22: }
  23:  
  24: public class Person
  25: {
  26:     [SQLite.AutoIncrement, SQLite.PrimaryKey]
  27:     public int ID { get; set; }
  28:     public string FirstName { get; set; }
  29:     public string LastName { get; set; }
  30: }

Now you just need to specify your architecture for your app (x86, x64, ARM) and when you build, the appropriate sqlite3.dll will be packaged in your app automatically.  This also happens during the packaging step for the store so the right item is included for each architecture-specific package.

WARNING: Do not package in DEBUG mode as you will fail certification.  Ensure that you build/package in RELEASE mode prior to submitting to the store or running the app certification toolkit (also referred to as WACK in some places).  You will get false positives if you are compiled in DEBUG mode.

This should make your development much easier without having to  swap out DLL files each time.

Using the new package in your C++ app

If you are a C++ developer you will do the same steps for installing and adding as a reference to your app.  In the C++ project system there is no 'Add Reference’ menu on the project context menu, but you will choose References and then the Add Reference button shows up.

Once you have the reference to the SQLite SDK then as a C++ developer you can just #include the header and go to work:

   1: #include <sqlite3.h>

Since C++ projects are already architecture-specific you don’t have to worry about the AnyCPU situation because there isn’t one!  You’ll get IntelliSense on the API by just including the header.  The Extension SDK mechanism already includes the C++ props file to help the project system know where to get the header for development and the lib for linking when building.  Most C++ developers will interact with SQLite using the native APIs and not need any additional wrapper library.

Using the new package in your JavaScript app

If you are using JavaScript/HTML to developer your app, you will follow the same flow as the C#/VB flow.  Add a reference to both the SQLite SDK as well as the C++ runtime (to declare the dependency).  As to accessing SQLite in your app, you’ll need a WinRT wrapper library to do that.  The one that seems to be gaining favor is the SQLite3-WinRT library on GitHub.  I have not personally used this, but seen a lot of people using this.  It allows you to use the JavaScript programming model in a familiar way:

   1: var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\db.sqlite';
   2: SQLite3JS.openAsync(dbPath)
   3:   .then(function (db) {
   4:     return db.runAsync('CREATE TABLE Item (name TEXT, price REAL, id INT PRIMARY KEY)');
   5:   })
   6:   .then(function (db) {
   7:     return db.runAsync('INSERT INTO Item (name, price, id) VALUES (?, ?, ?)', ['Mango', 4.6, 123]);
   8:   })
   9:   .then(function (db) {
  10:     return db.eachAsync('SELECT * FROM Item', function (row) {
  11:       console.log('Get a ' + row.name + ' for $' + row.price);
  12:     });
  13:   })
  14:   .then(function (db) {
  15:     db.close();
  16:   });

If using JavaScript this might be the way to go for your app.

Summary

I’m very glad the SQLite team worked to get this deployment package out there.  I think for some Microsoft developers, using SQLite is fairly new and this SDK package will make it easier to ensure you have the right bits at the right time.  Of course you are free to do it your own way, but I think this will ease the process a little bit.

Why no NuGet? Well, the NuGet infrastructure right now doesn’t support some of these semantics around native components to deal with headers, linking and architecture-specific deployments.  We’ll continue to work with them to see if we can drive these changes into that platform.

So please feel free to download via the Visual Studio ‘Extensions and Updates’ option from within VS, download directly from the Visual Studio Gallery, or download from the SQLite site themselves.  Once installed, once an update is available, VS will notify you that an update is available and you can install it.

Hope this helps!

| Comments

At the “Developing Windows 8 Metro style apps with C++” event that happened on 18-May-2012, we saw and heard some very interesting things.  If you were watching live then hopefully you didn’t see how I tried to work through my presentation while my disk was suspiciously guzzling every last byte until it eventually ran out of space!  But I digress…

During the keynote presentation by Herb Sutter, we brought up several customers that are well-known in the native code world to talk about their experiences with Metro style apps and C++/Cx.  In particular hopefully this one caught your eye:

SQLite case study slide

That’s right, the team for SQLite was there to discuss how they were able to take their existing Win32 codebase and ensure that it worked well on Windows 8 as well as for Metro style apps.

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen().– Source: http://www.sqlite.org/about.html

Dr. Richard Hipp, the founder of SQLite, was on hand to announce the availability of the experimental branch they’ve been working on as well as that when the Release Preview of Windows 8 is made public that he will merge this code to the main trunk for SQLite, making it supported by them.  This is a really great thing for developers as SQLite has been a proven embedded database for numerous platforms and many, many customers.  The team prides themselves on testing and has millions of test cases that are validated each release.

As a Windows (and perhaps more specifically .NET) developer, you may not have had to build any lib from Open Source before of this type (i.e., native code) and since a binary is not being provided yet until Release Preview for Windows 8, I thought I’d share my tips on building the experimental bits, adding them to your projects and then using them with a client library.

UPDATE: I created a HOWTO video demonstrating the following steps of building and using from a C# Metro style app.

Building SQLite from source

If you are looking for the sqlite3.dll with this WinRT support anywhere on the sqlite.org site, you won’t find it.  You will have to build the source yourself. UPDATE:Since the origination of this post the SQLite team has released a version already compiled for 32/64-bit.  I highly recommend you get the code from the source rather than from any third party site.  Microsoft has worked with the team at SQLite to ensure compatibility and store certification.  For most .NET developers who have never grabbed native code source from an Open Source project and had to build it before, the maze of knowing what you should do can be confusing.  I’ve put together a cheat sheet on building SQLite from source for a Windows (and .NET developer) and put it on my SkyDrive: Building SQLite from source.  The OneNote I have has the details you need for the tools that will be required. 

In a nutshell you’ll need:

  • Visual Studio (easiest way to get the C++ compiler)
  • ActiveTcl
  • Update for gawk.exe
  • Fossil (the source control system used by SQLite)

Once you have these, you are ready to build SQLite.  Again, I’ll defer to my instructions on the details of setup.  Once your setup is complete, from a developer command prompt you’d run:

   1: nmake -f Makefile.msc sqlite3.dll FOR_WINRT=1

The result of this will give you basically 4 files that are of likely most importance to you: sqlite3.c, sqlite3.h, sqlite3.dll, sqlite3.pdb.

NOTE: The resulting pdb/dll that is built will be architecture specific.  If you used an x86 command prompt then that is what you have.  Be aware of this (as noted later in this post).

At a minimum you’ll want sqlite3.dll if you are a .NET developer, but as a native code developer you will likely be more interested in the others as well.  After this step, you now have a Windows Store compliant version of SQLite to include in your applications.

Runtime versus client access

Now at this point is where I’ve seen some confusion.  Folks are asking How do I include this, don’t I need a WinMD file to reference?  Let me diverge a bit and explain a few things.

The result of compiling the binary above produces primarily one thing…which I will call the “Engine” in this post.  This is the SQLite runtime and logic required to create/interact with SQLite database files.  This is NOT, however, an access library, which I will call the “Client” in this post.  If you are a managed code or JavaScript developer, at this point, all you have is the Engine, the database runtime.  You have no Client to access it.

Now, if you are a C++ developer you are probably okay at this point and don’t care much about what I have to say.  You have the header and are likely saying I’ve got the header, get out of my way.  And that is okay.  For C++ developers I think you’ll likely be accessing the database more directly through the SQLite APIs provided in the header.

I call out this distinction because this step provides you with the database engine you need to create a database and have it be store-compliant.  So if you are a JavaScript or .NET developer, what are you to do?  Stay tuned…let’s first get the Engine included in our app package.

Including SQLite in your app package

As I noted above, as a native code developer, having the header, lib and c file you may be okay and don’t care to read this.  I  personally think, however that I’d always just want the binary from my vendor rather than always include source in my files.  That said, the SQLite build process does product the amalgamation (sqlite3.c) you can just include in your native code app.

If you choose to go the binary file route (sqlite3.dll) then you need to simply follow a few principles to ensure that it is included in your package when you build your app/package.  These principles are simple:

  • include the architecture-specific binary
  • ensure the sqlite3.dll is marked as Content in your project
  • ensure you note that you now have a native code dependency (not needed if you are already a C++ Metro style app)

These two items will ensure that when you build (even for debug via F5) or when you package for the store, that the Engine is included in your package.  Marking as content is simply ensuring that after you add the file to your project, ensure the file properties note that it is content.  In .NET apps this is making the Build Action property Content.  In JavaScript applications ensure the Package Action is marked Content.

Declaring the native code dependency means you simply add a reference to the Microsoft C++ Runtime Library via the Add Reference mechanisms in .NET and JavaScript applications.  By doing this (and again, this is a requirement of including SQLite in your app) you now cannot be architecture-neutral. This means no more AnyCPU for .NET.  When producing a package you’ll have to produce architecture-specific packages before uploading to the store.  Not to worry though as Visual Studio makes this incredibly easy.  The one thing you’ll have to remember though is that you’ll have to change the sqlite3.dll before building the packages as the DLL is architecture-specific itself.

Now this all should be easier right?  Wouldn’t it be nice if you could just Add Reference to the Engine?  I personally think so.  I’ll be working with the SQLite team to see if they will adopt this method to make it as easy as this:

SQLite Extension SDK

In doing so, you as a developer would just add a reference to the Engine and then during build time Visual Studio (well MSBuild actually) will do all the right things in picking up the architecture-specific binary for your app.  No fiddling on your part.  This method also makes it easier for C++ developers as well as a props file would automatically be merged to include the .lib for linking and the header file for development.  This method uses the Visual Studio Extension SDK mechanism that was introduced in Visual Studio 11.

NOTE: Again note that as a managed (.NET) app I’d also have to ensure that my package includes the Microsoft C++ Runtime package in order for this to work and pass store certification.

Native code developers may scoff at this approach, but I could get started in 2 steps: Add Reference, #include.  No tweaking of my project files at all because the Extension SDK mechanism in VS does all this for me behind the scenes.

So why don’t I just give you the VSIX to install and accomplish the above?  Well simply, because SQLite is not my product and we’ve had a good relationship with their team and I want to make sure they understand the benefits of this method before jumping right in.  I hope that they will like it as I think it makes it *really* simple for developers.

Accessing the Engine from your app

Great, you’ve compiled the bits, you’ve understood how to ensure sqlite3.dll gets packaged in your application…now how do you use it!!!  Here’s the assessment of where we are at for Metro style apps as of the writing of this post.

C++ app developers: I think most C++ developers will get the header file (sqlite3.h) and be okay on their own with SQLite.  At this stage for them there doesn’t appear to be a real huge benefit of a WinRT wrapper to use the Engine.

.NET developers: I’ve messed around with a few libraries and believe the sqlite-net project to be the most favorable for what I believe most use cases will be for SQLite and Metro style apps.  This is a .NET-only library (not WinRT) but is basically a “LINQ to SQLite” approach.  The Mono team uses this one as well.  The necessary .NET 4.5 Core changes are already included in the project on github.  So you just need to get the SQLite.cs file and include it in your project.  Using this library allows you to write code like this:

   1: public sealed partial class BlankPage : Page
   2: {
   3:     public BlankPage()
   4:     {
   5:         this.InitializeComponent();
   6:  
   7:         string dbRootPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
   8:         using (SQLiteConnection db = new SQLiteConnection(Path.Combine(dbRootPath, "mypeople.sqlite")))
   9:         {
  10:             db.CreateTable<Person>();
  11:  
  12:             db.RunInTransaction(() =>
  13:                 {
  14:                     for (int i = 0; i < 10; i++)
  15:                     {
  16:                         db.Insert(new Person() { FullName = "Person " + i.ToString() });
  17:                     }
  18:                 });
  19:         }
  20:     }
  21: }
  22:  
  23: public class Person
  24: {
  25:     [AutoIncrement, PrimaryKey]
  26:     public int ID { get; set; }
  27:     public string FullName { get; set; }
  28:     public string EmailAddress { get; set; }
  29:     public double Salary { get; set; }
  30: }

This is clearly just a sample, but demonstrates the simplicity of the library. 

NOTE: In the snippet above you do want to make sure you are creating your database in a path that is accessible from the AppContainer.  The best place is in the app’s ApplicationData location.  When specifying a path to SQLite in Open() for creation, give an explicit path always to ensure you aren’t relying on a temp file creation.

Some may ask about System.Data.Sqlite and this cannot be used because of the dependency of ADO.NET which is not a part of the .NET Framework Core profile.

Now this leads us to JavaScript developers.  Currently, there is not easy way for you to access this.  The Developer and Platform Evangelism team are working on some samples that are not quite complete yet.  JavaScript developers will need a WinRT library in order to access/create/query the Engine.  There are some out there (I haven’t played around with any of these) that would be good to see if they meet your needs.  Here are some pointers:

At the C++ event we talked with the SQLite team about a WinRT client library and will continue to talk with them to see if this is something of interest.  SQLite has a great history of working with the community and have a desire to continue this.  In the meantime, there are options for you to get started.  Also note, that since these are WinRT libraries they could also be used from C++ and .NET in Metro style apps.  At this point though it is my personal opinion that existing .NET libraries for .NET offer more value (i.e. LINQ) than how these WinRT ones exist.

Summary

This was a great announcement that the SQLite team made for Metro style app developers.  WinRT provides some existing local storage mechanisms which you should explore as well, however none that have structured storage with a query processor on top of it.  I’m really glad that the SQLite team was able to make a few diff’s to their code to accommodate a few store compliance areas and continue to offer their great product to this new class of applications.  It is very simple to get started by ensuring you have the Engine and picking your Client of your choice and write your app using SQLite for some local/structured storage!

Hope this helps and stay tuned for the release preview of Windows 8!