将 SQL 保留在存储过程与代码中的优缺点是什么 [英] What are the pros and cons to keeping SQL in Stored Procs versus Code

问题描述

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

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 脚本来更新查询
• 更容易移植到另一个数据库 - 没有移植过程

存储过程的优势:

• 性能
• 安全

推荐答案

我不喜欢存储过程

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

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

当数据类型改变时，你最终还是会重新编译它，或者你想返回一个额外的列，或者其他什么.总体而言，您可以透明地"从应用程序下方更改 SQL 的次数非常少

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 代码.

编程语言，包括 C#，有一个神奇的东西，叫做函数.这意味着您可以从多个地方调用相同的代码块！惊人的！然后，您可以将可重用的 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.

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

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

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

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?

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

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

为什么您的 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?

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

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

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

In this case it is easier 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 可能不在源代码控制中，因此无法通过基于 Web 的 SCM 浏览器等访问.

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.

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

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.

还有一个纯粹的努力的问题.将所有内容分解为 百万层 如果你想向你的 CEO 证明为什么他们只花了 700 万美元来建立一些论坛，但否则为每件小事创建一个存储过程只是额外的工作没有任何好处.

There's also the issue of sheer effort. It might make sense to break everything down into a million tiers if 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.

这篇关于将 SQL 保留在存储过程与代码中的优缺点是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！