Использование SQLite в Windows Phone 7

Я уже писал о том, как можно использовать встроенные средства для хранения данных в Windows Phone 7. Понятно, что для некоторых задач использование реляционного хранилища было бы намного удобнее и эффективнее. Тем не менее, в Windows Phone 7 CTP, представленном на MIX 2010 нет встроенной поддержки SQL Compact и пока не ясно, появится ли она в финальном релизе. Между тем, через несколько дней после официального выхода Windows Phone 7 SDK CTP было объявлено о портировании переносимой БД SQLite на Windows Phone 7. Как просили многие из вас, я расскажу о том, как это можно использовать в своих приложениях.

SQLite — это переносимая база данных, подобная SQL Compact и имеющая реализации для огромного числа платформ. Предпосылкой к переносу на Windows Phone 7, судя по всему, послужила уже существующая реализация для настольной версии Silverilght. Признаться честно, просмотрев исходные коды SQLite, я не могу сказать, что они мне нравятся. Однако, эта библиотека действительно работает на Windows Phone 7 и ее можно использовать в своих приложениях.

Итак, для того, чтобы использовать SQLite в своих приложениях нужно загрузить исходные коды библиотеки. Загрузив, не пугайтесь – исходные коды библиотеки занимают 10 Мб, это действительно так. Однако, не стоит переживать, при сборке проекта SQLite собирается в сборку размером в 600Кб (плюс, если необходимо, файл с отладочной информацией 1.4Мб). Размер библиотеки 600Кб – это, конечно, тоже не мало, но по сравнению с 10 Мб выглядит несущественно. Вместе с исходным кодом самой библиотеки поставляется пример приложения, работающего с SQLite. С него мы и начнем рассмотрение работы.

Как я и говорил, исходные коды SQLite оформлены очень специфично. Это, наверное, дело вкуса, поэтому пока не обращайте внимания на стиль написания кода. Демонстрационное приложение содержит несколько кнопок для выполнения операций – открытия/закрытия БД, создание таблицы, добавления информация и удаления БД. Для каждой из кнопок существует отдельный обработчик. Давайте посмотрим на каждый из них.

Открытие/закрытие БД

Исходный код:

Sqlite3.sqlite3 db=null;

public bool OpenDB()
{
    int rc;
    db = new Sqlite3.sqlite3();
    rc = Sqlite3.sqlite3_open(fileName, ref db);
    if (rc != 0)
    {
        lbOutput.Text += "\nCannot open database: " + Sqlite3.sqlite3_errmsg(db);
        db = null;
        return false;
    }
    lbOutput.Text += "\nDatabase opened.";
    return true;
}

public bool CloseDB()
{
    int rc;
    if (db != null)
    {
        rc = Sqlite3.sqlite3_close(db);
        if (rc != 0)
        {
            lbOutput.Text += "\nCannot close database: " + Sqlite3.sqlite3_errmsg(db);
            db = null;
            return false;
        }
    }
    lbOutput.Text += "\nDatabase closed.";
    return true;
}

Как видно, в форме сохраняется ссылка на объект Sqlite3.sqlite3, представляющий базу данных. Загрузка базы данных осуществляется посредством вызова метода Sqlite3.sqlite3_open. Закрытие текущей базы данных происходит путем вызова метода Sqlite3.sqlite3_close. Каждое из действий записывается в элемент lbOutput, которое находится на форме. Все остальные методы вызывают метод OpenDB в случае, если поле формы db оказывается пустым и используют открытую базу данных для дальнейших действий.

Создание/удаление таблицы

Исходный код:

private void btnTest_Click(object sender, RoutedEventArgs e)
{            
    int rc;
    string errMsg = string.Empty;
    if (db == null)
        if (!OpenDB())
            return;
    rc = Sqlite3.sqlite3_exec(db, btnCreate.Content.ToString().Substring(2), (Sqlite3.dxCallback)this.callback, null, ref errMsg);
    if (rc != Sqlite3.SQLITE_OK)            
        lbOutput.Text += "\nError: " + Sqlite3.sqlite3_errmsg(db);
    else
        lbOutput.Text += "\nCommand completed successfully";
}

