Store piece of text in application for further use

Question

I have a business Winforms application, which heavily uses access to a database on SQL Server.

All the database logic is already in the database in form of views and stored procedure.

However, an special need has arisen, and I need to embed like 10-20 queries in the winforms application itself, not stored procedure. For small queries, I simply concatenate using "+" sign each row all assigned to a string which is passed to a SqlDataAdapter to be executed. But for larger queries with several dozens of lines it is impractical.

I wonder if there's a way to store my files where I create the queries which are named like "qryPrivateMonthlyReport.sql" somewhere in the code (a resource may be?), to further be read and use to be executed in the SqlDataAdapter.

Thanks in advance,

Answer 1

I add them as resources. It allows me to reference the text of the file with a simple Properties.Resources.MyFileResourceNameHere. It is the cleanest solution I have found for embedding larger queries in a Visual Studio project.

Also, when I do embed them, rather than use the "+" operator, I use the literal string @"multi-line query here";, which does not require the "+" between lines.

Edit: Some of the comments below mention the extra indirection involved in keeping the SQL in a separate resource file, and I agree. My rules of thumb are:

• If it is a 1-liner, I keep it as a regular string constant.

• If it is a multi-line statement, I use a literal string constant (i.e. @"") so I can keep line-breaks, indentation, and not have a lot of " +.

• If it is so large that I need SQL syntax highlighting (e.g. SQL Management Studio, or Visual Studio), I make it a resource file, so I can edit and test the file independent of the .NET code.

Answer 2

Option 1

Take a look at dapper-dot-net which is a micro ORM that would allow you to convert your queries to objects. You can put these in a data access layer and if you need a full blown ORM later like entity framework it would be easy to move it.

http://code.google.com/p/dapper-dot-net/

There's also massive by Rob Connery https://github.com/robconery/massive

If you're running bigger queries that may be a sign that you need a full blown ORM.

Option 2

Use a parameterized query. You can write you SQL to take parameters. You can store .sql files, or you can have an xml file that contains your queries. Your queries would contain the parameters:

I would imagine your xml would look something like so:

<?xml version="1.0" encoding="UTF-8" ?>
<sql-queries>
    <statement>
        <id>get-id</id>
        <sql>SELECT UserId FROM User WHERE UserName = @UserName AND Password = @Password;</sql>
    </statement>
</sql-queries>

After loading your query (either from reading a text or reading an XML file). You can execute using parameters.

using (SqlCommand cmd = new SqlCommand(sql))

{
    // Create the parameter objects as specific as possible.
    cmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50);
    cmd.Parameters.Add("@Password", System.Data.SqlDbType.NVarChar, 25);

    // Add the parameter values.  Validation should have already happened.
    cmd.Parameters["@UserName"].Value = UserName;
    cmd.Parameters["@Password"].Value = Password;
    cmd.Connection = connnection;

    try
    {
        cmd.Connection.Open();
        var userId = cmd.ExecuteScalar();
    }
    catch (SqlException sx)
    {
        // Handle exceptions before moving on.
    }
}

Code from: http://software-security.sans.org/developer-how-to/fix-sql-injection-microsoft-.net-with-parameterized-queries

One thing I would definitely do in this case is encrypt the XML so that users can't modify it, but that will be up to you to do that. You can check out this link on doing so Encrypt and decrypt a string

Answer 3

If you need to store them with the code, you could just put them in a data access class as constant string literals (depending on the levels of flexibility you require).

e.g.

        private const string GetAQuery =  @"select * from a
                                          where b = c";

When debugging you would have to accept the slightly odd indentation. You can add them to resources as Sako73 mentioned, but this adds a level of indirection that you will have to judge if its worth it or not for your particular situation.