locked
Using StreamInsight to pull SQL Server data repeatedly. RRS feed

  • Question

  • I have a typed class as follows:

        public class CallEvent
        {
            public int CallID { get; set; }
            public int CustomerID { get; set; }
            public int ContactCenterID { get; set; }
            public int CallDurationSeconds { get; set; }
            public DateTime CallEventDateTime { get; set; }
        }

    I can easily obtain data using a SQL Server stored procedure into a point stream and output with a simple pass-though LINQ query. However, the simple application I created only collects (and aggregates if I use another more complex LINQ query) the dataset being initially called.  My problem is that I have these events being inserted continuously into the database and need to query the database on regular intervals.

    I would like to create a repeatable call to the database using StreamInsight.  I have the stored procedure that SI uses to collect data and tracks what has been processed.  A simple example of the procedure code is as follows:

    BEGIN
    BEGIN TRANSACTION

    SELECT CallID, CustomerID, ContactCenterID, CallDurationSeconds, CallDateTime
     FROM CUST.Calls
    WHERE Processed = 0
    ORDER BY CallID;

    UPDATE CUST.Calls
      SET Processed = 1
    WHERE Processed = 0

    COMMIT TRANSACTION
    END

    I know I need a timer and I will have it fire every 15 seconds, having SI call the stored procedure, process the stream and aggregate the data over that 15 period using tumbling and hopping windows.  The LINQ Queries to do this is not my issue.  My issue is that I'm new to SI and I'm trying to determine what part of the SI code; the query, the binding, what; would reside in my timer event process procedure.  Currently I'm trying to get away with just using IEnumerable sink.  Can I do that or is a special input adapter going to be needed?

    Thanks.

    Friday, January 30, 2015 10:39 PM

All replies

  • You need to create a source for your call events. The adapter model is an older approach to working with StreamInsight. The Rx approach is much simpler. Call it SqlCallEventSource. This class needs to implement the observable pattern by implementing IObservable<CallEvent>. Inside your SqlCallEventSource, you can setup a timer to call your sproc on a 15 second interval. When your sproc call returns rows, project/map them into CallEvents, and then send the events to your subscribers. After that, you just need to get the stream of events coming off the source and run it through your query logic before sending the results to a sink for output.
    Thursday, February 19, 2015 1:59 PM