你把 SQL 语句放在你的 c# 项目中的什么地方？

Where do you put SQL Statements in your c# projects?

提问by Steve

I will likely be responsible for porting a vb6 application to c#. This application is a windows app that interacts with an access db. The data access is encapsulated in basic business objects. One class for one table basically. The existing vb6 business objects read and write to the DB via DAO. I have written DALs and ORMs a few times before but they all targeted SQL Server only. This one will need to target access and sql server. In previous projects, I would place the SQL strings in the private parts of the business object and maybe move the redundant sql code like connecting, creating command, in into a common base class to reduce the code.

我可能会负责将 vb6 应用程序移植到 c#。此应用程序是一个与访问数据库交互的 Windows 应用程序。数据访问被封装在基本业务对象中。基本上一桌一班。现有的 vb6 业务对象通过 DAO 读取和写入数据库。我之前写过几次 DAL 和 ORM，但它们都只针对 SQL Server。这将需要目标访问和 sql server。在之前的项目中，我会将 SQL 字符串放在业务对象的私有部分，并可能将连接、创建命令等冗余 sql 代码移动到一个公共基类中以减少代码。

This time, i'm thinking about writing the SQL strings into a .settings file or some other key/value type text file. I would then write a sql utility to edit this file and allow me to run and test the parameterized queries. These queries would be referenced by name in the business object instead of embedding the sql into code.

这一次，我正在考虑将 SQL 字符串写入 .settings 文件或其他一些键/值类型的文本文件。然后我将编写一个 sql 实用程序来编辑此文件，并允许我运行和测试参数化查询。这些查询将在业务对象中按名称引用，而不是将 sql 嵌入到代码中。

I know a standard approach is to create a DAL for each targeted database and have the configuration state which DAL to use. I really don't want to create the two DAL classes for each database. It seems like it would be less code if I just referenced the correct query by keyname and have the proper type of connection.

我知道一种标准方法是为每个目标数据库创建一个 DAL，并拥有要使用的 DAL 的配置状态。我真的不想为每个数据库创建两个 DAL 类。如果我只是通过键名引用正确的查询并具有正确的连接类型，那么代码似乎会更少。

So, are you guys doing things like this? How would or have you approached this problem? What works best for you?

那么，你们在做这样的事情吗？你将如何或已经如何解决这个问题？什么最适合你？

Thanks!

谢谢！

采纳答案by marc_s

Well, there's a lot of options - so it really depends on what your most pressing needs are :-)

嗯，有很多选择 - 所以这真的取决于你最紧迫的需求是什么:-)

One approach might be to create SQL statements as text files inside your VS solution, and mark them as "embedded resource" in the "build action". That way, the SQL is included in your resulting assembly, and can be retrieved from it at runtime using the ResourceManifestStream of the .NET framework:

一种方法可能是在 VS 解决方案中将 SQL 语句创建为文本文件，并在“构建操作”中将它们标记为“嵌入资源”。这样，SQL 就包含在生成的程序集中，并且可以在运行时使用 .NET 框架的 ResourceManifestStream 从中检索：

