WinRTでSQLiteが使えるようになったみたいなので試してみました

InfoQで素敵なニュースが。

WinRT、Metroアプリ、Windows 8 MobileでSQLite

SQLiteがWinRTで使えるようになったみたいです！Windows PhoneにはSQL Server Compact EditionとLINQ to SQLが使えてましたがWinRTでは軽量なDBは特に提供されていません。(VS2012 RC時点)なので、RDBになれてる人にとってはSQLiteがWinRTに対応したというのはとってもウキウキしますね！

使い方は、InfoQのサイトにある参考サイトがとてもいい感じにまとまってます。

というわけで使ってみる

というわけで自分でも使ってみました。SQLiteの公式サイトから自分の環境に対応するSQLiteをダウンロードします。というかx86とx64版があるのですが、CPUに依存するアプリになってしまうのかな。そこがちょっと心配。

とりあえず、SQLiteの公式サイトのダウンロードページからダウンロードします。

ダウンロードしたzipを解凍すると、以下のファイルが出来ました。

C#で使うのはdllっぽいです。BlankAppを選択してSQLiteAppという名前でプロジェクト作ります。プロジェクトフォルダの直下にsqlite3.dllをプロジェクト直下に置きます。そしてプロパティの出力ディレクトリにコピーの箇所を常にコピーに変更します。

このdllはネイティブのdllらしいので、C#で使うにはC#用のライブラリを別途追加します。これはNuGetから入れれるみたいです。ということでさくっと入れてしまいましょう。名前はsqlite-netです。

こいつは、ソースコードを入れてくれるので、SQLite.csとSQLiteAsync.csがプロジェクトに追加されます。

よいよ本題のプログラム！MainPage.xamlにボタンをこんな感じで置きます。

ボタンクリックイベントで、テーブルを作成します。テーブルを作る前の下準備としてとりあえず、テーブルにマッピングされるクラスを作ります。InfoQのサイトの参考リンクには単一テーブルしかなかったのでここではリレーションあるケースにチャレンジ！！と思ったらデフォでは外部キーサポートしてないんですね。おとなしくデフォで使おう。
ということで、一応こんな感じの従業員と部署を表すクラス作りました。

public class Department
{
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Employee
{
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }

    public int DepartmentId { get; set; }
}

そして、ボタンクリックのイベントでテーブルを作ります。

private async void buttonCreateDB_Click(object sender, RoutedEventArgs e)
{
    var conn = new SQLiteAsyncConnection("SampleDb");
    await conn.CreateTablesAsync<Department, Employee>();
}

ビルドして実行！すると例外が出ました。変なDLL読み込んだ的なメッセージだったのでよく見てみるとCPUがAnyをターゲットにしてました。ということでSQLiteのx64版を入れてるのでビルドターゲットのCPUをx64に変更して再実行！今度はうまくいきます。
SQLite Browserで実際に作成されたDBを開いてみるとテーブルが作られてることが確認できます。（DBはC:\Users\ユーザ名\AppData\Local\Packages\GUID\LocalData\SampleDbに出来てます。ApplicationData.Current.LocalFolderで取得できるパスです。

データの投入

テーブル作っただけじゃあれなので、buttonInsertというボタンを画面においてクリックイベントに以下のように書きます。
(x64にCPUを変更するとデザイナが起動しなかったので、Any CPUに設定を戻して画面デザインをしましょう。正式版では対象のCPUに何を指定してもデザイナ起動してほしいですね)

private async void buttonInsert_Click(object sender, RoutedEventArgs e)
{
    var conn = new SQLiteAsyncConnection("SampleDb");
    // 10個部署を作って
    await conn.InsertAllAsync(
        Enumerable.Range(1, 10).Select(i => new Department { Name = "部署" + i }));

    // 各部署へ100人登録
    var departments = await conn.QueryAsync<Department>("select * from Department");
    foreach (var d in departments)
    {
        await conn.InsertAllAsync(
            Enumerable.Range(1, 100).Select(i => new Employee 
            { 
                Name = "田中 " + d.Name + "No." + i,
                DepartmentId = d.Id
            }));
    }
}

実行してボタンを押します。（何も表示されませんが…）そして、SQLite Browserでデータを確認します。

select 
  e.Id as Id,
  e.Name as Name,
  d.Name as DeptName
from Employee e
inner join Department d on e.DepartmentId = d.Id

実行すると、ちゃんとデータが入っていることが確認できます。

データの取得

最後に、データを取得します。取得は、QueryAsyncでさくっとやります。以下のようなEmployeeテーブルとDepartmentテーブルを結合した結果を取りたいと思います。

select
    e.Id as Id,
    e.Name as Name,
    d.Name as DepartmentName
from Employee e
inner join Department d on e.DepartmentId = d.Id
order by e.Id

なので、このSQLにマッチする型を以下のように定義します。

public class EmployeeView
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string DepartmentName { get; set; }
}

そして、ButtonとListViewを画面に追加します。ListViewにはEmployeeViewクラスを表示するためのItemTemplateを設定します。

<ListView x:Name="listView" HorizontalAlignment="Left" Height="555" Margin="84,203,0,0" VerticalAlignment="Top" Width="1272">
    <ListView.ItemTemplate>
        <DataTemplate>
            <StackPanel Orientation="Horizontal">
                <TextBlock Text="{Binding Id}"/>
                <TextBlock Text=", " />
                <TextBlock Text="{Binding Name}"/>
                <TextBlock Text=", " />
                <TextBlock Text="{Binding DepartmentName}"/>
            </StackPanel>
        </DataTemplate>
    </ListView.ItemTemplate>
</ListView>

ボタンクリックには、以下のようなコードを書きます。

private async void buttonSelect_Click(object sender, RoutedEventArgs e)
{
    var conn = new SQLiteAsyncConnection("SampleDb");
    var emps = await conn.QueryAsync<EmployeeView>(
        @"
        select
            e.Id as Id,
            e.Name as Name,
            d.Name as DepartmentName
        from Employee e
        inner join Department d on e.DepartmentId = d.Id
        order by e.Id");
    listView.ItemsSource = emps;
}