使用存储过程时，哪个ORM是最好的

I have Business objects (DEVELOPERS WRITE) and some SPROCS (DBA WRITE)

我有业务对象(开发人员编写)和一些SPROCS (DBA编写)

Can anyone recommend a good object mapper to deal with this kind of setup.

有人能推荐一个好的对象映射器来处理这种设置吗?

I tried codesmith and nhibernate and had trouble. I do not mind if my ORM is free or paid.

我尝试了codesmith和nhibernate，但遇到了麻烦。我不介意我的ORM是免费的还是付费的。

8 个解决方案

#1

SubSonic has excellent support for sprocs. It will wrap each one in a helper method and you can retrieve strongly-typed collections or entities from the results if you want. I show a way to do that in this blog post. As long as your sproc returns the same schema as SELECT * FROM TableName would, it will work with your SubSonic entities.

亚音速对sprocs有很好的支持。它将把每一个都封装在一个helper方法中，如果您愿意，您可以从结果中检索强类型的集合或实体。我在这篇博客文章中展示了一种方法。只要您的sproc返回与SELECT *相同的模式，它将与您的亚音速实体一起工作。

As far as generating classes based on your db, SubSonic generates partial classes so you can extend them as needed. You could also do mappings from the SubSonic generated classes to your actual model.

就基于db生成类而言，亚音速生成部分类，因此您可以根据需要扩展它们。您还可以做从亚音速生成的类到实际模型的映射。

#2

Subsonic has a flexible solution:

亚音速有一个灵活的解决方案:

    class CustomerOrder {
        private string productName;

        public string ProductName {
            get { return productName; }
            set { productName = value; }
        }
        private int total;

        public int Total {
            get { return total; }
            set { total = value; }
        }

    }

Then:

然后:

List<CustomerOrder> orders = Northwind.SPs.CustOrderHist("ALFKI")
        .ExecuteTypedList<CustomerOrder>();

Subsonic is a solid "Swiss Army knife" style ORM.

亚音速是一种坚固的“瑞士军刀”风格。

#3

Disclaimer: I am the author of Dapper.

免责声明:我是《衣冠楚楚》的作者。

If you are looking for a simple object mapper that handles mapping procs to business objects Dapper is a good fit.

如果您正在寻找一个处理将procs映射到业务对象的简单对象映射程序，那么Dapper是一个很好的选择。

Keep in mind it ships with no "graph management", "identity map" and so on. It offers a bare bone, complete solution which covers many scenarios other ORMs do not.

记住，它没有“图形管理”、“标识映射”等等。它提供了一个裸骨，完整的解决方案，涵盖了许多其他orm没有的场景。

Nonetheless, it offers one of the fastest object materializers out there, which can be 10x faster than EF or even 100x faster than subsonic in some benchmarks.

尽管如此，它还是提供了一种最快的物质体，比EF快10倍甚至比亚音速快100倍。

The trivial:

微不足道的:

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

Can be mapped with the following:

可绘制如下图:

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

Additionally you have support for:

此外，您还支持:

A multi-mapper that allows you single rows to multiple objects
一个多映射器，允许单个行到多个对象。
Input/Output/Return param support
输入/输出/返回参数的支持
An extensible interface for db specific parameter handling (like TVPs)
用于db特定参数处理的可扩展接口(如TVPs)

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

Can be mapped with:

可以映射:

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 allow for a custom parameter implementation:

最后，dapper还允许定制参数实现:

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

When implementing this interface you can tell dapper what parameters you wish to add to your command. This allow you to support Table-Valued-Params and other DB specific features.

在实现这个接口时，您可以告诉dapper您希望添加到您的命令中的哪些参数。这允许您支持表值解析器和其他DB特定特性。

#4

Depending on the database Entity Framework, or NHibernate are likely your best options (examples in links).

根据数据库实体框架，或者NHibernate可能是您最好的选择(链接中的示例)。

