Using SQLite with c#.net

This tutorial covers how to use Sqlite database using c#.

What is SQLITE?
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

Create a console application:
usingsqlite1

Add reference to Sqlite:

System.Data.SQLite.Core nuget package contains the basic libraries needed to connect and retrieve data from SQlite. If you want to use Entity Framework and LINQ with SQLite then you can install System.Data.SQLite.EF6 and System.Data.SQLite.Linq respectively. For the scope of this tutorial we will only add System.Data.SQLite.Core  package.

usingsqlite2

 

Creating SQLite database using c#:

SQLiteConnection.CreateFile("sqliteTest.sqlite");

Now that we have our database, let’s connect to it. To do this we will need a connection string. We will use below connection string, you can find more samples at: https://www.connectionstrings.com/sqlite/

“Data Source = sqliteTest.sqlite; Version = 3;”

Connect to database:

var connString = "Data Source = sqliteTest.sqlite; Version = 3;";
var conn = new SQLiteConnection(connString);
conn.Open();

Create a new table:

Let’s create a new table called “Employee” with columns: Id,Name,Salary. We will keep Id as auto incremented primary key.

var creatTableSql = "CREATE TABLE 'Employee' ('Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , 'Name' VARCHAR, 'Salary' DOUBLE)";
var commnad = new SQLiteCommand(creatTableSql, conn);
commnad.ExecuteNonQuery();

Insert some data to table:

Now let us add some record to this table.

var insertSql = "Insert into Employee (Name,Salary) values ('Emp1',10000);";
var commnad = new SQLiteCommand(insertSql, conn);
commnad.ExecuteNonQuery();

insertSql = "Insert into Employee (Name,Salary) values ('Emp2',20000);";
commnad = new SQLiteCommand(insertSql, conn);
commnad.ExecuteNonQuery();

ExecuteNonQuery can run Insert,Update,Delete commands.

Returning data in DataSet:

In most cases you will want to get a subset of data and perform some operations on it. You can achieve this by using SQLiteDataAdapter and DataSet. Let’s get all the records in Employee table where salary is greater than 1000.

var selectQuery = "Select * from Employee where Salary > 1000;";
var da = new SQLiteDataAdapter(selectQuery,conn);
var dataSet =new DataSet();
da.Fill(dataSet);

You can access the table data via dataSet.Tables property.
Eg: dataSet.Tables[0]

usingsqlite3

That covers the basics. This tutorial was made with Visual Studio 2015.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami