Beginner's guide to SQLite for Razor Pages and Linux

This guide shows one how to set up SQLite3 for the first time for use with .Net applications under Linux.

-Raising Awesome 2022

Introduction

When making data persistent between user sessions, it's not unlikely that you have been simply writing data to a file.  You may even put some delimiters between data fields that you split to an array later.  Soon or later, though, you will out grow this.  You will want a database or you will find yourself writing one from scratch.

You don't have to be stuck with a giganto SQL or Oracle solution, though.  SQLite may be the perfect choice.  It is a perfect choice for lower traffic non-simultaneous transactions like you'd want to support a small web page or for Maker projects.

This guide will show you how to set up your first SQLite database in the Linux environment and talk to it with C# .Net.

SQLite Installation

  1. First, see if you already have SQLite3.  Type this in a terminal:
    sqlite3
    If you don't have it, it will show you an error.  If you do have it, skip to step 3.

  2. Install it by typing the following at the command prompt:
    sudo apt update
    sudo apt install sqlite3
  3. Install the .Net package.  In the terminal, go to the root folder of your .Net package.  Type the following:

    sudo dotnet add package System.Data.SQLite.Core

Create a SQLite3 Database Table

First, you need to create your database.  At a terminal in the folder where you want to store the database file, type the following:

sqlite3 mydatabase.db

To create the table to hold your data, you need to sketch that out in your head.  I then go to a text editor to write the script that will create the table in SQLite3.  Here is an example of a simple table:

CREATE TABLE mytable (
     ID text PRIMARY KEY not null,
     name text not null
);

If you want to add another field after this, here is an example on how to do so to add a date field:

alter table blogs add column dateWritten date;

In this example, I have a date data type.  This is just an illusion as SQLite will convert it to text.

Read Data in the SQLite3 Database with C#

To talk to the database, you will need to be sure that your permissions are set on the file correctly.  If you run into any errors, check that first.  Here is example code to open up the database and insert a record:

using System.Data.SQLite;
...
public void ReadData(string name)
{                 
            string myID=name;
            string myTitle="";
            
            string cs = @"URI=file:/filepath/databasefile";
            SQLiteConnection sqlite_conn = new SQLiteConnection(cs);
            sqlite_conn.Open();
            
            SQLiteDataReader sqlite_datareader;
            SQLiteCommand sqlite_cmd;
            sqlite_cmd = sqlite_conn.CreateCommand();
            sqlite_cmd.CommandText = "SELECT ID,char_Title FROM blogs where ID='"+ myID +"'";
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            if (sqlite_datareader.Read())
            {
                myID = sqlite_datareader.GetString(0);
                myTitle=sqlite_datareader.GetString(1);      
            }
            sqlite_datareader.Close();
            sqlite_conn.Close();
}

Insert, Update, and Delete

SQLite3 will also do Insert, Update, and Delete pretty much like Microsoft SQL would using the ExecuteNonQuery method instead.

Here is a snippet that shows how to add form data into the database:

                ...
                sqlite_conn = new SQLiteConnection(cs);
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText="INSERT INTO mytable (ID, char_title, dateWritten) VALUES (@ID,@title,@date)";
                sqlite_cmd.Parameters.AddWithValue("@ID", Request.Form["myID"].ToString());
                sqlite_cmd.Parameters.AddWithValue("@title", Request.Form["myTitle"].ToString()+" ");
                sqlite_cmd.Parameters.AddWithValue("@date", DateTime.Now);
                _ = sqlite_cmd.ExecuteNonQuery();
                ...
            
Have a question?
Back to Home