private string LoadSQLStatement(string statementName)
{
    string sqlStatement = string.Empty;

    string namespacePart = "ConsoleApplication1";
    string resourceName = namespacePart + "." + statementName;

    using(Stream stm = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
    {
        if (stm != null)
        {
            sqlStatement = new StreamReader(stm).ReadToEnd();
        }
    }

    return sqlStatement;
}

You need to replace "ConsoleApplication1" with your actual namespace, in which the sql statement files reside. You need to reference them by means of the fully qualified name. Then you can load your SQL statement with this line:

您需要将“ConsoleApplication1”替换为您的实际命名空间，sql 语句文件驻留在该命名空间中。您需要通过完全限定名称来引用它们。然后你可以用这一行加载你的 SQL 语句：

string mySQLStatement = LoadSQLStatement("MySQLStatement.sql");

This however makes the queries rather "static", e.g. you cannot configure and change them at runtime - they're baked right into the compiled binary bits. But on the other hand, in VS, you have a nice clean separation between your C# program code, and the SQL statements.

然而，这使得查询相当“静态”，例如，您无法在运行时配置和更改它们 - 它们被直接烘焙到已编译的二进制位中。但另一方面，在 VS 中，C# 程序代码和 SQL 语句之间有很好的清晰分离。

If you need to be able to possibly tweak and change them at runtime, I'd put them into a single SQL table which contains e.g. a keyword and the actual SQL query as fields. You can then retrieve them as needed, and execute them. Since they're in the database table, you can also change, fix, amend them at will - even at runtime - without having to re-deploy your whole app.

如果您需要能够在运行时调整和更改它们，我会将它们放入单个 SQL 表中，其中包含例如关键字和作为字段的实际 SQL 查询。然后，您可以根据需要检索它们并执行它们。由于它们在数据库表中，您还可以随意更改、修复、修改它们 - 即使在运行时 - 无需重新部署整个应用程序。

Marc

马克

回答by Miserable Variable

I'll tell where I won't put it ever, something I saw done in some code I inherited. It was in Java, but applies to any language

我会告诉我永远不会把它放在哪里，这是我在继承的一些代码中看到的。它在 Java 中，但适用于任何语言

• A base class that declared protected static member variables for for SQL statements, inited to null, with a get method that returns individual SQL statements

• A sub class for each supported database server, with an init method that assigns to the base class member variables

• Several DA classes that use the base class method to retrieve SQL statements

• The application start-up class with the responsibility to create the correct sub-class object and call its init method

• 为 SQL 语句声明受保护的静态成员变量的基类，初始化为 null，具有返回单个 SQL 语句的 get 方法

• 每个受支持的数据库服务器的子类，具有分配给基类成员变量的 init 方法

• 几个使用基类方法检索SQL语句的DA类

• 应用程序启动类，负责创建正确的子类对象并调用其 init 方法

I will also not go into explaining why I will not do this ever :-)

我也不会解释为什么我永远不会这样做:-)

回答by Mike

One method we used is to have a class that would connect to the DB and methods to call procedures and in the method parameter you would provide the procedure name. so all the SQL code is in the procedure. we would use overloads for the different return types

我们使用的一种方法是让一个类连接到数据库和调用过程的方法，并在方法参数中提供过程名称。所以所有的 SQL 代码都在程序中。我们将对不同的返回类型使用重载

class ConnectToSQL()
{
        //connectSql code (read from setting file i assume)

        XMLDataDocument runProcedure(string procedureName);
        int runProcedure(string procedureName);

        //etc....
}

回答by Matt Kocaj

LINQ to DataSetsounds like the way to go for you.

LINQ to DataSet听起来很适合您。

If you havent used the .NET 3.5 before / LINQ then you're in for a treat. LINQ will save you writing your raw sql in string literals and provide you with a more logical way to creating querys.

如果您在使用 LINQ 之前还没有使用过 .NET 3.5，那么您就大功告成了。LINQ 将节省您在字符串文字中编写原始 sql 的工作，并为您提供一种更合乎逻辑的方式来创建查询。

Anyway, check this link out for using LINQ on Access databases - http://msdn.microsoft.com/en-us/library/bb386977.aspx

无论如何，请查看此链接以在 Access 数据库上使用 LINQ - http://msdn.microsoft.com/en-us/library/bb386977.aspx

回答by Andrey Shchekin

When I really need it, I put the queries into individual *.sql files, then include them into Resources.resx. There is a 'Files' section in it, which allows you to include Embedded Resource files.

当我真的需要它时，我将查询放入单独的 *.sql 文件中，然后将它们包含到 Resources.resx 中。其中有一个“文件”部分，允许您包含嵌入式资源文件。

After that, I can use generated Resources.MyQuery property which both guarantees that resource exists and saves me from writing a custom resource load method.