int callback(object pArg, System.Int64 nArg, object azArgs, object azCols)
{
    int i;
    string[] azArg = (string[])azArgs;
    string[] azCol = (string[])azCols;
    String sb="";// = new String();
    for (i = 0; i < nArg; i++)
        sb+=azCol[i] + " = " + azArg[i] + "\n";
    lbOutput.Text += ("\n" + sb.ToString());
    return 0;
}

private void btnDrop_Click(object sender, RoutedEventArgs e)
{
    int rc;
    string errMsg = string.Empty;
    if (db == null)
        if (!OpenDB())
            return;
    rc = Sqlite3.sqlite3_exec(db, btnDrop.Content.ToString().Substring(2), (Sqlite3.dxCallback)this.callback, null, ref errMsg);
    if (rc != Sqlite3.SQLITE_OK)
        lbOutput.Text += "\nError: " + Sqlite3.sqlite3_errmsg(db);
    else
        lbOutput.Text += "\nCommand completed successfully";
}

На самом деле все дальнейшие операции по манипулированию данными – это выполнение SQL-команд. Для этого используется метод" sqlite3_exec, который принимает на вход текущее соединение с БД, SQL-код и метод для обратного вызова. Также на вход передается ссылка на объект-строку, куда будет помещено сообщение об ошибке, в случае если она произойдет. Как видно, в обоих методах перед выполнением проверяется текущее соединение с БД и, если оно закрыто, открывается. В качестве SQL-команды в метод передаются параметры btnCreate.Content.ToString().Substring(2) и btnDrop.Content.ToString().Substring(2) — дело в том, что ребята, создававшие пример поместили SQL-команды прямо внутрь текста кнопки.

Также очень загадочно выглядит метод обратного вызова. На самом деле в нем происходит обработка результата (в случае, если это выборка) и отображение его в том же элементе lbOutput.

Вставка записей

Исходный код:

private void btnInsert_Click(object sender, RoutedEventArgs e)
{
    int rc;
    string errMsg = string.Empty;
    if (db == null)
        if (!OpenDB())
            return;
    rc = Sqlite3.sqlite3_exec(db, btnInsert.Content.ToString().Substring(2), (Sqlite3.dxCallback)this.callback, null, ref errMsg);
    if (rc != Sqlite3.SQLITE_OK)
        lbOutput.Text += "\nError: " + Sqlite3.sqlite3_errmsg(db);
    else
        lbOutput.Text += "\nCommand completed successfully";
}

Собственно здесь подход ничем не отличается от предыдущего случая — для исполнения команды используется метод sqlite3_exec, ну а сама SQL-команда по-прежнему хранится внутри кнопки.

Выборка записей

На самом деле исходный пример не содержал кода для выборки данных. Поэтому я решил восполнить этот пробел и написать в том же духе (нужно же соблюдать общий стиль) еще один метод для выборки данных.

Исходный код:

private void SelectData_Click(object sender, RoutedEventArgs e)
{
    if (db == null)
    {
        if (!OpenDB())
        {
            return;
        }
    }

    string errMsg = string.Empty;
    
    int result = Sqlite3.sqlite3_exec(db, @"select * from test", (Sqlite3.dxCallback)this.callback, null, ref errMsg);
    if (result != Sqlite3.SQLITE_OK)
        lbOutput.Text += "\nError: " + Sqlite3.sqlite3_errmsg(db);
    else
        lbOutput.Text += "\nCommand completed successfully";
}

Как видно, здесь в метод sqlite3_exec передается SQL-команда для выборки данных. В этом случае как раз сработает callback-обработчик, который выведет полученные данные на форму.

Ну а теперь давайте попробуем создать собственное приложение на базе SQLite с более читаемым кодом и посмотрим на все сценарии.

Отдельное приложение для работы с данными

Прежде всего здесь же создадим еще один проект для приложения Windows Phone 7 и сделаем ссылку на библиотеку SQLite. Добавим на форму несколько кнопок (добавление записей, очистка и получение записей из БД).

Получим небольшую несложную XAML-разметку.

