New-DiffGist: a powershell cmdlet for posting diffs fast

August 22, 2012

In the late nights when I have time to work on Callisto (my Windows 8 XAML toolkit), I often am making changes that I really wish I could either pair with someone on or at a minimum solicit some feedback. Primarily single-person open source projects are lonely :-).

Last night was no different. I had a user of Callisto having a problem with a specific code path. While my testing of the fix was fine, I didn’t want to rush it without him understanding the simple fix. I had no good way of showing him a combined diff to comment on at the time. My problem was, what I thought, simple: how do I quickly share a diff from a GitHub project without requiring them to have a fork?

I set out to the best place to have an argument, Twitter. I asked the same question and got a lot of different responses, but almost all of them pointing to what appears to be the typical git flow in this matter is to use pull requests for code reviews and sharing potential updates. This really frustrated me as I thought it was unnatural to the goal I was trying to accomplish. Lots of folks told me this was the way to do it and that I was thinking about it wrong. To them I say this is broken. I didn’t want to pollute pull requests to be a mix of legitimate pull requests to “in-flight” code changes that shouldn’t be even considered for merge yet. I simply wanted a quick, light-weight way to share a diff graph.

Paul Betts (as he has always awesomely helped with my GitHub questions) pointed me to the thing I was ultimately looking for:

        1: git diff > current.diff

This gave me the output that I needed and Gist understands color coding for this data so I just needed to post it there. Good thing is that GitHub has an API that is relatively simple to understand. Since I’ve been using poshgit as my shell lately I set out to create a CmdLet for my flow. I wanted one command that would grab the diff and post to a Gist and give me the URL. I’ve never touched a line of PowerShell programming before and set out to start.

Honestly, it was painful to understand PowerShell for me in this simple task. The PowerShell “IDE” is slow to help with any really good flow. I wanted some VS experience to bootstrap me and I knew I didn’t want a full-on assembly approach. A PowerShell script should do me just fine. I scoured many sites online and started out with my script. After a few hours of searching/trying I was almost there but still not successful. I ended the night posting back to Twitter with a Gist of my attempt.

It was late, I went to bed. As is how most things happen on the interwebs, I awoke to some pointers to where someone has already done most of the work! Despite my searching I never found something that basically took me 90% there already. This was in the form of PsGist. Boom! This is exactly what I needed. I just needed to modify the input from a literal file to the source of the diff. I spent an unnecessary hour fighting with an unauthorized error on the GitHub API due to some unexpected value being in the string captured after entering credentials, but after that was solved I was done. I decided to merge this back to PsGist and submit a pull request for the new command. A few minutes later and after a few questions/quick modification, it was merged. I now have a simple/fast flow to quickly say “hey, here’s what I’m working on, what do you think?” to anyone without having to do any different branching that isn’t currently in my flow.

       1: New-DiffGist -Description "SettingsFlyout fix" -Public

After the module is imported the above command will perform a diff, and immediately post that to Gist and give you the URL (and optionally launch in browser if you use the –Launch param). I know it may sound trivial, but it helps my flow and is the simple thing that I was looking for. Thanks Ryan for the PsGist starting point (and for merging my new CmdLet)!

Hope this helps someone!

UPDATED HOWTO: SQLite with Windows 8 apps

