ORM that works with stored procedures in ASP.NET 4 and SQL Server

Question

I would like to know if there is a good ORM that can automate some of the manual work that I have to do right now. Our application makes heavy use of stored procedures, which means that any interaction with the database goes through the stored procedure.

Right now, I have to invoke stored procedures in the traditional way, and this is pretty tedious. Is there an ORM that handles stored procedures well that:

• Have I / O parameters that are structured, which means table type values
• Easily resolve output parameters as user-defined types, both scalar and tabular values
• Returning multiple recordsets

Answer 1

Dapper has fairly extensive support for stored procedures.

Trivial:

create proc spGetOrder @Id int as select * from Orders where Id = @Id select * from OrderItems where OrderId = @Id 

It can be compared with the following.

 var grid = cnn.QueryMultiple("spGetOrder", new {Id = 1}, commandType: CommandType.StoredProcedure); var order = grid.Read<Order>(); order.Items = grid.Read<OrderItems>(); 

In addition, you have support for:

• A multi-match that allows you to use separate lines for multiple objects
• Support for input, output and return options.
• Extensible interface for processing parameters of a specific database (for example, TVP)

So for example:

 create proc spGetOrderFancy @Id int, @Message nvarchar(100) output as set @Message = N'My message' select * from Orders join Users u on OwnerId = u.Id where Id = @Id select * from OrderItems where OrderId = @Id return @@rowcount 

May be displayed using:

 var p = new DynamicParameters(); p.Add("Id", 1); p.Add("Message",direction: ParameterDirection.Output); p.Add("rval",direction: ParameterDirection.ReturnValue); var grid = cnn.QueryMultiple("spGetOrder", p, commandType: CommandType.StoredProcedure); var order = grid.Read<Order,User,Order>((o,u) => {o.Owner = u; return o;}); order.Items = grid.Read<OrderItems>(); var returnVal = p.Get<int>("rval"); var message = p.Get<string>("message"); 

---

Finally, Dapper also allows you to implement a custom parameter:

 public interface IDynamicParameters { void AddParameters(IDbCommand command); } 

When implementing this interface, you can tell Dapper which parameters you want to add to your command. This allows you to maintain tabular parameters and other database-specific functions.

Now you use it in Stack Overflow ...

Answer 2

Browse the Entity Framework .

Answer 3

If memory is used, LINQ to SQL has the ability to map methods to stored procedures. When you create LINQ in an SQL context inside Visual Studio, you can connect your database and drag and drop tables into the design view. It will detect any stored procedures and generate the necessary methods inside the model objects.

See the Scott Guthrie blog post LINQ to SQL (Part 6 - Retrieving Data with Stored Procedures) .