SQLite with WINRT Framework
Want to Share data across
Metro app?
MS does not support this feature, I have done
a workaround to achieve this….
Converted SQLite source code entirely to
support winRT Framework version, This will help us to create and access the
database apart from the installed location. Supported filed locations are
Public & private Documents, Downloads, Pictures, Music and videos. This option doesn’t support in native
version of winRT application (Plugin).
This library supports the following functionality
Ø Bulk Insert, update and delete
Ø Encryption
database
Ø Database
Shrink
Ø Transaction
SQLite
“Congruent.CsharpSqlite” Library details
Operation
|
SQL Wrapper Method Name
|
Bulk Insert
|
BulkInsert<T>( object collection)
|
Bulk Update
|
BulkUpdate<T>( object collection)
|
Bulk Delete
|
BulkDelete<T>(object collection)
|
Encrypt Database file
|
DBPassword(string password)
|
Database
Shrink
|
DatabaseShrink()
|
Transaction
|
BeginTransaction()
Rollback()
Commit()
|
Add
reference to the library “Congruent.CsharpSqlite”
Bulk Insert, update
and delete
For example if you want to insert data to the user table,
Assuming SqLite has the table named “user” with the column “username” & “DOB”
. Now we have collection of user data and
insert to sqlite database . In this scenario we need to use Bulk insert methods
as well as update and delete , it will be
complie the query first time after that use complied query for insert collection records .
var Dbpath = await KnownFolders.DocumentsLibrary.CreateFolderAsync("DBFile", CreationCollisionOption.OpenIfExists);
connection = new SQLiteAsyncConnection(string.Format("{0}\\{1}",
Dbpath.Path, "Sqlitedb.db"));
////Create table
connection.CreateTableAsync<User>();
var listUser
= new List<User>();
listUser.Add(new User() {
UserID = Guid.NewGuid(), DOB = DateTime.Now, Name = "senthil" });
listUser.Add(new User() {
UserID = Guid.NewGuid(), DOB = DateTime.Now, Name = "arun" });
listUser.Add(new User() {
UserID = Guid.NewGuid(), DOB = DateTime.Now, Name = "somu" });
////Bulk insert user collection
connection.BulkInsert<User>(listUser);
int i = 0;
foreach (var item in listUser)
{
item.Name = item.Name +
i.ToString();
i++;
}
////Bulk update user collection
connection.BulkUpdate<User>(listUser);
////Bulk delete user collection
connection.BulkDelete<User>(listUser);
Encrypt Database file
The library support encrypt (SEE) the database
file. The sqlite db file doesn’t encrypt the untill set the password protection.
Once done the protection SEE allows SQLite to read and write encrypted database
files. All database content, including the metadata, is encrypted so that to an
outside observer the database appears to contain white noise.
For example
1. The initial DB file doesn’t have
the password protection . So that need to set new password or override existing
password use below code
var Dbpath = await KnownFolders.DocumentsLibrary.CreateFolderAsync("DBFile", CreationCollisionOption. OpenIfExists);
connection = new SQLiteAsyncConnection(string.Format("{0}\\{1}",
Dbpath.Path, "Sqlitedb.db"));
connection.ChangePassword ("abcd1234");
2.
Once done the file encryption after that we need to provide password
for reading encrypted file, Use the below example
var Dbpath = await KnownFolders.DocumentsLibrary.CreateFolderAsync("DBFile", CreationCollisionOption.ReplaceExisting);
connection
= new SQLiteAsyncConnection(string.Format("{0}\\{1}",
Dbpath.Path, "Sqlitedb.db"));
connection.DBPassword("abcd1234");
gridview1.DataContext
= connection.Table<User>();
Database Shrink
Shrinking data
files recovers space by moving pages of data from the end of the file to
unoccupied space closer to the front of the file. When enough free space is
created at the end of the file, data pages at end of the file can deallocated
and returned to the file system.
var Dbpath = await KnownFolders.DocumentsLibrary.CreateFolderAsync("DBFile", CreationCollisionOption.ReplaceExisting);
connection = new SQLiteAsyncConnection(string.Format("{0}\\{1}", Dbpath.Path, "Sqlitedb.db"));
connection.DatabaseShrink();
Transaction
A
Transaction is single unit of work. Data modifications made during the
transaction are committed and become a permanent part of the database. If a
transaction encounters errors and must be canceled or rolled back, then all of
the data modifications are erased.
try
{
connection.BeginTransaction();
var listUser
= new List<User>();
listUser.Add(new User() {
UserID = Guid.NewGuid(), DOB = DateTime.Now, Name = "senthil" });
listUser.Add(new User() {
UserID = Guid.NewGuid(), DOB = DateTime.Now, Name = "arun" });
listUser.Add(new User() {
UserID = Guid.NewGuid(), DOB = DateTime.Now, Name = "somu" });
////Bulk insert user collection
connection.BulkInsert<User>(listUser);
connection.Commit();
}
catch
{
connection.Rollback();
