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 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项目详细说明，地址如下：

 

 

https://github.com/oysteinkrog/SQLite.Net-PCL