C# 将 SQL 保留在存储过程与代码中的优缺点是什么

What are the pros and cons to keeping SQL in Stored Procs versus Code

提问by Guy

What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to establish which, for this particular project, would be best.

将 SQL 保留在 C# 源代码或存储过程中的优点/缺点是什么？我一直在与我们正在开展的开源项目（C# ASP.NET 论坛）上的一位朋友讨论这个问题。目前，大部分数据库访问是通过在 C# 中构建 SQL 内联并调用 SQL Server DB 来完成的。所以我试图确定哪个对于这个特定的项目是最好的。

So far I have:

到目前为止，我有：

Advantages for in Code:

代码中的优点：

• Easier to maintain - don't need to run a SQL script to update queries
• Easier to port to another DB - no procs to port

• 更易于维护 - 不需要运行 SQL 脚本来更新查询
• 更容易移植到另一个数据库 - 没有移植的过程

Advantages for Stored Procs:

存储过程的优点：

• Performance
• Security

• 表现
• 安全

采纳答案by Orion Edwards

I am not a fan of stored procedures

我不喜欢存储过程

Stored Procedures are MORE maintainable because: * You don't have to recompile your C# app whenever you want to change some SQL

存储过程更易于维护，因为： * 当您想更改某些 SQL 时，您不必重新编译 C# 应用程序

You'll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can 'transparently' change the SQL out from underneath your app is pretty small on the whole

无论如何，当数据类型发生变化时，您最终还是要重新编译它，或者您想返回一个额外的列，或者其他什么。总体而言，您可以“透明地”从应用程序下方更改 SQL 的次数非常少

• You end up reusing SQL code.

• 您最终会重用 SQL 代码。

Programming languages, C# included, have this amazing thing, called a function. It means you can invoke the same block of code from multiple places! Amazing! You can then put the re-usable SQL code inside one of these, or if you want to get really high tech, you can use a library which does it for you. I believe they're called Object Relational Mappers, and are pretty common these days.

编程语言，包括 C#，有一个神奇的东西，叫做函数。这意味着您可以从多个地方调用相同的代码块！惊人的！然后，您可以将可重用的 SQL 代码放入其中之一，或者如果您想获得真正的高科技，您可以使用一个为您做这件事的库。我相信它们被称为对象关系映射器，现在非常普遍。

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

当您尝试构建可维护的应用程序时，代码重复是最糟糕的事情！

Agreed, which is why storedprocs are a bad thing. It's much easier to refactor and decompose (break into smaller parts) code into functions than SQL into... blocks of SQL?

同意，这就是为什么存储过程是一件坏事。将代码重构和分解（分解成更小的部分）成函数比将 SQL 分解成……SQL 块要容易得多？

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

你有 4 个网络服务器和一堆使用相同 SQL 代码的 Windows 应用程序现在你意识到 SQl 代码有一个小问题，所以你宁愿......在一个地方更改 proc 或将代码推送到所有网络服务器，在所有 Windows 框上重新安装所有桌面应用程序（单击一次可能会有所帮助）

Why are your windows apps connecting directly to a central database? That seems like a HUGE security hole right there, and bottleneck as it rules out server-side caching. Shouldn't they be connecting via a web service or similar to your web servers?

为什么您的 Windows 应用程序直接连接到中央数据库？这似乎是一个巨大的安全漏洞，并且因为它排除了服务器端缓存而成为瓶颈。他们不应该通过网络服务或类似于您的网络服务器进行连接吗？

So, push 1 new sproc, or 4 new webservers?

那么，推送 1 个新的 sproc 还是 4 个新的网络服务器？

In this case it iseasier to push one new sproc, but in my experience, 95% of 'pushed changes' affect the code and not the database. If you're pushing 20 things to the webservers that month, and 1 to the database, you hardly lose much if you instead push 21 things to the webservers, and zero to the database.

在这种情况下，推送一个新的 sproc会更容易，但根据我的经验，95% 的“推送更改”会影响代码而不是数据库。如果你在那个月向网络服务器推送 20 件东西，向数据库推送 1 件东西，如果你改为将 21 件东西推送到网络服务器，而零件推送到数据库，你几乎不会损失太多。

More easily code reviewed.

更容易审查代码。

Can you explain how? I don't get this. Particularly seeing as the sprocs probably aren't in source control, and therefore can't be accessed via web-based SCM browsers and so on.

你能解释一下怎么做吗？我不明白这个。特别是看到 sprocs 可能不在源代码控制中，因此无法通过基于 Web 的 SCM 浏览器等访问。

