ASP.NET - 存储SQL查询的全局资源文件？ [英] ASP.NET - Storing SQL Queries in Global Resource File?

问题描述

这是个好主意来存储我的SQL查询在一个全球性的资源文件，而不是在我的codebehind拥有它吗？我知道存储过程将是一个更好的解决办法，但我没有在这个项目上的奢侈。

Is it a good idea to store my SQL queries in a global resource file instead of having it in my codebehind? I know stored procedures would be a better solution but I don't have that luxury on this project.

我不希望查询都在我的网页，并认为一个中央存储库将是一个更好的主意。

I don't want queries all over my pages and thought a central repository would be a better idea.

推荐答案

资源文件通常用于定位。但是，一个字符串就是一个字符串就是一个字符串，你真的要被发送在资源文件中的任何旧串到你的数据库？

Resource files are usually used for localization. But a string is just a string is just a string, and do you really want to be sending any old string in a resource file to your database?

我完全同意别人，你应该使用LINQ或类型化数据集等同意个人我只有求助于文本查询的时候多年来屈指可数，而当我做它通常是类似以下内容：

I completely agree with others that you should be using linq or typed datasets, etc. Personally I've only had to resort to text queries a handful of times over the years, and when I do it's usually something like the following:

您设置了一个小框架，那么所有你需要做的就是保持一个XML文件。一个单一的特定的XML文件是一个更容易管理和部署比资源DLL。您还可以存储SQL查询和有关他们与只是一些命名约定一些元数据一个众所周知的地方（资料库）。

You set up a small framework and then all you need to do is maintain an Xml file. An single specific xml file is a lot easier to manage and deploy than a resource dll. You also have a well known place (repository) that stores Sql Queries and some metadata about them versus just some naming convention.

永远不要低估一个（简单）类的使用，在一个字符串。一旦你使用类开始可以再加​​入下来的东西，你不能（容易）做的只是一个简单的字符串的道路。

Never underestimate the utility of a (simple) class over a string literal. Once you've started using the class you can then add things down the road that you can't (easily) do with just a simple string.

---

记事本编译器，所以道歉，如果这是不是100％。这只是一个如何相互作用的一切草图。

Notepad compiler, so apologies if this isn't 100%. It's just a sketch of how everything interacts.

public static class SqlResource
{
    private static Dictionary<string,SqlQuery> dictionary;

    public static Initialize(string file)
    {
        List<SqlQuery> list;

        // deserialize the xml file
        using (StreamReader streamReader = new StreamReader(file))
        {
            XmlSerializer deserializer = new XmlSerializer(typeof(List<SqlQuery>));
            list = (List<SqlQuery>)deserializer.Deserialize(streamReader);
        }
        dictionary = new Dictionary<string,SqlQuery>();
        foreach(var item in list )
        {
            dictionary.Add(item.Name,item);
        }
    }
    public static SqlQuery GetQueryByName(string name)
    {
        SqlQuery query = dictionary[name];

        if( query == null )
            throw new ArgumentException("The query '" + name + "' is not valid.");

        if( query.IsObsolete )
        {
           // TODO - log this.
        }
        return query;

    }
}

public sealed class SqlQuery
{
    [XmlAttributeAttribute("name")]
    public bool Name { get; set; }

    [XmlElement("Sql")]
    public bool Sql { get; set; }

    [XmlAttributeAttribute("obsolete")]
    public bool IsObSolete { get; set; }

    [XmlIgnore]
    public TimeSpan Timeout { get; set;}

    /// <summary>
    /// Serialization only - XmlSerializer can't serialize normally
    /// </summary>
    [XmlAttribute("timeout")]
    public string Timeout_String 
    {
        get { return Timeout.ToString();  }
        set { Timeout = TimeSpan.Parse(value); } 
    }
}

您的xml文件可能类似于

your xml file might look like

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSqlQuery xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SqlQuery name="EmployeeByEmployeeID" timeout="00:00:30" >
      <Sql>
SELECT * From Employee WHERE EmployeeID = @T0     
      </Sql>
    </SqlQuery>
    <SqlQuery name="EmployeesForManager" timeout="00:05:00" obsolete="true" >
      <Sql>
SELECT * From Employee WHERE ManagerID = @T0      
      </Sql>
    </SqlQuery>
</ArrayOfSqlQuery>

这篇关于ASP.NET - 存储SQL查询的全局资源文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！