Refresh data from stored procedure

Question

I have a c# entity framework application. I am trying to run a stored procedure from code (no problem with that). its long running, around 30 mins. I write a log of each transaction to a SQL table as the process goes through. I am looking to initiate the procedure from the app but then show the last 10 records of the log on screen maybe re querying every 10 seconds. this will show the progress.

 private void Window_Loaded_1(object sender, RoutedEventArgs e)
    {
        Task.Run(() => _serviceProduct.RefreshAllAsync());

        _cvsLog = (CollectionViewSource)(FindResource("cvsLog"));
        var dispatcherTimer = new System.Windows.Threading.DispatcherTimer();
        dispatcherTimer.Tick += new EventHandler(dispatcherTimer_Tick);
        dispatcherTimer.Interval = TimeSpan.FromSeconds(10);
        dispatcherTimer.Start();
    }


 private void dispatcherTimer_Tick(object sender, EventArgs e)
    {
        _cvsLog.Source = _serviceProduct.GetRefreshLog();
    }

I have altered the code to simplify. The thread blocks on the dispatcherTime_Tick process. It looks like the stored procedure is away fine.

Here is the called service.

 public ObservableCollection<RefreshLog> GetRefreshLog()
    {
        using (var db = new HiggidyPiesEntities())
        {
            var recs = (from x in db.RefreshLogs orderby x.LG_ID descending select x).Take(30);
            var obs = new ObservableCollection<RefreshLog>(recs);
            return obs;
        }
    }

I have been down the background worker route and task.run but the procedure keeps blocking the thread.

I have even thought of initiating a SQL job from code and then monitor the log after that with calls to the database. Maybe service broker may be a choice to consider?

any thoughts of what road I should go down with this type of problem? thanks in advance Scott

You could try a DispatcherTimer. This works like a background worker except it runs on the UI thread. Your timer could refresh your ItemsSource about every ten seconds. — Travis Pettry, Oct 28 '14 at 18:45
Your UI stack is completely irrelevant to question asked, removed WPF tag. — Dean Kuga, Oct 28 '14 at 20:09
You can use **SqlDependency**. Witch version of SQL Server do you have ? — Max, Nov 02 '14 at 19:50
I am using SQL 2008 R2 in this instance. Will look into this a little later. — scottsanpedro, Nov 03 '14 at 11:49

score 4 · Accepted Answer · edited May 23 '17 at 11:58

Given the nature of this data being just a status to the user, it seems fine to do READ UNCOMMITTED. You can try these two options, both of which seem fairly straight-forward:

First thing to try is to set a Session/Connection property:

public ObservableCollection<RefreshLog> GetRefreshLog()
{
   using (var db = new HiggidyPiesEntities())
   {
      db.context.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
      var recs = (from x in db.RefreshLogs orderby x.LG_ID descending select x).Take(30);
      var obs = new ObservableCollection<RefreshLog>(recs);
      return obs;
   }
}

Second thing to try is setting up the transaction isolation level through EF:

public ObservableCollection<RefreshLog> GetRefreshLog()
{

   using (var scope = new TransactionScope(TransactionScopeOption.Required,
             new TransactionOptions() {
                 IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
             }))
   {
      ObservableCollection<RefreshLog> obs;

      using (var db = new HiggidyPiesEntities())
      {
         var recs =
              (from x in db.RefreshLogs orderby x.LG_ID descending select x).Take(30);
         obs = new ObservableCollection<RefreshLog>(recs);
      }

      scope.Complete();
      return obs;
   }

}

Both ideas taken from various answers here on this question: Entity Framework with NOLOCK. First suggestion based on Frank.Germain's answer, second one based on Alexandre's.

And just to have it mentioned as an option, you might want to look into the SNAPSHOT ISOLATION feature that was introduced in SQL Server 2005:

Snapshot Isolation in SQL Server
ALTER DATABASE SET Options (search for the string "snapshot_is")

The first section using the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED started the process perfectly. — scottsanpedro, Nov 04 '14 at 09:54
@clive , is there any additional information you are wanting to see? — Solomon Rutzky, Nov 05 '14 at 15:11
As a courtesy, if you like this answer, please upvote the answer where this one was derived from :) Thank you! — Frank.Germain, Jan 09 '15 at 15:02

score 1 · Answer 2 · answered Nov 03 '14 at 12:03

If you are using EF 6.0 and .NET 4.5 you should try to use latest feature and acync support: Entity Framework 6 async query, Task-based Asynchronous Pattern support in EF It looks like the feature is design exactly for your and similar problems.

You can combine it with DispatcherTimer suggested by @user2002076.

To read you progress every x seconds you can develop another stored procedure and inside you can hint SQL with READ UNCOMMITTED.

Obviously all this require EF 6.0 and .NET 4.5 but the implementation will be nice and short.

Refresh data from stored procedure

2 Answers2

Linked