之后，我可以使用生成的 Resources.MyQuery 属性，它既能保证资源存在，又能避免我编写自定义资源加载方法。

回答by Maksym Gontar

If i'd had to create application for both SQL and Access, I'd use some IDAL interface, DALCommon with common functionality implementation and separate DALSql and DALAccess, inherited from DALCommon, with some specific stuff, like exceptions, transactions handling, security etc.
I used to keep stored procedure names or queries in resource files.

如果我必须为 SQL 和 Access 创建应用程序，我会使用一些 IDAL 接口，带有通用功能实现的 DALCommon 和单独的 DALSql 和 DALAccess，从 DALCommon 继承而来，带有一些特定的东西，如异常、事务处理、安全性等.
我曾经在资源文件中保留存储过程名称或查询。

回答by Mike

Sometimes, like with custom reporting apps, you really need to embrace the impedance mismatch, and give special importance to the SQL. In these cases I recommend the following: For each module that contains SQL strings, create a single static "SQL" class to hold them all. Some of the SQL strings will likely require parameters, so be consistent and put each string behind it's own static method.

有时，就像自定义报告应用程序一样，您确实需要接受阻抗不匹配，并特别重视 SQL。在这些情况下，我建议如下： 对于每个包含 SQL 字符串的模块，创建一个静态“SQL”类来保存它们。一些 SQL 字符串可能需要参数，所以要保持一致并将每个字符串放在它自己的静态方法后面。

I only do this for the occasional custom reporting app, but it always works out great and feels refreshing and liberating. And it's quite nice to come back months later to make an enhancement, and find all of the SQL waiting for you in a single SQL.cs file. Just by reading that one file, it all comes back, and often this is the only file that needs to be changed.

我只为偶尔的自定义报告应用程序这样做，但它总是很好用，让人耳目一新和解放。而且很高兴几个月后回来进行改进，并在单个 SQL.cs 文件中找到所有等待您的 SQL。只需读取那个文件，一切就会回来，而且通常这是唯一需要更改的文件。

I don't see a need in these cases for hiding the SQL in resources or elsewhere. When SQL is important, then it's important. Interestingly, more and more developers are now freely mixing SQL with C#, including I believe this site, because essentially, that's what LINQ is.

在这些情况下，我认为不需要将 SQL 隐藏在资源或其他地方。当 SQL 很重要时，它也很重要。有趣的是，现在越来越多的开发人员自由地将 SQL 与 C# 混合使用，包括我相信这个站点，因为本质上，这就是 LINQ。

Finally, as always, make sure you are not susceptible to SQL injection attacks. Especially if user input is involved, make sure you are using some kind of parameterization and that you are not using string concatenation.

最后，与往常一样，确保您不易受到 SQL 注入攻击。特别是如果涉及用户输入，请确保您使用某种参数化并且您没有使用字符串连接。

回答by user2337812

Embedding solutions shown above may not work if SQL Query has a "where" cause like , but for the same Query the next run needs PropertyID='113' as the PropertyID is read-in.

如果 SQL 查询具有“where”原因，如 ，则上面显示的嵌入解决方案可能不起作用，但对于相同的查询，下次运行需要 PropertyID='113'，因为读入了 PropertyID。

回答by bbsimonbb

Glad you asked! Put your sql in a QueryFirst .sql template.

很高兴你问了！将您的 sql 放在 QueryFirst .sql 模板中。

It's automatically compiled into your app as an embedded resource, but you don't care. You just write it, in a real sql window, connected to your DB, with syntax validation and intellisense for tables and columns, then use it, via the generated Execute()methods, with intellisense for your inputs and results.

它会作为嵌入式资源自动编译到您的应用程序中，但您不在乎。您只需在真实的 sql 窗口中编写它，连接到您的数据库，对表和列进行语法验证和智能感知，然后通过生成的Execute()方法使用它，并为您的输入和结果使用智能感知。

disclaimer : I wrote QueryFirst.

免责声明：我写了 QueryFirst。