c# - 当我将长查询粘贴到变量中时出现错误(ASP.NET MVC RAZOR)

Question

我可以询问这个长脚本的格式解决方案吗？我是asp的新手，因为在声明长脚本时，在PHP中是不同的，在PHP中只需复制并粘贴就可以了。但在 ASP 中。脚本必须排得很长。

示例脚本

    String sql = "SELECT
   timesheet.date,

  MAX(CASE
    WHEN timein1 = 8 and note ='HOLIDAY' and counter = 1
    then (SELECT description FROM holidays WHERE timesheet.date = holidays.date)
    WHEN timein1 = 8 and note ='SICK LEAVE' and counter =1 then 'SICK LEAVE'
    WHEN timein1 = 8 and note ='VACATION LEAVE' and counter =1  then 'VACATION LEAVE'
    WHEN timein1 = 8 and note ='REGULAR LOGGED' and counter =1  then log
    END) as note1,


  MAX(case
  WHEN timeout1 = 12 and note ='HOLIDAY' and counter =2
  then (SELECT description FROM holidays WHERE timesheet.date = holidays.date)
  WHEN timeout1 = 12 and note ='SICK LEAVE' and counter =2 then 'SICK LEAVE'
  WHEN timeout1 = 12 and note ='VACATION LEAVE' and counter =2 then 'VACATION LEAVE'
  WHEN timeout1 = 12 and note ='REGULAR LOGGED' and counter =2 then log
  else ''
  end) as note2,

  MAX(case
  WHEN timein2 = 13 and note ='HOLIDAY' and counter =3
  then (SELECT description FROM holidays WHERE timesheet.date = holidays.date)
  WHEN timein2 = 13 and note ='SICK LEAVE' and counter =3 then 'SICK LEAVE'
  WHEN timein2 = 13 and note ='VACATION LEAVE' and counter =3 then 'VACATION LEAVE'
  WHEN timein2 = 13 and note ='REGULAR LOGGED' and counter =3  then log
  else ''
  end) as note3,

  MAX(case
  WHEN timeout2 = 17 and note ='HOLIDAY' and counter =4
  then (SELECT description FROM holidays WHERE timesheet.date = holidays.date)
  WHEN timeout2 = 17 and note ='SICK LEAVE' and counter =4  then 'SICK LEAVE'
  WHEN timeout2 = 17 and note ='VACATION LEAVE' and counter =4  then 'VACATION LEAVE'
  WHEN timeout2 = 17 and note ='REGULAR LOGGED' and counter =4  then log
  else ''
  end) as note4


FROM timesheet

LEFT JOIN schedules ON timesheet.empid = schedules.empid

WHERE timesheet.empid='40' and YEAR(timesheet.date) = 2017

AND MONTH(timesheet.date) = 5

AND timesheet.date <= CURDATE()

AND timeStatus='OK'

GROUP BY timesheet.date,timesheet.empid "

解决asp.net要求的问题示例脚本是这样的

选择 timeshet.date, MAX(CASE WHEN timein1 =8 and note = 'HOLIDAY' and counter = 1 等等等等............

谢谢大家..

Answer 1

连接长字符串有 3 种常见方法:

1. 使用verbatim string literal (在双引号前用 @ 符号标记)。示例:

   String sql = @"SELECT timesheet.date, MAX(CASE WHEN timein1 = 8 
                  and note ='HOLIDAY' and counter = 1 ...";
   

2. 使用concatenation operator (+)对于每一行。示例:

   String sql = "SELECT timesheet.date, MAX(CASE WHEN timein1 = 8 and note ='HOLIDAY' and counter = 1 ..." + 
                ... +
                "GROUP BY timesheet.date,timesheet.empid";
   

3. 使用StringBuilder对于多个命令。示例:

   var sb = new StringBuilder();
   sb.Append("SELECT timesheet.date,");
   sb.Append("MAX(CASE WHEN timein1 = 8 and note ='HOLIDAY' and counter = 1 ..."); 
   // other append methods
   
   String sql = sb.ToString();
   

逐字字符串方法是最有效的，因为它仅由另一个双引号分隔(仅需要转义双引号，例如 "" 表示 ").

如果要与其他变量值执行字符串连接，则需要将 String.Format 与逐字字符串和复合格式结合使用，如下所示:

String sql = String.Format(@"SELECT ... 
                            FROM timesheet LEFT JOIN schedules ON timesheet.empid = schedules.empid
                            WHERE timesheet.empid='40' AND YEAR(timesheet.date) = {0} 
                            AND MONTH(timesheet.date) = {1} 
                            AND timesheet.date <= CURDATE() 
                            AND timeStatus='OK' 
                            GROUP BY timesheet.date,timesheet.empid", List, List_month);

注意:如果您有这样的长查询字符串，我更喜欢创建一个 stored procedure像这样:

CREATE DEFINER = [definer_name] PROCEDURE [procedure_name]
-- [parameters if any]
BEGIN 
    SELECT timesheet.date, MAX(CASE WHEN timein1 = 8 and note ='HOLIDAY' and counter = 1 ...
END;

然后通过 MySQL database connection 调用该过程:

using (var connection = new MySqlConnection("[MySQL_connection_string]"))
{
    try
    {
        connection.Open();
        using (var command = new MySqlCommand("[procedure_name]", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            // add parameters here

            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                // reading data from database
            }

            // other stuff
        }
        connection.Close();
    }
    catch (Exception e)
    {
        // handle exceptions here
    }
}