问题描述
在 C# 源代码或存储过程中保留 SQL 有哪些优点/缺点?我一直在与朋友讨论我们正在开发的一个开源项目(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 代码存在一个小问题，所以您宁愿......在一个地方更改 proc 或推送代码到所有的网络服务器，重新安装所有的桌面应用程序(clickonce 可能有帮助)在所有的 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 个新的存储过程，还是 4 个新的网络服务器?
So, push 1 new sproc, or 4 new webservers?
在这种情况下， 更容易推送一个新的存储过程，但根据我的经验，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.
更容易审查代码.
你能解释一下吗?我不明白这个.特别是因为存储过程可能不在源代码控制中，因此无法通过基于 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 保存在存储过程与代码中的优缺点是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！