none
Asycn Data Table Load RRS feed

  • Question

  • Hi Experts,

    Am using a library to parse SQL Server generated files & also a newbie with C# :). So am able to read the file using the library and load into a data table now the challenge what am facing as it appears as an Asychronous operations it may be creating multiple instances of Data Table for each and every row.. 


    What am looking for

    Entire content processed by XeExtract available for further data manipulations available in a single DT once the entire file is read"processed"..

    Kindly help

    Code sample below

    USING Microsoft.SqlServer.XEvent.XELite;
    
    private static DataTable CreateDataTable()
            {
                var dt = new DataTable();
                dt.Columns.Add("Event_Name", typeof(string));
                dt.Columns.Add("object_name", typeof(string));
                dt.Columns.Add("object_id", typeof(Int32));
            return dt;
    
            }
    
       public static void XeFile(String InputFilePath)
            {
    
    
    
                var xeStream = new XEFileEventStreamer(InputFilePath);
                xeStream.ReadEventStream(
    
                        xevent =>
                        {
                           XEextract(xevent);
                            return Task.CompletedTask;
                        },
               CancellationToken.None).Wait();
    
    
            }
    
            private static void XEextract(IXEvent xe)
            {
                var dt = CreateDataTable();
                var row = dt.NewRow();
                row["Event_Name"] = xe.Name;
    
                xe.Fields.TryGetValue("object_id", out object object_id);
                row["object_id"] = object_id == null ? 0 : object_id;
    
                xe.Fields.TryGetValue("object_name", out object object_name);
                row["object_name"] = object_name == null ? string.Empty : object_name;
    
    }

    Thanks

    Priya


    • Edited by Priya Bange Friday, December 20, 2019 5:52 PM ..
    Friday, December 20, 2019 5:49 PM

Answers

  • In `XEetract` you are creating a new row using `NewRow` but that doesn't add the row to the table. You need to explicitly do that.

    var row = dt.NewRow();
    dt.Rows.Add(row);


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Priya Bange Saturday, December 21, 2019 5:54 PM
    Saturday, December 21, 2019 4:12 PM
    Moderator

All replies

  • We have no insight into how your ReadEventStream works but since you're passing a method to it and that method creates a new DataTable each time then it is probably because of that. If you want a single table then you should create the table outside the method and either pass it to the method (preferred) or use a field.

    public static void XeFile ( string inputFilePath )
    {
       var dt = CreateDataTable();
    
       var xeStream = new XEFileEventStreamer(inputFilePath);
       xeStream.ReadEventStream(x => {
          XEextract(x, dt);
          return Task.CompletedTask;
       }, CancellationToken.None).Wait();
    }
    
    private static void XEextract ( IXEvent xe, DataTable dt)
    {
       //var dt = CreateDataTable();
        ...
    }

    However DataTable is not thread safe so if ReadEventStream is going to be calling your XEextract method across multiple threads then this won't work. Again, since we have no insight into how ReadEventStream works you'll have to figure out whether that is an issue or not.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Priya Bange Saturday, December 21, 2019 7:52 AM
    • Unmarked as answer by Priya Bange Saturday, December 21, 2019 10:01 AM
    Friday, December 20, 2019 8:50 PM
    Moderator
  • We have no insight into how your ReadEventStream works but since you're passing a method to it and that method creates a new DataTable each time then it is probably because of that. If you want a single table then you should create the table outside the method and either pass it to the method (preferred) or use a field.

    public static void XeFile ( string inputFilePath )
    {
       var dt = CreateDataTable();
    
       var xeStream = new XEFileEventStreamer(inputFilePath);
       xeStream.ReadEventStream(x => {
          XEextract(x, dt);
          return Task.CompletedTask;
       }, CancellationToken.None).Wait();
    }
    
    private static void XEextract ( IXEvent xe, DataTable dt)
    {
       //var dt = CreateDataTable();
        ...
    }

    However DataTable is not thread safe so if ReadEventStream is going to be calling your XEextract method across multiple threads then this won't work. Again, since we have no insight into how ReadEventStream works you'll have to figure out whether that is an issue or not.


    Michael Taylor http://www.michaeltaylorp3.net

    Dear Sir, 

    I tried the above approach but when I check my dt datatable after loop exit it returns null with the debugger on I can see the data table does get loaded with records. Please advise.

    public static void XeFile(string InputFilePath)
            {
                var dt = new DataTable();
                dt.Columns.Add("Event_Name", typeof(string));
                dt.Columns.Add("object_name", typeof(string));
                dt.Columns.Add("object_id", typeof(Int32));
    
                var xeStream = new XEFileEventStreamer(InputFilePath);
                xeStream.ReadEventStream(
    
                        xevent =>
                        {
                            XEextract(xevent, dt);
                            return Task.CompletedTask;
                        },
               CancellationToken.None).Wait();
    
                var result = from row in dt.AsEnumerable()
                             group row by row.Field<Guid>("Event_Name") into newGrope
                             select new
                             {
                                 attach_activity_id = newGrope.Key,
                                 event_name = newGrope.Max(x => x.Field<string>("object_name")),
                                 object_name = newGrope.Max(x => x.Field<string>("object_id")) };
    
                var dttable = new DataTable();
    
                dttable.Columns.Add("event_name", typeof(string));
                dttable.Columns.Add("object_name", typeof(string));
                dttable.Columns.Add("object_id", typeof(Int64));
    
    
    
                foreach (var item in result)
                {
                    dttable.Rows.Add(item.event_name, item.object_name, item.object_id);
                }
    
    
            }
    
       private static void XEextract(IXEvent xe, DataTable dt)
            {
                var row = dt.NewRow();
                row["Event_Name"] = xe.Name;
    
                xe.Fields.TryGetValue("object_id", out object object_id);
                row["object_id"] = object_id == null ? 0 : object_id;
    
                xe.Fields.TryGetValue("object_name", out object object_name);
                row["object_name"] = object_name == null ? string.Empty : object_name;
    
                xe.Fields.TryGetValue("object_type", out object object_type);
                row["object_type"] = object_type == null ? string.Empty : object_type;
    }

    Saturday, December 21, 2019 10:06 AM
  • In `XEetract` you are creating a new row using `NewRow` but that doesn't add the row to the table. You need to explicitly do that.

    var row = dt.NewRow();
    dt.Rows.Add(row);


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Priya Bange Saturday, December 21, 2019 5:54 PM
    Saturday, December 21, 2019 4:12 PM
    Moderator
  • In `XEetract` you are creating a new row using `NewRow` but that doesn't add the row to the table. You need to explicitly do that.

    var row = dt.NewRow();
    dt.Rows.Add(row);


    Michael Taylor http://www.michaeltaylorp3.net

    Wow, thanks a ton Sir... Am very surprised it worked without  add(row) for another similar implementation. Thanks again :) Happy weekend to you ..
    Saturday, December 21, 2019 5:41 PM