Work with data in WinRT. Part 2. Working with the database on the example of SQLite

In the previous section, we looked at how to access the file storage of an application. Since there is direct access to the file storage, we can use almost any embedded database. Today, SQLite is probably the most popular cross-platform embedded database. In this regard, when porting applications, you will most likely have to work with SQLite, the work with which will be discussed further.

In general, the article turned out more similar to the installation and operation of SQL Lite.

The article will describe
Installing an extension for the tool.
Adding SQLite to a project
Add sqlite-net provider
Work with DB through linq provider (in the style of LinqToSql, EF)
Work with DB through SQL queries (in ADO.NET style)

')
In the demonstration project , a very simple example of working with a list of books will be included in the article.

Tim Heuer's blog has a great article on how to add SQLite support. Consider here instructions for adding SQLite.

Studio preparation. Installing the extension.

The easiest way to provide an opportunity to work with SQLite is to install a special extension. To do this, open the Extensions and Updates (in the Tools menu).


After installing the extension, you will need to restart the studio and now we can add a link to the SQLite library to our projects in our projects.

Adding SQLite to a project

First of all, you need to add a link to the SQLite library (Right click on the project - Add reference):


Note the Microsoft Visual C ++ Runtime Package link. This component is used by the SQLite library. If we don’t add it, our project will start and work, but it must be added, because if we don’t specify a link, the project will not validate the Windows App Certification Toolkit.

By default, the project will not compile.

In order for the project to be compiled it is necessary in Configuration Manager (The last item in the Build menu) to select the x86, x64 or Arm platform



Unfortunately, when laying out an application, you will have to compile the project separately for each platform, but until the SQL Lite port on pure C # appears, it will not be possible to compile with the Any CPU option.

In this case, in the properties of the project, you can return back Any CPU for Platform and Platform Target options.

Add Linq Provider

We can significantly simplify working with SQLite by adding a sqlite-net wrapper from Tim Heuer to our .NET project to work with SQLite similarly to the LinqToSql provider. This wrapper will be useful for those who prefer linq instead of SQL and also simplify porting applications from Windows Phone.

For those who are familiar with the package manager, you can install the wrapper with the command:

Install-Package sqlite-net 

For those who prefer to work with the visual package manager, the package can be installed through the menu Manage NuGet packages (available in the context menu of the project (right-click on the project)) or through the Tools -> Library Package Manager -> Manage NuGet Packages for Solution studio menu ...

Solving the problem with Cyrillic names. (User Names in Russian)

Since the username is part of the path of the isolated storage of the application, and because of the small bugs, sqlite-net cannot work with the Cyrillic paths. Your application may not work for those users whose name is indicated by the Cyrillic.

After installing Sqlite-net, two files are added to our project.

SQLite.cs
SQLiteAsync.cs

to correct the error, in the sqlite.net file in the constructor of the public SQLiteConnection class (string databasePath, bool storeDateTimeAsTicks = false), you need to change the string

 var r = SQLite3.Open(DatabasePath, out handle); 

on

 var r = SQLite3.Open16(DatabasePath, out handle); 

Now we are ready to work with the database.

To connect to the database, you can use the following code:

 using (var db = new SQLiteConnection(dbPath)) { //db code } 

where dbPath is the full path to the database in the file system:

 dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "databasename.db"); 

sqlite-net allows you to work with Linq provider, and in the style of ADO.NET. Consider both points in more detail.

Add sqlite-net provider

For example, we need to save a list of products in the database. The first step is to prepare the appropriate models.

For example, we will create a simple product model in which the primary key with auto-increment and a text field of 250 characters will be specified:

 public class Product { [PrimaryKey, AutoIncrement] public int ProductId { get; set; } [MaxLength(250)] public string Name { get; set; } } 

Suppose we have a separate DataLayer class for working with a database. In the class constructor, create a database connection string:

 private readonly string dbPath; public DataLayer() { dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "products.db"); } 

