c# – 使用SqlDependency导致不断更新

我从 this MSDN page中提取了一个例子并且几乎逐字地使用了它.运行时代码编译正确,但无论是否实际对返回的数据进行了更改,changeCount都会无限增加.实际发生更改时,dataGridView1会正确反映更改.为什么我的sqlDependency看起来像是在循环中触发,即使显然没有变化？

这是来源：

#region Using directives
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.sqlClient;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Permissions;
using System.Text;
using System.Windows.Forms;
#endregion

namespace PreAllocation_Check
{
    public partial class Form1 : Form
    {
        int           changeCount = 0;
        const string  tableName = "MoxyPosition";
        const string  statusMessage = "Last: {0} - {1} changes.";
        DataSet       dataToWatch = null;
        sqlConnection MoxyConn = null;
        sqlCommand    sqlComm = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender,EventArgs e)
        {
            if (CanRequestNotifications())
            {
                sqlDependency.Start(GetConnectionString());

                if (MoxyConn == null)
                    MoxyConn = new sqlConnection(GetConnectionString());

                if (sqlComm == null)
                {
                    sqlComm = new sqlCommand(Getsql(),MoxyConn);

                    sqlParameter prm = new sqlParameter("@Quantity",sqlDbType.Int);
                    prm.Direction = ParameterDirection.Input;
                    prm.DbType = DbType.Int32;
                    prm.Value = 100;
                    sqlComm.Parameters.Add(prm);
                }

                if (dataToWatch == null)
                    dataToWatch = new DataSet();

                GetData();
            }
        }

        private void Form1_FormClosed(object sender,FormClosedEventArgs e)
        {
            sqlDependency.Stop(GetConnectionString());
            if (MoxyConn != null)
                MoxyConn.Close();
        }

        private bool CanRequestNotifications()
        {
            try
            {
                sqlClientPermission sqlPerm = new sqlClientPermission(PermissionState.Unrestricted);
                sqlPerm.Demand();
                return true;
            }
            catch
            {
                return false;
            }
        }

        private string GetConnectionString()
        {
            return "server=***;database=***;user id=***;password=***";
        }

        private void GetData()
        {
            dataToWatch.Clear();
            sqlComm.Notification = null;
            sqlDependency sqlDep = new sqlDependency(sqlComm);
            sqlDep.OnChange += new OnChangeEventHandler(sqlDep_OnChange);

            using (sqlDataAdapter adapter = new sqlDataAdapter(sqlComm))
            {
                adapter.Fill(dataToWatch,tableName);
                dataGridView1.DataSource = dataToWatch;
                dataGridView1.DataMember = tableName;
            }
        }

        private string Getsql()
        {
            return "SELECT PortID,CONVERT(money,SUM(PreAllocPos),1) AS PreAllocation,SUM(AllocPos),1) AS Allocation,SUM(PreAllocPos) - SUM(AllocPos),1) AS PreLessAlloc " +
                   "FROM MoxyPosition " +
                   "WHERE CONVERT(money,PreAllocPos,1) <> CONVERT(money,AllocPos,1) " +
                   "GROUP BY PortID " +
                   "ORDER BY PortID ASC;";
        }

        void sqlDep_OnChange(object sender,sqlNotificationEventArgs e)
        {
            ISynchronizeInvoke i = (ISynchronizeInvoke)this;

            if (i.Invokerequired)
            {
                OnChangeEventHandler tempDelegate = new OnChangeEventHandler(sqlDep_OnChange);
                object[] args = { sender,e };
                i.BeginInvoke(tempDelegate,args);
                return;
            }

            sqlDependency sqlDep = (sqlDependency)sender;
            sqlDep.OnChange -= sqlDep_OnChange;

            changeCount++;
            DateTime LastRefresh = System.DateTime.Now;
            label1.Text = String.Format(statusMessage,LastRefresh.TimeOfDay,changeCount);

            GetData();
        }
    }
}

编辑：值得注意的是,我想要运行此数据库的数据库当前没有启用Broker服务,因此要测试我的代码我备份了我的目标数据库并使用新名称恢复它,然后运行ALTER DATABASE my_db_name SET ENABLE_BROKER反对.我的所有测试都在这个备用数据库上,这意味着我是唯一的用户.

解决方法

这是一个老问题,但问题是您的查询不符合要求.

简短回答：
将模式名称添加到表“FROM DBO.MoxyPosition”

更长的答案：
您可以看到list of requirements here,这与创建索引视图非常相似.注册 sql依赖项时,如果它无效,通知会立即触发,让您知道它无效.当你考虑它时,这是有道理的,因为Visual Studio如何知道sql Engine的内部要求是什么？

因此,在sqlDep_OnChange函数中,您将要查看依赖项触发的原因.原因在于e变量(info,source和type).有关事件对象的详细信息,请访问：

> Info options
> Source
> Type

针对您的具体情况,请注意MS describes的Type属性：

Gets a value that indicates whether this notification is generated 
because of an actual change,OR BY THE SUBSCRIPTION.

c# – 使用SqlDependency导致不断更新

解决方法

猜你在找的C#相关文章