Windows应用程序SqlDepedency无限调用Onchange [英] Windows Application SqlDepedency Calling Onchange infinitely

问题描述

我有在其中执行sqldependency的控制台应用程序.我的问题是，当我将commandType设置为Text时，它工作正常.但是，如果我将commandType用作StoredProcedure，onchange方法将无限调用.

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.

请参见下面的代码:

        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();
        }

我的存储过程是:

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

如果我将存储过程中的select语句复制为命令文本，并将commandType设置为Text，则一切正常.

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????

非常感谢.

马赫什语

推荐答案

您应该检查SqlNotificationEventArgs参数的值.仅当 Type 为更改和

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.

这篇关于Windows应用程序SqlDepedency无限调用Onchange的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！