c# - SQL Server 中数据更改时自动刷新应用程序

Question

我使用 SQL Server，并且有 3 个应用程序服务器。当我的数据库中的表发生更改时，我需要那些应用程序服务器刷新本地缓存数据。我对已知更改使用触发器并通过服务代理队列发送消息。然后我创建一个存储过程并将其分配给激活队列的存储过程，在这个存储过程中我收到消息，但我不知道我应该如何在我的应用程序中调用刷新方法。

Answer 1

我有类似的问题，但这段代码解决了问题:

public class QueryNotification
{
    public DataSet DataToWatch { get; set; }
    public SqlConnection Connection { get; set; }
    public SqlCommand Command { get; set; }
    
    public string GetSQL()
    {
        return "SELECT * From YourTable";
    }
    
    public string GetConnection()
    {
        return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    }
    
    public bool CanRequestNotifications()
    {
    
        try
        {
            var perm = new SqlClientPermission(PermissionState.Unrestricted);
            
            perm.Demand();
            return true;
        }
        catch
        {
            return false;
        }
    }
    
    public void GetData()
    {
        DataToWatch.Clear();
        Command.Notification = null;
        var dependency = new SqlDependency(Command);
        dependency.OnChange += dependency_OnChange;
    
        using (var adapter = new SqlDataAdapter(Command))
        {
            adapter.Fill(DataToWatch, "YourTableName");
        }
    }
    
    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
    
        var i = (ISynchronizeInvoke)sender;
    
        if (i.InvokeRequired)
        {
    
            var tempDelegate = new OnChangeEventHandler(dependency_OnChange);
    
            object[] args = { sender, e };
    
            i.BeginInvoke(tempDelegate, args);
    
            return;
        }
    
        var dependency = (SqlDependency)sender;
    
        dependency.OnChange -= dependency_OnChange;
    
        GetData();
    }
    
}

更新:

检查权限:

public bool CanRequestNotifications()
{
    try
    {
        var perm = new SqlClientPermission(PermissionState.Unrestricted);
                    
        perm.Demand();
        return true;
    }
    catch
    {
        return false;
    }
}

例如在您的窗口加载:

if (!_queryNotification.CanRequestNotifications())
{
    MessageBox.Show("ERROR:Cannot Connect To Database");
}
                
SqlDependency.Stop(_queryNotification.GetConnection());
SqlDependency.Start(_queryNotification.GetConnection());
    
if (_queryNotification.Connection == null)
{
    _queryNotification.Connection = new SqlConnection(_queryNotification.GetConnection());
}
    
if (_queryNotification.Command == null)
{
    _queryNotification.Command = new SqlCommand(_queryNotification.GetSQL(),
    _queryNotification.Connection);
}
if (_queryNotification.DataToWatch == null)
{
    _queryNotification.DataToWatch = new DataSet();
}

GetData();