ASP.NET - Storing SQL Queries in Global Resource File?

Question

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.

Answer 1

I would look up strongly typed datasets with tableadapters and let the tableadapters handle all queries. When you are used with it you'll never go back.

Just add a dataset to your solution, add a connection, and a tableadapter for a table, then start build all querys (update, select, delete, search and so on) and handle it easy in code behind.

Answer 2

Ok, I'll try to answer again, now when I have more information.

I would make a query-class that hold all querystrings as shared properties or functions that could be named quite well to be easy to use.

Answer 3

I am in the same situation with some developers preferring to write the queries in the resource file. We are using subsonic and I would prefer to use stored procedures rather then using direct queries.

One option, even though it is bad is to place those queries in a config file and read when needed but this is a very bad option and we may use it if everyone cannot be agreement of using the stored procedures.

Answer 4

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?

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:

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.

---

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

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"&gt;
    <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>

Answer 5

You could use the XML config file to associate names with stored procedures too. I'm doing that for a current C# project. The "query" would define what procedure to call.

Since some database engines don't support stored queries, that's not always an option.

Sometimes for small projects, it's OK to use parameterized SQL queries (don't concatenate string). This is especially true for select statements.

Views can also be used for selects instead of stored procedures.

Rob