CODEWITHSUNDEEP
c#sqlsql-servervisual-studioprofiler
Cristian Perjescu
Cristian Perjescu

Reputation: 1

Infinite iteration with SQL XEvents QueryableXEventData

The request is: using XEvents API from SQL, I have to extract and analyze the event logs. I have personalized this session "QuickSessionTSQL" so that it only logs procedures or queries. The below code runs just fine, except that once it goes inside the first foreach block, it never leaves, it is just waiting for events to come, even if there is no other event to iterate on. It creates an infinite loop...

If I perform an SQL query, then it generates an event and the foreach will iterate that event in real time.

I tried to break it but I could not find a proper solution.

Has anyone dealt with this before?

        //Connection string
        SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
        csb.DataSource = @"DESKTOP-85BSKEM\MSSQLSERVER2016";
        csb.InitialCatalog = @"master";
        csb.IntegratedSecurity = true;

        //Session name
        string sessionName = "QuickSessionTSQL";

        SqlConnection sqlConnection = new SqlConnection(csb.ConnectionString);
        SqlStoreConnection connection = new SqlStoreConnection(sqlConnection);
        BaseXEStore store = new XEStore(connection);
        Session s = store.CreateSession(sessionName);

        s.Start();

        QueryableXEventData xEvents = new QueryableXEventData(csb.ConnectionString, sessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache);

        foreach (var evt in xEvents)
        {
            foreach (PublishedEventField fld in evt.Fields)
            {
                string fieldName = fld.Name;
                string fieldValue = fld.Value.ToString();
                Debug.WriteLine(fieldName + " " + fieldValue);
            }
        }

Upvotes: 0

Views: 274

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89386

The choice of IEnumerable for this has some drawbacks. It's the base type for all collections, and gives you LINQ access to the stream. But it's a bit awkward to terminate.

Anyway, I think the pattern should be something like enumerating the collection on a background thread, and signaling from the foreground thread when you want the enumeration to terminate. Like this:

    var xEvents = new QueryableXEventData(csb.ConnectionString, sessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache);
    var cancellationTokenSource = new CancellationTokenSource();

    var reader = Task.Factory.StartNew((o) =>
               {
                   foreach (var evt in xEvents)
                   {
                       foreach (PublishedEventField fld in evt.Fields)
                       {
                           string fieldName = fld.Name;
                           string fieldValue = fld.Value.ToString();
                           Console.WriteLine(fieldName + " " + fieldValue);
                       }
                   } 
               }, TaskCreationOptions.LongRunning
                , cancellationTokenSource.Token);

    Console.WriteLine("Hit any key to exit");
    Console.ReadKey();
    cancellationTokenSource.Cancel();
    xEvents.Dispose();
    s.Stop();
    reader.Wait();

Upvotes: 1

Related Questions