Windows Application SqlDepedency Calling Onchange infinitely

Question

Hi,

I have console application in which I am doing sqldependency. My problem is when I set commandType as Text, it is working fine. But if I use commandType as StoredProcedure, onchange method is calling infinitely.

Please see the code below:

        static DataSet myDataSet;
        static SqlConnection connection;
        static SqlCommand command;

        static void Main(string[] args)
        {

            // Remove any existing dependency connection, then create a new one.
            string connstr = "Data Source=XYZ;Initial Catalog=Dev;Integrated Security=True";
            string ssql = @"[dbo].[SchedulerPendingControlRequestIDFetch]";

            CanRequestNotifications();


            SqlDependency.Stop(connstr);
            SqlDependency.Start(connstr);


            if (connection == null)
                connection = new SqlConnection(connstr);
            if (command == null)
                command = new SqlCommand(ssql, connection);
            command.CommandType = CommandType.StoredProcedure;

            if (myDataSet == null)
                myDataSet = new DataSet();
            GetAdvtData();

            System.Console.ReadKey();
            connection.Close();
        }

        private static bool CanRequestNotifications()
        {
            SqlClientPermission permission =
                new SqlClientPermission(
                PermissionState.Unrestricted);
            try
            {
                permission.Demand();
                return true;
            }
            catch (System.Exception)
            {
                return false;
            }
        }



        private static void GetAdvtData()
        {
            myDataSet.Clear();
            // Ensure the command object does not have a notification object.
            command.Notification = null;
            // Create and bind the SqlDependency object to the command object.
            SqlDependency dependency = new SqlDependency(command,null,100);

            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                adapter.Fill(myDataSet, "ControlRequest");

            }
        }

        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency =
        (SqlDependency)sender;

            dependency.OnChange -= dependency_OnChange;

            Console.WriteLine(e.Info.ToString() + e.Source.ToString());
            GetAdvtData();
        }

My stored Procedure is:

IF OBJECT_ID('SchedulerSirasColcoDetailFetch') IS NOT NULL
 DROP PROCEDURE SchedulerSirasColcoDetailFetch
Go
PRINT 'Creating stored procedure SchedulerSirasColcoDetailFetch'
Go

CREATE PROCEDURE [dbo].[SchedulerSirasColcoDetailFetch]
AS
BEGIN


 SELECT Colco_Code AS 'CountryCode',Connection_String AS 'Url',Resend_Interval AS 'ResendInterval',
   Default_Encoding AS 'Encoding' FROM dbo.SirasColcoDetail
END

If I copy the select statement inside stored procedure as my command text and set the commandType as Text, everything is working fine.

could you please let me know what the issue is????

Thanks a lot in advance.

Mahesh

Answer 1

You're supposed to check the values of the SqlNotificationEventArgs argument. Only if Type is Change and Source is Data where you notified for a data change.

You'll discover that you're not notified for data changes, but for incorrect settings or incorrect query. Your query and connection settings must comply with the requirements specified in Creating a Query for Notifications.