C# how to use multiple lines instead of 1

Question

I have a long sql code, but want to break it up into multiple lines for easier reading. How to do this in c# controller. The following is my code. I know we are supposed to add &_ or something to denote a line break and a + or something... not sure exact syntax..

result = db.Database.SqlQuery<FRT>("SELECT DISTINCT PRODUCTS.[Item Num],    PRODUCTS.Description, PRODUCTS.[Case Pack]
                                                         FROM PRODUCTS INNER JOIN (ORDERS INNER JOIN [ORDER_DETAILS] 
                                                         ON ORDERS.[Order Number] = [ORDER_DETAILS].[Order Number]) 
                                                         ON (PRODUCTS.[Item Num] = [ORDER_DETAILS].[Item Num]) 
                                                         AND (PRODUCTS.[Princ ID] = ORDERS.[Princ ID])
                                                         WHERE ((PRODUCTS.[Princ ID] = '')  AND (ORDERS.[Cust ID] = '' ) 
                                                         AND (PRODUCTS.Discontinued = 'False'))
                                                         ORDER BY PRODUCTS.Description;).ToList();)

answer:

    string sql = @"SELECT DISTINCT PRODUCTS.[Item Num] as [Item_Num], PRODUCTS.Description, PRODUCTS.[Case Pack] as [Case_Pack] 
                                                    FROM PRODUCTS INNER JOIN (ORDERS INNER JOIN [ORDER_DETAILS]
                                                    ON ORDERS.[Order Number] = [ORDER_DETAILS].[Order Number]) 
                                                    ON (PRODUCTS.[Item Num] = [ORDER_DETAILS].[Item Num]) 
                                                    AND (PRODUCTS.[Princ ID] = ORDERS.[Princ ID]) 
                                                    WHERE ((PRODUCTS.[Princ ID] = '" + PrincId + @"')  
                                                    AND (ORDERS.[Cust ID] = '" + CustId + @"' ) 
                                                    AND (PRODUCTS.Discontinued = 'False')) 
                                                    ORDER BY PRODUCTS.Description ";

            result = db.Database.SqlQuery<FRT>(sql).ToList();

I have added @ at the beginning and that seems to help, but not sure how to handle when passing parameter. When I add parameter, it breaks. — user1929393, Nov 09 '14 at 12:41
Just to mention it, I'd discourage you from building your SQL Like this. Why? Because of SQL Injection vulnerability: ( http://en.wikipedia.org/wiki/SQL_injection ) Try to use proper SQL Parameters instead, it would also make your life easier: http://stackoverflow.com/a/18752337/265165 — thmshd, Nov 09 '14 at 13:13

score 0 · Answer 1 · answered Nov 09 '14 at 12:11

Do you mean you want to have the SQL query in different lines as a string? If so, you need to learn the C# syntax for manipulating strings.

So for example, string concatenation can be achieved, for example with the + symbol:

var name = "Nahuel";
var greetings = "Hello, ";

var concatenatedText = greetings + name;

// value of concatenatedText equals "Hello, Nahuel"

This is the same as if I were to concatenate the string value directly:

var concatenatedText = "Hello, " + name;

// value of concatenatedText equals "Hello, Nahuel"

You can use different lines when using the + symbol in string concatenation, useful for when your strings are way to long to be read in a single line:

var longText = "xxxxxxxxxxx" +
               "yyyyyyyyyyy" + 
               "zzzzzzzzzzz";

You can also using the String Format method:

var name = "Nahuel";
var formattedString = String.Format("Hello, {0}!", name);

// value of concatenatedText equals "Hello, Nahuel!"

This is, the format sees that there is a parameter (0) and takes the variable used with it to replace it.

You can also check the StringBuilder class for better performance on some scenarios.

Yes I know your answer works, but it seems easier to use @. That way you don't have to worry about those quotes on each line and adding + — user1929393, Nov 09 '14 at 12:35

nonamer92 · Accepted Answer · 2014-11-09T12:55:55.173

Try using the @ in the beginning of the String

result = db.Database.SqlQuery<FRGHT_CO>(@"SELECT DISTINCT PRODUCTS.[Item Num],   PRODUCTS.Description, PRODUCTS.[Case Pack]
                                                     FROM PRODUCTS INNER JOIN (ORDERS INNER JOIN [ORDER_DETAILS] 
                                                     ON ORDERS.[Order Number] = [ORDER_DETAILS].[Order Number]) 
                                                     ON (PRODUCTS.[Item Num] = [ORDER_DETAILS].[Item Num]) 
                                                     AND (PRODUCTS.[Princ ID] = ORDERS.[Princ ID])
                                                     WHERE ((PRODUCTS.[Princ ID] = '')  AND (ORDERS.[Cust ID] = '' ) 
                                                     AND (PRODUCTS.Discontinued = 'False'))
                                                     ORDER BY PRODUCTS.Description;).ToList();)

or, as Nahuel Ianni wrote, you can concatenate Strings with the '+' symbol and then add your query parameters(PrincID,CustID) :

Made an assistance function:

 public String BuildQuery(int princId, int CustId)
        {
            return "SELECT DISTINCT PRODUCTS.[Item Num],   PRODUCTS.Description, PRODUCTS.[Case Pack] " +
                                                     "FROM PRODUCTS INNER JOIN (ORDERS INNER JOIN [ORDER_DETAILS] " +
                                                     "ON ORDERS.[Order Number] = [ORDER_DETAILS].[Order Number]) " +
                                                     "ON (PRODUCTS.[Item Num] = [ORDER_DETAILS].[Item Num]) " +
                                                     "AND (PRODUCTS.[Princ ID] = ORDERS.[Princ ID]) " +
                                                     "WHERE ((PRODUCTS.[Princ ID] = " + princId + ")  AND (ORDERS.[Cust ID] = " + CustId + " ) " +
                                                     "AND (PRODUCTS.Discontinued = 'False')) " +
                                                     "ORDER BY PRODUCTS.Description";
        }

ranifisch,This seems to work adding @, but what when passing a parameter. I'm passing two query parameters. PrincId, and CustId. What is syntax for this? — user1929393, Nov 09 '14 at 12:21
WHERE ((PRODUCTS.[Princ ID] = '" + PrincId + "') does not work it says error New Line in Constant, not using Nahuel answer as that is too much typing, just using @ — user1929393, Nov 09 '14 at 12:38
WHERE ((PRODUCTS.[Princ ID] = '" + PrincId + @"') this seems to work, adding @ after the parameter — user1929393, Nov 09 '14 at 12:50

C# how to use multiple lines instead of 1

2 Answers2