转载

Win10手记-为应用集成SQLite(二)

接上篇内容，这里给大家分享我的辅助访问类，采用了异步方法，封装了常用的访问操作，一些操作还是纯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 -1;    }   }   /// <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 -1;    }   }   /// <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 -1;    }   }   /// <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 -1;    }   }   /// <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 -1;    }   }   /// <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 -1;    }   }   /// <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 main where Id=?", new string[] { Guid.NewGuid().ToString() });             if (result != null)                 return result;             return null;          }

初始化数据库时可以一次性创建需要的表，我创建的表如下：

Win10手记-为应用集成SQLite(二)