Why Am I Getting An 'open Data Reader' Exception With My Sqldependency Subscription?

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 y

Solution 1:

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;

for exmaple:

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

namespaceTestApp
{
    classProgram
    {
        staticvoidMain(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;");
        }
        privatestaticvoidget_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();
            }
        }
        staticvoiddependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("dependency Info = {0}, time: {1}",e.Info, DateTime.Now);
            get_msg();
        }
    }
}

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.

Solution 2:

maybe just a workaround ... but have you tried to set MultipleActiveResultSets to true on your connection?