UWP SQLite的使用

1.准备工作

  1.首先我们要给项目添加“SQLite for Universal Windows Platform”扩展

    点击菜单栏的“工具”-“扩展和更新”，然后在“联机”中搜索

  2.安装扩展后我们再添加“SQLite.Net-PCL ”的Nuget程序包

    在解决方案管理器中右击项目的“引用”，打开“管理Nuget程序包管理”，在“浏览”中搜索并安装

    现在已经准备完成了，可以开始使用了。

2.使用

    先定义一个数据模板，接下来会用到（这里需要"using SQLite.Net.Attributes;"）

public class DataTemple
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }     //主键

    public string Name { get; set; }
    public string Age { get; set; }
}

  1.建立数据库

string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "mydb.sqlite");
SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), path);

  2.建表

conn.CreateTable<DataTemple>(); //默认表名同范型参数  
//以下等效上面  
//conn.CreateTable(typeof(DataTemple));

  3.增

    增一条：

conn.Insert(new DataTemple(){   Name = textBox.Text, Age = textBox1.Text });

    增一堆：

List<DataTemple> dataTemples=new List<DataTemple>();
dataTemples.Add(……);
//添加若干项……
conn.InsertAll(dataTemples);

  4.删

    删一条：

conn.Execute("delete from DataTemple where Name = ?", name);

    全删：

conn.DeleteAll<DataTemple>();

  5.查

    按条件查询（name是string变量）：

    方法1：

List<DataTemple> query = conn.Table<DataTemple>().Where(v => v.Name == name).ToList();  

    方法2：

List<DataTemple> query = conn.Query<DataTemple>("select * from DataTemple where Name = ?", name);

    查询全部：

    方法1：

List<DataTemple> query = conn.Table<DataTemple>().ToList();

    方法2：

List<DataTemple> query = conn.Query<DataTemple>("select * from DataTemple");

3.Demo

  界面：

<Page
    x:Class="Sqlite.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="using:Sqlite"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d">

    <Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
        
        <StackPanel HorizontalAlignment="Center" VerticalAlignment="Center">
            <Button x:Name = "Add" Content = "添加" Width="80" Margin="0,10,0,0"
                    Click = "Add_Click" HorizontalAlignment="Center"/>
            <Button Name="Select" Content="按名查询" Width="80" Margin="0,10,0,0"
                    Click = "Select_Click" HorizontalAlignment="Center"/>
            <Button x:Name = "Retrieve" Content = "查询全部"  Width="80" Margin="0,10,0,0"
                    Click = "Retrieve_Click" HorizontalAlignment="Center"/>
            <Button Name="Del" Content="按名删除" Width="80" Margin="0,10,0,0"
                    Click = "Del_Click" HorizontalAlignment="Center"/>
            <Button Name="DelAll" Content="全删" Width="80" Margin="0,10,0,0"
                    Click = "DelAll_Click" HorizontalAlignment="Center"/>
            <TextBlock x:Name = "textBlock" TextWrapping = "Wrap" 
                       Text = "Name" Width = "100"/>
            <TextBox x:Name = "textBox" TextWrapping = "Wrap" Width = "100"/>
            <TextBlock x:Name = "textBlock1" TextWrapping = "Wrap" 
                       Text = "Age" Width = "100"/>
            <TextBox x:Name = "textBox1" TextWrapping = "Wrap" Width = "100"/>
            <TextBlock x:Name = "textBlock2" TextWrapping = "Wrap" 
                       Width = "324" Margin="10"/>
        </StackPanel>
        
    </Grid>
</Page>

  逻辑：

using System.Collections.Generic;  
using System.IO;  
using System.Linq;  
using SQLite.Net;  
using SQLite.Net.Platform.WinRT;  
using Windows.Storage;  
using Windows.UI.Xaml;  
using Windows.UI.Xaml.Controls;  
  
//“空白页”项模板在 http://go.microsoft.com/fwlink/?LinkId=402352&clcid=0x409 上有介绍  
  
namespace Sqlite  
{  
    /// <summary>  
    /// 可用于自身或导航至 Frame 内部的空白页。  
    /// </summary>  
    public sealed partial class MainPage : Page  
    {  
        string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "mydb.sqlite");    //建立数据库  
        SQLiteConnection conn;  
  
        public MainPage()  
        {  
            this.InitializeComponent();  
            //建立数据库连接  
            conn = new SQLiteConnection(new SQLitePlatformWinRT(), path);  
            //建表  
            conn.CreateTable<DataTemple>(); //默认表名同范型参数  
            //以下等效上面的建表  
            //conn.CreateTable(typeof(DataTemple));  
        }  
  
        //查询所有  
        private void Retrieve_Click(object sender, RoutedEventArgs e)  
        {  
            textBlock2.Text = "";  
            List<DataTemple> datalist = conn.Query<DataTemple>("select * from DataTemple");  
  
            /下面跟上面等效  
            //List<DataTemple> datalist = conn.Table<DataTemple>().ToList();  
  
            foreach (var item in datalist)  
            {  
                textBlock2.Text += item.Id + " " + item.Name + " " + item.Age + "\n";  
            }  
        }  
  
        //添加一个记录  
        private void Add_Click(object sender, RoutedEventArgs e)  
        {  
            conn.Insert(new DataTemple() { Name = textBox.Text, Age = textBox1.Text });  
        }  
  
        //删除一项  
        private void Del_Click(object sender, RoutedEventArgs e)  
        {  
            conn.Execute("delete from DataTemple where Name = ?", textBox.Text);  
        }  
  
        //查询  
        private void Select_Click(object sender, RoutedEventArgs e)  
        {  
            textBlock2.Text = "";  
            List<DataTemple> datalist = conn.Query<DataTemple>("select * from DataTemple where Name = ?", textBox.Text);  
  
            //下面的也是查询  
            //List<DataTemple> datalist = conn.Table<DataTemple>().Where(v => v.Name==name).ToList();  
  
            foreach (var item in datalist)  
            {  
                textBlock2.Text += item.Id + " " + item.Name + " " + item.Age + "\n";  
            }  
        }  
  
        //删除全部记录  
        private void DelAll_Click(object sender, RoutedEventArgs e)  
        {  
            conn.DeleteAll<DataTemple>();  
        }  
    }  
}

   界面：

  参考资料：Windows 10 Development - SQLite Database

                    https://github.com/oysteinkrog/SQLite.Net-PCL     （在test分支里面有示例代码）