Does Dapper support strongly typed objects with stored procedure?

Question

Basically, I want to use the “good” Dapper syntax for the stored procedure, without having to manually use exec MySproc @p1, @p2, @p3, @p4 , etc., but I need to be able to pass a strongly typed object with different sets properties and this object is used to map parameters. I know that I can do this with an anonymous object, but the script that I think of will be something like a complex search form in which you can search for several fields, and there can be quite a few parameters in the corresponding stored procedure (many of them by default).

Ideally, I would like 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, this does not work and throws an error because my Customer object may have a dozen or more properties, in which case I am only looking for two; It seems that Dapper just checks each property and assigns a value, assuming that sproc has a corresponding parameter if that cannot be.

I can do something like this using PetaPoco (pass a strongly typed object or an anonymous object), but I'm looking for something more abstract than PetaPoco.

Do I want to do something possible in Dapper (or another micro-ORM? I can’t use NHibernate or heavyweight ORM), or is there a way that I skip the same functionality without having to write exec, which could be a dozen parameters ?

Answer 1

If you want to specify parameters, you will need to do this 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 execute snf for, but you can create an assistant that does this and allows you to run: cust.ToProcParams('MySproc')

Alternatively, if you want to dynamically create this parameter, you can use it.

 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(); 

Answer 2

If you are using SQL Server, go to Insight.Database. https://github.com/jonwagner/Insight.Database/wiki It is more focused on stored procedures and uses SqlDeriveParameters to determine the correspondence between objects and stored procedures.

NOTE: this currently requires .NET 4.0, but if you are really interested in .NET 3.5, I can understand how complicated this is.