Problem executing query for SQL Server in C# with SQLDataReader

Question

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();
   thisConnection.Close();
}
catch (SqlException e)
{
   Console.WriteLine(e.Message);
}

Recommendations: put your IDisposable SqlConnection, SqlCommand and SqlDataReader into using(....) { ... } blocks to ensure proper disposal; open your connection AS LATE as possible - just before ExecuteReader - and not any earlier. — marc_s
– marc_s, Commented Dec 3, 2010 at 15:50
I fixed the syntax error with my SQL query. Now I get an "Enumeration yields no result" error after thisReader.Read() is executed. Im not sure why that is — Mad Gibberish
– Mad Gibberish, Commented Dec 6, 2010 at 9:15

JohnFx · Accepted Answer · 2010-12-03 15:48:08Z

16

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.

answered Dec 3, 2010 at 15:48

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian Over a year ago

and if you're going to use a concatenated string, use StringBuilder

JohnFx Over a year ago

@Brian - In this case it probably won't make a difference. If all the strings are literals like this, the compiler will combine them in a single string in the MSIL anyway. However, I'd still think just putting a @ on the front of the string and losing all the +"" business would be more readable anyway.

Joshua Over a year ago

I was about to say the same thing, the only drawback of the @ method is if tabs are left in the CIL looks like "SELECT\r\n\t\t\t\t\tdb.Name DBName,\r\n\t\t\t\t\t" etc. Makes for a pain when using Reflector to look at production code (when you aren't sure what version is deployed...)

kevchadders · Accepted Answer · 2010-12-03 16:01:26Z

2

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.

edited Dec 3, 2010 at 16:01

answered Dec 3, 2010 at 15:56

kevchadders

8,3454 gold badges44 silver badges61 bronze badges

Comments

Matt · Accepted Answer · 2010-12-04 14:06:55Z

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.

Collectives™ on Stack Overflow

Problem executing query for SQL Server in C# with SQLDataReader

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related