Week6

SQLite数据库本地存储：

实现 todo 表项的增、删、改、查；并且能保存及恢复应用状态。
需要保存：title，description，complete， date（年月日即可），image（Bonus项）
查询时为模糊查询，如下图，查询“现”即可显示日期为 title 或 description 或 date 中含有“现”的 item （查询到的 item 用字符串表示 title+description+date。若有多条，则每行一个item）。

数据库SQLite

添加步骤
- 在项目的解决方案中的 NuGet 包中，安装 SQLitePCL 和 Microsoft.Data.Sqlite 包
- 为项目中的所有小项目添加对他们的引用。

架构方案
这里，我们采用数据库操作和我们的 App 分离的构架。这样的目的是为了便于管理，可以更快定位出错的地方。我们并不采用官方文档提供的 SQLite 使用方法，但使用它对于数据库层代码的管理方式。即在总项目中增添一个 DataAccess 小项目专门进行数据库操作（详细架构方法查看官方文档）。

采取这个架构方案，我明显感觉到代码分离的好处。在写 DataAccess 时只需要关注数据库本身，对于 App 的状况，是不需要关心的；在 App 中对数据库进行请求的时候，我知道有这个接口，至于怎么实现的，也不需要关心。
下面是 DataAccess 的代码。对于接口调用代码则不进行展示，具体见总项目代码。

public static class DataAccess {
    private static string create = @"CREATE TABLE IF NOT EXISTS MyList(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                                                                        "Title VARCHAR(140)," +
                                                                        "Detail VARCHAR(140)," +
                                                                        "Date VARCHAR(140)," +
                                                                        "Completed INTEGER," + 
                                                                        "Image BLOB)";
    private static string insert = @"INSERT INTO MyList(Id, Title, Detail, Date, Completed, Image) " +
                                    "VALUES (?, ?, ?, ?, ?, ?)";
    private static string delete = @"DELETE FROM MyList " +
                                    "WHERE Id = ?";
    private static string update = @"UPDATE MyList " +
                                    "SET Title = ?, Detail = ?, Date = ?, Completed = ?, Image = ? "+ 
                                    "WHERE Id = ?";
    private static string vagueQuery = @"SELECT * " +
                                        "FROM MyList " + 
                                        "WHERE Title LIKE ? OR Detail LIKE ? OR Date LIKE ?";
    private static string titleQuery = @"SELECT * " +
                                        "FROM MyList " +
                                        "WHERE Title LIKE ?";
    private static string dateQuery = @"SELECT * " +
                                        "FROM MyList " +
                                        "WHERE Date LIKE ?";
    private static string seperate = new string('-', 80);

    public static SQLiteConnection connection;

    public static void InitializeDatabase() {
        connection = new SQLiteConnection("MyList.db");
        using (var statement = connection.Prepare(create)) {
            statement.Step();
        }
    }

    public static void AddData(long id, string title, string detail, string date,
                                int completed, byte[] image) {
        using (var statement = connection.Prepare(insert)) {
            statement.Bind(1, id);
            statement.Bind(2, title);
            statement.Bind(3, detail);
            statement.Bind(4, date);
            statement.Bind(5, completed);
            statement.Bind(6, image);
            statement.Step();
        }
    }

    public static void DeleteData(long id) {
        using (var statement = connection.Prepare(delete)) {
            statement.Bind(1, id);
            statement.Step();
        }
    }

    public static void UpdateDate(long id, string title, string detail, string date,
                                    int completed, byte[] image) {
        using (var statement = connection.Prepare(update)) {
            statement.Bind(1, title);
            statement.Bind(2, detail);
            statement.Bind(3, date);
            statement.Bind(4, completed);
            statement.Bind(5, image);
            statement.Bind(6, id);
            statement.Step();
        }
    }

