最近需要在UWP中用到数据库,一开始的想法是连接SQL Server,后面发现UWP连接SQL并不是那么直接,所以在一番搜索过后决定使用SQLite。
1.什么是SQLitebh
SQLite属于一个轻量级的数据库,大部分用于嵌入式、或者跨平台的应用开发。微软也为了推广UWP,大力推荐用户使用SQLite.
SQLite官方网站:https://www.sqlite/
微软官方的一些帮助文档:https://docs.microsoft/en-us/windows/uwp/data-access/sqlite-databases
2.如何在VS2017中的UWP项目添加对SQLite的引用
2.1 在(工具 --- 扩展和更新)中下载并安装SQLite for Universal WIndows Platform (也可以在前面提到的官网中进行下载VS扩展包)
注:一般在这里会提示扩展包是适配于VS2015,直接忽略继续安装
2.2 在(引用)上右键添加SQLite for Universal Windows Platform的引用
2.3 在(项目处右键 --- 管理NuGet程序包)中浏览搜索下载并安装SQLite.Net-PCL
其实在market里面有很多关于SQLite的程序包,但我们在这里只需要下载安装 SQLite.Net-PCL,如果安装错了会导致SQLite无法正常使用。
至此,前期的程序引用结束
3.简单Demo
3.1 创建一个新的空白通用应用3.2 按照前面所提到的添加两个引用,结果如图
3.3 创建一个界面,主要的操作就是创建一个表、写入一些数据、读取这些数据并绑定到ListView中,最后是删除数据。 下面的前段的XAML代码:
<Page
x:Class="SQLiteDemo.MainPage"
xmlns="http://schemas.microsoft/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft/winfx/2006/xaml"
xmlns:local="using:SQLiteDemo"
xmlns:d="http://schemas.microsoft/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats/markup-compatibility/2006"
mc:Ignorable="d">
<Grid Margin="20" Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
<Grid.RowDefinitions>
<RowDefinition Height="auto"/>
<RowDefinition/>
</Grid.RowDefinitions>
<StackPanel Orientation="Horizontal" Margin="0,10">
<Button Content="创建表" Click="OnClick" Margin="12,0,0,0"/>
<Button Content="写入数据" Click="OnInsert" Margin="12,0,0,0"/>
<Button Content="读取数据" Click="OnReadData" Margin="12,0,0,0"/>
<Button Content="删除全部数据" Click="OnDeleteData" Margin="12,0,0,0"/>
</StackPanel>
<ListView IsItemClickEnabled="True" Grid.Row="1" Name="lv" Margin="2,5" Background="LightGray">
<ListView.ItemContainerStyle>
<Style TargetType="ListViewItem">
<Setter Property="HorizontalContentAlignment" Value="Stretch"/>
<Setter Property="FontSize" Value="25"/>
</Style>
</ListView.ItemContainerStyle>
<ListView.ItemTemplate>
<DataTemplate>
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="2*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<TextBlock Grid.Column="0" Text="{Binding ID}"/>
<TextBlock Grid.Column="1" Text="{Binding Name}"/>
<TextBlock Grid.Column="2" Text="{Binding Age}"/>
<TextBlock Grid.Column="3" Text="{Binding Gender}"/>
</Grid>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</Grid>
3.4 添加一个附加Person类,用来指示表中各类元数据 (注意添加 using SQLite.Net.Attributes的命名空间)
using SQLite.Net.Attributes;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SQLiteDemo
{
[Table("per_info")]
class Person
{
[Column("id")]
[PrimaryKey]
[AutoIncrement()]
public int ID { get; set; }
[Column("name")]
[NotNull]
public string Name { get; set; }
[Column("age")]
public int Age { get; set; }
[Column("gender")]
[NotNull]
public string Gender { get; set; }
}
}
3.5 在MainPage.xaml.cs中添加后端逻辑 (同意注意添加对应的几个命名空间)
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;
using SQLite.Net;
using SQLite.Net.Attributes;
using SQLite.Net.Platform.WinRT;
using SQLite.Net.Interop;
using static System.Diagnostics.Debug;
using Windows.Storage;
using SQLiteDEmo;
// https://go.microsoft/fwlink/?LinkId=402352&clcid=0x804 上介绍了“空白页”项模板
namespace SQLiteDemo
{
/// <summary>
/// 可用于自身或导航至 Frame 内部的空白页。
/// </summary>
public sealed partial class MainPage : Page
{
public MainPage()
{
this.InitializeComponent();
dbname = "test.db";
}
public string dbname;
//创建表
private void OnClick(object sender, RoutedEventArgs e)
{
string fdlocal = ApplicationData.Current.LocalFolder.Path;
string filename = dbname;
string dbfullpath = Path.Combine(fdlocal, filename);
ISQLitePlatform platform = new SQLitePlatformWinRT();
// 连接对象
SQLiteConnection conn = new SQLiteConnection(platform, dbfullpath);
WriteLine("db pathe: " + conn.DatabasePath);
// 创建表
int rn = conn.CreateTable<Person>(CreateFlags.None);
WriteLine("create table res = {0}", rn);
conn.Dispose();
}
//插入数据
private void OnInsert(object sender, RoutedEventArgs e)
{
string localFolder = ApplicationData.Current.LocalFolder.Path;
string dbFullPath = Path.Combine(localFolder, dbname);
// 建立连接
using (SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), dbFullPath))
{
//conn.DeleteAll<Person>();
// 插入数据
Person[] stus =
{
new Person { Name="小王",Age = 21,Gender = "male" },
new Person { Name = "小赵",Age=30,Gender = "male" },
new Person {Name="小丁",Age=25,Gender = "male" },
new Person {Name="小马",Age=27,Gender = "female" },
new Person {Name="小陈",Gender = "male"}
};
int n = conn.InsertAll(stus);
WriteLine($"已插入 {n} 条数据。");
}
}
//读取数据
private void OnReadData(object sender, RoutedEventArgs e)
{
string localFolderPath = ApplicationData.Current.LocalFolder.Path;
string dbFullpath = Path.Combine(localFolderPath, dbname);
using (SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), dbFullpath))
{
// 获取列表
TableQuery<Person> t = conn.Table<Person>();
var q = from s in t.AsParallel<Person>()
orderby s.ID
select s;
// 绑定
lv.ItemsSource = q;
}
}
//删除数据
private void OnDeleteData(object sender, RoutedEventArgs e)
{
string localFolder = ApplicationData.Current.LocalFolder.Path;
string dbFullPath = Path.Combine(localFolder, dbname);
// 建立连接
using (SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), dbFullPath))
{
conn.DeleteAll<Person>();
TableQuery<Person> t = conn.Table<Person>();
var q = from s in t.AsParallel<Person>()
orderby s.ID
select s;
// 绑定
lv.ItemsSource = q;
}
}
}
}
3.6 最终按顺序,创建表--写入数据--读取数据 ,效果如图
4 总结 本文只是简单的汇总了一下网上的一些教程,并且附加了一个简单的Demo,希望能给刚入门的朋友可以有个快速了解SQLite的机会。
更多推荐
VS2017 UWP项目中使用SQLite数据库简易教程【附简单demo】
发布评论