如何在asp.net mvc项目中管理我的SQL查询脚本？

Question

我正在使用Dapper处理数据。

下面是一个代码示例：

using(var connection = new SqlConnection(ConfigurationManager.AppSettings["MyConnectString"])
{
     var sql = string.Format(
         @"SELECT [Column1]
             FROM [MyTable]
            WHERE [Column3] > {0}
              AND [Column4] < {1}"
         , myValue1
         , myValue2
     );
     var result = connection.Query<long>(sql).ToList();
}

我的项目编写了许多类似上面的sql脚本。

我想将脚本写到文件(可能是QueryAccount.config、QueryOrder.config、(xml格式)或其他文件)

然后我就可以从文件中加载脚本了。

我想要的是在文件中编写脚本，然后在文件中编写相同的类。(例如，我用Product.config编写所有查询产品脚本，用Order.config编写所有查询顺序脚本)，然后使用如下代码：

var cmd = MyCommandManager.GetScript("QueryProduct");
cmd.SetParam("@ProductId", 123);
cmd.SetParam("@InvoicingDate", DateTime.Now(-7))
...

文件中的脚本如下：

SELECT [ProductName]
FROM [Product]
WHERE [ProductId] = @ProductId
  AND [InvoicingDate] = @InvoicingDate

Answer 1

忽略不看好的人。SQL应该始终在它自己的文件中。这些文件的扩展名应该是.sql，而不是.config。因此，它们将在VS SQL编辑器中进行编辑，这是一种真正的安慰。我想您希望每个查询都有一个文件。将不同的查询分组到同一个文件中不会获得任何好处。我主张将这些文件放在使用它们的.cs文件旁边，将您一起打开的文件和您可能希望某天一起删除的文件组合在一起。

创建后，右键单击解决方案资源管理器中的.sql，属性-->构建操作-->嵌入式资源。然后，在MyCommandManager.GetScript()方法中，使用GetManifestResourceStream()访问查询文本。与存储过程相比，这有一个巨大的优势，即您的查询是用调用代码编译的，因此您不必担心同步存储过程和应用程序的版本。

如果所有这一切看起来像是大量的工作，那么它确实是这样的。这就是为什么没有人这样做的原因，但他们应该:-)抓取QueryFirst，它将为您完成，除此之外还有很多其他事情。免责声明:我写了QueryFirst。

Answer 2

如果您拥有对数据库的完全访问权限，则可以尝试实现一个Stored Procedure来存储SQL文本。您所需要做的就是在Dapper查询中引用存储的Proc名称，并将命令类型设置为StoredProcedure，这样就可以了：

using(var cn = new SqlConnection("MyConnectionString"))
{
    cn.Open();
    return cn.Query<MyModel>("MyProcName", new { Parameter1 = myValue1, Parameter2 = myValue2 }, commandType: CommandType.StoredProcedure);
}

使用SQL参数而不是将值注入到查询中是非常明智的做法，因为它可以防止SQL注入攻击。像这样重新格式化您的查询会有所帮助：

@"SELECT [Column1]
  FROM [MyTable]
  WHERE [Column3] > @Parameter1
  AND [Column4] < @Parameter2"

请注意，我的参数名与上面的dapper调用相匹配。但是，当我不使用存储过程时，我通常会在引用“存储”查询的类的顶部创建一个private const string。

public class QueryClass 
{
    private const string query = "SELECT * FROM Table1";

    public IEnumerable<MyModel> CallQuery()
    {
        // Dapper Query Details
    }
}

我订阅了一个Command/Query Pattern like this one，所以我从来没有遇到过查询存储的问题，因为每个类通常都有一个查询。

编辑

如果您喜欢命令/查询模式，我建议您查看MediatR，因为它是这种模式的一个很好的实现。

第二次编辑

我知道您试图通过将SQL查询添加到某种配置文件中来做什么，但是如果可以的话，我想建议您不要这么做。在我的上一份工作中，所有SQL查询都存储在编译到应用程序中的XML文件中。这似乎是一种管理查询的有效方法，但是一旦应用程序增长到甚至只有几个SQL XML文件，我们就很难管理在哪里可以找到哪些查询，最终我们在几个XML文件中有重复的查询。我们还遇到了许多关于拼写错误和其他XML结构错误的问题，这些错误直到运行时才被捕获，但我猜您可以在任何字符串中都有拼写错误，所以它不一定会消失。它最终会变得一团糟，造成的问题比解决的问题还要多。

我相信让SQL查询文本尽可能接近需要它的代码是一个更好的选择，如果你在命名空间和组织查询对象方面很聪明，你可以通过intellisense让devs很容易找到查询。