What is the best practice of writing SQL queries inside C # code

Question

In my current project, I am using SQL CE. Since it does not have support for stored procedures, I have to write sql queries inside the repository.

Option 1:

StringBuilder query = new StringBuilder(); query.Append("SELECT"); query.Append(" c.CUSTOMER_ID,"); query.Append(" COALESCE (c.FIRST_NAME, '') + ' ' + COALESCE (c.LAST_NAME, '') AS FULL_NAME"); query.Append(" ct.NAME as CUSTOMER_TYPE"); query.Append("FROM "); query.Append(" CT_CUSTOMER_TYPE AS ct INNER JOIN CUSTOMER AS c ON ct.CUSTOMER_TYPE_ID = c.CUSTOMER_TYPE_ID"); 

Option 2:

 string query = "SELECT c.CUSTOMER_ID, COALESCE (c.FIRST_NAME, '') + ' ' + COALESCE (c.LAST_NAME, '') AS FULL_NAME, ct.NAME as CUSTOMER_TYPE FROM CT_CUSTOMER_TYPE AS ct INNER JOIN CUSTOMER AS c ON ct.CUSTOMER_TYPE_ID = c.CUSTOMER_TYPE_ID"; 

Option 1 seems more readable, especially when I have 10+ tables in the join, but option 2 is faster.

Which option should I accept and what is the best in this case?

Answer 1

Option 2 may be several nanoseconds faster, but when you add time for the actual execution in the database (a few milliseconds), a few additional nanaoseconds are barely recorded as noise.

In any case, there is another option, the best of two worlds: @ -strings:

 string query = @" SELECT c.CUSTOMER_ID, COALESCE (c.FIRST_NAME, ''_ + ' ' + COALESCE (c.LAST_NAME, '') AS FULL_NAME ct.NAME as CUSTOMER_TYPE FROM CT_CUSTOMER_TYPE AS ct INNER JOIN CUSTOMER AS c ON ct.CUSTOMER_TYPE_ID = c.CUSTOMER_TYPE_ID "; 

Answer 2

Option 3 - use verbatim string litals :

 string query = @" SELECT c.CUSTOMER_ID, COALESCE (c.FIRST_NAME, '') + ' ' + COALESCE (c.LAST_NAME, '') AS FULL_NAME, ct.NAME as CUSTOMER_TYPE FROM CT_CUSTOMER_TYPE AS ct INNER JOIN CUSTOMER AS c ON ct.CUSTOMER_TYPE_ID = c.CUSTOMER_TYPE_ID"; 

Answer 3

I put the SQL string in resource files, this makes it easy to edit multi-line queries and provides strongly typed named access to these queries even with IntelliSence tooltips.

Answer 4

I always use the second method, since it is much faster. You use too many lines of code using the first method, which leads to a lot of overhead.

Answer 5

Have you ever thought about using LINQ?
http://blogs.msdn.com/b/matt/archive/2008/09/09/sql-ce-3-5-with-linq-to-sql.aspx . http://msdn.microsoft.com/en-us/library/system.linq.expressions.expression.coalesce.aspx

Answer 6

Why not option 3:

 "Select bla bla bla" "bla bla bla" "...." 

one long literal, broken into many lines.