On several projects I have used different relational databases like Oracle, Sql Server, MySql even Sybase -what an horrible experience-. I have never used SQLite or LocalDb _ -this one replace Sql Compact-_.
For the universal app I am developing, one of the constraints is that the application should work offline. I have choices between working with text files and using a lightweight databasethat I can embed into my solution. I decided to go with the SQLite and to discover this little beast, I created a dummy console application.
Packages I installed
I installed following packages via Nuget
System.Data.SQLite Core (X86/x64) 1.0.94
System.Data.SQLite Core (X86/x64) 220.127.116.11
All the following analysis is based on this version. The API can change in the future versions.
How to create a database?
There isn’t any database server -an external software to install-. The database is only a file that you can create via an external tool or using the A.P.I I indicated above in Packages section. Depending on the way you create, the location of this file will be different. We’ll have more details in the next section. While trying to learn how to use SQLite, I found 3 different ways to create a database
1 . Create a database using an external tool
The tools that got my attention on a simple google search are the followings:
- SQLite manager (firefox plugin can be quite handy)
- SQLite browser
- SQL Server Compact & SQLite Toolbox
The third is also a Visual Studio extension. I privileged this one because it is a compact tool that I can use within my development environment. It can be easily installed :
Tools ⇒ Extension and Updates ⇒ Search for SQL Server Compact & SQLite Toolbox
Once this extension is installed right click on Data Connections and chose Add SqLite Connection. The menu will guide you for the rest.
2 . Create a database using SQLiteConnection.CreateFile method
To insist on the fact that the database is really nothing but a file -as the name of the method suggests, no server behind, you have to get used to it-, here is how above method is implemented:
3. Create a database by opening a SQLite connection
This can be the most disturbing part. Using SQLite, you can create a database by creating a connection and opening that connection. This is something that we can probably not imagine on real database systems we have worked on.
Previously, I insisted on the fact that the SQLite database is in fact just a file. When you get away from the idea of the database and you think it as a file, creating a file when you are reading it/writing to it isn’t disturbing anymore is it? If we recall, in .Net we can create a file while writing to it, if it doesn’t exist. Do you see the similarity?
Where is the database?
If you create your SQLite database using a tool (point 1 above), the database will be created in whatever folder you select. If you create your SQLite database using the API however, there are a few subtle points. Let’s create our database:
When the statement above is executed a file named
TestDb.s3db is created
in the execution folder. Example Imagine you executed the program in
debug mode. You’ll find
It is also valid to determine a complete path instead of only database name. This way you
can control the location in which SQLite database will be created. The following code
snippet will create SQLite databases on
D:\ and not on the execution folder.
Have you noticed
@ symbol which makes connection string a verbatim string literal?
What happens if you remove it? Would the code still compile? :) Depending on the target
environment -windows 8 app, windows phone 8.1 app, wpf client app etc. - you might want
to have different locations. I’ll dig this point later on another article.
I really want to jump to the part where we do some concrete operation by inserting,
updating data and making select queries to see the result set. But there is another
important point that deserves a deeper understanding: SQLite connection string.
The samples you see on the web contain generally same parameters. I already used two
Data Source and
Version in the code snippets above. There are much more
and in the next post, I’ll try to push the analysis by decompiling library code to get
a complete map of all the parameters.