August 07, 2012

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

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum2" style="color: #606060">   2:</span> {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum3" style="color: #606060">   3:</span>     <span style="color: #0000ff">public</span> MainPage()</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum4" style="color: #606060">   4:</span>     {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum5" style="color: #606060">   5:</span>         <span style="color: #0000ff">this</span>.InitializeComponent();</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum6" style="color: #606060">   6:</span>         LoadData();</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum7" style="color: #606060">   7:</span>     }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum8" style="color: #606060">   8:</span>  </pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum9" style="color: #606060">   9:</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span> LoadData()</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum10" style="color: #606060">  10:</span>     {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum11" style="color: #606060">  11:</span>         var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, <span style="color: #006080">"db.sqlite"</span>);</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum12" style="color: #606060">  12:</span>         <span style="color: #0000ff">using</span> (var db = <span style="color: #0000ff">new</span> SQLite.SQLiteConnection(dbPath))</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum13" style="color: #606060">  13:</span>         {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum14" style="color: #606060">  14:</span>             db.CreateTable&lt;Person&gt;();</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum15" style="color: #606060">  15:</span>  </pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum16" style="color: #606060">  16:</span>             db.RunInTransaction(() =&gt;</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum17" style="color: #606060">  17:</span>                 {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum18" style="color: #606060">  18:</span>                     db.Insert(<span style="color: #0000ff">new</span> Person() { FirstName = <span style="color: #006080">"Tim"</span>, LastName = <span style="color: #006080">"Heuer"</span> });</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum19" style="color: #606060">  19:</span>                 });</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum20" style="color: #606060">  20:</span>         }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum21" style="color: #606060">  21:</span>     }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum22" style="color: #606060">  22:</span> }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum23" style="color: #606060">  23:</span>  </pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum24" style="color: #606060">  24:</span> <span style="color: #0000ff">public</span> <span style="color: #0000ff">class</span> Person</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum25" style="color: #606060">  25:</span> {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum26" style="color: #606060">  26:</span>     [SQLite.AutoIncrement, SQLite.PrimaryKey]</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum27" style="color: #606060">  27:</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span> ID { get; set; }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum28" style="color: #606060">  28:</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">string</span> FirstName { get; set; }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum29" style="color: #606060">  29:</span>     <span style="color: #0000ff">public</span> <span style="color: #0000ff">string</span> LastName { get; set; }</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum30" style="color: #606060">  30:</span> }</pre>

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';

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum2" style="color: #606060">   2:</span> SQLite3JS.openAsync(dbPath)</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum3" style="color: #606060">   3:</span>   .then(<span style="color: #0000ff">function</span> (db) {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum4" style="color: #606060">   4:</span>     <span style="color: #0000ff">return</span> db.runAsync(<span style="color: #006080">'CREATE TABLE Item (name TEXT, price REAL, id INT PRIMARY KEY)'</span>);</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum5" style="color: #606060">   5:</span>   })</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum6" style="color: #606060">   6:</span>   .then(<span style="color: #0000ff">function</span> (db) {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum7" style="color: #606060">   7:</span>     <span style="color: #0000ff">return</span> db.runAsync(<span style="color: #006080">'INSERT INTO Item (name, price, id) VALUES (?, ?, ?)'</span>, [<span style="color: #006080">'Mango'</span>, 4.6, 123]);</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum8" style="color: #606060">   8:</span>   })</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum9" style="color: #606060">   9:</span>   .then(<span style="color: #0000ff">function</span> (db) {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum10" style="color: #606060">  10:</span>     <span style="color: #0000ff">return</span> db.eachAsync(<span style="color: #006080">'SELECT * FROM Item'</span>, <span style="color: #0000ff">function</span> (row) {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum11" style="color: #606060">  11:</span>       console.log(<span style="color: #006080">'Get a '</span> + row.name + <span style="color: #006080">' for $'</span> + row.price);</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum12" style="color: #606060">  12:</span>     });</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum13" style="color: #606060">  13:</span>   })</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum14" style="color: #606060">  14:</span>   .then(<span style="color: #0000ff">function</span> (db) {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum15" style="color: #606060">  15:</span>     db.close();</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum16" style="color: #606060">  16:</span>   });</pre>

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!

Seeding your Metro style app with a SQLite database

June 28, 2012

It looks like people are really glad about being able to use SQLite within their Metro style apps. I had written two previous posts (Using SQLite in your Metro style app and HOWTO: Build and include SQLite) about this topic. I’m pleased to report that since those posts the SQLite team released a build (3.7.13 as of the datestamp on this post) which also provides the binary (32- and 64-bit versions) pre-compiled for you for inclusion in your Metro style app. You can get them from the SQLite download page.

I’ve received a few comments/questions that I thought I might clarify in my own opinion (and some facts) about using SQLite in your app.

Creating new databases

The first thing to understand is that your app lives in a secure sandbox during operation. This is also referred to as the AppContainer in the Metro style app world. What this means is that you can only do certain operations in certain places or through brokers provided by the various WinRT APIs. The first stumbling block I’ve seen people try to do is create a database in places where they cannot create databases. When using SQLite, regardless of whatever client method you use to program with it, you need to pass in a full path to where the database should be created (or an existing one that you are opening). Simply passing “foo.db” in the open method is not enough as that will assume an incorrect path to create the database file. Another incorrect thing that folks are doing is using the Windows.ApplicationModel.Package.Current.InstalledLocation.Path API. This represents the location of where your app is installed which is not an area you can directly write files/content.

NOTE: SQLite uses the CreateFile2 API which is not a WinRT broker API. This means that it is restricted to certain areas of the AppContainer.

The other area where people are trying to create files is in the document library location for the user. If you have declared the Document Library capability as well as provided a file association for the file you want to create, then you can read/write files in the Document Library using the WinRT broker APIs. This, however, is not possible using the CreateFile2 Win32 APIs.

This leaves the app’s ApplicationData location. So the correct path to create your database from your app is Windows.Storage.ApplicationData.Current.LocalFolder.Path as a starting point. Here’s an example (using the sqlite-net library and C#):

        1: using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "foo.db")))

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum2" style="color: #606060">   2:</span> {</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum3" style="color: #606060">   3:</span>     <span style="color: #008000">// do stuff here</span></pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum4" style="color: #606060">   4:</span> }</pre>

