将SQL保存在c#源代码或Stored Procs中有哪些优点/缺点?我一直在和一个朋友讨论这个问题,我们正在做一个开源项目(c# ASP。网论坛)。目前,大多数数据库访问都是通过在c#中构建内联SQL并调用SQL Server DB来完成的。所以我在试着确定,对于这个特定的项目,哪个是最好的。

到目前为止,我有:

in Code的优点:

更容易维护-不需要运行SQL脚本来更新查询 更容易移植到另一个DB -没有pros到移植

存储Procs的优点:

性能 安全


当前回答

我通常写OO代码。我猜你们大多数人可能也有同感。在这种上下文中,所有业务逻辑(包括SQL查询)都属于类定义,这在我看来是显而易见的。分割逻辑(部分驻留在对象模型中,部分驻留在数据库中)并不比将业务逻辑放到用户界面中更好。

关于存储过程的安全性优势,在前面的回答中已经说了很多。它们分为两大类:

1)限制直接访问数据。这在某些情况下确实很重要,当你遇到这样的情况时,存储过程几乎是你唯一的选择。然而,根据我的经验,这样的情况是例外而不是规则。

2) SQL injection/parametrized queries. This objection is a red herring. Inline SQL - even dynamically-generated inline SQL - can be just as fully parametrized as any stored proc and it can be done just as easily in any modern language worth its salt. There is no advantage either way here. ("Lazy developers might not bother with using parameters" is not a valid objection. If you have developers on your team who prefer to just concatenate user data into their SQL instead of using parameters, you first try to educate them, then you fire them if that doesn't work, just like you would with developers who have any other bad, demonstrably detrimental habit.)

其他回答

我通常写OO代码。我猜你们大多数人可能也有同感。在这种上下文中,所有业务逻辑(包括SQL查询)都属于类定义,这在我看来是显而易见的。分割逻辑(部分驻留在对象模型中,部分驻留在数据库中)并不比将业务逻辑放到用户界面中更好。

关于存储过程的安全性优势,在前面的回答中已经说了很多。它们分为两大类:

1)限制直接访问数据。这在某些情况下确实很重要,当你遇到这样的情况时,存储过程几乎是你唯一的选择。然而,根据我的经验,这样的情况是例外而不是规则。

2) SQL injection/parametrized queries. This objection is a red herring. Inline SQL - even dynamically-generated inline SQL - can be just as fully parametrized as any stored proc and it can be done just as easily in any modern language worth its salt. There is no advantage either way here. ("Lazy developers might not bother with using parameters" is not a valid objection. If you have developers on your team who prefer to just concatenate user data into their SQL instead of using parameters, you first try to educate them, then you fire them if that doesn't work, just like you would with developers who have any other bad, demonstrably detrimental habit.)

我参加的Microsoft TechEd安全会议的建议之一是,通过存储过程进行所有调用,并拒绝直接访问表。这种方法被宣传为提供额外的安全性。我不确定仅仅为了安全性是否值得这样做,但如果您已经在使用存储过程,那么这样做也无妨。

Definitely easier to maintain if you put it in a stored procedure. If there's difficult logic involved that will potentially change in the future it is definitely a good idea to put it in the database when you have multiple clients connecting. For example I'm working on an application right now that has an end user web interface and an administrative desktop application, both of which share a database (obviously) and I'm trying to keep as much logic on the database as possible. This is a perfect example of the DRY principle.

我更喜欢把它们保存在代码中(使用ORM,而不是内联或特别),这样它们就可以被源代码控制覆盖,而不必保存.sql文件。

此外,存储过程本身并不更安全。使用sproc可以像使用内联一样轻松地编写糟糕的查询。参数化内联查询与sproc一样安全。

我想再投一票赞成使用存储过程(尽管在维护和版本控制时它们会带来麻烦)作为一种限制对底层表的直接访问以获得更好的安全性的方法。