Using other databases in C#

A new version of the Einhugur C# UI framework was released a short while ago.

Since the framework is still literally a work in progress some changes an dupes are to be expected. Listbox now exists and its constructor now takes an array of column titles as well as the initial dimensions. So you may need to update code like

list = new ListBox(10, 20, 80, 20 );

to add a new last parameter

list = new ListBox(10, 20, 80, 20, new string[] {"col 1", "col 2"} );

to set the column titles.

Dont be surprised won the road if there are several constructors to pick from where you dont have to set the titles right then.

With that out of the way lets turn to using the Mono.SQLite database – with one on disk !

In prior editions of the tutorial we used the in memory SQLite database. We had

sqlite_conn = new SqliteConnection("Data Source=:memory:");

To switch this to use a database on disk we just need to alter the connection string.

  sqlite_conn = new SqliteConnection("URI=file:/Users/npalardy/testdb.sqlite;");

With the Mono driver for SQLite when you connect this will create the database if it doesnt already exist.

And .. tada ! Thats it 🙂 Instead of an in memory database now we are using one from disk.
Here’s the project as it exists so far.

In the poll I posted half the respondents 😛Sia they wanted to see how to use a different db. So we’ll alter this to use PostgreSQL since I happen to have a handy configuration already on my machine – but the steps will be similar for most databases.

First we need to grab a PostgreSQL database driver. We’ll add a NuGet package for this. Right click on Packages

We can filter using the field in the upper right to quickly find relevant packages

Note there are many PostgreSQL packages we can pick from. I honestly dont know all the pro’s and cons of the different packages. I have used the Nppqsql package ands so far it works as I need & expect.

Select that package and add it to the project. You may see several other packages also get aded. When a package depends on another to be able to work the packages that are depended on are also brought in automatically.

In Xojo terms we’ve basically added a “plugin” – but only to this project. Projects in VS have “per project plugins”

In our code we’ll need to addd a using clause to let the compiler know it should expect references to this new plugin in this code.

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using CloudKit;
using Einhugur.Forms;

using Mono.Data.Sqlite;
using Npgsql; // <<<<<<<<<<<<<<<<<

As well we need to adjust our connection so instead of using SQLite it uses PostgreSQL.

 // I deliberately left the variable name the same so you can see how
 // little needs adjustment

 // Mono.Data.Sqlite.SqliteConnection sqlite_conn;
 NpgsqlConnection sqlite_conn;

And the code where we create the connection needs to connect to the server

// sqlite_conn = 
// new SqliteConnection("URI=file:/Users/npalardy/testdb.sqlite;");

sqlite_conn = 
new NpgsqlConnection("Host=localhost;Username=XXXX;Password=XXXX;Database=norm_csharp");

The ONE other things we have to account for, since we’re using raw SQL. While the following will work for SQLite it wont work for PostgreSQL.

IDbCommand dbcmd = sqlite_conn.CreateCommand();
string sql = "create table if not exists employee ( firstname, lastname )";

SQLite is OK with an untyped create statement. PostgreSQL isn’t. So we do need to alter that as well.

IDbCommand dbcmd = sqlite_conn.CreateCommand();
string sql = "create table if not exists employee ( firstname varchar(50), lastname varchar(50) )";

And with that change everything should work again and now were using PostreSQL.

We could use a more generic data type for the now badly named sqlite_conn. Something more akin to Xojo’s Database. We could declare it as

            System.Data.IDbConnection sqlite_conn;

And our code could create & assign either a new sqlite connection or PostgreSQL one

Here’s the project as it exists so far.