    public static string VagueQueryData(string info) {
        string result = "";
        using (var statement = connection.Prepare(vagueQuery)) {
            statement.Bind(1, "%" + info + "%");
            statement.Bind(2, "%" + info + "%");
            statement.Bind(3, "%" + info + "%");
            while (statement.Step() == SQLiteResult.ROW) {
                string IsCompleted = (Int64)statement[4] == 1 ? "Yes" : "No";
                result += seperate + "\n";
                result += "Id\t\t: " + statement[0] + "\n";
                result += "Title\t\t: " + statement[1] + "\n";
                result += "Detail\t\t: " + statement[2] + "\n";
                result += "Date\t\t: " + statement[3] + "\n";
                result += "Completed\t: " + IsCompleted + "\n";
                result += seperate + "\n";
            }
        }
        if (result == "") result += "\tNo Matched Item!\n";
        return result;
    }

    public static string TitleQueryData(string info) {
        string result = "";
        using (var statement = connection.Prepare(titleQuery)) {
            statement.Bind(1, "%" + info + "%");
            while (statement.Step() == SQLiteResult.ROW) {
                string IsCompleted = (Int64)statement[4] == 1 ? "Yes" : "No";
                result += seperate + "\n";
                result += "Id\t\t: " + statement[0] + "\n";
                result += "Title\t\t: " + statement[1] + "\n";
                result += "Detail\t\t: " + statement[2] + "\n";
                result += "Date\t\t: " + statement[3] + "\n";
                result += "Completed\t: " + IsCompleted + "\n";
                result += seperate + "\n";
            }
        }
        if (result == "") result += "\tNo Matched Item!\n";
        return result;
    }

    public static string DateQueryData(string info) {
        string result = "";
        using (var statement = connection.Prepare(dateQuery)) {
            statement.Bind(1, "%" + info + "%");
            while (statement.Step() == SQLiteResult.ROW) {
                string IsCompleted = (Int64)statement[4] == 1 ? "Yes" : "No";
                result += seperate + "\n";
                result += "Id\t\t: " + statement[0] + "\n";
                result += "Title\t\t: " + statement[1] + "\n";
                result += "Detail\t\t: " + statement[2] + "\n";
                result += "Date\t\t: " + statement[3] + "\n";
                result += "Completed\t: " + IsCompleted + "\n";
                result += seperate + "\n";
            }
        }
        if (result == "") result += "\tNo Matched Item!\n";
        return result;
    }
}

还有一点就是分离 SQL 语句，这个是为了复用，我们这个是小型项目，所以体会不深，但是如果在大项目中，则是一件很棒的事情。

视图化数据库
安装 SQLite Expert Personal，可通过 GUI 界面对 database 进行查看和操作。
数据库图片：
多种匹配查询
可以选择查询匹配的要求。可以进行 日期匹配，标题匹配和模糊全匹配
日期查询：

数据库中图片存储
通过 SQLit DataType 介绍可以看到，SQLite 提供了一个 BLOB 类型来存储自定义或难以定义的类型。通俗来说，BLOB 可以做到，你存入什么，我就存什么。通过这一点，我们在数据库中加入一个 BLOB 类型的 Image 字段，来存储图片。这个字段究竟存储什么呢？图片文件是不太好的，所以我们将图片转成二进制数组进行存储。对于这个字段的处理，和普通字段没有任何区别，就是传入的参数为 byte[]。

这里利用 DataReader 来将图片流转成二进制数组。

private async void Select_Photo(object sender, RoutedEventArgs e) {
    // open the image file
    if (file != null) {
        /// ...
        /// Load the selected picture
        var stream = await file.OpenReadAsync();
        /// image to byte
        using (var dataRender = new DataReader(stream)) {
            var imgBytes = new byte[stream.Size];
            await dataRender.LoadAsync((uint)stream.Size);
            dataRender.ReadBytes(imgBytes);
            /// Load image file into image
            /// save bytes
            imgData = imgBytes;
        }
    }
}

从 byte 数组恢复图片。这里利用 MemoryStream 的二进制构造函数来构造流，然后再利用 BitMapImage 的流构造函数构造图片。

private async Task<BitmapImage> BytesToBitmapImage(byte[] imgByte) {
    try {
        MemoryStream stream = new MemoryStream(imgByte);
        BitmapImage bitmap = new BitmapImage();
        await bitmap.SetSourceAsync(stream.AsRandomAccessStream());
        return bitmap;
    }
    catch (ArgumentNullException ex) {
        throw ex;
    }
}