Relational Database

In C# language, connection to relational database is quite straight forward. You add the library, then add the code, and you have access. What is there to talk about?

Well, besides coding, the purposes of this article is to

  • Show you how to install SQLite as database provider. You can pick other database servers to use. I choose SQLite because it’s light weight. And the fact that SQLite requires no server makes it popular for small devices like SBC.
  • Show you how to use LibreOffice Base as database management tool to manage database objects. LibreOffice is available on both Raspbian and Ubuntu Mate. LibreOffice Base provides GUI to manage database tables. Feel free to use other tools of your choice.

    In order to connect LibreOffice Base with SQLite, you can use either ODBC or JDBC. In this article I will show you how to use ODBC to connect LibreOffice Base with SQLite.

    Alternatively, you can use database management software to help you. Tools such as DbVisualizer or DBeaver can be useful. Both have free and paid versions.

SQLite

For those of you who are unfamiliar with SQLite, please pay attention to the following special features found in SQLite.

  • Unless otherwise specified, every table in SQLite has a built-in primary key named RowId. This is for compatibility with older version of SQLite. If you do not want to use RowId, you need to define your table as without rowid, literally.
  • Columns in SQLite tables are type-less. You can almost put any type of values to any column. The column definition will sometimes affect conversion of the data inserted into table. You will need to have some understandings of how different types of data is stored and retrieved from the database. Beware, it is your responsibility to decide which type of data to retrieve when reading data from the database.

Before you begin, make sure you have enough understanding of these features.

Begin SQLite

To install SQLite, use command sudo apt install sqlite3.

For now, let’s create an empty database. We will create tables in the next section with LibreOffice Base. For demonstration, I will place the databases in folder /home/pi/db. I will then create a database named JobLog.db.

cd /home/pi/db
sqlite3 JobLog.db ".database"

The second command creates an empty database.

LibreOffice Base

I find it very useful to have some kind of database manager with GUI. If you have something else in mind, or you prefer to use command line. Please skip this sections and jump to section C# Program.

Since both Raspbian and Ubuntu Mate have LibreOffice installed by default, I will make use of LibreOffice Base to manage the SQLite databases.

ODBC

ODBC is only used to connect the databases with programs. You may use JDBC, if you are more familiar with it.

To install ODBC for SQLite, use commands:

sudo apt install unixodbc-bin
sudo apt install libsqliteodbc

ODBC uses DSN (Data Source Name) to identify a database. To create DSN, use command sudo ODBCCreateDataSourceQ4. To maintain (change) DSN, use command sudo ODBCManageDataSourcesQ4.

Select System type DSN which allows everyone to use this DSN.

For Driver, select SQLite3.

At Create Data Source Name Wizard screen, enter JobLog (or any name of your choice) for Name. The Name is the ODBC DSN. Enter full path of the database at Database field.

Finish the program prompts. The DSN is ready for connection.

Database Management with LibreOffice Base

To launch LibreOffice Base, open menu item Office | LibreOffice Base on Applications menu.

Select Connect to an existing database, then select ODBC. As you can see from the pull-down options, there are other types of database that you can connect to. Click on Next button to continue.

Here you enter the ODBC DSN that was created in the previous section. Click on Browse button to select from available ODBC DSN.

This database is not secured. So, skip the User name.

At this point, click on Test Connection to test the database connection. If it fails, verify the DSN and path to the database.

Register the database connection, so next time you won’t need to go through all these steps again.

The Save screen is where you save the LibreOffice Base database. This is the LibreOffice Base database, not the SQLite database. LibreOffice Base needs to save the information you’ve worked on to its own database. In this case, the LibreOffice Base database contains registered connection to the SQLite database. It will also contain objects (e.g. Reports) that you create later.

You can save it anywhere with any name. I’ll put it in the same location as SQLite database. The default file extension is “.odb”. After the database is saved, you should be able to find a file named JobLog.odb in the db folder.

Next, create tables/views for your SQLite database. I will not be going into too much details here. Some tips to help you manage the database objects.

  • SQLite database and LibreOffice Base database are two different files in the file system. SQLite database is where you put tables and views which will be accessed from C# program. LibreOffice Base database contains link to manage your SQLite database in GUI mode. LibreOffice Base also contains Reports and Forms.
  • In LibreOffice Base, you cannot enter values into tables without primary key. The default key “rowid” doesn’t count. If you want to enter values with LibreOffice Base, you need to specifically define a primary key, even with the same name as “rowid”.
  • SQLite has built in key RowId, which is hidden by default. You can add the column RowId and make it primary key. When insert records with RowId column, you can leave it blank and SQLite will increment the values automatically.
  • Tables and views are saved to SQLite database. Other objects (e.g. Query, Report, Form) are saved in LibreOffice Base database. (View and Query are not the same type of object.)
  • Table layouts can not be changed with Edit screen. Use SQL statement to alter table schema.
  • Open menu item Tools | SQL to type and execute SQL statements.

C# Program

On the MonoDevelop project which you want to access database, add Reference to Mono.Data.Sqlite. In the program, add using Mono.Data.Sqlite statements.

Reference to System.Data may be necessary, depending on your program code.

Here is a sample program segment that inserts one row to SQLite table.

public int Add(
    string severity,
    string msg)
{
    string connectionString = "URI=file:/home/pi/Database/JobLog.db,version=3";
    
    StringBuilder sql = new StringBuilder();
    sql.Append("insert into MessageLog");
    sql.Append("    (Severity, Message, LogTime)");
    sql.Append("    values");
    sql.Append("    (@Severity, @Message, datetime('now'))");
    
    int count = 0;

    using (SqliteConnection connection = new SqliteConnection(connectionString))
    using (SqliteCommand command = new SqliteCommand(sql.ToString(), connection)) {
        connection.Open();
        command.Parameters.Add(new SqliteParameter("@Severity", severity));
        command.Parameters.Add(new SqliteParameter("@Message", msg));
        count = command.ExecuteNonQuery();
    }

    return count;
}

Note on the system date and time: Raspberry Pi does not have battery to keep the system time. If your device is disconnected from Internet, the datetime() function may not return accurate values.

Hint on Logging

If the only purpose is to write message to a log, you can use NLog as an alternative to program code. See article More Packages for discussion on NLog installation.