More cons:

更多缺点：

Storedprocs live in the database, which appears to the outside world as a black box. Simple things like wanting to put them in source control becomes a nightmare.

Storedprocs 存在于数据库中，它在外界看来是一个黑匣子。想要将它们置于源代码控制中之类的简单事情变成了一场噩梦。

There's also the issue of sheer effort. It might make sense to break everything down into a million tiersif you're trying to justify to your CEO why it just cost them 7 million dollars to build some forums, but otherwise creating a storedproc for every little thing is just extra donkeywork for no benefit.

还有一个纯粹的努力的问题。如果你试图向你的 CEO 证明为什么他们只花了 700 万美元来建立一些论坛，那么将所有内容分解成一百万层可能是有意义的，否则为每件小事创建一个存储过程只是额外的麻烦事益处。

回答by Stu

The performance advantage for stored procedures is often negligable.

存储过程的性能优势通常可以忽略不计。

More advantages for stored procedures:

存储过程的更多优势：

• Prevent reverse engineering (if created With Encryption, of course)
• Better centralization of database access
• Ability to change data model transparently (without having to deploy new clients); especially handy if multiple programs access the same data model

• 防止逆向工程（当然，如果使用加密创建）
• 更好地集中数据库访问
• 能够透明地更改数据模型（无需部署新客户端）；如果多个程序访问相同的数据模型，则特别方便

回答by mbillard

Stored procedures.

存储过程。

If an error slips or the logic changes a bit, you do not have to recompile the project. Plus, it allows access from different sources, not just the one place you coded the query in your project.

如果出现错误或逻辑稍有变化，则不必重新编译项目。此外，它允许从不同的来源访问，而不仅仅是您在项目中对查询进行编码的一个地方。

I don't think it is harder to maintain stored procedures, you should not code them directly in the database but in separate files first, then you can just run them on whatever DB you need to set-up.

我不认为维护存储过程更难，你不应该直接在数据库中编码它们，而是首先在单独的文件中，然后你可以在你需要设置的任何数据库上运行它们。

回答by John Sheehan

I prefer keeping in them in code (using an ORM, not inline or ad-hoc) so they're covered by source control without having to deal with saving out .sql files.

我更喜欢将它们保留在代码中（使用 ORM，而不是内联或临时），因此它们由源代码控制覆盖，而无需处理保存 .sql 文件。

Also, stored procedures aren't inherently more secure. You can write a bad query with a sproc just as easily as inline. Parameterized inline queries can be just as secure as a sproc.

此外，存储过程本身并不是更安全。您可以像内联一样轻松地使用 sproc 编写错误的查询。参数化的内联查询可以像 sproc 一样安全。

回答by Eric Z Beard

This is being discussed on a few other threads here currently. I'm a consistent proponent of stored procedures, although some good arguments for Linq to Sql are being presented.

目前正在这里的其他一些主题上讨论这一点。我是存储过程的一贯支持者，尽管提出了一些关于 Linq to Sql 的很好的论据。

Embedding queries in your code couples you tightly to your data model. Stored procedures are a good form of contractual programming, meaning that a DBA has the freedom to alter the data model and the code in the procedure, so long as the contract represented by the stored procedure's inputs and outputs is maintained.

在代码中嵌入查询将您与数据模型紧密结合。存储过程是契约式编程的一种很好的形式，这意味着 DBA 可以自由地改变过程中的数据模型和代码，只要由存储过程的输入和输出表示的契约得到维护。

Tuning production databases can be extremely difficult when the queries are buried in the code and not in one central, easy to manage location.

当查询隐藏在代码中而不是集中在一个易于管理的位置时，调整生产数据库可能会非常困难。

[Edit] Here is another current discussion

[编辑] 这是当前的另一个讨论

回答by Rob Allen

Advantages for in Code:

• Easier to maintain - don't need to run a SQL script to update queries
• Easier to port to another DB - no procs to port

代码中的优点：

• 更易于维护 - 不需要运行 SQL 脚本来更新查询
• 更容易移植到另一个数据库 - 没有移植的过程

Actually, I think you have that backwards. IMHO, SQL in code is pain to maintain because:

事实上，我认为你有倒退。恕我直言，代码中的 SQL 很难维护，因为：

• you end up repeating yourself in related code blocks
• SQL isn't supported as a language in many IDE's so you have just a series of un-error checked strings performing tasks for you
• changes in a data type, table name or constraint are far more prevalent than swapping out an entire databases for a new one
• your level of difficulty increases as your query grows in complexity
• and testing an inline query requires building the project

