SQlite connection in C#.Net

Sqlite and C#.Net

What is SQLite ? Sqlite is a minimal SQL database popular on mobile computing. Is it worth in C#.Net ? Yes its.

SQLite can perform all SQL operations, query, insert,update and son on.

SQLite can be used as a local database for storing data temporarily on local machine on C#.Net applications.

We can also use it in memory only database where the data store will be removed after the program execution stopped.

Setup

Before using the SQLite we need to add following dependencies to our C#.Net project, using the Nugget Package Manager console.

Install-Package Microsoft.Data.Sqlite.Core
Install-Package SQLitePCLRaw.core
Install-Package SQLitePCLRaw.provider.sqlite3

Import the dependencies and configure the SQLite connection.

 string  sqllitCstr= "Data source=backend.db; ";
 SqliteConnection con = new SqliteConnection(sqllitCstr);
                SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());

This will create a SQLite database in bin/debug directory. Now we are ready to create a SQL table for storing user information.

string cmdString= "CREATE TABLE IF NOT EXISTS USERS ( ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME text );"; 
con.Execute()

The execution will result in creation of the users table, also check for the existence.

Dapper

I also used a micro ORM Dapper for querying and insert data into the table. Lets add the dapper package

Install-Package Dapper

Dapper require a model class representing the User class. Every table and class should have an identity column, Id( which is auto incremented).

internal class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        }

Data with Dapper

For inserting data we can create an object of class User and add it to the database using the Dapper Insert method.

using Dapper;
using Dapper.Contrib.Extensions;
...
...
 con.Insert<User>(new User() { Name = "scott" });
                    

Similarly querying the users will result in a list of User Objects.

using Dapper;
using Dapper.Contrib.Extensions;
...
var r = con.Query<User>("select * from users").ToList();