为什么我会收到一个“开放式数据读取器'例外，我的SqlDependency订阅？

本文介绍了为什么我会收到一个“开放式数据读取器'例外，我的SqlDependency订阅？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我终于，有一个令人惊讶的高数字的功能失调的例子的集合体，成功地获得的SqlDependency 对象的更改消息。这方面的知识可能prepare你对我的code中的inelegance或纯不正确。

I have finally, with an aggregate of an amazingly high numbers of dysfunctional examples, managed to receive change messages on a SqlDependency object. This knowledge may prepare you for the inelegance or plain incorrectness of my code.

我有一个 ActiveList＆LT; T＆GT ;:的ObservableCollection＆LT; T＆GT; 侦听改变它的类的数据库表中的数据源和重新填充自己。我用下面的code创建和初始化列表和的SqlDependency ：

I have an ActiveList<T>: ObservableCollection<T> class that listens for changes to it's DB table data source and repopulates itself. I use the following code to create and initialize the list and the SqlDependency:

构造器：

public ActiveList()
{
    PopulateList();
}

填充：

private void PopulateList()
{        Application.Current.Dispatcher.Invoke(System.Windows.Threading.DispatcherPriority.Normal, ((Action)(() =>
    {
        Clear();
        using (var dbContext = new XTimeDbContext())
        {
            var set = dbContext.Set<T>().ToList();
            this.AddRange(set);
        }
    })));
    SubscribeNotifications();
}

private void SubscribeNotifications()
{
    const string dependencyQuery = "select TITLE_ACTIVE, TITLE_NAME from dbo.TITLE";
    var dependency = new SqlDependency();
    dependency.OnChange += DependencyOnChange;
    var connectionString = _dbContext.Database.Connection.ConnectionString;

    //SqlDependency.Stop(connectionString);
    SqlDependency.Start(connectionString);
    using (var sqn = new SqlConnection(connectionString))
    {
        sqn.Open();
        using (var cmd = new SqlCommand(dependencyQuery, sqn))
        {
            cmd.Notification = null;
            dependency.AddCommandDependency(cmd);
            //dependency.OnChange += DependencyOnChange;
            using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                }
            }                
        }
    }
}

最后，听：

private void DependencyOnChange(object sender, SqlNotificationEventArgs sqlNotificationEventArgs)
{
    _trace.TraceInformation("DependencyOnChange called. Reason: '{0}', Source: '{1}', Type: '{2}'.", sqlNotificationEventArgs.Info, sqlNotificationEventArgs.Source,
        sqlNotificationEventArgs.Type);
    //if (!_isPopulating)
    //{
    //    PopulateList();
    //}
    SqlDependency.Stop(_dbContext.Database.Connection.ConnectionString;);
    SubscribeNotifications();
    _trace.TraceInformation("DependencyOnChange completed.");
}

在code是轻微混乱，由于大量小的，实验性的变化，但我的主要问题是，当我运行测试应用程序，使用一个 ActiveList ，我得到的第一个变更通知;我的日志显示DependencyOnChange称为。然后，调用 SqlDependency.Stop ，无论我把它，生成一个 InvalidOperationException异常，与消息：

The code is in slight disarray due to huge amounts of small, experimental changes, but my main problem is that when I run the test app that uses an ActiveList, I get the first change notification; my log shows "DependencyOnChange called". Then, the call to SqlDependency.Stop, wherever I place it, generates an InvalidOperationException, with the Message:

There is already an open DataReader associated with this Command which must be closed first.

我可以在我的code到处都晃来晃去的DataReader，所以这可能是造成这？

I can find no 'dangling' DataReaders anywhere in my code, so what could be causing this?

推荐答案

您应该叫 SqlDependency.Start（的connectionString）; 只有一次在起点和 SqlDependency.Stop（_dbContext.Database.Connection.ConnectionString;）; 只有一次末（当你决定你会不会跟着改变）。这些命令创建并丢弃队列变化事件。

You should call SqlDependency.Start(connectionString); just once at beginning and SqlDependency.Stop(_dbContext.Database.Connection.ConnectionString;); just once at the end (when you decide you will not follow changes). These commands creates and drops queues for change events.

下一行，你应该打电话时，你需要订购的下一个变化。

Next lines you should call whenever you need to subscribe for next change.

var dependency = new SqlDependency();
dependency.OnChange += DependencyOnChange;

有关〔实施例：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace TestApp
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlDependency.Start("server=<MyServer>;database=<MyDB>;User ID=<user>;Password=<pwd>;Integrated Security=false;");
            Console.WriteLine("Started..");
            get_msg();
            Console.ReadLine();
            SqlDependency.Stop("server=<MyServer>;database=<MyDB>;User ID=<user>;Password=<pwd>;Integrated Security=false;");
        }
        private static void get_msg()
        {
            using (SqlConnection con =
                            new SqlConnection("server=<MyServer>;database=<MyDB>;User ID=<user>;Password=<pwd>;Integrated Security=false;"))
            {
                SqlCommand com = new SqlCommand("SELECT MyTableID, SomeText FROM dbo.MyTable ", con);
                SqlDependency dependency = new SqlDependency(com);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                con.Open();
                com.ExecuteNonQuery();
            }
        }
        static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("dependency Info = {0}, time: {1}",e.Info, DateTime.Now);
            get_msg();
        }
    }
}

应该牢记，即SQL依赖是情况下，当改变DB并不频繁。在code例如认购的下一个变化是即时的，但它会等待一段时间好主意。

Should keep in mind, that SQL Dependency is for situations when changes in DB are not frequent. In code example subscription for the next change is instant, but it would be good idea to wait for a while.

这篇关于为什么我会收到一个“开放式数据读取器'例外，我的SqlDependency订阅？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

sqldependency