MVP可以在channel 9上传视频了，所以准备做个英文视频传上去分享给大家,本文做稿子。
Hello everyone,
As we all know, SQLite is a great and popular local database running on mobile devices. Consider of its powerful and useful features, lots of apps use SQLite database as the main way of application data storage, including Android and iOS apps. What's more, SQLite is a cross-platform database and also have windows version so that windows apps are easy to integrate with it. Today I will give a lesson about using SQLite in UWP project.
Install
Firstly, it's necessary for us to install SQLite for windows. you can download the binary install file . Actually, you can alse download the source and build it by youself, if you like source more. After you finnished installation, you can find the sqlite extension in the refrence window. It will look like this:
And we need to add the VC++ 2015 Runtime refrence too.
Project Configuration
Secondly, we need to add a framework named SQLite.Net for using SQLite effectively. In other words, SQLite.Net libary will help us access sqlite database more esaily. It have some relese versions which you can find in NuGet, and two of them are most useful, including SQLite.Net-PCL and SQLite.Net.Async-PCL.
What's the defference between SQLite.Net-PCL and SQLite.Net.Async-PCL framework is that SQLite.Net.Async-PCL support asynchronous operations. Actually I like async-await more, so I will install SQLite.Net.Async-PCL framework. Once we finish configurations, we can write some code to use SQLite now.
SQLiteDBManager
Let's we have some interesting codes to begin using this amazing tool now. What's more, I will provide some example codes written by myself for you. The mainly code is used to manager local SQLite database file and access it more easily, so I named it SQLiteDBMnager.
Before we access the data of database, we need import and manage the database file. In windows runtime framework we need to move or create database file in ApplicationData folder. And you can create a database file using SQLite Expert which is famous manage tool for SQLite database.
////// init db /// private static async void InitDBAsync() { try { var file = await ApplicationData.Current.LocalFolder.TryGetItemAsync("ysy.sqlite"); if (file == null) { var dbFile = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Data/ysy.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))); //create db tables var result = await dbConnect.CreateTablesAsync(new Type[] { typeof(Product), typeof(P2PData), typeof(ProductDetail), typeof(P2PDataDetail), typeof(ProductExtend), typeof(P2PDataExtend) }); Debug.WriteLine(result); } } catch (Exception ex) { Debug.WriteLine(ex.Message); } }
After we initialize database, we need to access the data of database. Fortunately, SQLite.NET provides some method for us, but we still do some works to simplify codes.
////// get current DBConnection /// ///public async Task 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; }
Add/Delete/Modify/Find
////// insert a item /// /// item ///public async Task InsertAsync(object item) { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.InsertOrReplaceAsync(item); } catch (Exception ex) { Debug.WriteLine(ex.Message); return -1; } } /// /// insert lots of items /// /// items ///public async Task InsertAsync(IEnumerable items) { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.InsertOrReplaceAllAsync(items); } catch (Exception ex) { Debug.WriteLine(ex.Message); return -1; } } /// /// find a item in database /// ///type of item /// item ///public async Task FindAsync (T pk) where T : class { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.FindAsync (pk); } catch (Exception ex) { Debug.WriteLine(ex.Message); return null; } } /// /// find a collection of items /// ///type of item /// sql command /// sql command parameters ///public async Task
> FindAsync (string sql, object[] parameters) where T : class { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.QueryAsync (sql, parameters); } catch (Exception ex) { Debug.WriteLine(ex.Message); return null; } } /// /// update item in table /// ///type of item /// item ///public async Task UpdateAsync (T item) where T : class { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.UpdateAsync(item); } catch (Exception ex) { Debug.WriteLine(ex.Message); return -1; } } /// /// update lots of items in table /// ///type of item /// items ///public async Task UpdateAsync (IEnumerable items) where T : class { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.UpdateAllAsync(items); } catch (Exception ex) { Debug.WriteLine(ex.Message); return -1; } } /// /// delete data from table /// ///type of item /// item ///public async Task DeleteAsync (T item) where T : class { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.DeleteAsync (item); } catch (Exception ex) { Debug.WriteLine(ex.Message); return -1; } } /// /// delete all items in table /// /// type of item ///public async Task DeleteAsync(Type t) { try { var dbConnect = await GetDbConnectionAsync(); return await dbConnect.DeleteAllAsync(t); } catch (Exception ex) { Debug.WriteLine(ex.Message); return -1; } }
Last but not least, we can access the database easily. For example,
////// get product tabele data /// ///private async Task
> GetFundDataFromDataBaseAsync() { var manager = SQLiteDBManager.Instance(); var funds =new List (); var products = await manager.FindAsync ("select * from Product", null); products.ForEach(p => { funds.Add(new Fund { Id=p.ProductId.ToString(), Host=p.Company, Image=p.Icon, Name=p.Name, Platform=p.FoundationName, QRNH=p.QRNH, WFSY=p.WFSY }); }); if (funds != null && funds.Count > 0) { return funds; } return null; }
You can get entire file .
Conclusion
SQLite database is a good choice as application data storage container, and more excellent that ApplicationSettings and Xml/Json data files in many ways, I think.