接上篇内容，这里给大家分享我的辅助访问类，采用了异步方法，封装了常用的访问操作，一些操作还是纯CLI的。
SQLiteDBManager
using System;
using System.Collections.Generic;
using System.Collections;
using System.Threading.Tasks;
using SQLite.Net;
using SQLite.Net.Async;
using Windows.Storage;
using System.Diagnostics;
using YunshouyiUWP.Model; namespace YunshouyiUWP.Data
{
public class SQLiteDBManager
{
private static SQLiteDBManager dbManager; /// <summary>
/// construct function
/// </summary>
public SQLiteDBManager()
{
InitDBAsync();
} /// <summary>
/// get current instance
/// </summary>
/// <returns></returns>
public static SQLiteDBManager Instance()
{
if (dbManager == null)
dbManager = new SQLiteDBManager();
return dbManager;
}
private static SQLiteAsyncConnection dbConnection; /// <summary>
/// get current DBConnection
/// </summary>
/// <returns></returns>
public async Task<SQLiteAsyncConnection> GetDbConnectionAsync()
{
if (dbConnection == null)
{
var path = await GetDBPathAsync();
dbConnection = new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), new SQLiteConnectionString(path, true)));
}
return dbConnection;
} /// <summary>
/// insert a item
/// </summary>
/// <param name="item">item</param>
/// <returns></returns>
public async Task<int> InsertAsync(object item)
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.InsertOrReplaceAsync(item);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return -;
} } /// <summary>
/// insert lots of items
/// </summary>
/// <param name="items">items</param>
/// <returns></returns>
public async Task<int> InsertAsync(IEnumerable items)
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.InsertOrReplaceAllAsync(items);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return -;
} } /// <summary>
/// find a item in database
/// </summary>
/// <typeparam name="T">type of item</typeparam>
/// <param name="pk">item</param>
/// <returns></returns>
public async Task<T> FindAsync<T>(T pk) where T : class
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.FindAsync<T>(pk);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return null;
}
} /// <summary>
/// find a collection of items
/// </summary>
/// <typeparam name="T">type of item</typeparam>
/// <param name="sql">sql command</param>
/// <param name="parameters">sql command parameters</param>
/// <returns></returns>
public async Task<List<T>> FindAsync<T>(string sql, object[] parameters) where T : class
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.QueryAsync<T>(sql, parameters);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return null;
}
} /// <summary>
/// update item in table
/// </summary>
/// <typeparam name="T">type of item</typeparam>
/// <param name="item">item</param>
/// <returns></returns>
public async Task<int> UpdateAsync<T>(T item) where T : class
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.UpdateAsync(item);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return -;
}
} /// <summary>
/// update lots of items in table
/// </summary>
/// <typeparam name="T">type of item</typeparam>
/// <param name="items">items</param>
/// <returns></returns>
public async Task<int> UpdateAsync<T>(IEnumerable items) where T : class
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.UpdateAllAsync(items);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return -;
}
}
/// <summary>
/// delete data from table
/// </summary>
/// <typeparam name="T">type of item</typeparam>
/// <param name="item">item</param>
/// <returns></returns>
public async Task<int> DeleteAsync<T>(T item) where T : class
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.DeleteAsync<T>(item);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return -;
}
} /// <summary>
/// delete all items in table
/// </summary>
/// <param name="t">type of item</param>
/// <returns></returns>
public async Task<int> DeleteAsync(Type t)
{
try
{
var dbConnect = await GetDbConnectionAsync();
return await dbConnect.DeleteAllAsync(t);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return -;
}
}
/// <summary>
/// get local path in application local folder
/// </summary>
/// <returns></returns>
private async Task<string> GetDBPathAsync()
{
var file = await ApplicationData.Current.LocalFolder.GetFileAsync("db.sqlite");
if (file == null)
{
var dbFile = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Data/db.sqlite"));
file = await dbFile.CopyAsync(ApplicationData.Current.LocalFolder);
} return file.Path;
} /// <summary>
/// init db
/// </summary>
private static async void InitDBAsync()
{
try
{
var file = await ApplicationData.Current.LocalFolder.TryGetItemAsync("db.sqlite");
if (file == null)
{
var dbFile = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Data/db.sqlite"));
file = await dbFile.CopyAsync(ApplicationData.Current.LocalFolder);
var dbConnect = new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), new SQLiteConnectionString(file.Path, true)));
var result = await dbConnect.CreateTablesAsync(new Type[] { typeof(Fund), typeof(P2P) });
Debug.WriteLine(result);
} }
catch (Exception ex)
{
Debug.WriteLine(ex.Message); }
} }
}
使用方法
以查找数据为例，如下：
public async Task<List<Fund>> GetFundDataAsync()
{
var result = await SQLiteDBManager.Instance().FindAsync<Fund>("select * from Fund where Id=?", new string[] { Guid.NewGuid().ToString() });
if (result != null)
return result;
return null; }
初始化数据库时可以一次性创建需要的表，我创建的表如下：
注意事项
1.要为项目引入SQLite.Net.Async-PCL以及VC++ runtime类库，如下：
2.具体操作SQLite方法请查看SQLite.Net项目详细说明，地址如下：