Where do you put SQL Statements in your c# projects?

Question

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.

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.

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?

Thanks!

Answer 1

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

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

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

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.

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.

Marc