Problem executing query for SQL Server in C# with SQLDataReader

I am trying to execute a query in C# using SqlDataReader but I receive an error message from the database, "Incorrect syntax near '.'".

I am not sure what is wrong with my SQL query. I can execute it perfectly fine in 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();
   thisConnect开发者_JS百科ion.Close();
}
catch (SqlException e)
{
   Console.WriteLine(e.Message);
}

You need spaces at the end of some of the lines you are concatenating:

For example:

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

Will create the SQL

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

Change it to

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

There are also a few other lines with the same problem.

This is a good example of why it is important to actually print out the concatenated string when testing a dynamically built SQL string instead of just pasting the code and editing out the quotes and + signs.

As John mentioned you need spaces at the end of your lines as you are missing a few.

As an example your initial error is coming from the first two lines

thisCommand.CommandText = "SELECT" 
                +"db.name DBName," 

which will equal

SELECTdb.name DBName,

Thats where the "Incorrect syntax near'.'" is coming from as db. is being added onto the SELECT statement.

JohnFx and kevchadders are right that the error is due to the resulting string not having appropriate whitespace. As for what you can do about it - if you'd like the query to remain more or less in the same format, you can use @-quoting:

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();

This will respect your line ends and other whitespace so you get to have a readable query you can easily copy-paste from the C# into SSMS, without the whitespace errors, without concatenation, and without all the StringBuilder.Append statements.