刷新存储过程中的数据 [英] 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;
}
}
我已经关闭了后台工作路由和 task.run，但程序一直阻塞线程.
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;
}
}
这两个想法都来自这个问题的各种答案:Entity Framework with NOLOCK.第一个建议基于 Frank.Germain 的回答，第二个基于 Alexandre 的回答.
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.
为了将其作为一个选项提及，您可能需要查看 SQL Server 2005 中引入的 SNAPSHOT ISOLATION 功能:
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 Options(搜索字符串snapshot_is")
- Snapshot Isolation in SQL Server
- ALTER DATABASE SET Options (search for the string "snapshot_is")
这篇关于刷新存储过程中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
