我正在尝试使用 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);
}
SqlConnection
,SqlCommand
和SqlDataReader
放入using(....) { ... }
块中以确保正确处理;尽可能晚地打开连接 - 就在ExecuteReader
之前 - 而不是更早。 - marc_s