PostgreSQL Stored Procedures With Dapper-Dot-Net in C#

This is just a quick post for anyone out there struggling to do something sensible in C# with the refcursor results that PostgreSQL functions are capable of returning.

I’ve been playing around with pgSql today, trying to get it to work with my favourite dot-net tools in Mono and I decided that I wanted to write the equivalent of a stored procedure in my database, pass it some parameters and return some tabular results. It turns out that there are two ways of achieving this: either I could return a set of tabular results directly from a database function or I could return a refcursor which points to my tabular results. I preferred the sound of returning a refcursor because it would mean that I could return multiple results sets as well being able to use dapper-dot-net’s nifty feature where it will automatically map all the columns to fields in a C# dynamic object.

This left me with a bit of a problem, especially when most of the documentation out there referenced a feature which used to be part of the npgsql but has been removed. If a function returned only one refcursor then npgsql used to ‘de-reference’ it and return the tabular results. All I’m going to say about this is that it pretty much breaks trying to use dapper-dot-net in the typical way that you would use it with MSSQL server.

What I want to be able to do:

        public User GetUserFromDb(int userId){
            using (IDbConnection connection = new NpgsqlConnection (DbConnectionString)) {
                connection.Open ();

                return connection.Query<User> (proc_GetUserForId, new{
                    UserId = userId
                }, commandType: CommandType.StoredProcedure).Single();
            }
        }
Unfortunately this will cause dapper to throw an exception as it can’t map the fields returned on the refcursor to the fields on my User class. So instead I have to do this:

        public dynamic GetUserFromDb(int userId){
            using (IDbConnection connection = new NpgsqlConnection (DbConnectionString)) {
                connection.Open ();

                return connection.QueryStoredProcPgSql<User> (proc_GetUserForId, new{
                    UserId = userId
                }).Single();

            }
        }
Where QueryStoredProcPgSql is defined as:

    public static class DatabaseHelpers
    {
        public static IEnumerable<T> QueryStoredProcPgSql<T>(this IDbConnection connection, string procName, dynamic parameters){
            IDbTransaction transaction = connection.BeginTransaction ();

            var resultsReference = (IDictionary<string, object>)connection.Query<dynamic> (\” + procName + \”, 
                (object)parameters, commandType: CommandType.StoredProcedure, transaction: transaction).Single();
            string resultSetName = (string)resultsReference [procName];
            string resultSetReferenceCommand = string.Format(@”FETCH ALL IN “”{0}“”, resultSetName);

            var result = connection.Query<T> (resultSetReferenceCommand, 
                null, commandType: CommandType.Text, transaction: transaction); 

            transaction.Commit ();

            return result;
        }
    }
Note that I’m using a transaction as the refcursor cannot be dereferenced without it. I don’t claim that this is best way of achieving this result or even particularly efficient but if you’re stuck then hopefully it will push you in the right direction.

If you wanted to extend this to work with multiple results sets then I imagine it should be entirely possible to change .Single() to .ToList() in the above helper method, return a bunch of refcursors and then all within the same transaction iterate through the references and get the results. If you’re returning different types in different results sets then you might have to get rid of the generics and return an IEnumerable of dynamic objects. This does remove one of dapper’s big benefits though.

Advertisements
Posted in Random Thoughts | 3 Comments

Moving Blogs

I’ve had enough of wordpress now and so I decided to move to blogger (the google service) and buy myself a nice domain name. I’ve got www.xrob.eu now so you can visit my blog there. WordPress wanted to charge me an additional $10 a year to link this blog to my domain so that’s why I’m switching blogging engines. I’m still going to leave this blog up here however so that people can continue to visit and look at the couple of tutorials and articles that people frequent. If you can’t access http://www.xrob.eu just yet don’t worry, it’s probably just the DNS propagating around the internet.

Posted in Random Thoughts | Leave a comment

A Few Problems with School IT departments

I’d like to begin this post by saying that I have nothing against individual IT departments but I direct this post primarily at the whole of the school-focused IT industry; I do this because I feel that school IT departments simply follow by example (the example being that of what is set by the IT industry). Although I think that it is mainly the IT industries fault individual school’s IT departments are not entirely blame-free as they collectively have done what they’ve been told.

I’m going to break this down into 5 sections and discuss each individually and then come to a conclusion; the sections will be: Computer Hardware, Networking Solutions, Choice of Student’s Software, Use of Monitoring ‘Spy’ Software and Communication with Students. Continue reading

Posted in Random Thoughts, School | 3 Comments

The UK Government’s Agressive Advertising Campaign

More than ever now you see and hear benefit fraud adverts on the TV and on the radio warning people about Benefit fraud. Every day on my bus to school I see an advert giving me a number to call to inform the Government about any ‘benefit cheats’ that I know of and it seems like a campaign of terror by the government to stop people who they consider to be ‘benefit fraudsters’. Continue reading

Posted in Random Thoughts | 1 Comment

Update

Well, It appears that I haven’t posted a blog post in a while so I’ll just post an update since I last posted :). Continue reading

Posted in Random Thoughts | 6 Comments

Programmer Sex

So…. I’m sitting around in #teenlug (irc.freenode.net) and I decide that I want to piss fophillips off :)… so I strike up a conversation about how stupidly laborious C is as compared to Ruby and it suddenly strikes me! Programming languages are a lot like sex… Continue reading

Posted in Development, Linux, Random Thoughts | 2 Comments

German Exchange

Well, the German exchange looms closer than ever now for me, I leave on Wednesday flying from Birmingham to Dusseldorf and then going on to Bochum. I think I might die on it because it seems that we’re constantly doing so much…

Wednesday: Get up at 4AM, go to Birmingham by train, go by plane to Dusseldorf and then by train to Bochum. Meet host families and listen to a really long (most probably boring) speech. Then we’re going on to see a musical; “Starlight Express” – I’ve heard it’s good but it’s 3 hours long and finshed at 21:30 – I think I might fall asleep in the middle of it. That’ll probably be atleast an 18 hour day at least :/.

Thursday: We’re going to Cologne (Koln) and going on a tour of the cathedrals and museums.

Friday: Following our exchange partners around school for the day and perhaps going to Hattingen

Saturday: Going to a football match – VfL Bochum vs. FC Bayern Munich

Sunday: Whatever the host family wants me to do

Monday: Following exchange partners around school and bowling in the evening

Tuesday: Following exchange parners around school again and then going home on the train and then plane and then train again 🙂

I am going to die!

Posted in School | Leave a comment