What is the best practice for writing sql queries inside c# code

On my current project I'm using SQL CE. Since it doesn't have support for stored procedures I have to write sql queries inside 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 like a much more readable, especially when I have 10+ tables in join, but option 2 is faster. Which option should I accept and what's the best practice in this case?

Answers


Option 2 may be a few nanoseconds faster, but when you add the time to actually execute in the database (several milliseconds) a few extra nanaoseconds barely registers as noise.

In any case, there is another option that's the best of both 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
    ";

Option 3 - use verbatim string literals:

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

I puts SQL string into resource files, it allows easy edit multiline queries, and provides strongly typed named access to that queries even with IntelliSence tooltips.


Why not option 3:

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

one long literal, split to many lines.


I always use the second method as it is much faster. You use up too many lines of code with the first method, leading to a larger overhead.


Have you 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


Need Your Help

FAILED CONFIGURATION: @BeforeClass setUp org.mockito.exceptions.base.MockitoException: Unable to initialize @Spy annotated field 'allRequestParams'

java spring-mvc jpa junit testng

I have an application (Spring MVC 4 + Hibernate/JPA + MySQL + Maven integration example using annotations), integrating Spring with Hibernate using annotation based configuration.