Day 8.2 – Dot NET On Linux – Connecting & Consuming SQLite with ADO.NET

Intro

SQLite is as light weight and simple yet remarkably powerful relational database. It requires no installation other than the existence of the executable and requires no running services SQLite does not support concurrent databases writes and in fact locks the whole database from other write transactions while writing. It also does not support network access;  it requires the executable, database file and implementing software to exist on the same machine.

SQLite has a good website for appropriate use here. Though this might not be suitable for a live website requiring concurrent usage across a network it still has several usages including strong integration within the Java based Android framework and is strongly promoted for storing application and user settings.

Mono has a SQLite ADO.NET data provider installed as default.

This post will run through installing, database and scheme creation and then connection and consumption through code.

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here

Installation

We are going to install sqllite3, the documentation and sqlitebrowserl  a simple yet convenient tool for managing and using SQLite databases. Version 3 is in the LMDE repositories along with sqlitebrowser and can be installed with the following command.

sudo apt-get install sqlite3 sqlite3-doc sqlitebrowser

Create a new Database

Open up sqllitebrowser via the launch icon which should be sitting inside the Programming menu. From the File menu select File –> New Database. You will be provided with a file dialog box. Find a suitable location to create the database file and select OK.

Create our Sample Table

To create a sample table go to the Edit menu and select; Edit File –> Create Table

Provide a sample name and then hit Add. To add a new field provide a name and a type from one of; TEXT, NUMERIC, BLOB and INTEGER PRIMARY KEY.

The screen shots below should be enough information to create the same SampleTable which mimics the same table we created in MySQL previously.

We could go into MySQL and generate a SQL script to create our SampleTable but this won’t actually work. I talk about this more in the next blog when I show how we can write code to interface with both MySQL and Sqlite with virtually no differences other than the connection string and a few tweaks.

SQLite DataTypes

You can read about the SQLite data types  here to help understand why they are very limited. In short the data type matches what you store and not what you define. This caused a few problems for me as you can imagine when I initially swapped out MySQL for SQLite in the previous code we wrote. This also explains why I have picked text to store a DateTime type.Connection.

The SQLite data provider comes installed and registered with mono. We don’t need to touch the machine.config to register the data provider like we did with MySQL. We still want to register the connection string in the machine.config. Search for and add in the following section:

<pre><connectionStrings>
    <add name="DotNetOnLinuxSQLLite" connectionString="Data Source=/PersonalData/DevProjects/SandBox/Git/DOT-NET-on-Linux/Day8ADOExample/ADOExample/SqliteDB/SampleSqlite.db;Version=3;" />
	</connectionStrings>

As you can see the connection string is pretty simple requiring just the location of the file and the version of SQLite.

The connection string can be easily accessed in code by:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinuxSQLLite"].ConnectionString;

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows the code behave agnostic to the database; see my next post where I talk about how to implement this.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download ( see at the top) where I have tried to implement a simple DAL layer with basic ORM capability.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s