哪种是管理SQL查询的最佳方法? [英] Which is the best way to manage sql queries?

问题描述

这可能是一个愚蠢的问题.
但是请回答我.

这是在代码中使用和操作SQL查询的最佳方法.
我尝试了3种方法，但仍然不知道哪种方法更专业.

1.在需要的时候，通过在C#代码中编写选择，插入，更新，删除查询.

2.通过在项目中创建一个DataSet Item并在DataSet DataTableAdapter中写入相应的选择"，更新"，插入"，删除"查询.

3.通过使用存储过程.

This may be a stupid question.
But please answer me.

Which is the best way to use and manipulate SQL queries in code..??

i have tried 3 methods and still don''t know which one is a more professional approach.

1. By writing select, insert, update, delete queries when ever needed inside the C# code.

2. By creating a DataSet Item in the project and writing corresponding Select, Update, Insert, Delete Queries in the DataSet DataTableAdapter.

3. By Using Stored Procedure.

推荐答案

好，始终建议使用存储过程.当您有多个依赖于同一数据库的应用程序时，存储过程就是黄金.它使您可以一次定义和维护查询逻辑，而不是在多个地方.

Well, using Stored Procedure is always recommended. Stored procedures are gold when you have several applications that depend on the same database. It let''s you define and maintain query logic once, rather than several places.

首选存储过程！ 这是存储过程的一些优点:

在可维护性方面:

可以将参数化查询用于简单/琐碎的操作，例如检索数据或在表中插入数据.如果您具有复杂的(处理)代码，则与内联查询相比，存储过程更易于维护.谁想在同一文件中维护程序源代码和复杂的SQL代码.同样，几乎所有复杂的参数化查询都可以拆分为多个字符串连接.信不信由你，我已经看到了一条超过60行的串联SQL查询.

关于速度:

首先，您可以考虑编译查询所花费的时间，以及创建执行计划所花费的时间(两者有时都比查询的执行时间更长).除非指定，否则存储过程仅被编译一次，并且执行计划被存储，因此服务器的重启不会使它松散.另外，对于查询，不是立即创建执行计划，而是在几次调用同一查询之后(取决于数据库).

在连接方面:

如果与sql服务器的连接速度较慢，并且查询量较大，则使用存储过程可能会更好，因为它们生成的网络流量较少，通常服务器与客户端之间的往返次数也较少.

同样，使用一个相对另一个还有很多其他优点/缺点，但是在大多数情况下，出价是针对存储过程的.

请在这里

1. Stored-Procedures-Optimization-Tips [ ^ ]
2. stored-procedure-optimization-tips [ ^ ]

Prefer Stored procedure!!!
Here are some advantages for Stored procedures:

In terms of maintainability:

One could use parametrized queries for simple/trivial operations, like retrieving data, or inserting data in table. Stored procedures are easier to maintain than inline queries if you have complicated (processing) code. Who would like to maintain program source code mixed with complicated SQL code in the same file. Also, almost all of the parametrized queries that are complex, can be found splitted in multiple string concats. Believe it or not, I''ve seen a SQL query concatenated in over 60 lines length.

In terms of speed:

First, you could think about the time it takes to compile the query and, also, the time it takes to create an execution plan (Both of them are sometimes longer than the execution time of the query). The stored procedures are compiled only once, unless specified, and the execution plan is stored, so a restart of the server wouldn''t loose it. Also, for queries, the execution plans aren''t created right away, but after a few calls of the same query (depending on the databse engine).

In terms of connection:

If you have a slow connection to the sql server, and large queries, you would probably be better off using stored procedures, as they generate less network traffic, and usually less roundtrips between the server and the client.

Also, there are also lot''s of other advantages/disadvantages of using one over another, but the bid would be for stored procedures in most of the cases.

Refer Here

1.Stored-Procedures-Optimization-Tips[^]
2.stored-procedure-optimization-tips[^]

在这里加两分钱.我还认为，在许多情况下，如其他人已经指出的那样，建议使用存储过程.

但是，如果您的查询本质上更具动态性，则将组合查询(甚至整个语句)的元素传递给存储过程可能没有意义.这将带来一个问题，其中动态逻辑被划分为两个不同的层，即应用程序和数据库.在这种情况下，我认为在您的应用程序中创建查询并针对数据库执行查询是更可取的方式.

Adding my two cents here. I also think that using stored procedures is advicable in many situations as others have already stated.

However, if your queries are more dynamic in nature then it may not make sense to pass the elements of a composed query (or even the whole statement) to a stored procedure. This would introduce a problem where the dynamic logic is separated to two different layers, the application and the database. In this kind of situation I feel that creating the queries in your application and executing them against the database is more preferred way.

这篇关于哪种是管理SQL查询的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！