Support CommanType.StoredProcedure and output/returnvalues

[GoogleCodeExporter]

Would be great if stored procedure output values and return values where 
supported.
Don't know if the need is great, but I replaced old linq2sql code with dapper 
in a project and the need emerged.

I have made a clone which supports this by adding optional commanType parameter 
to the query methods and an ParameterDirection attribute which I decorate the 
properties which should get values from output or return value.

Sample parameter class and usage attached, just fragment but you should get the 
flow.

Clone can be found here
http://code.google.com/r/mattias-dapper-dot-net-commandtype-storedprocedure-outp
ut/source
This can probably be improved with il & refactored, but it solved my needs.

Original issue reported on code.google.com by mattias%...@gtempaccount.com on 3 May 2011 at 3:09

Attachments:

• sample.cs

[GoogleCodeExporter]

Totally support passing in command type

my concern is that the api for the passing in the params seems a bit off to me 
... 

perhaps we can overload and have. 

class ReturnData 
{
  [OuputParam]
  public int Status {get;set;}

  [RowCount]
  public int RowCount {get;set;}

  [ReturnVal]
  public Guid Rval {get;set;}
}

Query<User>("spGetUser", new {userId} commandType:CommandType.StoredProcedure, 
out returnVal);

Or maybe even allow anon types with an optional ref using a convention:

var returnStuff = new {Id = -1, OutputParam = new Guid()};
Query<User>("spGetUser", new {userId} commandType:CommandType.StoredProcedure, 
ref returnVal);

Thoughts? 

Original comment by sam.saff...@gmail.com on 4 May 2011 at 12:03

[GoogleCodeExporter]

I'm definitely interested in this. I like both options.

Original comment by james.ne...@gmail.com on 4 May 2011 at 6:46

[GoogleCodeExporter]

Some thoughts below, my implementation was quick to solve real world customer 
problems, so if some time is spent on architecture I’m sure an better API can 
be thought of.

Having an anonomous wont work per say, but could work with reflection but a bit 
freaky.
var t = new {Id = default(Guid)};
t.Id = /*error no setter*/

Having an optional out parameter should do the trick as if you working with the 
same object you could just pass it twice.

Having multiple attributes for the same thing is just an matter of taste may be 
more readable but will also theoretically allow users to add multiple 
attributes giving different context to the property, could probably be solved 
using inheritance.

Another way of solving it would be to do a bit of refactoring and move 
commandTimeout, commandType, RowCount, and RowCount into a command parameter 
wrapper class.

public class CommandParameter<TReturn>
{
    public int? CommandTimeout { get; set; }
    public CommandType? CommandType { get; set; }
    public int RowCount { get; private set; }
    public TReturn ReturnValue { get; private set; }
    internal List<Func<object>> OutputMapper { get; set; }
}

And then add some extension/instance methods to that allowing to register 
output lambda expressions. Perhaps in a similar syntax that AutoMapper does 
giving a lot of flexibility.

public static class CommandParameterHelper
{
    public static CommandParameter<TReturn> OutputMapFor<TReturn>(
        this CommandParameter<TReturn> commandParameter,
        Func<object> map
        )
    {
        /*funkycode*/
        return commandParameter;
    }
}

Allowing for syntax kind of the below:
conn.Query(
    /*abbreviated*/,
    commandParameter: new CommandParameter<int>
    {
        CommandTimeout = 10,
        CommandType = CommandType.StoredProcedure
    }.OutputMapFor(
        () => searchParam.Id
    )
    );

This allows you to have serveral targets even local variables.

A command parameter wrapper would allow adding more command behaviors without 
changing the Query signatures.  It would also allow for not exposing to much of 
the internals.

Another way would be to just to use the CommandParameter class for everything. 
Something in the lines of:
public class CommandParameter : CommandParameter<DBNull, DBNull> { }
public class CommandParameter<TReturn> : CommandParameter<TReturn, DBNull> { }
public class CommandParameter<TReturn, TOutput>
{
    public int? CommandTimeout { get; set; }
    public CommandType? CommandType { get; set; }
    public int? RowCount { get; private set; }
    public TReturn ReturnValue { get; private set; }
    public TOutput OutputParameter { get; set; }
}

Allowing “generics” to make it easy to reflect and see if return value 
should be used etc. Not as flexible but perhaps more readable. 

/Matias

Original comment by mattias%...@gtempaccount.com on 4 May 2011 at 8:00

[GoogleCodeExporter]

I just added pretty elegant support for params, care to have a look and review? 

Original comment by sam.saff...@gmail.com on 11 May 2011 at 12:41

[GoogleCodeExporter]

done this is now supported, see: 

http://stackoverflow.com/questions/5962117/is-there-any-way-using-dapper-with-sq
l-stored-procedure/5971847#5971847

Original comment by sam.saff...@gmail.com on 11 May 2011 at 11:50

[GoogleCodeExporter]

Original comment by sam.saff...@gmail.com on 11 May 2011 at 11:50

• Changed state: Fixed