Substitution inside verbatim string literals?

Question

In relation with this question about how to write long SQL queries within C#., the solution suggested that a long sql query should be written as:

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

That makes me curious about another related question. Can I use substitution somehow? That is, how would I manage if say the table name changes but the query remains the same? Do I have to fall back to using the other approach of building a string using string concatenations or is there a more elegant way?

Answer 1

You might consider using LINQ to SQL, if you haven't already.

Answering the actual question, you could use string.Format as the others have mentioned if you're creating the query. If you wish to modify an existing string, try using string.Replace or Regex.Replace as described here. That is:

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
    ";
query.Replace("CT_CUSTOMER_TYPE", "NEW_TABLE_NAME");

Answer 2

Why not make use of string.Format? In the specific example you gave, you could do something like

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

And the invoke

string real_query = string.Format(query, tblName1, tblName2);