Now whenever I need to query this database I would use that same path from my app.

Seeding your app with starting data

Some folks want to start their application with some seed data. There are a few ways that you could do this. One way would be to create a database during startup and execute SQL statements against the newly-created database. You would basically be shipping a script in your app that you’d run on the first run of the app after install. If you went this route, then you’d use the method above to create the database and then execute your INSERT statements.

Another method is to use an existing database file that perhaps you’ve already created. The misconception here that people have is that since they include a seed database in their app that they can just open that database file and read/write on it. The read part is correct, however you will fail to write to that file as it is in the package install location and not the ApplicationData location. The first step you want to do in this approach is move your seed database to the place where you can write to it. You can use the Windows.Storage APIs to accomplish this. Here’s an example of how you might do this. This assumes that your app has a file named “Northwind.sqlite” in your package:

       1: // grab the file from the package installed location into a StorageFile

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum2" style="color: #606060">   2:</span> StorageFile seedFile = await StorageFile.GetFileFromPathAsync(</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum3" style="color: #606060">   3:</span>     Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path,</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum4" style="color: #606060">   4:</span>     <span style="color: #006080">"Northwind.sqlite"</span>));</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum5" style="color: #606060">   5:</span>  </pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum6" style="color: #606060">   6:</span> <span style="color: #008000">// copy the StorageFile to the ApplicationData folder</span></pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum7" style="color: #606060">   7:</span> await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);</pre>

Now the code above does the copy. Of course you would want to add some logic to verify that you aren’t overwriting an existing database. Just like anything else there are various ways to do this so I am not prescribing any one way. Once you get the data where you need it to be, then you can work with the database how you’d like.

I hope this helps understand the method of creating (in the right place) and seeding your app with a SQLite database. Hope this helps!

AutoHotkey–my new snippet tool

June 13, 2012

This week was TechEd North America, a conference from Microsoft for technical professionals covering the span of pretty much everything Microsoft produces to support IT professionals and software developers. I was pleased to have been invited to speak on developing Metro style apps in XAML for .NET developers. Like most developer presenters, I planned on showing a lot of demos, using different tools, editors, and varying code samples, URLs, etc. When you are a presenter at a conference you usually don’t have the luxury of sitting in your office and doing things without distractions. You want to get across your message of your presentation and also be able to have some good demonstrations articulating your points. When you have a lot of demos, most of the time presenters will rely on some form of snippets – something for them to either type in quickly, copy/paste, or drag/drop onto editors.

I’ve used various snippet concepts over the years:

Using the Visual Studio Toolbox area and dragging text there (yes, did you know you can do that)
VS Code Snippets
Custom WPF “always on top” snippet utility (developed by a WPF team member when she was doing presentations)
Other 3rd party macro tools

But mostly I, like others, have relied on good ol’ notepad. For each presentation I have a file and just blocks of code separated with headers denoting to me which step the snippet is for in the demonstration. I don’t always use snippets because I do have some sense of pride in being able to demonstrate yes, I do actually know what I’m talking about and not just always copy/pasting! However, again, for efficiency and to get many points across, it is an effective way to start from a blank slate (project) and build up how code gets structured for your particular concept.

Notepad has been great and reliable so I’ve always used it. The other methods are more laborious to set up and sometimes error prone…aside from the fact they don’t work in all scenarios (i.e., VS code snippets don’t work in XAML…argh).

This week while preparing in the speaker room with my colleague John Lam (who also gave a presentation on the Windows Runtime) he was using a new utility I hadn’t heard of before. I usually get my little widgets of knowledge from Scott Hanselman’s massive list of tools. Most I don’t use, but there are some really helpful gems in there. So I was surprised about this new tool John was showing me I hadn’t heard of before.

YES, I realize this is probably not a new tool and this invites comments of ‘duh, this has been around forever dude’ so feel free to not post those. It is new to me, like in that new used car kind of way.

When John was walking through his demo he was typing what seemed like random keystrokes in various places: VS, Blend, Notepad, dialogs, command prompt, web apps. All of these were translating into blocks of text, shell commands, etc. He lit up showing me about this new tool, AutoHotkey.

