{"id":1479,"date":"2022-10-22T16:10:38","date_gmt":"2022-10-22T22:10:38","guid":{"rendered":"https:\/\/www.great-white-software.com\/blog\/?p=1479"},"modified":"2022-10-22T16:10:38","modified_gmt":"2022-10-22T22:10:38","slug":"using-other-databases-in-c","status":"publish","type":"post","link":"https:\/\/www.great-white-software.com\/blog\/2022\/10\/22\/using-other-databases-in-c\/","title":{"rendered":"Using other databases in C#"},"content":{"rendered":"\n

A new version of the Einhugur C# UI framework was released a short while ago.<\/p>\n\n\n\n

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<\/p>\n\n\n\n

\nlist = new ListBox(10, 20, 80, 20 );<\/code><\/pre>\n\n\n\n
to add a new last parameter<\/p>\n\n\n\n
list = new ListBox(10, 20, 80, 20, new string[] {\"col 1\", \"col 2\"} );<\/code><\/pre>\n\n\n\n
to set the column titles.<\/p>\n\n\n\n
Dont be surprised won the road if there are several constructors to pick from where you dont have to set the titles right then.<\/p>\n\n\n\n
With that out of the way lets turn to using the Mono.SQLite database – with one on disk ! <\/p>\n\n\n\n
In prior editions of the tutorial we used the in memory SQLite database. We had <\/p>\n\n\n\n
sqlite_conn = new SqliteConnection(\"Data Source=:memory:\");<\/code><\/pre>\n\n\n\n
To switch this to use a database on disk we just need to alter the connection string.<\/p>\n\n\n\n
  sqlite_conn = new SqliteConnection(\"URI=file:\/Users\/npalardy\/testdb.sqlite;\");\n<\/code><\/pre>\n\n\n\n
With the Mono driver for SQLite when you connect this will create the database if it doesnt already exist.<\/p>\n\n\n\n
And .. tada ! Thats it \ud83d\ude42 Instead of an in memory database now we are using one from disk.
Here’s the project as it exists so far.<\/a><\/p>\n\n\n\n
In the poll I posted<\/a> half the respondents \ud83d\ude1bSia 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.<\/p>\n\n\n\n
First we need to grab a PostgreSQL database driver. We’ll add a NuGet package for this. Right click on Packages<\/em><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can filter using the field in the upper right to quickly find relevant packages<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
In Xojo terms we’ve basically added a “plugin” – but only to this project. Projects in VS have “per project plugins”<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
using System;\nusing System.Collections.Generic;\nusing System.Data;\nusing System.Diagnostics;\nusing CloudKit;\nusing Einhugur.Forms;\n\nusing Mono.Data.Sqlite;\nusing Npgsql; \/\/ <<<<<<<<<<<<<<<<<<\/code><\/pre>\n\n\n\n
As well we need to adjust our connection so instead of using SQLite it uses PostgreSQL.<\/p>\n\n\n\n
 \/\/ I deliberately left the variable name the same so you can see how\n \/\/ little needs adjustment\n\n \/\/ Mono.Data.Sqlite.SqliteConnection sqlite_conn;\n NpgsqlConnection sqlite_conn;<\/code><\/pre>\n\n\n\n
And the code where we create the connection needs to connect to the server<\/p>\n\n\n\n
\/\/ sqlite_conn = \n\/\/ new SqliteConnection(\"URI=file:\/Users\/npalardy\/testdb.sqlite;\");\n\nsqlite_conn = \nnew NpgsqlConnection(\"Host=localhost;Username=XXXX;Password=XXXX;Database=norm_csharp\");\n<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
IDbCommand dbcmd = sqlite_conn.CreateCommand();\nstring sql = \"create table if not exists employee ( firstname, lastname )\";<\/code><\/pre>\n\n\n\n
SQLite is OK with an untyped create statement. PostgreSQL isn’t. So we do need to alter that as well.<\/p>\n\n\n\n
IDbCommand dbcmd = sqlite_conn.CreateCommand();\nstring sql = \"create table if not exists employee ( firstname varchar(50), lastname varchar(50) )\";<\/code><\/pre>\n\n\n\n
And with that change everything should work again and now were using PostreSQL.<\/p>\n\n\n\n
We could<\/strong> use a more generic data type for the now badly named sqlite_conn<\/em>. Something more akin to Xojo’s Database<\/em>. We could declare it as<\/p>\n\n\n\n
            System.Data.IDbConnection sqlite_conn;<\/code><\/pre>\n\n\n\n
And our code could create & assign either a new sqlite connection or PostgreSQL one<\/p>\n\n\n\n
Here’s the project as it exists <\/a>so far.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … <\/p>\n
Continue reading “Using other databases in C#”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[34,4],"class_list":["post-1479","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-csharp","tag-macos"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/posts\/1479","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/comments?post=1479"}],"version-history":[{"count":1,"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/posts\/1479\/revisions"}],"predecessor-version":[{"id":1484,"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/posts\/1479\/revisions\/1484"}],"wp:attachment":[{"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/media?parent=1479"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/categories?post=1479"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.great-white-software.com\/blog\/wp-json\/wp\/v2\/tags?post=1479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}