如何在ADO.NET项目的代码内存储大型查询 [英] How to store large queries inside code for ADO.NET project

问题描述

我正在研究一个Web项目，其数据访问层基于ADO.NET（以最快的速度执行）。项目中有一些非常大的SQL查询，这些查询是用C＃代码内联编写的。我想知道是否可以将这些查询更优雅地移动到某个地方，以减少混乱，但是不确定可以使用哪种方法。我知道资源文件，但是由于某些查询已参数化，因此不能在这里使用。

I am working on a web project for which data access layer is based on ADO.NET (for fastest execution). There are some very large SQL queries in the project which are written inline in C# code. I was wondering if I can move those queries somewhere more elegantly to reduce some mess but am unsure what approach can be used. I know about resource files but those can't be used here as some queries are parameterized.

语言：C＃

推荐答案

如果您无法或不愿意使用存储过程，并且希望将Sql保持在与C＃代码接近的位置，则可以提取Sql并将其放入项目中包含的外部文件中

If you are unable or unwilling to use Stored Procedures and prefer to keep the Sql close to the C# code you could extract the Sql and put them in to external files, included in the project as per the images.

选项1：将文本文件与可执行文件一起复制到子文件夹中

并按以下方式访问其内容：

And access its contents like this:

    private String LoadFileContent()
    {
        String fileName = "Sql\\LoadAllData.sql";

        if (!File.Exists(fileName))
        {
            String errorMessage = String.Format("File '{0}' does not exist or access to it is denied", fileName);
            throw new FileNotFoundException(errorMessage, fileName);
        }

        String fileContent = String.Empty;
        using (StreamReader sr = File.OpenText(fileName))
        {
            fileContent = sr.ReadToEnd();
        }

        return fileContent;
    }

或

选项2：作为资源嵌入到程序集中的文本文件

并使用此方法访问文件：

And access the file using this method:

    private String LoadAssemblyResource()
    {
        Assembly assembly = Assembly.GetExecutingAssembly();
        String fileName = "StackOverflowWinForm.SQL.LoadAllData.sql";

        // Handy bit of debug code to list all the resource names in case there
        // is an issue trying to find/load a resource
        String[] resourceNames = assembly.GetManifestResourceNames();

        String fileContent = String.Empty;
        using (Stream stream = assembly.GetManifestResourceStream(fileName))
        {
            if (stream == null)
            {
                String errorMessage = String.Format("Resource File '{0}' does not exist", fileName);
                throw new MissingManifestResourceException(errorMessage);
            }

            using (StreamReader reader = new StreamReader(stream))
            {
                fileContent = reader.ReadToEnd();
            }
        }

        return fileContent;
    }

如果您正在开发Web，我建议使用后一种嵌入式资源方法应用程序或Web服务，因此您不必担心被黑客入侵/更改的Web服务器上的路径，安全性和文本文件的映射。

I would recommend the latter method of an Embedded Resource if you are developing a Web Application or Web Service so you don't have to worry too much about mapping paths, security, and text files on the web server being hacked/altered.

方法替换代码中的字符串文字并将其移动到外部文件。加载外部文件后，仍然可以使用与以前相同的方式来操作字符串。

Both of these methods replace the string literals in code and move them to an external file. Once the external file has been loaded, the string can still be manipulated in the same way as before.

我经常根据确切的情况和大小使用这两种方法。有问题的SQL。

I often use both methods depending on the exact circumstances and size of the Sql in question.

这篇关于如何在ADO.NET项目的代码内存储大型查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！