Using SQLite database in your Windows 10 apps
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 here. 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.
/// <summary>
/// init db
/// </summary>
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.
/// <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;
}
Add/Delete/Modify/Find
/// <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 -;
}
}
Last but not least, we can access the database easily. For example,
/// <summary>
/// get product tabele data
/// </summary>
/// <returns></returns>
private async Task<List<Fund>> GetFundDataFromDataBaseAsync()
{
var manager = SQLiteDBManager.Instance();
var funds =new List<Fund>();
var products = await manager.FindAsync<Product>("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 > )
{
return funds;
}
return null;
}
You can get entire file here.
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.
最新文章
- SeekBar 圆角问题
- JAVA设计模式《四》
- JS中跨域和沙箱的解析
- setTimeout不断重复执行
- hibernate用注解(annotation)配置sequence
- 基于吉日嘎底层架构的Web端权限管理操作演示-用户管理
- JVM 垃圾回收算法
- 进程外session（session保存在sqlserver）
- html系列教程--DOCTYPE a area
- Android Activity Fragment 生命周期
- PHP获取图片颜色值
- continue，break以及加上标签的使用（goto思路）
- 【bzoj2432】【NOI2011】兔农
- Js元素拖拽功能实现
- 前后端token机制 识别用户登录信息
- Qt在多线程中使用信号槽的示例
- HDU 1045 Fire Net 【二分图匹配】
- PHP开发小技巧①①—php实现手机号码显示部分
- 中文参考文献如何导入到endnote中
- javaweb购物车实现的几种方式