none
Query Notification subscription expires automatically after the Notification is raised RRS feed

  • Question

  • This is in continuation to my previous thread on QN here (http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/4bbc2ae6-cb03-488e-b9ed-e16cd264a81a).

    I have been able to successfully subscribe for QN on my custom queue using the following code:

    DataProvider temp=new DataProvider();
    SqlDependency.Start(temp.conString);
    //SqlDependency.Start(temp.conString, queueName);
    
    SqlConnection con=new SqlConnection(temp.conString);
    con.Open();
    
    using (con)
    {
    	// Create a new SqlCommand object.
    	using (SqlCommand command=new SqlCommand(temp.getModuleQualifiedName("GetAllImportableInvoiceIds"), con))
    	{
    		command.CommandType = CommandType.StoredProcedure;
    		// Create a dependency and associate it with the SqlCommand.
    		//SqlDependency dependency=new SqlDependency(command, "Service=" + serviceName, 31557600);
    		SqlDependency dependency=new SqlDependency(command);
    		
    		// Maintain the refence in a class member.
    
    		// Subscribe to the SqlDependency event.
    		dependency.OnChange += new
    		  OnChangeEventHandler(handler);
    
    		// Execute the command.
    		using (SqlDataReader reader = command.ExecuteReader())
    		{
    			// Process the DataReader.
    			reader.Dispose();
    		}
    	}
    }
    
    

    The handler is invoked successfully for the query. However, the Notification subscription expires immediately after the event is raised, and I need to subscribe again to keep receiving the Notifications each time in the handler itself.

    Am I doing something wrong, or is this the intended behavior?


    I always think tomorrow will have more time than today. And every today seems to pass-by faster than yesterday.
    Rahul Singla | http://www.rahulsingla.com
    Tuesday, August 17, 2010 5:45 AM

Answers

  • Hi Rahul,
     
    You're exactly right about this. The notification subscription is good for exactly one update. Once its firing, it expires and you have to re-execute the query (with the SqlDependency attached) to register the subscription again.
     
    Cheers,
    Bob
     
    "Rahul Singla" wrote in message news:67ce5403-9672-44f6-9c8d-3cec648494e6...

    This is in continuation to my previous thread on QN here (http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/4bbc2ae6-cb03-488e-b9ed-e16cd264a81a).

    I have been able to successfully subscribe for QN on my custom queue using the following code:

    DataProvider temp=new DataProvider();
    SqlDependency.Start(temp.conString);
    //SqlDependency.Start(temp.conString, queueName);
    
    SqlConnection con=new SqlConnection(temp.conString);
    con.Open();
    
    using (con)
    {
    	// Create a new SqlCommand object.
    	using (SqlCommand command=new SqlCommand(temp.getModuleQualifiedName("GetAllImportableInvoiceIds"), con))
    	{
    		command.CommandType = CommandType.StoredProcedure;
    		// Create a dependency and associate it with the SqlCommand.
    		//SqlDependency dependency=new SqlDependency(command, "Service=" + serviceName, 31557600);
    		SqlDependency dependency=new SqlDependency(command);
    		
    		// Maintain the refence in a class member.
    
    		// Subscribe to the SqlDependency event.
    		dependency.OnChange += new
    		 OnChangeEventHandler(handler);
    
    		// Execute the command.
    		using (SqlDataReader reader = command.ExecuteReader())
    		{
    			// Process the DataReader.
    			reader.Dispose();
    		}
    	}
    }
    
    

    The handler is invoked successfully for the query. However, the Notification subscription expires immediately after the event is raised, and I need to subscribe again to keep receiving the Notifications each time in the handler itself.

    Am I doing something wrong, or is this the intended behavior?


    I always think tomorrow will have more time than today. And every today seems to pass-by faster than yesterday.
    Rahul Singla | http://www.rahulsingla.com
    Tuesday, August 17, 2010 2:35 PM

All replies

  • Did you try to declare and instantiate SqlDependency variable outside of "using" blocks? I believe this variable will be disposed very quickly because it is created inside of those blocks and I am suspecting this is the reason why it expires.


    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, August 17, 2010 10:22 AM
    Moderator
  • Hi, I will definitely try that, but I would be surprised if this is at fault. First, the delegate gets invoked when updates are made to underlying tables (even if I make them 10 minutes after this code is executed), and only after the first delegate fire, does the subscription expire (and I executes the same code again to again hook into it).

    So, I suspect its something different.


    I always think tomorrow will have more time than today. And every today seems to pass-by faster than yesterday.
    Rahul Singla | http://www.rahulsingla.com
    Tuesday, August 17, 2010 10:40 AM
  • Hi Rahul,
     
    You're exactly right about this. The notification subscription is good for exactly one update. Once its firing, it expires and you have to re-execute the query (with the SqlDependency attached) to register the subscription again.
     
    Cheers,
    Bob
     
    "Rahul Singla" wrote in message news:67ce5403-9672-44f6-9c8d-3cec648494e6...

    This is in continuation to my previous thread on QN here (http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/4bbc2ae6-cb03-488e-b9ed-e16cd264a81a).

    I have been able to successfully subscribe for QN on my custom queue using the following code:

    DataProvider temp=new DataProvider();
    SqlDependency.Start(temp.conString);
    //SqlDependency.Start(temp.conString, queueName);
    
    SqlConnection con=new SqlConnection(temp.conString);
    con.Open();
    
    using (con)
    {
    	// Create a new SqlCommand object.
    	using (SqlCommand command=new SqlCommand(temp.getModuleQualifiedName("GetAllImportableInvoiceIds"), con))
    	{
    		command.CommandType = CommandType.StoredProcedure;
    		// Create a dependency and associate it with the SqlCommand.
    		//SqlDependency dependency=new SqlDependency(command, "Service=" + serviceName, 31557600);
    		SqlDependency dependency=new SqlDependency(command);
    		
    		// Maintain the refence in a class member.
    
    		// Subscribe to the SqlDependency event.
    		dependency.OnChange += new
    		 OnChangeEventHandler(handler);
    
    		// Execute the command.
    		using (SqlDataReader reader = command.ExecuteReader())
    		{
    			// Process the DataReader.
    			reader.Dispose();
    		}
    	}
    }
    
    

    The handler is invoked successfully for the query. However, the Notification subscription expires immediately after the event is raised, and I need to subscribe again to keep receiving the Notifications each time in the handler itself.

    Am I doing something wrong, or is this the intended behavior?


    I always think tomorrow will have more time than today. And every today seems to pass-by faster than yesterday.
    Rahul Singla | http://www.rahulsingla.com
    Tuesday, August 17, 2010 2:35 PM