SQL query executes in C# but it does not return any result

Question

I need to execute my SQL Server xevent creation with my C# and starting it then read its content from file. The query executes fine and no errors are displayed however the session is not added in SQL Server so no results are returned. I executed the query on SSMS and it works with concatenation of the variables to the query with N' but when I copy the same to C# it shows incorrect syntax though. Any help?

My C#:
        
public ActionResult CreateSession(string id)
{
    System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString);
    string dbName = id;
    string path = @".\\wwwroot\\ExecutedQueries\\"+dbName;
    System.IO.Directory.CreateDirectory(path);
    string fileName = dbName +DateTime.Now.ToString("dd_MM_yyyy_hh_mm_ss") +".xel";
    string pathString = System.IO.Path.Combine(path, fileName);
    if (!System.IO.File.Exists(pathString))
    {
        System.IO.FileStream fs = System.IO.File.Create(pathString);                   
    }
    string sql="DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION " + dbName + "_Session1"
    + " ON SERVER" + "ADD EVENT sqlserver.sql_statement_completed"
    + "(ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name," +
   " sqlserver.client_hostname, sqlserver.username) WHERE(sqlserver.database_name =''"  + dbName + "'')),"
    + "ADD EVENT sqlserver.sql_statement_starting(ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username) WHERE(sqlserver.database_name =''" + dbName + "''))"
    + "ADD target package0.event_file( SET filename =''" + pathString + "'')"
    + "WITH(MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,"
    + "MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB,"
    + " MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF);";

    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con))
    {
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    return RedirectToAction("BeginSession", new { database = dbName });
}

public ActionResult BeginSession(string database)
{
    System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString);

    string sql = "DECLARE @sql nvarchar(max) = N'ALTER EVENT SESSION "+@database+"_Session1 ON SERVER STATE = START  '";

    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con))
    {
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    return RedirectToAction("ExecutedQueries", new { dataBase = database });
}

public ActionResult ExecutedQueries(string dataBase)
{    
    System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString);
   
    string path = @"./wwwroot/ExecutedQueries/" + dataBase
    string fileName = dataBase + "*.xel";
    string pathString = System.IO.Path.Combine(path, fileName);
    try
    {
        byte[] readBuffer = System.IO.File.ReadAllBytes(pathString);
        foreach (byte b in readBuffer)
        {
            Console.Write(b + " ");
        }
        Console.WriteLine();
    }
    catch (System.IO.IOException e)
    {
        Console.WriteLine(e.Message);
    }       

    return View();
}

Does query work when run in SQL Server Management Studio? Could data be going into a different server (or database)? What is the connection string? — jdweng, Jul 22 '20 at 10:03
this query works in SSMS when i add N' ' ' near dbName (ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username) WHERE(sqlserver.database_name =N'''+@db + N''')) but when i run it on c# it shows a syntax error near the N'''. — Turkia Golli, Jul 22 '20 at 10:18
Put a beginning of query : "Use " + dbName + "; DECLARE @sql nvarchar(max) ..... — jdweng, Jul 22 '20 at 10:25

Jamie Burns · Accepted Answer · 2020-07-22T10:55:48.697

0

This generates SQL that can be executed:

string sql = "CREATE EVENT SESSION " + dbName + "_Session1" +
 " ON SERVER" +
  " ADD EVENT sqlserver.sql_statement_completed" +
  "( ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name," +
           " sqlserver.client_hostname, sqlserver.username) WHERE(sqlserver.database_name ='" + dbName + "'))," +
  "ADD EVENT sqlserver.sql_statement_starting( ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name,sqlserver.client_hostname, sqlserver.username) WHERE(sqlserver.database_name ='" + dbName + "'))" +
"ADD target package0.event_file( SET filename ='" + pathString + "')" +
"WITH(MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS," +
      "MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB," +
     " MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF);";

I've changed it so that it's creating the event directly (instead of creating a @sql variable which wasn't executed), and there was a missing space before ADD EVENT, which was generating SERVERADD instead of SERVER ADD.

edited Jul 22 '20 at 10:55

answered Jul 22 '20 at 10:08

Jamie Burns

1,258
9
21

1

thank you for your reply , you're right i miss the last quote but still the main problem is even it is executed without errors there is no results, no session is created on sql Server. – Turkia Golli Jul 22 '20 at 10:13
Hmm, ok. Is the connection string being used in C# definitely pointing to the right server/database, using a user that has the right permission? – Jamie Burns Jul 22 '20 at 10:15
yes definitely , i am executing other queries in the same controller , and they work just fine. – Turkia Golli Jul 22 '20 at 10:20
Ok, so that SQL statement is only creating a sql variable, but it's not executing it. Try adding this to the end of the SQL statement, after the final semi-colon: EXEC sp_executesql @sql – Jamie Burns Jul 22 '20 at 10:25
Or remove the DECLARE sql nvarchar(max) part, and the double single-quotes, and execute the command directly – Jamie Burns Jul 22 '20 at 10:26
unfortunately , still no results – Turkia Golli Jul 22 '20 at 10:34
this is what occured when i remove the declare nvarchar : System.Data.SqlClient.SqlException : 'Incorrect syntax near 'BDStock'. Incorrect syntax near 'EVENT'. Incorrect syntax near 'target'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.' – Turkia Golli Jul 22 '20 at 10:42
What I'd recommend is writing the SQL in SSMS (starting with CREATE EVENT SESSION), including the values of dbName and pathString that you know work. Then copy that into C#, and check that it works (before you edit the string to have variables in it). Once that's working, you can look to add dbName and pathString back in. I suspect this is a problem with quotes somewhere, but without running it and seeing your updates, it's hard to say for sure. – Jamie Burns Jul 22 '20 at 10:48
Actually, try this. It seems there was a missing space before ADD EVENT, and I removed the extra quotes that aren't needed. I'll edit my answer. – Jamie Burns Jul 22 '20 at 10:53

SQL query executes in C# but it does not return any result

1 Answers1