内联SQL查询的最佳实践

I'm working with an asp.net website that uses a lot of inline SQL queries... and I'm wondering if it is best to create the inline queries on the fly:

我正在使用一个使用大量内联SQL查询的asp.net网站......我想知道最好是在运行时创建内联查询：

int i = 500;

    using (SqlConnection conn = new SqlConnection(connStr))
    {
        SqlCommand com = new SqlCommand(conn);
        ...
        com.CommandText = "select from table where column < @parameter";
        ...
    }

Or to have a class to hold all queries needed for the application. Something like this:

或者有一个类来保存应用程序所需的所有查询。像这样的东西：

class SqlQueries
{
    private string query1 = 
          "select * from tblEmployees where EmployeeName = @EmployeeName";

    private string query2 = 
          "select * from tblVacation where EmployeeName = @EmployeeName";

    public string Query(string s) 
    { 
        string str = string.Empty;

            switch (s) 
            {
                case "query1":
                    str = query1; 
                    break; 
                case "query2":
                    str = query2; 
                    break;
            }     

    return str;    

    }
}

Thank you!

谢谢！

8 个解决方案

#1

I've used a lot of ADO.NET queries in my day and I have always used the first method. The second method is an interesting idea, but it might be cumbersome to edit those queries if you are at another place in the code that uses it. It also makes it harder to see what a query is doing at a particular place in code. Example:

我在我的日子里使用了很多ADO.NET查询，并且我一直使用第一种方法。第二种方法是一个有趣的想法，但如果您在使用它的代码中的另一个位置，编辑这些查询可能会很麻烦。它还使得在代码中的特定位置查看查询的内容变得更加困难。例：

string sql = "Update User set age = @age where UserId = @UserId";

tells a developer what is happening, while:

告诉开发人员发生了什么，同时：

string sql = SqlQueries.Query("updateAge");

Leaves questions about what table/columns are being updated. Also, with the first one, you know exactly what params need to be added.

留下有关正在更新的表/列的问题。另外，对于第一个，您确切地知道需要添加什么参数。

If you are writing this query in several places that might change things

如果您在几个可能会改变的地方编写此查询

#2

It's not terrible to put the literal directly in the method, as long as you always call that same method every time you want to run that query. However, if you are going to copy that string literal into multiple places in your code, then a constant is definitely preferred. However, rather than taking a string as the argument for the Query method in your second example, it should take an enumeration value.

将文字直接放在方法中并不可怕，只要您每次要运行该查询时始终调用相同的方法即可。但是，如果要将该字符串文字复制到代码中的多个位置，则绝对优先使用常量。但是，不是在第二个示例中将字符串作为Query方法的参数，而是应该使用枚举值。

However, if you are using the second method you described, I would ask you why you don't just start using stored procedures instead?

但是，如果您使用的是您描述的第二种方法，我会问您为什么不开始使用存储过程？

#3

I would recommend using stored procedures as a much better solution to your problem than hard coded in-line queries. If you have to change the query at a later date, you don't have to rebuild your application, so bugs in your query can be fixed without needing to deploy the whole application. The 2nd option you have there is a maintenance nightmare waiting to happen. It all looks so nice when you have one or two queries in it, but that begins to look a bit more ugly when you have tens or hundreds in there. Your code looks like it's c#, so I would recommend checking out the Microsoft Enterprise Library,

我建议使用存储过程作为比硬编码内联查询更好的解决方案。如果您必须在以后更改查询，则不必重建应用程序，因此可以修复查询中的错误，而无需部署整个应用程序。你在那里的第二个选择是维护噩梦等待发生。当你有一个或两个查询时，它看起来都很漂亮，但当你有数十或数百个查询时，这看起来有点难看。您的代码看起来像是c＃，所以我建议您查看Microsoft Enterprise Library，

http://msdn.microsoft.com/en-us/library/ff632023.aspx

You might need to download a different version depending on what version of the .NET framework you are developing with.

您可能需要下载不同的版本，具体取决于您正在开发的.NET框架的版本。

#4

I think it's OK to have the queries "inline" as long as they aren't repeated in several places. If that starts to happen, then you might want to start creating Query classes.

我认为只要不在多个地方重复查询就可以“查询内联”。如果这种情况开始发生，那么您可能希望开始创建Query类。

#5

In both the cases you are ultimately building/fetching String which you will pass to CommandText. So there would be no such difference. Only thing you need to consider in your case is how you would maintain the code or how will other people understand your code.

在这两种情况下，您最终构建/获取将传递给CommandText的String。所以不会有这样的差异。在您的情况下，您唯一需要考虑的是如何维护代码或其他人如何理解您的代码。

#6

If you're going to use inline SQL at least don't put it in the web page code because it will be painful when you make database changes to know what it affects. Putting all the queries in one classes might be a bit disorganized, but if you grouped them by functional classes (like manager classes for your business objects) it might be easier to deal with.

如果你打算使用内联SQL，至少不要把它放在网页代码中，因为当你进行数据库更改以了解它会影响什么时会很痛苦。将所有查询放在一个类中可能有点混乱，但如果按功能类（如业务对象的管理器类）对它们进行分组，则可能更容易处理。

#7

If you absolutely have to have "inline" sql as opposed to stored procedures (and I have done this for utility type applications that merely interact with a database, rather than own it), I would suggest putting your SQL into an embedded resource file. This will make your queries easier to maintain (although you will still need to re-compile your app to make changes).

如果你必须拥有“内联”sql而不是存储过程（我已经为只与数据库交互的实用程序类型应用程序执行了此操作，而不是拥有它），我建议将SQL放入嵌入式资源文件中。这将使您的查询更易于维护（尽管您仍需要重新编译您的应用程序以进行更改）。

#8

If your queries are longer than a line or two, you should consider putting them in their own .sql file. Set the build action on the file to embedded resource, and access it with a call to GetManifestResourceStream(). That way, you're elevating your sql to the status of a proper language, with syntax highlighting, validation and intellisense (when you connect VS to your DB). Needless to say, this hugely facilitates maintenance.

如果您的查询超过一行或两行，则应考虑将它们放在自己的.sql文件中。将文件的构建操作设置为嵌入式资源，并通过调用GetManifestResourceStream（）来访问它。这样，您就可以将sql升级为适当语言的状态，具有语法突出显示，验证和智能感知（当您将VS连接到数据库时）。不用说，这极大地方便了维护。

If all this seems like a hassle, grab my VS extension, QueryFirst. Create your .sql files with the provided template and they will automatically be wired up for compilation. But you won't care because you'll just access the queries via the generated classes.

如果这一切看起来都很麻烦，请抓住我的VS扩展，QueryFirst。使用提供的模板创建.sql文件，它们将自动连接进行编译。但是你不会在乎，因为你只需通过生成的类访问查询。

Sql is the only computer language I can think of that we accept to see chopped up in string literals. It ought to be a scandal.

Sql是我能想到的唯一一种计算机语言，我们接受这种语言在字符串文字中被切断。这应该是一个丑闻。

#1

string sql = "Update User set age = @age where UserId = @UserId";