In this blog, I’m explaining how to create and use the sqlite database in our application.

Introduction

SQLite is a relational database management system which compliant ACID properties and implements most of the SQL standard using a dynamically and weakly typed SQL syntax. SQLite is a popular choice as embedded database for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, embedded systems etc.

Installation

For installation of sqlite database in your application:-

Go to solution explorer, right click on it, select ‘Manage Nuget Package’ – a window will open and SQLITE on search textbox and select ‘System.Data.SQLte’ – it will install the sqlite database in your application as shown in the figure.

Creation of Database File

Once the SQLite database is installed in your application, now you can create the database file using the following statement:-

SQLiteConnection.CreateFile(@"D:\Sumit\Posting work\Blog\mySQLiteDatabase"
); //Create a SQLITE database file

This statement will create a sqlite database file named nySQliteDatabse.

Create a table in database

SQLiteConnection dbConnection = new SQLiteConnection("Data Source="+path+";Version=3;"); //Create the connection with database
string myQuery = "create table student (Id int, Name varchar(50), Address varchar(50))"; //SQL query to create a table with three columns
SQLiteCommand command = new SQLiteCommand(myQuery, dbConnection);//Create a SQLite command which accepts the query and database connection
dbConnection.Open();//Open the connection with database
command.ExecuteNonQuery();//Executes the SQL query
dbConnection.Close();//Close the connection with database

The few above statements will create a table in the database named ‘student’ with three columns.

Insert

string myQuery = "insert into student (Id, Name, Address) values(001,'James Rowling', 'Manhattan')"; //SQL query to insert data
SQLiteCommand command = new SQLiteCommand(myQuery, dbConnection);//Create a SQLite command which accepts the query and database connection.
dbConnection.Open();//Open the connection with database
command.ExecuteNonQuery();//Executes the SQL query
dbConnection.Close();//Close the connection with database

Retrieving the data

string myQuery = "select * from student"; //SQL query to retrieve data from table
SQLiteCommand command = new SQLiteCommand(myQuery, dbConnection);//Create a SQLite command which accepts the query and database connection.
dbConnection.Open();//Open the connection with database
SQLiteDataReader dataReader = command.ExecuteReader();//Executes the SQL query
dbConnection.Close();//Close the connection with database

Update

string myQuery = "update student set Name = 'Rose Salvatore' where Id = 002"; //SQL query to update data from table
         
SQLiteCommand command = new SQLiteCommand(myQuery, dbConnection);//Create a SQLite command which accepts the query and database connection.
dbConnection.Open();//Open the connection with database
command.ExecuteReader();//Executes the SQL query
dbConnection.Close();//Close the connection with database

Delete

string myQuery = "delete from student where Id = 002"; //SQL query to delete data from table
SQLiteCommand command = new SQLiteCommand(myQuery, dbConnection);//Create a SQLite command which accepts the query and database connection.
dbConnection.Open();//Open the connection with database
command.ExecuteReader();//Executes the SQL query
dbConnection.Close();//Close the connection with database