我的C＃windows应用程序的内联SQL或存储过程？ [英] Inline SQL or stored procs for my C# windows app?

问题描述

我是存储过程的人，但我公司的很多人在他们的应用程序中使用内联

sql，有时将sql放在文本文件中，有时候

硬编码吧。他们没有从procs中看到太多的好处，并且无论如何说，他们是特定于db的 - 如果我们从SQL Server更改为Oracle

一天会怎么样？


你说专家怎么说？这是其中一个没有正确答案

问题吗？


谢谢，


Burt

I''m a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don''t see much benefit from procs, and say anyway
they''re are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

推荐答案

通过SQL Server的RPC（存储过程）路径比

语言快得多（"在大多数情况下，文本SQL）路径。


此外，你可以在数据库中做一些非常复杂的事情

- 交易， UDF，触发器，多语句触发器，

表变量，游标等难以或不可能使用文本sql进行
即使参数化也是如此。

我的2美分。

彼得


-

网站： http://www.eggheadcafe.com

UnBlog： http://petesbloggerama.blogspot.com

短网址&更多： http://ittyurl.net


Burt写道：

The RPC (stored proc) path through SQL Server is a lot faster than the
language ("text SQL") path in most cases.

In addition, there are a number of very sophisticated things you can do
inside the database - transactions, UDFs, triggers, multistatement procs,
Table variables, cursors and so on that are difficult or impossible to do
with text sql even if it is parameterized.
My 2 cents.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Burt" wrote:

我是一个存储过程的人，但我公司的很多人在他们的应用程序中使用内联

sql把sql放在一个文本文件中，有时候会把它硬编码。他们没有从procs中看到太多的好处，并且无论如何说，他们是特定于db的 - 如果我们从SQL Server更改为Oracle

一天会怎么样？


你说专家怎么说？这是其中一个没有正确答案

问题吗？


谢谢，


Burt

I''m a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don''t see much benefit from procs, and say anyway
they''re are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

Burt，


好​​吧，有点像是没有正确答案之一。的问题。它总是

取决于具体情况。


如果你有一个会被一遍又一遍地调用的例程，

然后真的没有理由不在存储过程中使用它。


是的，如果你移动到另一个数据库，你将不得不迁移它们，但
$ b $老实说，将存储过程从一个数据库移动到另一个数据库是多么困难？如果你有动态的sql，那么你将遇到同样的问题，你将不得不改变动态的sql生成器（或者字符串为/ b $ b $）确保你没有使用任何特定于数据库的功能。


仅仅因为你使用动态sql并不意味着你不是使用数据库的b $ b动态sql中的特定功能。


老实说，我会使用Microsoft提供的企业框架中的数据访问应用程序块

。这将有助于减少使用存储过程并从一个数据库更改为另一个数据库
（您不必担心更改从<​​br /调用存储过程的方式） >
托管代码，例如），你可以使用它公开的对象模型

来生成动态的sql，它可以在任何具有
$ b $的数据库中工作b DAAB的提供者，假设你没有在你的SQL中使用任何特定于数据库的功能

。


就个人而言，我认为动态sql有一个地方，但对于已经重复调用的已建立的逻辑，存储过程是可行的方法。

对我来说，我会使用动态sql来保存对象模型

到数据库，但没有对

数据库执行多语句逻辑。


希望这有帮助。< br $> b $ b -

- Nicholas Paldino [.NET / C＃MVP]

- MV * @水疗m.guard.caspershouse.com

" Burt" < bu ******* @ yahoo.com写信息

新闻：11 ********************** @ q75g2000hsh.googlegr oups.com ...

Burt,

Well, it kind of is one of those "no right answer" questions. It always
depends on context.

If you have a routine that is going to be called over and over again,
then there really is no reason to not have it in a stored procedure.

Yes, you will have to migrate these if you move to another database, but
honestly, how hard is it to move a stored procedure from one database to
another? If you have dynamic sql, then you are going to run into the same
problem, you will have to change the dynamic sql generator (or strings for
the sql) to make sure you are not using any database-specific features.

Just because you are using dynamic sql doesn''t mean that you are not
using database-specific features in the dynamic sql.

Honestly, I would use something like the Data Access Application Block
in the Enterprise Framework that is offered by Microsoft. It will help
mitigate using stored procedures and changing from one database to another
(you won''t have to worry about changing how you call stored procedures from
managed code, for example), and you can use the object model exposed by it
to generate dynamic sql that will work across any database that has a
provider for the DAAB, assuming you don''t use any database-specific features
in your SQL.

Personally, I think that dynamic sql has a place, but for established
logic which will be called repeatedly, stored procedures are the way to go.
For me, I would use dynamic sql for things like persisting an object model
to the database, but not performing multi-statement logic against the
database.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Burt" <bu*******@yahoo.comwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...

我是存储过程的人，但我公司的很多人都使用内联

在他们的应用程序中使用sql，有时将sql放在一个文本文件中，有时

硬编码。他们没有从procs中看到太多的好处，并且无论如何说，他们是特定于db的 - 如果我们从SQL Server更改为Oracle

一天会怎么样？


你说专家怎么说？这是其中一个没有正确答案

问题吗？


谢谢，


Burt

I''m a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don''t see much benefit from procs, and say anyway
they''re are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

Burt写道：

Burt wrote:

我是一个存储过程的人，但是我公司的很多人在他们的应用程序中使用内联

sql，有时将sql放在一个文本文件中，有时

硬编码。他们没有从procs中看到太多的好处，并且无论如何说，他们是特定于db的 - 如果我们从SQL Server更改为Oracle

一天会怎么样？


你说专家怎么说？这是其中一个没有正确答案

的问题吗？


谢谢，


Burt

I''m a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don''t see much benefit from procs, and say anyway
they''re are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

由于SQL注入成为一些数据库的大问题，我会建议坚持使用

存储过程。所有你需要做的就是运行存储过程你可以禁用

其他查询方法。

-

问候JJ（UWA）

Due to SQL injection becoming a large problem to some databases, i would
suggest sticking with
stored procs. As all you have to do is run the stored procs you can disable
other query methods.
--
Regards JJ (UWA)

这篇关于我的C＃windows应用程序的内联SQL或存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！