Add the Init method to the class that will create the database and the Product table when this method is executed:

 public void Init() { using (var db = new SQLiteConnection(dbPath)) { db.CreateTable<Product>(); } } 

When this method is executed, the products.db database will be created in the local folder. This will be a regular sqlite database that can be used on other platforms. We can also use any utility to view the database. For example, if we take the free SQLite Expert Personal and open this database in it, we can see the diagram of our table:


Let's consider some examples of work for typical scenarios. For example, the code to add a new product:

 public Product AddProduct(string name) { var product = new Product() {Name = name}; using (var db = new SQLiteConnection(dbPath)) { db.Insert(product); } return product; } 

When executing this method, the Product object will automatically be assigned the ProductId property from the database.

The following piece of code gets the product by the selected identifier:

 public Product GetProductById(int productId) { using (var db = new SQLiteConnection(dbPath)) { return db.Table<Product>().FirstOrDefault(i => i.ProductId == productId); } } 

You can also use a direct sql query to retrieve the records:

 public Product GetProductById(int productId) { using (var db = new SQLiteConnection(dbPath)) { return db.Query<Product>("select * from product where ProductId=?", productId).FirstOrDefault(); } } 

Accordingly, we can get all the records similarly:

 public List<Product> GetAllProducts() { using (var db = new SQLiteConnection(dbPath)) { return db.Table<Product>().ToList(); } } 

Another nice feature of the wrapper is the ability to delete a product by ID (which is missing in LinqToSQL):

 public void DeleteProduct(int id) { using (var db = new SQLiteConnection(dbPath)) { db.Delete<Product>(id); } } 

A full example showing a very simple example of working with a list of books can be downloaded here .

Work with DB through SQL queries (in ADO.NET style)

Despite the convenience of the Linq provider, in some cases it may be more convenient to work with the database directly using SQL queries, for example, when porting existing code. sqlite-net allows you to fully work with the database, both for performing simple queries that do not return any data, or with data that return complex data types.

In the following examples we will look at how to perform simple queries, queries with parameters, and queries that return a simple data type and a table.

For examples, we will consider the same as in 6.3. DataLayer class for working with data, with a connection string in the constructor:

 private readonly string dbPath; public DataLayer() { dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "products.db"); } 

Consider a similar table creation method:

 public void Init() { using (var db = new SQLiteConnection(dbPath)) { db.CreateCommand( @"CREATE TABLE ""Product""( ""ProductId"" integer primary key autoincrement not null , ""Name"" varchar(250) );" ).ExecuteNonQuery(); } } 

In some cases, it is required to execute a query with input arguments. In this case, you can add an unlimited number of parameters using the “?” Symbol in the sql query for each parameter. For example, consider the implementation of the method of adding a product by name:

 public void AddProduct(string name) { using(var db=new SQLiteConnection(dbPath)) { db.CreateCommand("INSERT INTO Product (Name) VALUES (?)", name).ExecuteNonQuery(); } } 

In some cases, we may need to return a simple data type as a result of a query. So, the query returning the number of rows in the database will look like this:

 public int GetCount() { using (var db = new SQLiteConnection(dbPath)) { return db.CreateCommand("SELECT COUNT(ProductId) FROM Product").ExecuteScalar<int>(); } } 

In cases where we need to return tables, we need to have the appropriate model to retrieve the record. For our table, the model will look like this:

 public class Product { public int ProductId { get; set; } public string Name { get; set; } } 

now we can get through the sql query all the records from our database:

 public List<Product> GetAllProducts() { using(var db=new SQLiteConnection(dbPath)) { return db.CreateCommand("select * from product").ExecuteQuery<Product>(); } } 

Results

As you can see, using SQL Lite is no more difficult to work with SQL CE. Thus, you can use a single cross-platform database in all versions of your application. In the November article, I will look at how to create a cross-platform application using this library.

PS Many thanks to VoldemarRinger and Stasus for their help, advice and solution to the problem in studying the problem with the Cyrillic.