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.
The Blog of Rob 