c# - 使用 SQLDataReader 在 C# 中执行 SQL Server 查询时出现问题

Question

我正在尝试使用 SqlDataReader 在 C# 中执行查询，但我从数据库收到一条错误消息，“‘.’附近的语法不正确”。

我不确定我的 SQL 查询有什么问题。我可以在 SQL Server Management Studio 中完美地执行它。

try
{
   SqlConnection thisConnection = new SqlConnection();
   thisConnection.Open();
   SqlCommand thisCommand = thisConnection.CreateCommand();
   thisCommand.CommandText = "SELECT"
                +"db.name DBName,"
                +"tl.request_session_id,"
                +"wt.blocking_session_id,"
                +"OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,"
                +"tl.resource_type,"
                +"h1.TEXT AS RequestingText,"
                +"h2.TEXT AS BlockingText,"
                +"tl.request_mode"
                +"FROM sys.dm_tran_locks AS tl"
                +"INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id"
                +"INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address"
                +"INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id"
                +"INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id"
                +"INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id"
                +"CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1"
                +"CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2";

   SqlDataReader thisReader = thisCommand.ExecuteReader();
   while (thisReader.Read())
   {
      Console.WriteLine("\t{0}\t{1}", thisReader["DBName"], thisReader["BlockedObjectName"]);
   }
   thisReader.Close();
   thisConnection.Close();
}
catch (SqlException e)
{
   Console.WriteLine(e.Message);
}

Answer 1

你需要在你连接的一些行的末尾有空格:

例如:

            +"tl.request_mode"
            +"FROM sys.dm_tran_locks AS tl"

将创建 SQL

 ...tl.request_modeFROM sys.dm_tran_locks AS tl...

改成

            +"tl.request_mode " 
            +"FROM sys.dm_tran_locks AS tl"

还有其他几行也有同样的问题。

这是一个很好的例子，说明了为什么在测试动态构建的 SQL 字符串时实际打印出连接的字符串而不是仅仅粘贴代码并编辑掉引号和 + 号很重要。