AutoHotkey is a very small tool that basically is a mini macro language. I’m going to completely under-sell it for it’s likely true abilities, but even for the simplest use it has been a new favorite. AutoHotkey works by listening to the accessibility features in Windows (also referred to UIA) for anything that has input focus. Yes, that’s right, anything. You define a ‘macro’ keyword and then what the command defines. For me, this was just needing to be a series of copy/paste automation. Here’s an example of one of my snippets:

        1: ; clear clipboard

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum2" style="color: #606060">   2:</span> ^+x::</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum3" style="color: #606060">   3:</span> clipboard = ; null</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum4" style="color: #606060">   4:</span> return</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum5" style="color: #606060">   5:</span>  </pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum6" style="color: #606060">   6:</span> ; Initial StackPanel stubs</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum7" style="color: #606060">   7:</span> ::d1::</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum8" style="color: #606060">   8:</span> clipboard = </pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum9" style="color: #606060">   9:</span> (</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum10" style="color: #606060">  10:</span> <span style="color: #0000ff">&lt;</span><span style="color: #800000">StackPanel</span><span style="color: #0000ff">&gt;</span></pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum11" style="color: #606060">  11:</span>             <span style="color: #0000ff">&lt;</span><span style="color: #800000">TextBlock</span> <span style="color: #ff0000">FontSize</span><span style="color: #0000ff">="53"</span> <span style="color: #ff0000">x:Name</span><span style="color: #0000ff">="FirstName"</span> <span style="color: #0000ff">/&gt;</span></pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum12" style="color: #606060">  12:</span>             <span style="color: #0000ff">&lt;</span><span style="color: #800000">Button</span> <span style="color: #ff0000">Content</span><span style="color: #0000ff">="Click Me"</span> <span style="color: #ff0000">Click</span><span style="color: #0000ff">="Button_Click_1"</span> <span style="color: #0000ff">/&gt;</span></pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum13" style="color: #606060">  13:</span>         <span style="color: #0000ff">&lt;/</span><span style="color: #800000">StackPanel</span><span style="color: #0000ff">&gt;</span></pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum14" style="color: #606060">  14:</span> )</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum15" style="color: #606060">  15:</span> send ^v</pre>

<pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum16" style="color: #606060">  16:</span> return</pre>

Anything preceded with a semicolon is a comment. The next line is the macro command it will listen for when input has focus. In the above there are two “^+x” means CTRL+SHIFT+X. The command is followed by two colons which is the delimiter for the command. The simpler one for “d1” shows how you issue a copy/paste. I tell it what I want to put on the clipboard, then say to send a CTRL+V (paste) and end the script with a ‘return’ statement.

The beauty is that there is no “app” that you have to run – your script is basically the app. You create your script in a text file named with an .ahk extension. When your script is complete, double-click on it and it is now listening. You’ll get an icon in the system tray showing you that it is running and some options (i.e., you can pause it, edit, reload to tweak):

AutoHotkey example

What is cool is that if you want to see how it is working and what it is doing you can look at the “spy” feature:

AutoHotkey spy

to see how it is listening to automation events and input focus.

The other great feature it has is that you can compile your script. What this does is take your script (ahk file) and compiles the AutoHotkey runtime into it as well, producing an EXE. Now you can take that EXE to any machine and double-click and boom, your snippets are available and listening. So now I can can compile my snippets for each presentation and put them alongside my other presentation materials on my SkyDrive…keeping everything together and quickly restorable to any machine. Awesome.

I immediately started using it and became an instant evangelist to other presenters that moment. John Papa used it in his presentation as well and Pete Brown I think is now converted as well. For me it worked great, no issues.

Creating the script was a bit of trial and error because the documentation is, well, not great. It does SO MUCH MORE than what I’m using it for which is why I felt the docs lacking for the simple cases. The ‘return’ keyword was critical for me to get mine working without error. When you install AutoHotkey there is also an “Extras” folder that contains plugins for various editors: VIM, Emacs, TextPad, SciTE, Notepad++ and more. These allow you to get syntax highlighting in these editors quickly.

Thanks to John Lam for turning me on to this. UPDATE: I’m the idiot because it *is* on Scott’s list. My search wasn’t good enough apparently :-) ~~and maybe Scott Hanselman will consider it for his ultimate tools list this year~~. What is also awesome is that there is a Chocolatey installer for it so I just added this to my personal just-repaved-my-machine-please-give-me-my-utilities package. Be sure to check it out if you find yourself doing a lot of snippets.

Hope this helps!

HOW TO: Use SQLite in C# Metro style app

June 05, 2012

I got a few questions and comments about how to actually include SQLite in a C# Metro style app. Since perhaps it wasn’t clear in describing in my post, I thought a quick video might help demonstrate the steps to build and use SQLite in a C# Metro style app.

The video walks through actually building SQLite from the source (Visual Studio 2012 required…express is fine) and adding it to a C# Metro style app, create a database, populate with some data based on a class and databind the query to a ListView. The video references my OneNote notebook on the tools you need to download and build the SQLite source. It also demonstrates using the sqlite-net library from NuGet on interacting with SQLite in a C# application.

This is a quick video to demonstrate the concept on how to get started and is not a full end-to-end sample.

NOTE: This video only demonstrates how to build SQLite until the team itself merges the WinRT changes and produces the supported build. Until then this is a step you’d have to do on your own and these private branches are not fully supported by them until merged to their main release branch.

I hope this helps clarify things!