Start SQL Server extended event session using c#

Question

I need to start a SQL Server extended event session through my C# code.

I have this code to create a session and then to execute it, but it is not working, any help please?

Code :

SQL statements to create a session

string sql = "DECLARE @file nvarchar(500), @path nvarchar(500), @db sysname = DB_NAME(); 

SET @file = RTRIM(N'D:\XE\ ') + @db;
SET @path = @file + N'day'; 

EXEC master.dbo.xp_create_subdir @file;

DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION ' + @db + N'_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 = N''' + @db + N''' ) ),
  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 = N''' + @db + N''')
)
ADD target package0.event_file
(
  SET filename = N''' + @path + N'''
) 
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=ON);';

EXEC master.sys.sp_executesql @sql;"

string sql1=" DECLARE @fichier nvarchar(500),@database sysname = DB_NAME(); 


DECLARE @sql1 nvarchar(max)  =N'ALTER EVENT SESSION  ' + @database + N'_Session ON SERVER STATE = START  '

EXEC master.sys.sp_executesql @sql1;"

Answer 1

I think the problem here is that you've put your SQL execution statements into your C# strings. Instead, you should use the framework's System.Data.SqlClient methods to execute your query. See the following:

How to directly execute SQL query in C#?