Does Dapper support strongly typed objects with a stored procedure?

Question

Basically, I want to use the "nice" Dapper syntax for a stored procedure, without having to manually use exec MySproc @p1, @p2, @p3, @p4 and so on, but I need to be able to pass in a strongly typed object with various properties set and have this object be used to map the parameters. I know I can do this with an anonymous object, but the scenario I'm thinking of would be something like a complex search form where several fields can be searched, and the corresponding stored procedure can have quite a lot of parameters (many with defaults).

Ideally I'd want to be able to do something like this:

var cust = new Customer();
cust.FirstName = ...
cust.LastName = ...

// using .NET 3.5 so need to use ugly syntax :(
var result = connection.Query<Customer>("MySproc", cust, null, false, null, CommandType.StoredProcedure).Single();

however, that doesn't work and throws an error because my Customer object may have a dozen or more properties, and I'm only looking for two in this case; Dapper seems to be just checking every property and assigning a value, assuming there is a corresponding parameter in the sproc when there might not be.

I can do something similar to this using PetaPoco (pass in a strongly-typed object or an anonymous object) but I'm looking for something a little more abstracted than PetaPoco is.

Is what I want to do possible in Dapper (or another micro-ORM? I cannot use NHibernate or a heavyweight ORM), or is there a way I'm overlooking to get the same functionality short of having to write an exec statement with what could be a dozen parameters?

Buy

Answer 1

If you would like to specify the params you will need to do so explicitly:

var result = connection.Query<Customer>("MySproc", 
     new {cust.Id, cust.Name}, // specify the params you want to give it.  
     null, 
     false, 
     null, 
     CommandType.StoredProcedure).Single();

We do not do a sp_help params sniff for procs though you could potentially build a helper that does that and allows you to run: cust.ToProcParams('MySproc')

Alternatively, if you want to build this param dynamically, you can use.

var dp = new DynamicParameters(); 
dp.Add("Id", cust.Id);
dp.Add("Name", cust.Name);
var result = connection.Query<Customer>("MySproc", 
         dp,
         null, 
         false, 
         null, 
         CommandType.StoredProcedure).Single();

Escheat

That's what I was afraid of, since using the anonymous objects precludes me from building up an object dynamically based on inputs. The idea of the helper method is interesting, though. – Buy

Now that looks interesting. I'll have to play around with that and see how it works. Much appreciated, Sam! – Buy

Answer 2

If you are using SQL Server, check out Insight.Database. https://github.com/jonwagner/Insight.Database/wiki It is more oriented towards stored procedures and uses SqlDeriveParameters to determine the mapping between objects and the stored procedures.

NOTE: it currently requires .NET 4.0, but if you are really interested in a .NET 3.5 version, I can see how hard that would be.

Inaudible