Reading and Writing Data Into SQLite Database Using C# (CSharp)

This is the second part of our SQLite C# Tutorial Series. In the first part we learned to connect to a sqlite database and create tables but we didn't add any data into the database.

Here We will learn

1. How to Insert data into a SQLite database table using C# language
2. How to Read back the data from the SQlite database using C#

This will teach the users how to perform basic database CRUD operation on SQLite using C# .This code can be modified to create custom data acquisition and logging applications on PC or Single Board Computers like Raspberry Pi.

First Part of the Instructable where we teach you how to connect to the SQLite database using C# can be found here

Original tutorial on Reading and writing data into SQlite database using C# can be found here

1. Supplies are similar to the Previous one
2. You will need Visual Studio and DB Browser for SQLite

Source Codes + Sample Database

1. Source codes for reading and writing into SQLite Database Using C# can be downloaded from GitHub Repo

Before Reading and writing data into SQLite DB , you'll need to use the System.Data.SQLite package, which is a .NET provider for SQLite So that we can use the various classes needed for inserting data into the SQlite database.

We have explained how to install System.Data.SQLite package for your project in the previous Instructable .Please refer that.

Now you can import the required name spaces

In the previous Instructable, we learned to create a table inside the SQLite database programmatically. If you have opened the newly created database using DB browser software and look under Browse Data Tab ,you will find the columns empty.

Please note that the name of our Table is Customers.

This is because we have not added any data to sqlite DB.

Here we will learn how to insert data into the SQLite Database fields using C# language.

First thing we should do is to construct a SQL query to insert the data into our database for that purpose we will use the SQL INSERT command.

Traditionally if we are directly adding data to Sqlite db using SQL command we will issue some thing similar to the one shown below.

Here the parameters inside the VALUES will be upended to the corresponding column's inside the customers table. This method is susceptible to SQL injection attacks and is not recommended

SQL Injection is a type of security vulnerability that occurs when an attacker is able to manipulate a SQL query by injecting malicious SQL code into a web application's input fields, URL parameters, or cookies.

The most effective way to prevent SQL injection attacks is to ensure that user input is properly validated, sanitized, and parameterized.By using prepared statements with parameterized queries, user input is treated as data, not executable code and is the most effective method for preventing SQL injection

So here we will be using parameterized queries to send data to sqlite db to write. Here is an example of the above query in Parametrized form

Here the original values like Jenny Darlene', 50, '1970-03-08' etc are replaced by parameters starting with an @ sign.

For eg VALUES (@NameParam,@AgeParam,@DateOfBirth,@Email,@Price)

Then we will add the values that we want to insert into the sqlite database using .Parameters.AddWithValue() method which is part of the SQLiteCommand class.

This method will replace the parameter (Eg @Name) with the corresponding value (Eg "Jenny Darlene").

Basic steps involved in writing data into the SQLite Database is shown below.

1. First Create a SqliteConnection object called Connection
2. Open a connection to the database, MyConnection.Open();
3. Now create a SQLiteCommand object to insert the data using SQLiteCommand(SQLQueryInsertData, MyConnection)
4. Now we will use InsertDataCommand.Parameters.AddWithValue() method to insert data into the table
5. then we will .ExecuteNonQuery() method to write our data into the SQLite table
6. Now close the connection.

Now i will show code snippets that illustrate the above steps of inserting data into sqlite using C#.

We will avoid some of the already covered parts like database connection and table creation.

First we add our value name,age etc using the Parameters.AddWithValue() method . Here

@NameParam is replaced with the name that we are giving So "Johnny Austin Dave".For text values you need to use the quotes.

For integer and float values no quotes needed as shown below.

Once all the data is added we call the InsertDataCommand.ExecuteNonQuery(); to write data into the SQLite database.

Before running the code

After you have run the code, You can see that one row has been upended to the sqlite database as shown here.

Here we will learn how to read the values stored inside each row of the SQLite database and display them on the console using C# .

Before running this code we need to make sure that your database contains some data. We have a sample databse in your repo.

To read the data from an SQLite database we have to use SQLiteDataReader class provided by the System.Data.SQLite namespace. It enables efficient reading of data from an SQLite database using a forward-only cursor, meaning you can only traverse the data in one direction, from the first record to the last.

Our SQlite database is going to look like this

Please note that the SQL query we are executing is the following

The below C# code shows how to read data from the rows of SQLite database.

we create a SQLiteDataReader object called MyDataReader which will receive the data from the MyCommand.ExecuteReader().

The ExecuteReader() method is a function of the SQLiteCommand class in the ADO.NET SQLite library, commonly used for interacting with SQLite databases in .NET applications. This method executes an SQL query (typically a SELECT query) against the SQLite database and returns an instance of SQLiteDataReader, which allows the developer to read the resulting data from the query, one row at a time.The method is specifically designed for queries that return multiple rows of data. It is used when you expect to retrieve more than one record from the database.

This line begins a loop that continues to iterate through the rows returned by the query. The Read() method of the DataReader advances the reader to the next row, returning true if there is another row available, and false if there are no more rows left to read

Here we are using the MyDataReader["Column Name"] format to access the values. Here we have to convert the retrieved values to appropriate datatypes before using as shown below.

This reads the value from the Id column, which is of type int in the database. The Convert.ToInt32() method is used to ensure the value is properly converted from its original type to an integer.

the output of the program can be seen below.

In this way all the values present in the sqlite database are read and displayed onto the Console using CSharp (C#).

All Source codes available on Github