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?