从刷新存储过程中的数据 [英] Refresh data from stored procedure
问题描述
我有一个C＃实体框架的应用程序。
我试图运行的代码存储过程（有没有问题）。其长期运行，大约30分钟。我写的日志每笔交易到SQL表作为过程经历。
我期待启动从应用程序，但随后显示最后10个记录在日志上的画面，也许再每10秒查询。这将显示进度。
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();
}
我改变了代码，以简化。在dispatcherTime_Tick过程中的线程块。它看起来像存储过程是走精细。
I have altered the code to simplify. The thread blocks on the dispatcherTime_Tick process. It looks like the stored procedure is away fine.
下面是被调用的服务。
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.
我甚至以为发起从代码SQL作业的，然后监视后的日志与调用数据库。
也许服务代理可能是一个选择的考虑？
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
推荐答案
由于该数据是仅仅给用户一个状态的性质，它似乎罚款做READ UNCOMMITTED。您可以尝试这两个选项，这两个看起来相当简单：
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;
}
}
第二件事是尝试建立事务隔离级别通过EF：
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;
}
}
从这里各种各样的回答，而采取的想法在这个问题上：实体框架与NOLOCK 。基于Frank.Germain的回答，根据亚历山大的第二个第一个建议。
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:
- 的快照在SQL Server隔离
- 的 ALTER DATABASE SET选项（搜索字符串snapshot_is）
- Snapshot Isolation in SQL Server
- ALTER DATABASE SET Options (search for the string "snapshot_is")
这篇关于从刷新存储过程中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