<phoneNavigation:PhoneApplicationPage 
    x:Class="DataDrivenApp.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:phoneNavigation="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls.Navigation"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="800"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}">

    <Grid x:Name="LayoutRoot" Background="{StaticResource PhoneBackgroundBrush}">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>

        <!--TitleGrid is the name of the application and page title-->
        <Grid x:Name="TitleGrid" Grid.Row="0">
            <TextBlock Text="MY APPLICATION" x:Name="textBlockPageTitle" Style="{StaticResource PhoneTextPageTitle1Style}"/>
            <TextBlock Text="Data" x:Name="textBlockListTitle" Style="{StaticResource PhoneTextPageTitle2Style}"/>
        </Grid>

        <!--ContentGrid is empty. Place new content here-->
        <Grid x:Name="ContentGrid" Grid.Row="1">
            <Grid.RowDefinitions>
                <RowDefinition Height="Auto"/>
                <RowDefinition Height="*"/>
            </Grid.RowDefinitions>
            
            <StackPanel Orientation="Horizontal">
                <Button Content="Add" Click="AddButton_Click"/>
                <Button Content="Clear" Click="ClearButton_Click"/>
                <Button Content="Refresh" Click="RefreshButton_Click"/>
            </StackPanel>
            
            <ListBox x:Name="Data" Grid.Row="1">
            </ListBox>
        </Grid>
    </Grid>
    
</phoneNavigation:PhoneApplicationPage>

Теперь напишем небольшие обработчики для событий нажатия на каждую из кнопок.

Создание БД и заполнение данными:

private void AddButton_Click(object sender, RoutedEventArgs e)
{
    var db = new Sqlite3.sqlite3();

    if (Sqlite3.sqlite3_open("test.db", ref db) == 0)
    {
        string errMsg = string.Empty;

        if (Sqlite3.sqlite3_exec(db, @"create table peoples (id int primary key, name text)",
            (Sqlite3.dxCallback)(delegate(object pArg, System.Int64 nArg, object azArgs, object azCols) { return 0; }),
            null, ref errMsg) == Sqlite3.SQLITE_OK)
        {
            for (int i = 1; i <= 20; i++)
            {
                Sqlite3.sqlite3_exec(db, String.Format(@"Insert into peoples (id,name) values ({0}, ""{1}"")", i, Guid.NewGuid().ToString().Replace("-", "").Substring(1, 5)),
                    (Sqlite3.dxCallback)(delegate(object pArg, System.Int64 nArg, object azArgs, object azCols) { return 0; }),
                    null, ref errMsg);
            }

            MessageBox.Show("Created", "Data", MessageBoxButton.OK);
        }
        else
        {
            MessageBox.Show("Table creating error");
        }
    }
    else
    {
        MessageBox.Show("Db opening error");
    }
}

Удаление БД:

private void ClearButton_Click(object sender, RoutedEventArgs e)
{
    var db = new Sqlite3.sqlite3();

    if (Sqlite3.sqlite3_open("test.db", ref db) == 0)
    {
        string errMsg = string.Empty;

        if (Sqlite3.sqlite3_exec(db, @"drop table peoples",
            (Sqlite3.dxCallback)(delegate(object pArg, System.Int64 nArg, object azArgs, object azCols) { return 0; }),
            null, ref errMsg) == Sqlite3.SQLITE_OK)
        {
            MessageBox.Show("Dropped", "Data", MessageBoxButton.OK);
        }
        else
        {
            MessageBox.Show("Table dropping error");
        }
    }
    else
    {
        MessageBox.Show("Db opening error");
    }
}

Отображение информации на форме:

private void RefreshButton_Click(object sender, RoutedEventArgs e)
{
    var db = new Sqlite3.sqlite3();

    if (Sqlite3.sqlite3_open("test.db", ref db) == 0)
    {
        string errMsg = string.Empty;
        Data.Items.Clear();

        if (Sqlite3.sqlite3_exec(db, @"select * from peoples",
            (Sqlite3.dxCallback)(delegate(object pArg, System.Int64 nArg, object azArgs, object azCols)
        {
            Data.Items.Add(((string[])azArgs)[1]);
            return 0;
        }),
            null, ref errMsg) == Sqlite3.SQLITE_OK)
        {
            MessageBox.Show("Refreshed", "Data", MessageBoxButton.OK);
        }
        else
        {
            MessageBox.Show("Table refreshing error");
        }
    }
    else
    {
        MessageBox.Show("Db opening error");
    }
}

В данном случае код стало немного более читаемым.

Таким образом, работа с реляционными структурами в Windows Phone 7 возможна уже сегодня и вы можете использовать SQLite в ваших приложениях, если вам недостаточно функциональности Isolated Storage.