Home > DeveloperSection > Blogs > Creating, Inserting, Updating and Deleting in SQLite

Creating, Inserting, Updating and Deleting in SQLite


Database Database 
Ratings:
1 Comment(s)
 12825  View(s)
Rate this:

Creating, Inserting, Updating and Deleting in SQLite

 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


good tutorial

By web tester on   2 years ago
good tutorial

Don't want to miss updates? Please click the below button!

Follow MindStick