Introduction
SQLite is a serverless SQL database engine.
It can be easily embedded into any software application, without the need to run any other database services on the host machine. The entire database engine and data are included in a single file, which is platform-independent.
Therefore depending on the project type, and data requirements, it could be a brilliant idea to use SQLite instead of another RDBMS system. Mozilla, Apple, Adobe and others are efficiently using SQLite.
This tutorial is showing how to connect and retrieve SQLite data from a .NET application.
Creating the tutorial’s database
We are going to create a new database, using one of the available SQLite management tools.
I am using wxSQLite+, which is pretty simple and doesn’t require any special setup.
However feel free to browse and maybe select an alternative one.
So, if running wxSQLite+ for the first time, from the Transaction menu, uncheck “Auto start transaction”.
We are going to create a new SQLite database, called contacts.db.
Simply click on File -> Open/Create and save contacts.db on your preferred location.
Creating a table and adding sample data
Right click on Tables and select Create Table.
We are going to create a table named “friends” and add three columns (ID, name, phone). The ID will be the primary key of the table, having the autoincrement option ON.
If you are familiar with the SQLite SQL syntax, you can always use a “create table” command, instead of using the gui to create the table.
Now let’s add a couple of sample rows via SQL.
INSERT INTO friends VALUES (null, 'Mary', '0900200200'); INSERT INTO friends VALUES (null, 'John', '0800100100');
Do a “SELECT” query at the end to verify that the sample data have been successfully stored.
Getting the SQLite ADO.NET provider
SQLite can be used with any modern programming language, however an appropriate SQLite driver/wrapper is required to handle the database connection.
For .NET, I would recommend the SQLite ADO.NET 2.0/3.5 provider, which you can download here for free.
Creating a new C# Console Application
For the purpose of this tutorial, let’s create a new C# Console Application.
From the SQLite ADO.NET archive which you have downloaded, we only need the System.Data.SQLite.DLL file (\bin). So let’s just extract the dll and add a reference to it on our project.
The SQLite ADO.NET provider’s syntax is very similar to the corresponding Microsoft SQL Server ADO.NET provider’s syntax.
The connection string must contain the full path of the SQLite database file (contacts.db).
Here is the complete source code which is required to select data from the “friends” table and output the result on the Console:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SQLite; namespace SQLiteTestCS { class Program { static void Main(string[] args) { String connString = "Data Source=E:\\contacts.db"; using (SQLiteConnection conn = new SQLiteConnection(connString)) { StringBuilder query = new StringBuilder(); query.Append("SELECT * "); query.Append("FROM friends "); query.Append("ORDER BY name"); using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), conn)) { conn.Open(); using (SQLiteDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { Console.WriteLine(dr.GetValue(0) + " " + dr.GetValue(1) + " " + dr.GetValue(2)); } } } } Console.ReadLine(); } } }
And the result should look like this:
Conclusion
As it was demonstrated on the tutorial, it is quite easy to setup an SQLite database, and use it as a data source on any .NET project.
Can you please help me, in doing the same with INSERT and other SQL commands.
My attempts at inserting, yields a database not found error.
Help appreciated
Soham
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
Below is the simple SQLite Helper that I have created. Hope it will be usefull in your projects
namespace DataAccessLayer
{
public class SqLiteDatabaseHelper
{
private static string connString;
public SqLiteDatabaseHelper(string connectionString)
{
connString = connectionString;
}
public DataTable GetDataTable(string sql)
{
var dt = new DataTable();
try
{
using (var conn = new SQLiteConnection(ConnectionStringHelper.ConnectionString))
{
conn.Open();
SQLiteCommand mycommand = new SQLiteCommand(conn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
public static DataSet GetDataSet(string sql)
{
DataSet ds = new DataSet();
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
{
da.Fill(ds);
return ds;
}
}
}
}
public static int ExecuteNonQuery(string sql)
{
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
int rowsUpdated = cmd.ExecuteNonQuery();
return rowsUpdated;
}
}
}
public static string ExecuteScalar(string sql)
{
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
object value = cmd.ExecuteScalar();
if (value != null)
{
return value.ToString();
}
else
{
return “”;
}
}
}
}
}
}
Hello Soham,
Check the connString. It should be the location of the databse file. Ex: (String connString = “Data Source=E:\\contacts.db”; ) or E:\\contacts.CACHE
Thank you Ranjan, very useful to me.
Soham
This may help, here is Linq to SQLite.
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx
[…] manager and another management tool called wxSQLitePlus found here based on this tutorials found here (just being explicit) Hoping i’m not posting any stupid stuff that will be ignored, here are […]