#5

The LINQ to SQL designer will give you type-safe sprocs as methods on the DataContext object. You can map those to objects for CRUD operations fairly easily.

LINQ to SQL designer将为您提供类型安全的sprocs作为DataContext对象的方法。您可以很容易地将它们映射到CRUD操作的对象。

In fact, I'm in the middle of doing exactly that.

事实上，我正在做这个。

#6

Since you've got a DBA writing the sprocs, I would think the best thing to do would be to work closely with him to figure out how to map the tables to objects, and how to structure the database so that it works with your domain model. There's nothing wrong with sprocs, they just require close collaboration between the developers and the DBAs.

既然有一个DBA在编写sprocs，我认为最好的做法是与他密切合作，找出如何将表映射到对象，以及如何构造数据库，使其与域模型协同工作。sprocs没有问题，它们只需要开发人员和dba之间的密切协作。

Ideally, the DBA in question is part of your project team...

理想情况下，所讨论的DBA是项目团队的一部分……

#7

I like the way the Entity Framework handles sprocs right now. You can associate sprocs with the crud operations of an entity, it even detects which sprocs match up with the properties of your entity. The one big downside right now is if you associate one sproc with an entity you must associate all the crud operations with a sproc.

我喜欢实体框架现在处理sproc的方式。您可以将sprocs与实体的crud操作相关联，它甚至可以检测哪些sprocs与实体的属性相匹配。现在的一个大缺点是，如果您将一个sproc与一个实体相关联，那么您必须将所有crud操作与一个sproc相关联。

This EF Sproc article has some great examples of how to use sprocs in EF and has some really nice Extension methods for it as well.

这篇EF Sproc文章有一些关于如何在EF中使用Sproc的很好的例子，并且有一些很好的扩展方法。

#8

The main issue I see with this, is that by going with SP you are automatically loosing lot of the flexibility you get when using ORM, specially on the retrieval of information. Because of this, I am sure you won't be able to use All of the features of most ORM.

我看到的主要问题是，通过使用SP，你会自动失去使用ORM时的灵活性，特别是在信息检索方面。正因为如此，我确信您将无法使用大多数ORM的所有特性。

For example, if you use linq2sql, you will have pretty much wrapper to the SPs. You can also map insert, deletes and updates of the generated entities to stored procedures. Where you loose a lot is on the retrieval of information, both because the queries are now fixed (and you might retrieve more information than needed i.e. extra columns - or create lots of SPs) and on lazy loading.

例如，如果您使用linq2sql，那么您将拥有很多jsp的包装器。还可以将生成的实体的插入、删除和更新映射到存储过程。在检索信息时，您会丢失很多信息，这是因为查询现在是固定的(而且您可能会检索到比需要的更多的信息，例如额外的列——或者创建大量的SPs)，以及延迟加载。

Update: I am more a linq2sql guy, but I would take a second look at the assumptions you are taking about NHibernate. In particular, I doubt it will force column order as it is configured with column names (see http://nhibernate.info/blog/2008/11/23/populating-entities-from-stored-procedures-with-nhibernate.html). It also supports something I don't know how to do with linq2sql: http://nhibernate.info/blog/2008/11/23/populating-entities-with-associations-from-stored-procedures-with-nhibernate.html. Note, I don't mean that last one isn't supported with linq2sql, just that I don't know how to ;)

更新:我更喜欢linq2sql，但是我想再看看您对NHibernate的假设。特别是，我怀疑它会强制列顺序，因为它配置了列名(参见http://nhibernate.info/blog/2008/11/23/populatentis-from-storing-procedure - hibernate -nhibernate.html)。它还支持一些我不知道如何使用linq2sql的东西:http://nhibernate.info/blog/2008/11/23/populating-entiation -with- associated -from-stored-procedure -with-nhibernate.html。注意，我并不是说最后一个不支持linq2sql，只是我不知道如何;)

#1