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

Answer 1

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.