As a little surprise for developers, Windows 8 doesn’t come out with native database support. In the past months some SQLite portings came out, but none of the them was working really good. After the Windows Phone Summit (where Microsoft announced Windows Phone 8), the situation started to be more clear: SQLite will be the database officialy supported by Microsoft and SQLite will be officially released both for Windows 8 and Windows Phone 8. This way it’s likely to think that, as developers, we will be able to share not only the database but also the data access layer between the two platform: this will help us a lot porting our apps from one platform to the other.
Now SQLite’s branch for WinRT is available and we can start to use it to store the data of our applications, with the help of another library called sqlite-net, that provides LINQ based APIs to work with data, both with sync and async support.
In this post we’ll see how to start using SQLite in a XAML / C# application, how to create our first database and how to make some simple operations, like storing and reading data.
Adding SQLite to our Metro style app
The first step is to download from the official SQLite website the WinRT version: be careful that, since it’s a natice code library, you should get the specific version for the platform you’re going to support (x86 or x64). ARM is still not supported, but I expect it to be released soon.
For this example I’ve downloaded, from the Download page, the x86 version, which file name is sqlite-dll-winrt-x86-3071300.zip
After you’ve downloaded it, you’ll have to extract the content somewhere and add the file sqlite3.dll to your project: be careful that, since the DLL it’s a native library that contains the SQLite engine (so it doesn’t provide any API to interact with it), you’ll simply have to copy it in the root of your project and make sure that the Build Action is set to Content.
Now that you have the engine you need something to interact with it: please welcome sqlite-net, a library available on NuGet and that supports WinRT, that provides data access APIs to interact with the database, using LINQ-based syntax.
Adding it is very simple: just right click on your project, choose Manage NuGet packages, search online for the package with name sqlite-net and install it. The package will add two classes in your project: SQLIite.cs (that provides sync access) and SQLIteAsync.cs (that provides, instead, asynchronous operations to interact with the database).
Now you’re ready to create your first database.
Create the database
The sqlite-net approach should be familiar to you if you’re a Windows Phone developer and you have already worked with SQL CE and native database support: we’ll have a class for every table that we want to create and we’re going to decorate our properties with some attributes, that will tell to the library how to generate them. We’ll use for this example a very simple class, that can be used to store a list of persons:
public class Person
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(30)]
public string Name { get; set; }
[MaxLength(30)]
public string Surname { get; set; }
}
In this example you can see some of the simple attributes you can use to decorate your classes:
- PrimaryKey is used to specify that the column will be the primary key of the table.
- AutoIncrement usually is used in couple with a primary key; when this option is enabled the value of this column will be a number that will be automatically incremented every time a new row is inserted in the table.
- MaxLength can be used with string properties to specify the maximum number of chars that will be stored in the column.
After you’ve defined the tables, it’s time to create the database. Since WinRT relies as much as possible on an asynchrnous pattern, we’ll use the async version of sqlite-net.
private async void CreateDatabase()
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
await conn.CreateTableAsync<Person>();
}
The first step is to create a SQLiteAsynConnection object, that identifies the connection to the database, like in the example: the parameter passed to the constructor is the name of the file that will be created in the local storage. Then we call the CreateTableAsync<T> method for every table that we want to create, where T is the type of data that we’re going to store in it (in the example, every row will be an element of the Person class). Notice that the method returns a Task, so we can use the keyword await to perform the operation asyncrhonously.
Play with the data
Now that we have a database, we can have some fun by adding and reading some data. Both operations arew very simple and, in both case, we’ll need a SQLiteAsyncConnection object that points to the same database.
To insert data we use the InsertAsync method, that simply accepts as parameter an istance of the object we’re going to save. Obviously, the object’s type should match the table’s type. Here is an example:
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
Person person = new Person
{
Name = "Matteo",
Surname = "Pagani"
};
await conn.InsertAsync(person);
}
To query the data, instead, we can access directly to the table using the Table<T> object: it supports LINQ queries, so we can simply use LINQ to search for the data we need. Then, we can call the ToListAsync method to get a List<T> of objects that matches the specified query. In the following example we look in the table Person all the users which name is Matteo and we print the results in the Output Window.
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
var query = conn.Table<Person>().Where(x => x.Name == "Matteo");
var result = await query.ToListAsync();
foreach (var item in result)
{
Debug.WriteLine(string.Format("{0}: {1} {2}", item.Id, item.Name, item.Surname));
}
}
Where is my data?
If you want to take a look at your data, you can access to the path where Windows 8 stores the local storage of application. To find it, simply get the value of the property Windows.Storage.ApplicationData.Current.LocalFolder.Path. Once you have it, you’ll find in that folder a file with the same name that you’ve specified as parameter when you’ve created the SQLiteAsyncConnection object. If you want to open it, I suggest you to download and install an utility called SQLite Database Browser, that you can find at the website http://sqlitebrowser.sourceforge.net/.
With this utility you can open the database and explore it: you can see the tables, query the data and so on. Have fun!
Update: here you can download a sample project
Thanks for the write-up, Matteo! I’ve managed to successfully repeat all the steps in your tutorial.
Good to know, let me know if you have any question!
Thanks for the feedback!
could you please explain in detail how to connect sqlite database connection using javascript,HTML…..
Hi, I’m sorry but I don’t have experience in developing Windows Store apps using HTML and Javascript.
Take a look at this post, it can be helpful http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx
What is the syntax for RunInTransactionAsync? This works for RunInTransaction but needs Action for Async – cannot work out what it is expecting?
no good:
mydb.RunInTransactionAsync(() =>
{
mydb.InsertAsync(new ACCOUNTS() { account_num = “12345”}
Thanks!
Hi Jenny,, which is the object you are going to use to interact with the database in your anonymous method.
the RunInTransactionAsync methods requires a parameter of type Action
Here is an example:
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);
await conn.RunInTransactionAsync(async (connection) =>
{
Person person = new Person
{
Name = “Matteo”,
Surname = “Pagani”
};
await connection.InsertAsync(person);
});
I hope this helps!
Cheers
Thanks for the write-up, I thought I have to use only WebAPI to access data in metro-app,, very helpful!
Really awesome blog. Thank u so much dude. Everything works well as i expected.
Just having 1 question .If i use sqllite in this way ,will the app pass WACK tets for the appstore ?
Hi Shameer,
I’m really glad that you like my blog 🙂
Yes, WACK is passed if you use SQLite, you just have to make sure to declare in your manifest that you’re using the Microsoft.VCLibs.110 framework. Here is how to do it: http://blog.usmanahmed.info/dependency-on-microsoftvclibs110-framework
Great article Matteo! Thank you for sharing with us.
I have a question for you: I’m beginning porting of a Windows Phone 7 app to Windows 8, and where I was using a SQL CE 3.5 database, now I will use a SQLite db, so I will try to convert it.
Once I have a SQLite db ready to use, how can I import it in the “Windows.Storage.ApplicationData.Current.LocalFolder” and how can I map it with LINQ to create an ORM layer ?
TIA
Good question 🙂 Let me investigate a bit and I’ll come back with a reply.
Thanks! Very useful!!!
you’re welcome
When we publish an app that uses an SQLite database will other users be able to read information from the database from their own computers?
I mean to say how accessible will the SQLite database be to other users?
The database is created into the local storage, that is located inside the user folder. If the user finds out which folder is he can see and access to the database. if you want to avoid it, you have to encrypt the file.
Okay thanks so much. This really helped.
Nice Post Matteo. Can you suggest simple encryption solution? We want to implement that in our app.
Thanks..nice writing!!!!
In my case i want to insert some default entries to the table at the time of db creation. How can i determine whether the db is already created or not?Because i dont want insert default entries every time user run the app.
thanks in advance
You can check if the database file is already existing or not in the local storage. You’re going to insert default data only if the file is not existing:
private async void CreateDatabase(){
bool isDatabaseExisting = false;
try
{
StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("people");
isDatabaseExisting = true;
}
catch
{
isDatabaseExisting = false;
}
if (!isDatabaseExisting)();
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
await conn.CreateTableAsync
}
}
Thank you so much !!!!
Hi,
I have a problem. I am getting exception An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) during the CreateDatabase method. How can I solve this problem? thx
I’ve never experienced that issue. Can you try sending me a sample project with your problem? Contact me using the contact form and I’ll reply to you with my mail address.
can i use sync functions?
i try to create MVVM structure, and i get data use TolistAsync() procedure in model view, but, i can’t dispaly data on screen.
The problem you’re experiencing probably happens because the ToListAsync() method returns a generic list, not an ObservableCollection. For this reason, the controls that are in binding with the collection aren’t automatically updated every time something changes in the collection. and bind the new collection to the control.
Try to convert the result of the ToListAsync() method into an ObservableCollection
Cheers
Thanks, i’ll try to use it.
Hi Matteo, i followed your step but facing exception.”An exception of ‘SQLite.SQLiteException’ occured in Apps_Name.DLL but was not handle in user code”.Please help me to solve this issue.
Hi Khalid, the message isn’t helping me to find what’s wrong. Have you installed the Visual Studio extension? Have you downloaded the latest sqlite-net package from NuGet? Otherwise, if you can send me your code I will take a look.
Miro i had the same error and i solved it by referencing the sqlite dll from the main project (App) instead of a class lib.
Hi,
I need your help 🙂 I have a problem when I generate my project : “SQLite.SQLiteException Could not open database file: C:UsersClémentAppDataLocalPackagescc121a7a-eac7-4655-8163-98ce14222e78_vjm86x7agcmvtLocalStatepeople (CannotOpen)”.
However, I have follow all your instructions.
Have you got an idea ?
can you send me using the contact form a sample project with your problem? I can’t reproduce it
I have found the problem, he comes from the accent in my firstname. Maybe because the SQLite class. Have you got any idea (workarround) ?
Thx 🙂
no 🙁 ?
Can you send me a sample project I can play with? Thanks!
Hi,
I am attaching My Sample project Where I am creating multiple Updates in the Second Transaction. It throws ‘cannot open’ exception. First Click on ‘CreateDb’ button and then Click on ‘Test Update’ button.
Project Link: https://skydrive.live.com/redir?resid=3031BCF7E1275424!228&authkey=!AJge6sgTIsg8EaY
This Bug is really weird, any help will do.
Thanks In Advance
Hi Matteo, thanks for the really useful example.
I was asking me if and how it is possible to make joins in the db using linq; I would be interested in using
var query = conn.Table with multiple related tables … is it possible?
Thanks and keep up the great work !
Ciao !
can you give me a runnable example?I can’t deal with my sqliteexception ! thank you!
Hi, I have updated the post with a working sample project. Check it out if it works for you!
Good question, I’ll do some research and I’ll get back to you!
Thank you very much, very helpful to me
Grazie mille! Ora sono operativo 🙂
Can u share sample source code I am getting an exception
Hi, you’ll find a sample project attached at the end of the post.
Matteo, great post, I am new in sqllite, and new in windows 8, but does sqllite support image datatype like the SQL CE?
Not directly, to save images you have to use the BLOB datatype, that allows to store binary data.
How can I use foreign key constrain in SQLite for WinRT ? Can you please demo that scenario ?
Hi Farhan,
I’ll investigate a bit and I’ll try to find the time to write a separate blog post. Actually, the operation is not really straightforward because sqlite-net doesn’t have built in support for foreign keys and relations, so you can’t do Person.Orders, for example, like you would do with LINQ to SQL or Entity Framework.
I have followed your tutorial in creating my app. Now I’m stuck with the fetching of images from sqlite.
Kindly help me with that.
“await conn.InsertAsync(person)” returns always 1, how can I get the PersonID as return value ? In method summery it’s written that it returns auto incremented primary key if it has one, but I am not getting.
Hi,
Anybody tried with DeleteAsync() or UpdateAsync() APIs. is it working for anyone. Though I didn’t get any exception while using this API, but the data inside the database is not deleted.Anybody has solution for this problem?
Thanks & Regards,
Suman
hi i am facing problem with await conn.InsertAsync(People);
it is giving exception “the await operator can only be used with in async method.
Make sure that the method where you’re using the InsertAsync() method is marked as async, like I’ve done in the example with the Button_Click_1 event handler.
How to store DateTime in SQLite database ? According to http://www.sqlite.org/datatype3.html, “SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values”, then what should me the data type of property in class (table) file ? String data type generates varchar(140) in SQLite
And one more thing, is it possible to store blob data type data in SQLite ?
Every Time i Change Package.appxmanifest this delete my datbase?
how can i Change this?
this is the function for the init of my database…
private async void InitDatabase()
{
bool isDatabaseExisting = false;
try
{
StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(“Firmen.SQLite”);
isDatabaseExisting = true;
}
catch
{
isDatabaseExisting = false;
}
if (!isDatabaseExisting) {
await db.CreateTableAsync();
MessageDialog dialog = new MessageDialog(“Die Datenbank wurde neu angelegt! Bitte das Programm neu starten!”, “Programm Hinweis”);
dialog.ShowAsync();
}
}
How can I execute sql query without knowing table structure? I mean, we don’t know what is the client will pass. They will just give the query and we have to execute.
for example:
“CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT)”;
OR
“CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT, phone REAL)”;
OR
“CREATE TABLE IF NOT EXISTS employee (id REAL, name TEXT, add TEXT)”;
Any sql query they can ask, is it possible in metro app?
I am not able to create a database using SQLite with my Metro application.
Description:
I continue to receive a runtime exception on a failure to load SQLite3.dll.
Observation:
I observed that the database directory never gets created in the app package’s LocalState directory. After observing this, I literally implemented file path creation at runtime and dropped the sqlite3.dll is in the intended location.
Yet, I still receive an exception on failed to load SQLite3.dll.
1.My CRUD code that leverages SQLite compiles fine.
2.SQLite dependencies (packages.config, SQLite.cs, and sqlite3.dll) are located in a separate metro class library instead of the application’s executable project.
3.The sqlite3.dll is based on the ARM processor.
4.The sqlite3.dll is set to Content, Copy Always.
Code:
SQLiteAsyncConnection connection = new SQLiteAsyncConnection(DATABASE_DIRECTORY_NAME);
return await connection.CreateTableAsync();
Please help…
We have create our database externally and when we want to connect to the database it gives error. Should we have to create the database at runtime ? because the sample code you have sent works if we click to the button create database and insert data then we can read otherwise it gives there is no such table.
I can’t make sqlite-net work on Windows Phone 8. The namespace Community.CsharpSqlite is not found. Do you have a solution to that or an alternative way to access SQLite on WP8?
At the moment I’m not aware of a LINQ library, like sqlite-net, that works with Windows Phone 8. As soon as I find some information I’ll surely blog about it, the topic is really interesting also for me.
Hi,
In your article you have mentioned that, we have to install sqlite-net by right clicking on the project.
Do we have to follow the same process for all new Windows Store app we are creating?
Yes, because every project has its own set of referenced and NuGet packages, they are not shared among all the Visual Studio projects.
Your post was very helpful. Thank you.
Just had 2 questions:
1)How to specify foreign key relationships between tables.. I came across Sqlite.Indexed attribute which does the same thing but it doesn’t specify which table it indexes..
2)Can we use the same for an WPF app??
1) I’m working on a blog post about using relationships, I’ll cover this topic very soon 🙂
2) No, the same code won’t work, since it’s based on WinRT APIs, that are available only for Windows Store apps for Windows 8.
All right.. Actually I am building an app which has the database tables without relationships.. Please write the blog on that soon:) Thank you..
heeloo
can u help me with the foreign key ?
or if i can , can u give me how to join or make a view in linq c# ?
its my first time use linq
hihihi
thanks
Actually sqlite-net is a bit limited, compared to other LINQ to SQL implementations, so you can’t use it to make joins. I’m waiting for new and better library to come to improve sqlite support in Windows 8.
btw , where i can find the library of sqlite-net ?
i am so confuse with linq
hihi
please give me some information
thanks
It’s described in the post: you have to install sqlite-net using NuGet package manager, that you can open by right clicking on the project, choosing Manage Nuget packages and looking for sqlite-net using the search box.
Hello sir ,
in Your code , ehm , how i can know the data already sucessfully inserted ?
maybe some try catch or ?
please help me sir
Hi shamir,
the answer is yes: if you don’t get any exception when the InsertAsync execute method is called, then the data has been successfully inserted.
Hi Matteo
I have some questions for you.
*Can we have many tables in one db?
*how to create table?
*how to execute sqlite command?
Thanks , please reply
Hi,
yes, you can have many tables in one single db.
For the other questions, please refer to the post: it’s described both how to create tables and how to do operations with them.
Cheers
I have downloaded your example. I have still message when I press any button:
“Could not open database file: C:UsersMichałAppDataLocalPackagese12bc76c-4f76-4198-8ed7-a6b0d8828dd3_e7ma2tg35b58yLocalStatepeople.db (14)”
Have you already created it? If you don’t find a way to fix the issue, try to send me a sample project using the contact form and I’ll take a look.
But I have this message when I press button which should create a DB for me. So this msg is possible when I try to add something, not when I am creating a DB, yes?
I will send you my code to see how it works in my project. Thanks 🙂
hi matteo, i wanna to ask something important for my project. how to insert data from textbox to database?
Hi, the code is exactly the same you saw in the post, the only difference is that, to populate the object data to store in the database, you access to the Text property of the textboxes.
For example, if you have two textboxes, one for the name and one for the surname, you can do something like:
Person person = new Person();
person.Name = txtName.Text;
person.Surname = txtSurname.Text;
After that you proceed to store the Person object like you’ve seen in the post.
Thank you for the code example. It explained a lot. I was looking for something I could use across platforms and this is it.
sorry before cause i’am noob in sqlite. Thankyou for your reply before, now i want to read my data from database but there still an error occuring.i use textbox to read data..
this is my code:
private async void LoginUser(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“UserDatabase.sqlite”);
var query = conn.Table().Where(x => x.Username == InUsername.Text);
var result = await query.ToListAsync();}
Hi, the code seems fine to me. Which is the error that is raised?
How to create two tables of type Person in the db ?
db.CreateTable(); // create a table of Person type
//insert some data and bind to some UI in one page
//Now somewhere in the app, I have the different data of type Person class
//how to insert into separate table (like Person2 for ex) ? so that both the tables are not messed up ?
the above code creates a single table. How can I have a different table with the same Person type of columns ?
You can’t, unless you give to the entity that is used to map the table a different name (for example, Person2). In this case you’ll have two tables, Person and Person2, with the same fields.
By the way, I’ll suggest you to think twice about this architecture, having the same table “duplicated” with different names isn’t a good practice.
Thanks for the info. I have 2 ListBoxes each contains different data, but of same type. So, what do you do to store data instead of having two different tables of sametype ? Give me some idea
You can add another column to the table to use as a filter. I’m guessing that you have the same type of data (the Person) but that belong to different categories (like, for example, the gendre). Use that field to filter the data to display the in the two ListBoxes (for example, one with just men and one with just women).
Hello.
How would one update their name, without using the InsertAsync? As in, user inputs name into text field -> text field.text replaces the name in the database.
Thanks!
You have to use the UpdateAsync method, passing as parameter the modified object.
Hi, I got all the things you have mentioned now I want to provide password protection to my database file how to do that?
Actually, I’m not aware about built-in support to encrypt the database. You can use Windows 8 encryption APIs to encrypt the file that is created in the local storage.
Windows 8 Phone APp question
I am having trouble interfacing with the Windows Phone sqlite3 dll.
I have this interface code working on Windows 8 Desktop but when I use it on the Windows Phone it gives me an error.
Of course , there I realize that the Windows Phone has a different DLL than for the Windows Desktop but I am assuming the interface to each is the same.
I am using VB in my application
1. This is how I have the Sqlite3 dll installed and the version I am using.
Sqlite for Windows Phone 3.7.15.1
Installed under
C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.7.15.1\
2. The code I am using to interface to Sqlite3. The code blows up when I execute the Open statement. I am able to detect the sql file is in working storage.
Dim localFolder As Windows.Storage.StorageFolder = Windows.Storage.ApplicationData.Current.LocalFolder
Dim dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, “MyDataBase.sqlite”)
Dim isf As IsolatedStorageFile
isf = IsolatedStorageFile.GetUserStoreForApplication()
If (isf.FileExists(dbPath) = True) Then
SQLOK = Open(dbPath, db)
endif
‘ the interface I am using to the sqlite3 dll
_
Public Function Open(ByVal filename As String, ByRef db As IntPtr) As Integer
End Function
3. I am getting the following errors when I execute the Open for Sqlite3
When I execute I am detecting the sql file at this location. I am assuming that this is the correct location in the working storage – YYYYYY is the name of the file
dbPath = “C:\Data\Users\DefApps\AppData\{6AAA4956-725D-440B-A801-F370CFAD1001}\Local\YYYYYY.sqlite”
An exception of type ‘System.TypeInitializationException’ occurred in XXXXX.DLL but was not handled in user code
System.TypeInitializationException was unhandled by user code
HResult=-2146233036
Message=The type initializer for ‘ XXXXX.UtilityTest’ threw an exception.
Source= XXXXX
TypeName=WIn8Calc.UtilityTest
StackTrace:
at XXXXX.UtilityTest.VB$StateMachine_1_SetConnectionString.MoveNext()
InnerException: System.NotImplementedException
HResult=-2147467263
Message=The method or operation is not implemented.
Source=Windows
StackTrace:
at Windows.Storage.ApplicationData.get_LocalSettings()
at XXXXX.UtilityTest..cctor()
InnerException:
Where XXXX is the name of my app
Hi, did you take a look to my post about using sqlite-net in Windows Phone 8?
http://wp.qmatteoq.com/working-with-sqlite-in-windows-phone-8-a-sqlite-net-version-for-mobile/
Feel free to contact me again if you still have issues.
Hi,Thanks for your post.can you give me example source code for updating sqlite table.
Hi, I am still having trouble with the interface to sqlite3. I have studied all the other references including the GtHub download from Hueme. My code runs fine on Windows 8 desktop , I have numerous apps in the Windows store using this code. I am trying to port one of my apps to the Windows Phone. I am using VB. I have also made reference to the correct Windows Phone Sqlite3.DLL and am using the latest. My interface to sqlite3 is simple , if I can use this method to interface to sqlite3 then all of my apps will port to the Phone. That is why I am trying to fix this.
Below is the code I am successfully executing on the Windows Desktop. I check working storage to see if the data base is already there and if not I copy into working storage. The database has been included in my project as content and copy if newer. Once I determine the data base is in working storage or I copy the data base into working storage then I try to open it.
I have two problems with the Phone work.
1. If I use “Task” on the await on my FileExists function the app blows up – what should I be using?
2. If I convert the Task to a subroutine and try it , it works but then it blows up on when I try to open the data base when I call Open(dbPath, db). I get the error System.TypeInitializationException was unhandled by user code
3. The question is why doesn’t the same code that runs on the desktop windows 8 run on the Phone Windows 8? and is there another way or something wrong with interfacing with a DLL (sqlite3.dll) the way I was doing it on the desktop. One would think that since both platforms are Windows 8 and there has been a port of sqlite3 to the Windows Phone that this code would working without any changes.
4. Another thought I had was maybe the code for determining the data path to the database should be different for the windows phone than the Windows desktop?
Any help or ideas on this matter would be greatly appreciated.
Thanks Jerry
PS – I am sure that there are many other VB users out there that would like to use sqlite3 as their data engine.
Public Sub New()
InitializeComponent()
‘ call checkdb to see if data base is available in working storage , if not then copy it in working storage
Call CheckDB()
‘ set the connection string of the database – open the database
Call SetConnectionString()
End Sub
Private Async Sub CheckDB()
‘ routine that is called to check the data base in working storage
Await FileExists()
End Sub
Public Async Function FileExists() As Task
‘ error my first problem – on the phone it blows up on the Task Statement – how is this converted and why?
Try
‘ this code tests to see if the data base is in working storage
Dim localFolder As Windows.Storage.StorageFolder = Windows.Storage.ApplicationData.Current.LocalFolder
Dim sampleFile As StorageFile = Await localFolder.GetFileAsync(“LeaseNeg.sqlite”)
Return
Catch e1 As Exception
End Try
‘ if the data base is not in local storage then seed the data from local to working storage
Dim seedFile = Await StorageFile.GetFileFromPathAsync(Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, “LeaseNeg.sqlite”))
Await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder, “LeaseNeg.sqlite”, CreationCollisionOption.ReplaceExisting)
End Function
Public Sub SetConnectionString()
Dim localSettings As Windows.Storage.ApplicationDataContainer = Windows.Storage.ApplicationData.Current.LocalSettings
Dim localFolder As Windows.Storage.StorageFolder = Windows.Storage.ApplicationData.Current.LocalFolder
‘ determine the path to the file in working storage
Dim dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, “LeaseNeg.sqlite”)
Dim errmsg As String
‘ error open the file in local storage – it blows up here with initialization
SQLOK = Open(dbPath, db)
If (SQLOK = 0) Then
Return
Else
errmsg = “Error: failed to open statement with message .” + GetErrmsg(db).ToString
SQLOK = SQLOK
End If
End Sub
‘ interface statement to sqlite3
_
Public Function Open(ByVal filename As String, ByRef db As IntPtr) As Integer
End Function
Very Helpful post!
I was wondering if there is a way/attribute to define Primary key as a combination of two columns while defining the class? Thanks in advance.
Personally I’ve never tried, have you tried to apply the primary key attribute to more than one column?
Hello,
Is it possible to set a password to a sqlite-net database in a winrt app so the database is encrypted? How can I do that?
Thanks
This feature is not built in, you have to encrypt the database by yourself using the Windows Runtime APIs.
Hello,
I have a sqlite database with Guid data types, when I look into the sqlite database I see that the Guid data types are correct, but when I use sqlite-net in my project and I read the records from my sqlite database I can’t read the Guid’s. My Guid field looks like garbage. I added SQLITE_SUPPORT_GUID to compilation symbol to support Guid, but I get the following error: System.FormatException: Guid should contain 32 digits with 4 dashes.
Could you help me?
Hi,
i wanted to use JavaScript project in Win98 App with SQLite can you guide me how ?
Hi, sorry but I can’t help a lot, because I’m not a web developer and I’ve never develop a Windows 8 application using WinJS. Take a look at this post by Tim Heuer, it mentions how to work with SQLite using HTML and Javascript: http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx
Hi…
Can any one can give the code for Update query…
Thanks in advance..
Hi, you just have to get the item you want to change from your database and then pass it to the UpdateAsync() method exposed by the connection.
Recipe recipe = await connection.Table().Where(x => x.Id == recipeId).FirstOrDefaultAsync();
recipe.Title = “Updated title”;
connection.UpdateAsync(recipe);
hey
I’m Developing an app for windows 8 store..i’m now stuck up with the database connectivity..trying for the past one week..i followed all ur steps but i ve an error stating that NO SUCH TABLE:PERSON.pls help me to come out of this..Thanks in advance
Hi Nachi, can you send me a sample of your project that can help me to reproduce the error?
Hi qmatteoq,
This is my first step..i just wanted to know the connectivity error..
Hi Nachi, upload the project somewhere and give me the link, the link you’ve pasted in this comment doesn’t work.
Thanks!
Sir where to upload it…
Do you have a SkyDrive or Dropbox account? If yes, upload it and create a sharing link.
https://skydrive.live.com/redir?resid=1EDF15D162209A10!134
Hi, from what I see in your project the CreateDatabase method is defined but never called, so the database is never created. You should call the CreateDatabase method, for example, in the constructor of the page.
thank you for the reply sir..can u send the code for creating and inserting values into the database at runtime for sqlite…
Can you clarify what you mean? The code to create and insert values into the database is available in the post.
Sir i need to get value from the user and reflect in the database.
Just follow the post, specifically the chapter titled “Play with the data”. The sample code shows you how to insert a new value in the database. In my sample the values (like name and surname) are fixed in the code, in your real scenario probably they are retrieved from a TextBox, but the code to store the data in the database is the same.
https://skydrive.live.com/redir?resid=1EDF15D162209A10!146
Here is the edited code..But the inserted values are not reflected in the DB…
Can you please include on Skydrive also all the folders and the other files that are part of the project? Otherwise I’m not able to run it because it complains about missing files (like StandardStyles.xaml)
https://skydrive.live.com/redir?resid=1EDF15D162209A10!146&authkey=!ALo5A4vwekPI_uA
Sorry but the most important folders, like Assets or Properties, are still missing. Please zip the solution’s folder and upload it.
https://skydrive.live.com/redir?resid=1EDF15D162209A10!165
Can you describe me in details which is the problem you’re facing? I’ve tried to insert some data and to retrieve it and everything is working fine, even when the app is closed and opened again.
Send us the app..so that we can map the errors..
I haven’t changed anything in the sample project you sent me, I’ve tested the app you’ve sent me and I see that everything is working fine.
Thank you sir..It was our mistake.we didn’t properly see the database..Thank you so much
hey qmatteoq,
Since i’m new to sqlite,i need some help in all sorts of queries like UPDATE,DELETE,DISPLAY.If possible share me some links that can guide me through.Thank you
The SQLiteConnectionAsync object that is described in the post exposes all the methods you need, like UpdateAsync() and DeleteAsync().
hi sir, i am new to SQLite. how to retrieve the data from the database dynamically.my table has two fields.one is id and one is name. when I click my button it has to display entire table but the reference shouldn’t be made.
Hi, can you explain better your scenario please? I haven’t understood what you’re trying to do.
sir, thanks for the reply.the exact query for retrieving the data from the database.
during run time, how to display the table values without referring to a single attribute as like which was in your post.
Do you mean retrieving a set of data from the table and display it on the screen?
yes sir.
Hi, the process to get the results from the database and display it on the screen has nothing to do with SQLite, since the procedure is independent from the data source. After you’ve retrieved all the data from the table (using the methods explained in the post), you need to include in the XAML a control to display the collection, like GridView or ListView. Once you’ve defined a template for the single item (using the ItemTemplate property), you have to set your collection as ItemsSource of the control in the XAML. I strongly suggest you to read some books or tutorials about how XAML and binding works, because it’s hard to create a complex application without having such knowledge.
one more question sir.
how to validate data with the database. for example I have a login table and login page. if the user enters the values, it must be checked with the database and the further actions must be carried. if the user is not valid then it must show some errors.
The validation has nothing to do with the database, but it’s a generic process. In an authentication scenario probably you’ll have a table to store users, with a field for username and a field for the password. You’ll have to get the user you need to authenticate from the table and check that the values of the username and password fields are the same that, for example, the user has inserted in two TextBox controls that are placed in the page. The process to retrieve users from the table is the same explained in the post.
conn.InsertAsync(person) returns total no. of rows inserted i.e. 1, is there any other method with returns ‘id’ of the inserted row?
Hi, after you’called the InsertAsync method the person object is already updated with the data stored in the database. This means that, if you’ll take a look at the Id property of the Person object, you’ll find the value that SQLite has automatically assigned to it as identifier.
Hi,
How to retrieve a db content and display it in COMBOBOX dropdown list..
Hi, you have to use the standard mechanism exposed by XAML: after you have a collection of items to display (like the one that you can retrieve from the database using the Table object), you have to assign it to the ItemsSource property of your ComboBox control.
This is my design:
This the code behind
private async void Category_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“A”);
await conn.CreateTableAsync();
var query = conn.Table();
var result = await query.ToListAsync();
foreach (var item in result)
{
Category_Name.DataContext=item.H_name;
}
}
But its not retrieve the contents from table B.H_name is the contents i need to display.
Hi, this approach is not correct. I suggest you a good book or a good tutorial about XAML, because you are missing some basic concepts that will make your word really hard if you don’t learn how to use them. A XAML control can have just one DataContext, which is the class that the control will be able to access for binding purposes. So, in your code, you’re basically overwriting the DataContext everytime the foreach statement is executed.
What you need to do is to assign directly the ItemsSource property of the Combobox control to the “result” variable, where you’re storing the content of the table.
the design is
ComboBox HorizontalAlignment=”Right” x:Name=”Category_Name” ItemsSource=”{Binding}” SelectionChanged=”Category_SelectionChanged” Width=”264″ Margin=”0,10,37,600″
I ve assigned the ItemSource to Binding in my xaml code and The ItemSource is assigned to result in c#,though it is not displaying.
ComboBox HorizontalAlignment=”Right” x:Name=”Category_Name” ItemsSource=”{Binding}” SelectionChanged=”Category_SelectionChanged” Width=”264″ Margin=”0,10,37,600″
Code:
private async void Category_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“PickorKick”);
await conn.CreateTableAsync();
var query = conn.Table();
var result = await query.ToListAsync();
Category_Name.ItemsSource = result;
}
But how this will fetch me the exact row of my table
I’m Half done.
Give me the Syntax to retrieve a single row from the table and store it in result.
await conn.CreateTableAsync();
var query = conn.Table();
var result = await query.ToListAsync();
Category_Name.ItemsSource = result;
The syntax to get a single row is displayed in the post: you have to use the Where() operator to filter your data and then apply the FirstOrDefaultAsync() operator to get a single result.
I ve a textblock and need to display contents from the database in a random manner(i.e.,Each time the user should be displayed with a new message from DB)..hw can i acheive that.
You can use the Random class (http://msdn.microsoft.com/en-us/library/system.random.next.aspx) to choose a random element from the collection, passing as parameter the number of rows of the table.
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“ex”);
await conn.CreateTableAsync();
var query = conn.Table();
var res = await query.FirstOrDefaultAsync();
Random r = new Random(res.Id);
text1.Text = r.Next().ToString();
Id is the column name of table Person1.But i’m displayed with some random values.i need that Id values to be displayed in a random manner in the textBlock(text1)
Because you have to use the generated number to get an element from the table, not to display it in the TextBox. You have to do something like this:
await List people = conn.Table .ToListAsync();
Random r = new Random();
int num = r.Next(people.Count);
text1.Text = people[num].Name;
await List ex = conn.Table Person1().ToListAsync();
i vean error(only assignment can be used as a statement) in the above line. ex is my database name and Person1 is the table name.where i’m going wrong.
Hi Nachi,.ToListAsync(), not conn.Table.Person1().ToListAsync().
I’m happy to help you and support you, but please don’t keep posting comments until I’ve replied, I don’t always have the time to answer immediately to questions 🙂
About your code, please read more carefully my posts: as you can see from the samples I’ve published, the syntax is wrong, it should be conn.Table
https://skydrive.live.com/redir?resid=1EDF15D162209A10!166
https://skydrive.live.com/redir?resid=1EDF15D162209A10!167
Here is a Snapshots of my page.How to rectify the error.
thank u for ur kind help..i ve resolved the problem wit the help of ur code…
I’m using telrick Datetime picker..I need to store the selected value in the DB(sqlite)..The value in DateTime Text has to be stored in my Sqlite Db.How to do that.Help me with some code.
SQLite supports the DateTime format, so it’s enough to add a new property to your class which type is DateTime.
thank u.when i use the datetime class both date and time gets stored.i need only the date value to get stored in the db.And i have another query.I need to display user with some notification at their desktop in the specified date.is it possible?
Sqlite doesn’t support just date types, they are always in the datetime format. You need to store the complete datetime and, when you have to display the data on the screen, you need to format it to display just the date (for example, by using a converter). To display notifications you can use push notifications or background tasks, that are able to execute operations in background and send notifications to the user.
I want to extract the first column of my database. I did the program as mentioned above. I want the first column as List type. How to extract the first column alone from the db? Also, how to extract second column alone? Or third column? And convert to List ?
Actually I don’t know about a built in method to do that, you have to write on your own by querying the table to get all the rows and, for every row, to take just the first column’s value. Then, you add all the values to a List.
Thank you Matteo.My application has lot of values to be stored in the database as default.When i use Person person=new Person() and insert my values, it is getting Inserted each time when i call that page.How to avoid this and have a default values when the app is launched.
If you need to do the operation only the first time is launched, you can use a boolean property saved in the application’s settings (the IsolatedStorageSettings class). When the application is launched for the first time you change the property and store the value in the settings. Then, everytime the app starts, you retrieve the value from the settings and, according to it, you load or not the default values.
Do i need to have a paid windows live dashboard account to use push notifications.is there any other possibility???
Hi, the information is not correct. Push notifications are just XML files that are sent over a HTTP Channel to a Uri, that is created when the applications subscribes itself to receive push notifications with the Windows Push Notification Service. You can send notifications with any technology, as long as it’s able to send POST requests using the HTTP protocol. There’s no need of paid subscriptions or accounts. You can find all the needed information here http://msdn.microsoft.com/en-us/library/windows/apps/xaml/Hh868244(v=win.10).aspx
i suppose this is possible with Sqlite too.I have textbox values in an blankpage and i need to display the values in another blankpage.how is this navigation possible.
Hi Senthil,
frankly I didn’t understand completely your question. Navigation and passing data between two pages is independent from the data source, so you can use the same approach you use with every other source: you can use, for example, the navigation service class, that accepts as parameters of the navigation method the page where to redirect the user and an object to carry on.
How to create a table with multiple column (composite) primay key?
e.g
public class CSPartsIdentified
{
[PrimaryKey, MaxLength(15)]
public string PK_CALLID { get; set; }
[PrimaryKey, MaxLength(20)]
public string PK_ITEMNUM { get; set; }
[MaxLength(8)]
public int QUANTITY { get; set; }
[MaxLength(25)]
public string LOCATION { get; set; }
}
Actually there’s a fork of sqlite-net that tries to add support for composite primary keys but, as far as I know, it’s not officially supported yet.
Hi Matteo,
My App is more of an remainder.User will input the date and time of when he has to reminded of the activity.How to popup the remainder to the user.
Hi, take a look at this sample, it should be helpful for your scenario: http://code.msdn.microsoft.com/windowsapps/Schedules-Toast-notificatio-457e599b
protected override void OnNavigatedTo(NavigationEventArgs e){
UpdateList();
}
private void Button_Click_1(object sender, RoutedEventArgs e){
var template = ToastNotificationManager.GetTemplateContent(ToastTemplateType.ToastText01);
DateTime d;
DateTime.Parse( DateTime.TryParse(txt.Text,out d);
ToastNotificationManager.CreateToastNotifier().AddToSchedule(new ScheduledToastNotification(template, new DateTimeOffset(d)));
UpdateList();
}
private void UpdateList()
{
List items = new List();
foreach (var n in ToastNotificationManager.CreateToastNotifier().GetScheduledToastNotifications())
{
items.Add(new Item { DeliveryTime = n.DeliveryTime });
}
}
public class Item{
public DateTimeOffset DeliveryTime { get; set; }
}
I have modified the source code.error is that The parameter is incorrect.though i have specified the correct input format in the textbox(txt).
I see something wrong in this approach:
DateTime.Parse( DateTime.TryParse(txt.Text,out d);You’re doing a double parse, which is not needed. You just have to call DateTime.Parse(txt.Text) or DateTime.TryParse(txt.Text, out d), not both.
Hi matteo,
I need to update the database value when the user closes the aplication…where should i write the code.i.e at which part of app.xaml should i write the code.
There are two event handlers to manage your scenario: in the App.xaml.cs file: Applicaton_Closing, that is executed when the application is closing, and Application_Suspending, that is execute when the application is suspending (the user has pressed the Start button, he tapped on a toast notification, etc).
Great. Works Perfectly fine.
Please provide queries for update and delete also..
Thanks
Nikki Punjabi
Hi, the approach is exactly the same, you just have to use the methods UpdateAsync() (passing as parameter the object to update) and DeleteAsync() (passing as parameter the object to delete), exposed by the SQLiteAsyncConnection object.
Also, how do you specify the name of a column using variables?
hi Alex, foreign keys and direct column access are not supported by sqlite-net. You have to rely on another wrapper recently released that I’ve described here http://wp.qmatteoq.com/a-new-sqlite-wrapper-for-windows-phone-8-and-windows-8-the-basics/
OK, I can’t use this then. I was having issues with the other one but I’ll go back to it.
is it possible to use sqlite as a backend database service for managing the data in front of windows 8 metro app?… means in front there will be a windows 8 app and using internet from smart phone and the app communicates the database server that using sqlite??
Sure, why not? if you use a server side technology, you can choose the one you prefer. The best approach would be to expose your server side data with a service (like a WCF or REST one, for example with WebAPI) and interact with it from your Windows 8 app.
Hi can any one give a code example for binding the data in a gridview after fetching the data from the database using the method described here?
Hi, actually there’s no difference between getting data from the database or from any other data source. Since you’re working with collections of objects, it’s enough that you assign the data retrieved from the database as ItemSource property of the GridView control.
Example:
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
var query = conn.Table().Where(x => x.Name == "Matteo");
var result = await query.ToListAsync();
MyGridView.ItemsSource = result;
}
I have some question.
I just need to put sqlite3.dll in the root of my project and then add sqlite-net wrapper.Is it means that should not add reference to sqlite3.dll?
Hi, this post was written before the SQLite runtime was available as a Visual Studio extension, which is now the best and official approach to use SQLite in your Windows 8 or Windows Phone 8 application. See here for mode details: http://wp.qmatteoq.com/sqlite-for-winrt-is-now-available-on-visual-studio-gallery/
Oh,I got it. Thank you so much.
is there a method to encrypt sqlite database file so that users cannot open it with sqlite browser? like putting a password/
Hi, out of the box no, you have to manually encrypt the file eventually.
Hi qmatteoq, thanks for your blog it really helps.
I have a question, how can i be able to display my data from SQLite database to a datagridview?
thanks in advance
Hi, do you mean the Windows 8 GridView control or the old DataGridView that was part of Windows Forms? Because such a control doesn’t exist for Windows Store apps.
Can you give link to artical or blog which tells us how to encrypt a sqlite file using c# ? or how ican lock app local folder?
You can take a look at this article from the MSDN documentation: http://msdn.microsoft.com/en-us/library/windows/apps/hh464985.aspx
Hi Matteo,
I need to write a code which can retrieve (in a list) all the content of a columns. What i have to write?
For example, let’s take your code snippet:
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);
var query = conn.Table().Where(x => x.Name == “Matteo”);
var result = await query.ToListAsync();
foreach (var item in result)
{
Debug.WriteLine(string.Format(“{0}: {1} {2}”, item.Id, item.Name, item.Surname));
}
}
what i have to write into the variable query for get a list that contains all the value of the columns “fermata1”?
Thanks in advance
Hi, you can use a Select() statement to get only the values you need, like:
await conn.Table().Select(x=>x.Fermata1).ToListAsync();
how to make app package in windows store app with sqlite database .whan i made a package and install it..it create database with 0kb size with no tables and data
Hi,
did you follow all the exact steps in the post? I’ve published many Windows Store apps which use SQLite and I didn’t have any issue.
yes i am following.i copy database in to local folder which is run perfectly in visual studio but when i make a app package it is not working
What would be the best way to implement Sql Lite on a Metro app using Sql lite if we do now know what the database structure will be. So on the app depending on who logs in we build the database as needed from data coming in for that user. Would building a class for each table is not so easy. Would we have to look at some generic class structure to try and re-use only using the fields we need? We are new to Metro apps so any help will be appreciated.
In this case probably I would go with the SQLite Wrapper (https://sqlitepcl.codeplex.com) which can be used to perform manual queries. It should be easy, with this approach, to work with a data set that it’s not fixed but dynamic.
Hey Matteo
I have problem with SqlException, Please help me, because i need this database example.
Code from MainPage.xaml.cs :
public class Person
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(30)]
public string Name { get; set; }
[MaxLength(30)]
public string Surname { get; set; }
}
private async void CreateDatabase()
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);
await conn.CreateTableAsync();
}
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);
await conn.RunInTransactionAsync(async (connection) =>
{
Person person = new Person
{
Name = “Matteo”,
Surname = “Pagani”
};
await connection.InsertAsync(person);
});
}
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people”);
var query = conn.Table().Where(x => x.Name == “Matteo”);
var result = await query.ToListAsync();
foreach (var item in result)
{
Debug.WriteLine(string.Format(“{0}: {1} {2}”, item.Id, item.Name, item.Surname));
}
}
When I click on Button i have this exception :
An exception of type ‘SQLite.SQLiteException’ occurred in DataBoundApp1.DLL but was not handled in user code
Are you able to provide me a sample project to reproduce the issue? I can’t say what’s wrong by your code.
Pingback: Universal App with SQLite – Part 2 | A developer's blog
sir..
how to use collate no case in linq manner while creating sqlite table…
As far as I know, it’s not supported by sqlite-net. You have to use the manual wrapper, which allows you to perform SQL queries.
Hi Matteo,
Thanks.Your post is helpful to me.As in your attachment,you are using file named people.db.As well as its available in the project.How is the file created.I also created database by writing the following code:
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(“people.db”);
await conn.CreateTableAsync();
I am also able to get the inserted data.But no such file people.db created can be seen in my project.Even I am able to run your project after excluding the file from the project.Is this file necessary? If yes how can I open it.Like open with..
Thanks in advance
Waiting for reply
Try to set the path in the following way:
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, “people.db”), true);
I am using sqlite in windows store app,but it is not allowing me to pass windows certifaction kit test,it fails package compliance test,says have to declare c++ reference n manifiest file,how i can do that?
Try to add to the references of your project the library “Microsoft Visual C++ Runtime Package”.
How to get data like “Matteo” and show it in a text block ?
Just retrieve the data as shown in the example (using the Table object and the Where() method) and then assign the result to the Text property of the TextBlock control.
is there any way to include already existing database in my project so that when I install app it uses that database to retrieve information. for example I have dictionary database and want to use it. In my case it is not practical to create database in app code because it is quite large and I already have it. For now I manually copied database to app data folder and it works, but I want for my app to use that database when app is installed on other system from store.
Hi, yes it’s possible, I’ve explained it in another blog post: http://wp.qmatteoq.com/import-an-already-existing-sqlite-database-in-a-windows-8-application/
Can not create the SQLiteAsyncConnection object. The error showed is The type or namespace SQLiteAsyncConnection does not exists …. are you missing some….
Help me please
Hi, from the error it seems that you didn’t correctly installed the library in your application. Did you use NuGet?
Hello,
I am having an app in WP store whose one of the table’s schema needs to be updated now. Can you please let me know how to do that?
Have already checked, http://www.monkeyslaps.com/sqlite-on-wp-db-schema-management/
where the option available is to delete and recreate the db altogether but i wouldn’t like to implement it.
Another option available is, using “Local database”, like
http://www.geekchamp.com/articles/windows-phone-local-database-schema-upgrade-part1—adding-new-columns
but again, as i had already included SQLLite8 in my project, migrating to this approach will be troublesome.
Is there any better option you can suggest?
Thanks in advance! Indeed, your article is very helpful.
Hello, let me see if I’ve correctly understood your question: do you need to update the database schema when you release an update of your application, is it correct?