Recently I've been working with Mono, PostgreSQL and Massive ( a lightweight 'ORM' written by Rob Connery) trying to see how much of my day to day job I can move outside of the Microsoft realm. While trying to use Massive with PostgreSQL I ran into a problem with the connectionStrings add tag's providerName attribute. Today's post will walk you through how to duplicate the message and more importantly how to fix the problem.
The Setup
For this project I am using PostgreSQL for the backend using my hockeydb database. If you don't have PostgreSQL installed and you are using a mac you can use this handy utility Postgres.app, which is brought to you by the good folks ate Heroku. For the non-mac crowd visit the PostgreSQL site and get the appropriate install for your machine. After you have PostgreSQL installed you can grab a copy of the hockeydb database here. After setting up postgres and loading the database its time to check your Mono environment setup.
For more information about the hockeydb and how to restore the PostgreSQL backup check out my previous post Hockey Databank Database.If you already have Mono and MonoDevelop setup you can skip this paragraph, if not read on for a quick overview of how to get it setup. First thing to do is go to the Mono Project Site and follow the download and install instructions. Once you have mono installed go to the MonoDevelop Download page and follow the the download and install instructions.
The final setup step is to download the PosgreSQL .NET driver. Download the driver Npgsql2 driver and unzip it into a directory of your choosing.
Now we are ready to start!
Creating the Solution and Adding Massive
After you have started MonoDevelop click on the 'Start New Solution…' link. In the 'New Solution' dialog Select 'C#' and then 'Console Project'. Name the solution HockeyDbConsole and click the 'Forward' button. We won't be creating a GUI so ensure that the GTK# Support checkbox is NOT checked and click the 'OK' button. You should now see the HockeyDbConsole project under a solution with the same name.
My next step is to download the Massive code for PostgreSQL and add it to my project. Grab it from here Massive.PostgreSQL.cs. You will also need to add the following references for Massive's dependencies:
- Pngsql (the assembly that you downloaded during setup)
- System.Configuration
- System.Data
- Microsoft.CSharp
After adding my references there is one more file I need to add to the project, an app.config file. Unlike VisualStudio MonoDevelop doesn't offer a way to add an app.config but it does have a way to add an empty XML file. When you add the empty file make sure to name it app.config. Once the file is in the project update it so its contents look like this:
Go ahead and build the project when the build is done you should have no errors.
Getting Ready to Query the abbrev Table
To keep the output simple I will query the abbrev table. It contains a key for all the abbreviations used throughout the database. One of the ways to query the table using Massive is to create a class that maps to the table you wish to query. I will create a class named Abbrev, that way its easy to figure out which table I'm working with. Following the Massive README file https://github.com/robconery/massive/ My Abbrev class looks like this:
Now that I have the Abbrev class I'm ready to run a query. I will add code to retrieve all the records in the abbrev table and list the results after the "Hello World" message. First I need to new up an Abbrev object and call it table. I will then make a call table.All() and loop through the results writing the records to STDOUT. Here's what the updated Main method looks like:
Ok, looks like everything is in place its time to run it. When I do I see this error (I've added the red and bolded the most important part of the error):
Unhandled Exception: System.Configuration.ConfigurationErrorsException: Failed to find or load the registered .Net Framework Data Provider 'Npgsql'.
at System.Data.Common.DbProviderFactories.GetFactory (System.String providerInvariantName) [0x00026] in /private/tmp/monobuild/build/BUILD/mono-2.10.9/mcs/class/System.Data/System.Data.Common/DbProviderFactories.cs:80
What does the error mean? It's telling me that .NET doesn't know what providerName="Npgsql" in my connect string maps to. After some head scratching and a few Google search I was able to get past this issue by declaring a Npgsql as a Data Provider.
Registering Npgsql as a Data Provider
To register Npgsql as a data provider I need to add a few lines to my app.config file. Here's what the app.config file looks like now:
As you can see I had to ad the system.data tag. Within it I added the Npgsql information under the DbProviderFactory tag. I set the invariant value to the base namespace and it is what is used in the providerName attribute. I was able to gather the information for the type attribute using the Assembly Browser tool within MonoDevelop. Right click on the Npgsql.dll reference and selecting the 'Open' option. Once the browser opens up expand the Npgsql node on the left. Next, search for Factory and the NpgsqlFactory class is visible.
Now that I have the app.config file updated I will re-run the app. This time I see the results of my query.
For the demo I added the system.data information to the app.config file. Since I'm going to be using this on multiple projects I've added the add tag to my machine.config file's system.data > DbProviderFactory tag.
Summary
By adding just a few lines to my app.config file I was able to use a PostgreSQL connectionString with Massive to run a simple query. I hope this post helps you get through this issue faster than I did.
Resources
- Mono
- MonoDevelop
- PosgreSQL (Mac)
- PostgreSQL (Non-Mac)
- Npgsql2 (.NET PosgreSQL driver)
- Massive.PostgreSQL.cs
- PosgtreSQL version of hockeydb For more information on the database you can read my previous blog post: Hockey Databank Database.
