Working with databases in C#

The trek continues !

One of the common things we need our applications to do is interact with a database. Sometimes its just a local SQLite, or other stand alone db engine like that, or some “Big Iron” database like Oracle, MS SQL Server, PostgreSQL, etc. Or maybe a NoSQL database like Mongo ?
With Xojo there are only a handful of plugins Tod ell with the vast majority of databases. Either you use the ones provided by Xojo. Or the ones from MBS or Valentina, or CubeSQL. There just arent that many vendors or plugins to have to wade through

In C#, because its widely available on so many platforms, there are tons of NuGet packages to select from. I cant tell you all of them or which specific ones will / wont work on macOS. I haven’t tried them all 🙂
Typing in “database” to the search field in the package manager gives a scrolling list that is immense.

325,832 packages is much like saying “there are 325,832 plugins for Xojo”
Christians good but I dont think even he’s got that many 😛

So there is a LOT to choose from and databases are no exception. This chapter isn’t a “how to do anything with any database in C#” but how to use one specific NuGet package to use SQLITE.

So lets get started !

I’m still adding to my standard “Getting Started project”

In order to “add a plugin” in Xojo you’d add it to the IDE’s Plugins, restart and it would be available in every project you opened. VS works differently and each solution can have a different list of packages and even different versions of the same package.

For our purpose we’re going to add a reference to our project to make this package usable in our code.

Select the references item and right click (or you can just select the Project > Add Reference… menu item)

For THIS project we’re going to add Mono.Data.SQLite and System.Data
Check the check box next to them and they’ll be added to the list on the right

Press select & they’ll be added to your project.

Our app doesnt have a lot of code in it – yet – so tying a database into it is a little contrived.
All we have is a window that we have textfields on and it just has a got focus event.
Lets add a listbox to the window so we can put some data into something familiar.

list = new ListBox( (float)textfield2.Left,
                    (float)(textfield2.Top + textfield2.Height + 12),
                    (float)(kDefaultWidth - 20),
                    (float)(kDefaultHeight - (textfield2.Top + 
                              textfield2.Height + 12 + 12)) );
            list.LockLeft = true;
            list.LockTop = true;
            list.LockRight = true;
            list.LockBottom = true;

return new Control[] { textfield1, textfield2, list };

I defined 2 constants for the default height and width of the window so I could reuse those elsewhere. And yes, control locking works just like in Xojo 🙂

namespace getting_started
{
	public class MainWindow : Window
	{

        const int kDefaultWidth = 600;
        const int kDefaultHeight = 400;

And I altered my SetUpWindow method to use those constants instead of the hard coded literals.

What you should notice is how C# does a CAST – in Xojo its like

INTEGER( expression you want treated as an integer )

almost like a function call. In C# its

(INTEGER) expression you want treated as an integer 

so you have to be sure to put brackets around the expression you want to cast. Often I just write

(INTEGER)( expression you want treated as an integer )

just to be safe. BU the “style gods” and language pedants get upset at this verbosity 🙂

OK so now we have a list to show data in. We could load the data in the control creation method but we’ll add it elsewhere since thats more like what we would do in many cases. I’m going to add a button next to the lower text field, just because that seems a good place, and then hook the database code into the pushbutton push event. I added a declaration of the button with the listbox and other controls :

        Button pushButton;

and added this code to the method creating all the controls

pushButton = new Button((float)(textfield2.Left + textfield2.Width + 12),
                        (float)(textfield2.Top),
                        80,
                        22);

pushButton.Pressed += pushbutton_Pressed;

return new Control[] { textfield1, textfield2, list, pushButton };

For right now the method for pushbutton_Pressed looks like

private void pushbutton_Pressed(object sender, EventArgs e)
{
            

}

You should still be able to run & the button will just do nothing.

To use Sqlite in this code we will need to add a using statement.

using Mono.Data.Sqlite;

Typically these are all at the top of whatever source code file you’re working in. I like to group them

The Sqlite package we added uses a form of access to the DB that is very similar to what Xojo has, but its not identical.

The code we’re going to add will use local variables, but moving those to be application or window wide is a trivial operation so it really won’t matter much.

First we need to declare a variable to hold the Sqlite_connection – this would be analogous to defining a variable for the SQLiteDatabase object in Xojo.

Mono.Data.Sqlite.SqliteConnection sqlite_conn; 
// in Xojo var sqlite_conn as SQLiteDatabase
   

Then we need to create the instance – this will attach to an in memory database. In Xojo this is different – we just dont open a database file

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

Then we actually tell this new connection to OPEN and attach to whatever database we set up as the data source

sqlite_conn.Open();

About this point you’re wondering Great but how do I check errors ? This is one are where .Net and Xojo, now, agree. .Net uses EXCEPTIONS for error handling ( at least with this database although I expect others are similar)

So in reality our code should look like

Mono.Data.Sqlite.SqliteConnection sqlite_conn;

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

    sqlite_conn.Open();
 }
 catch (Mono.Data.Sqlite.SqliteException exc)
 {
   Console.WriteLine("An error occurred {0}", exc.Message);
}

All the rest of the code we’ll add will be inside the TRY CATCH block following the opening of the connection.

The API that .Net uses for many database is ADO. Its a lot like what Xojo uses but has syntax differences. See https://learn.microsoft.com/en-us/sql/ado/guide/ado-programmer-s-guide?view=sql-server-ver16

About the biggest difference is that ADO seems a tad more low level. In some ways that makes certain thinks possible and in other ways it’s a pain because it means you write more code.

Onwards !

In order to RUN any sql command we need a command object – an IDbCommand. Then we configure that object and then tell it to execute.

IDbCommand dbcmd = sqlite_conn.CreateCommand();
string sql = "create table employee ( firstname, lastname )";
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();

Just as in Xojo there are several ways to execute sql statements – some that return results, some that return a single result, or ones that return no results at all. The previous code returns no results hence its a “non-query”.

And since this is an in memory database we’ll just add a few rows to it.

sql = "insert into employee ( firstname, lastname ) values('Norm', 'Palardy')";
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();

sql = "insert into employee ( firstname, lastname ) values('Joe', 'Smith')";
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();

Now we have a database connection, a table, and some data in it. To read that data we’ll need the ADO equivalent of a Xojo recordset or rowset. This is an IDataReader. Also since the command were going to execute will return a reader well just a different method – just like in Xojo where you’d use SQLSelect or SelectSQL.

sql = "SELECT firstname, lastname FROM employee";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();

while (reader.Read())
{
     string firstName = reader.GetString(0);
     string lastName = reader.GetString(1);
     list.AddRow(firstName + " " + lastName);
}

One thing to notice is that unlike Xojo’s rowset and recordset YOU dont have to remember to say “move to the next row” – and if you’ve ever forgotten that and wondered why your application runs into an infinite loop you’ll appreciate this.

Reader.Read not only moves to the next row of the returned rows it also returns a boolean value when it succeeds. This way you can move to the next row without having to write any code. Make sure you check out ALL the possible GetXXXXX methods that the reader has – they are extensive. In our simplfusage above we just retrieve, by column position, the strings returned and add that as a single row to the listbox.

As a good citizen we should ALWAYS dispose of the objects we created.

  // clean up
reader.Dispose();
dbcmd.Dispose();
sqlite_conn.Close();

And – were done !

Note there are things I have not covered

• creating a new database
• configuring the connection to read from an existing db file (see For the 2.0 profile in the new assembly on https://www.mono-project.com/docs/database-access/providers/sqlite/)
• using prepared statements (which also exist)

I’m sure there are many other things

And as always a link to the project so far