async void Create()
{
// 数据文件保存的位置
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, " db1.sqlite " );
// 打开创建数据库和表
using ( var db = new SQLite.SQLiteConnection(dbPath))
{
// 创建表
var result = db.CreateTable<Model.Person> ();
await new MessageDialog( " 返回值： " + result).ShowAsync();
}
}
private async void Insert()
{
// 连接数据库
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, " db1.sqlite " );
using ( var db = new SQLite.SQLiteConnection(dbPath))
{
// 插入操作。首先声明一个集合
ObservableCollection<Person> Collection = new ObservableCollection<Person> ();
// 单条插入语句
db.Insert( new Person() { FirstName = " 宋兴柱1 " , LastName = " Sindrol " });
Collection.Add( new Person() { FirstName = " 宋兴柱2 " , LastName = " Sindrol1 " });
Collection.Add( new Person() { FirstName = " 宋兴柱3 " , LastName = " Sindrol2 " });
// 多条插入集合
var result = db.InsertAll(Collection);
await new MessageDialog( " 返回值： " + result).ShowAsync();
}
}
private async void Update()
{
// 更新语句
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, " db1.sqlite " );
using ( var db = new SQLite.SQLiteConnection(dbPath))
{
SQLiteCommand cmd = db.CreateCommand( " update person set FirstName=‘lisa‘ where LastName=‘Sindrol‘ " );
var result = cmd.ExecuteNonQuery();
await new MessageDialog( " 返回值： " + result).ShowAsync();
}
}
private async void Delete()
{
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, " db1.sqlite " );
using ( var db = new SQLite.SQLiteConnection(dbPath))
{
// 单行删除操作
db.Delete<Person>( 1 );
// 多行删除
var result = db.DeleteAll<Person> ();
await new MessageDialog( " 返回值： " + result).ShowAsync();
}
}
private async void Select()
{
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, " db1.sqlite " );
using ( var db = new SQLite.SQLiteConnection(dbPath))
{
// 查询所有数据绑定到UI
List< object > list = db.Query( new TableMapping( typeof (Person)), " select * from Person " );
gridView.ItemsSource = list;
}
}
Person类如下图所示：
class Person
{
[SQLite.AutoIncrement, SQLite.PrimaryKey]
public int ID { get ; set ; }
public string FirstName { get ; set ; }
public string LastName { get ; set ; }
}