你在哪里把你的C＃项目SQL语句？ [英] Where do you put SQL Statements in your c# projects?

问题描述

我很可能会负责移植VB6的应用程序为C＃。这个应用程序是一个Windows应用程序，与Access数据库进行交互。数据访问被包封在基本业务对象。一类为一个表基本上是这样。现有的VB6的业务对象读取和写入通过DAO数据库。我已经写DALS和奥姆斯前几次，但他们都只是有针对性的SQL Server。这人会需要针对Access和SQL Server。在以前的项目，我会放在SQL字符串中的业务对象的私处，也许移动像连接，建立指挥，成为一个公共基类来减少代码冗余SQL代码。

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.

这一次，我正在考虑写SQL字符串成.settings文件或一些其他的键/值类型的文本文件。然后我会写一个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.

我知道一个标准的方法是创建一个DAL为每个目标数据库，并配置状态要使用的DAL。我真的不想为每个数据库创建两个DAL类。看起来这将是更少的代码，如果我只是引用由键名正确的查询，并有适当的连接类型。

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.

那么，你家伙做这样的事情？如何将或已经你走近这个问题？
什么工作最适合你？

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

谢谢！

推荐答案

嗯，有很多的选择 - 所以这真的取决于你的最迫切的需求是： - ）

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

一种做法是创建SQL语句作为文本文件您VS解决方案里面，并将其标记为打造行动，嵌入的资源。这样，SQL包含在您得到的组件，并且可以从它在运行时使用.NET框架的ResourceManifestStream检索：

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:

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;
}

您需要使用您的实际命名空间，以取代ConsoleApplication1，其中的sql声明文件驻留。您需要通过完全合格的名称来引用它们。然后，你可以用下面这行加载您的SQL语句：

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:

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

然而，这使得查询，而静，例如您不能配置并在运行时改变它们 - 他们是置于编译的二进制位。但在另一方面，在VS，你有你的C＃程序代码之间的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.

如果你需要能够有可能调整并在运行时改变它们，我把它们放进其中包含如一个SQL表关键字和实际的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.

马克

这篇关于你在哪里把你的C＃项目SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！