SQLite Getting Ready for Windows 10 (Universal App Platform)
Hello, dear All!
I want to talk about a small library for working with SQLite in Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1, and now also for Windows 10 UAP. The library has been over a year old and since problems with her for all the time did not arise, then, I think, you can tell others about her.
Why all this?
The library within the framework of Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1 lasted more than a year. The question is: why now I decided to talk about it? The fact is that now is the time to port your applications for Windows 10, but there is no official SQLite SDK yet.
Here there is, the SDK for Windows Phone 8 , Windows Phone 8.1 , the Windows 8 and8.1 the Windows . But not for Windows 10 .
And most likely it will not be until the release of Windows 10. Therefore, it makes sense to look at this library.
Let's go The
library is called SQLite.WinRT .
I am not its 100% author. I just gathered many pieces of code wandering around the internet together. Therefore, if you believe that I somehow violated your copyrights, then let me know - we will agree.
Source codes can be found here .
You can install the library using NuGet. There are 5 packages:
For successful compilation, you will have to get rid of Any CPU and assemble the project separately for each platform: x86, x64, ARM.
For Windows 10 UAP applications, you must add a link to the following SDKs:
For Windows 8.1 applications, you must add a link to the following SDKs:
For Windows Phone 8.1 applications, you must add a link to the following SDKs:
For Windows Phone 8.0 applications, you must add a link to the following SDKs:
How to use the library?
For an example, we will need the following classes:
Add Record
Record Update
All fields except the primary key are updated.
If you really need it, you can do it like this:
In this case, only that will be updated.
Delete record
or so:
Data Sample
And here LINQ will help us:
Data Migration
Attentive reader noticed 2 interesting challenges:
The first creates or updates tables based on the declared context properties. Columns in the table can only be added. Cannot be deleted.
The second launches a list of migrations whose version is smaller than the database version.
Migrations are declared like this:
Migration example:
Something like that. More information can be found in unit tests.
And now I am ready to listen to your comments, suggestions and objections.
I want to talk about a small library for working with SQLite in Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1, and now also for Windows 10 UAP. The library has been over a year old and since problems with her for all the time did not arise, then, I think, you can tell others about her.
Why all this?
The library within the framework of Windows Phone 8.0 Silverlight, Windows Phone 8.1, Windows 8.1 lasted more than a year. The question is: why now I decided to talk about it? The fact is that now is the time to port your applications for Windows 10, but there is no official SQLite SDK yet.
Here there is, the SDK for Windows Phone 8 , Windows Phone 8.1 , the Windows 8 and8.1 the Windows . But not for Windows 10 .
And most likely it will not be until the release of Windows 10. Therefore, it makes sense to look at this library.
Let's go The
library is called SQLite.WinRT .
I am not its 100% author. I just gathered many pieces of code wandering around the internet together. Therefore, if you believe that I somehow violated your copyrights, then let me know - we will agree.
Source codes can be found here .
You can install the library using NuGet. There are 5 packages:
- SQLite Library Core
- SQLite Library for Windows UAP
- SQLite Library for Windows Phone 8.0
- SQLite Library for Windows Phone 8.1
- SQLite Library for Windows 8.1
For successful compilation, you will have to get rid of Any CPU and assemble the project separately for each platform: x86, x64, ARM.
For Windows 10 UAP applications, you must add a link to the following SDKs:
- Microsoft Visual C ++ 14 AppLocal Runtime Package for Windows UAP
For Windows 8.1 applications, you must add a link to the following SDKs:
- Microsoft Visual C ++ 2013 Runtime Package for Windows
- SQLite for Windows Runtime (Windows 8.1)
For Windows Phone 8.1 applications, you must add a link to the following SDKs:
- Microsoft Visual C ++ 2013 Runtime Package for Windows Phone
- SQLite for Windows Phone 8.1
For Windows Phone 8.0 applications, you must add a link to the following SDKs:
- SQLite for Windows Phone
How to use the library?
For an example, we will need the following classes:
public sealed class DatabaseContext : BaseDatabaseContext
{
private DatabaseContext(SQLiteConnection connection): base(connection)
{
}
public async static Task CreateContext()
{
const string dbName = "db.sqlite";
var folder = CorePlatform.Current.LocalFolder;
var connectionString = new SQLiteConnectionString(Path.Combine(folder, dbName), true);
var connection = SQLiteConnectionPool.Shared.GetConnection(connectionString);
var ctx = new DatabaseContext(connection);
await ctx.CreateSchemeAsync();
await ctx.UpdateSchemeAsync();
return ctx;
}
public IEntityTable- Items { get { return provider.GetTable
- (); } }
public IEntityTable
Categories { get { return provider.GetTable(); } }
}
[Table("Items")]
public class Item
{
[PrimaryKey, AutoIncrement]
public int ItemID { get; set; }
public int CategoryID { get; set; }
public string Title { get; set; }
}
[Table("Categories")]
public class Category
{
[PrimaryKey, AutoIncrement]
public int CategoryID { get; set; }
public string Name { get; set; }
}
Add Record
var category = new Category();
category.Name = "category";
await db.Categories.InsertAsync(category);
Record Update
category.Name = "category2";
await db.Categories.UpdateAsync(category);
All fields except the primary key are updated.
If you really need it, you can do it like this:
var count = db.Categories
.Update()
.Set(t => t.Name).EqualTo("test name")
.Where(t => t.CategoryID).IsBetweenAnd(3, 4)
.Execute();
In this case, only that will be updated.
Delete record
var count = db.Items.Delete()
.Where(t => t.CategoryID).IsLessThanOrEqualTo(3)
.And(t => t.Title).IsEqualTo("item0")
.Execute();
or so:
var category = ...;
await db.Categories.DeleteAsync(category);
Data Sample
And here LINQ will help us:
var query =
from c in db.Categories
join i in db.Items on c.CategoryID equals i.CategoryID
select i;
var items = await query.ToListAsync();
Data Migration
Attentive reader noticed 2 interesting challenges:
await ctx.CreateSchemeAsync();
await ctx.UpdateSchemeAsync();
The first creates or updates tables based on the declared context properties. Columns in the table can only be added. Cannot be deleted.
The second launches a list of migrations whose version is smaller than the database version.
Migrations are declared like this:
[DatabaseUpdate(typeof(DbChangeset1))]
[DatabaseUpdate(typeof(DbChangeset2))]
[DatabaseUpdate(typeof(DbChangeset3))]
[DatabaseUpdate(typeof(DbChangeset4))]
public sealed class DatabaseContext : BaseDatabaseContext { ... }
Migration example:
public class DbChangeset1 : IDatabaseChangeset
{
public int Version { get { return 1; } }
public void Update(IEntityProvider provider)
{
// так как структура обновляется сама, то тут обновляем только данные.
}
}
Something like that. More information can be found in unit tests.
And now I am ready to listen to your comments, suggestions and objections.