原文:
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>();
}
}
}
界面：
https://github.com/oysteinkrog/SQLite.Net-PCL （在test分支里面有示例代码）