• 你最终会在相关的代码块中重复自己
• 许多 IDE 不支持 SQL 作为一种语言，因此您只有一系列未检查错误的字符串为您执行任务
• 数据类型、表名或约束的更改比将整个数据库换成新数据库要普遍得多
• 随着查询的复杂性增加，您的难度也会增加
• 并测试内联查询需要构建项目

Think of Stored Procs as methods you call from the database object - they are much easier to reuse, there is only one place to edit and in the event that you do change DB providers, the changes happen in your Stored Procs and not in your code.

将存储过程视为您从数据库对象调用的方法 - 它们更容易重用，只有一个地方可以编辑，并且如果您确实更改了数据库提供程序，更改将发生在您的存储过程中而不是您的代码中.

That said, the performance gains of stored procs is minimal as Stu said before me and you can't put a break point in a stored procedure (yet).

也就是说，存储过程的性能提升是最小的，正如 Stu 在我之前所说的那样，你不能在存储过程中放置​​断点（目前）。

回答by Eugene Katz

One of the suggestions from a Microsoft TechEd sessions on security which I attended, to make all calls through stored procs and deny access directly to the tables. This approach was billed as providing additional security. I'm not sure if it's worth it just for security, but if you're already using stored procs, it couldn't hurt.

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

回答by Andrew G. Johnson

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.

如果你把它放在存储过程中，肯定更容易维护。如果涉及的复杂逻辑将来可能会发生变化，那么当您有多个客户端连接时，将其放入数据库中绝对是个好主意。例如，我现在正在开发一个具有最终用户 Web 界面和管理桌面应用程序的应用程序，这两个应用程序共享一个数据库（显然），我试图在数据库上保留尽可能多的逻辑。这是DRY 原则的完美示例。

回答by SQLMenace

Think of it this way

这样想

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

你有 4 个网络服务器和一堆使用相同 SQL 代码的 Windows 应用程序现在你意识到 SQl 代码有一个小问题，所以你宁愿......在一个地方更改 proc 或将代码推送到所有网络服务器，在所有 Windows 框上重新安装所有桌面应用程序（单击一次可能会有所帮助）

I prefer stored procs

我更喜欢存储过程

It is also easier to do performance testing against a proc, put it in query analyzer set statistics io/time on set showplan_text on and voila

对 proc 进行性能测试也更容易，将它放在查询分析器 set statistics io/time on set showplan_text on 中，瞧

no need to run profiler to see exactly what is being called

无需运行探查器即可准确查看正在调用的内容

just my 2 cents

只有我的 2 美分

回答by Keith

You list 2 pro-points for sprocs:

您为 sproc 列出了 2 个专业点：

Performance - not really. In Sql 2000 or greater the query plan optimisations are pretty good, and cached. I'm sure that Oracle etc do similar things. I don't think there's a case for sprocs for performance any more.

性能 - 不是真的。在 Sql 2000 或更高版本中，查询计划优化非常好，并且被缓存。我确信 Oracle 等也做类似的事情。我认为不再需要 sproc 来提高性能。

Security? Why would sprocs be more secure? Unless you have a pretty unsecured database anyway all the access is going to be from your DBAs or via your application. Always parametrise all queries - never inline something from user input and you'll be fine.

安全？为什么 sproc 会更安全？除非您有一个非常不安全的数据库，否则所有访问都将来自您的 DBA 或通过您的应用程序。始终参数化所有查询 - 永远不要从用户输入中内联某些内容，您会没事的。

That's best practice for performance anyway.

无论如何，这是性能的最佳实践。

Linq is definitely the way I'd go on a new project right now. See this similar post.

Linq 绝对是我现在进行新项目的方式。看到这个类似的帖子。

回答by Rick

I fall on the codeside. We build data access layer that's used by all all the apps (both web and client), so it's DRY from that perspective. It simplifies the database deployment because we just have to make sure the table schema's are correct. It simplifies code maintenance because we don't have to look at source code and the database.

我属于代码方面。我们构建了所有应用程序（Web 和客户端）都使用的数据访问层，因此从这个角度来看它是 DRY 的。它简化了数据库部署，因为我们只需要确保表架构是正确的。它简化了代码维护，因为我们不必查看源代码和数据库。

I don't have much problem with the tight coupling with the data model because I don't see where it's possible to really break that coupling. An application and its data are inherently coupled.

我对与数据模型的紧密耦合没有太大问题，因为我看不到真正打破这种耦合的地方。应用程序及其数据本质上是耦合的。