What are the best practices on formatting inline sql using ADO.NET in C#

Question

I know there have been numerous questions here about inline sql vs stored procedures... I don't want to start another one like that! This one is about inline (or dynamic) sql.

I also know this point has become more or less moot with Linq to SQL and its successor Entity Framework.

But... suppose you have chosen (or are required by your superiors) to work with plain old ADO.NET and inline (or dynamic) sql. What are then the best practices for this and for formatting the sql?

What I do now is the following: I like to create my SQL statements in a stored procedure first. This gives me syntax coloring in SQL Server Management Studio and the ability to test the query easily without having to execute it in code through the application I'm developing.

So as long as I'm implementing/debugging, my code looks like this:

    using (SqlConnection conn = new SqlConnection("myDbConnectionString"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "myStoredProcName";
            // add parameters here
            using (SqlDataReader rd = cmd.ExecuteReader())
            {
                // read data and fill object graph
            }
        }
    }

Once the debugging and testing phase is done, I change the code above like this:

    using (SqlConnection conn = new SqlConnection("myDbConnectionString"))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = GetQuery();
            // add parameters here
            using (SqlDataReader rd = cmd.ExecuteReader())
            {
                // read data and fill object graph
            }
        }
    }

And I add an extra private method e.g. GetQuery() in which I copy/paste the whole block of the stored procedure like this:

    private string GetQuery()
    {
        return @"
    SET NOCOUNT ON;
    SELECT col1, col2 from tableX where id = @id

    -- more sql here
        ";
    }

Working like this has the benefit that I can revert the code easily to call the stored procedure again if I have to debug/update the sql code later, and once it's done I can easily put the sql code back with copy/paste, without having to put quotes around every line and stuff like that.

Is it good practice to include newlines in the query?
Are there other things or tricks that I haven't thought of which can make this approach better?
How do you guys do things like this?
Or am I the only one who still uses (has to use) inline sql?

Answer 1

Inline (with or without the literal @"..." syntax) is fine for short queries... but for anything longer, consider having the tsql as a file in the project; either as embedded resources / resx, or as flat files. Of course, by that stage, you should probably make it a stored procedure anyway ;-p

But having it as a separate file forces the same separation that will make it a breeze to turn into a stored procedure later (probably just adding CREATE PROC etc).

One issue with inline - it makes it so tempting for somebody to concatenate user input... which is obviously bad (you've correctly used parameters in the example).

Answer 2

I've used .NET resource files in the past. These were handy for keeping a library of all queries used in a particular code library, particularly when the same query might be used in multiple places (yes, I realize this also indicates some poor design, but sometimes you need to work within the box given to you).