在 ASP.Net Web 应用程序中页面刷新时发出多个 SQL 通知 [英] Issue of multiple SQL notifications in ASP.Net web application on page refresh

问题描述

我在使用 SQL Server 通知时遇到问题.我正在 ASP.net 中开发一个 Web 应用程序，其中一个页面需要收到有关 SQL Server 数据库中一个表中的新条目的通知.我正在使用 SQL Server 通知服务和 Signal R 来实现此功能.我的网页获取有关新数据条目的更新似乎一切正常.

使用通知的页面刷新时出现问题.我发现数据库中单个条目的通知数量随着刷新次数的增加而增加.因此，如果我刷新页面三次，我会收到 3 个条目的通知.我有点担心当连接的用户数量增加时这是否会给服务器带来负担.此外，如果在处理使用新条目更新页面的请求时出现错误，用户会收到多条具有相同文本的错误消息.我尝试调试我的代码，发现使用的 SqlDependency 对象的 on change 事件每次都使用不同的 ID 多次触发.以下是我的代码使用通知所做的操作的简要概述 -

1. 我使用的是 SQL Server 2012 并且为数据库设置了 enable_broker.

2. 在 global.asax 中，我使用 application_start 和 application_stop 事件来启动和停止 SqlDependency.

3. 在页面代码中，我在页面加载时设置一个新的 SqlDependency 对象，使用命令对象来监视表的确切数据字段.

4. 当 SqlDependency 对象的 onchange 触发时，我将使用 Signal R hub 类通知 UI.然后我删除 SqlDependency 对象的 OnChange 处理程序，调用 SqlDependency.Stop(connectionstring)，设置 SqlDependency对象为空，调用 SqlDependency.Start(connectionstring) 并最终使用命令对象再次设置 SqlDependency 对象以更新数据.这整个设置为 nothing-stop-start-reset 对象是为了继续监控数据的变化.

上述步骤工作正常，但当我刷新页面时，这些步骤会重复刷新次数.我通过更改代码和调试尝试了很多东西，但似乎没有解决问题.现在我想知道是不是我错过了某些设置.

请帮我解决这个问题.如果需要任何其他信息，例如环境、编码详细信息等，也请告诉我.

问候，谭美

解决方案

我已经通过使用下面的代码解决了以下问题，它对我有用.

<块引用>

1. SingletonDbConnect.cs

公共类 SingletonDbConnect{私有静态 SingletonDbConnect dbInstance；私有静态字符串 connString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;私有只读 SqlConnection conn = new SqlConnection(connString);私有 SingletonDbConnect(){}公共静态 SingletonDbConnect getDbInstance(){如果(dbInstance == null){dbInstance = new SingletonDbConnect();}返回数据库实例；}公共 SqlConnection getDbConnection(){尝试{conn.Close();连接.Open();}捕获(SqlException e){}最后{}返回连接；}}

<块引用>

2. SqlDependencyEvent.cs

公共类SqlDependencyEvent{内部静态 int PageLoadCounter = 0;public void getEmailMessagesByEmailId(Guid emailid){SingletonDbConnect conn = SingletonDbConnect.getDbInstance();使用 (MembersController.command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), conn.getDbConnection())){MemberController.command.Notification = null;if (MembersController.dependency == null){MemberController.dependency = new SqlDependency(MembersController.command);MembersController.dependency.OnChange += new OnChangeEventHandler(emailMessages_OnChange);}var reader = MembersController.command.ExecuteReader();}页面加载计数器++；}私人无效emailMessages_OnChange(对象发件人，SqlNotificationEventArgs e){if (e.Type == SqlNotificationType.Change){if (MembersController.dependency != null){MemberController.dependency.OnChange -= emailMessages_OnChange;}NotificationHub.EmailUpdateRecords();SingletonDbConnect conn = SingletonDbConnect.getDbInstance();使用 (MembersController.command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), conn.getDbConnection())){MemberController.command.Parameters.Add(new SqlParameter("@emailaccountid", defaultemailid));MemberController.command.Notification = null;MemberController.dependency = new SqlDependency(MembersController.command);MembersController.dependency.OnChange += new OnChangeEventHandler(emailMessages_OnChange);var reader = MembersController.command.ExecuteReader();}页面加载计数器++；}}}

<块引用>

3. MembersController.cs

public class MembersController : Controller{SingletonDbConnect conn = SingletonDbConnect.getDbInstance();内部静态 SqlCommand 命令 = null；内部静态 SqlDependency 依赖项 = null；////获取:/成员/公共 ActionResult 索引(){SqlDependency.Stop(conn.getDbConnection().ConnectionString);SqlDependency.Start(conn.getDbConnection().ConnectionString);返回视图()；}}

它解决了我的问题并为我工作，即使我们刷新页面超过 1，但 SqlDependency 只会调用一次.我使用了一个 MemberController 来启动和停止 SqlDependency，它是你自己的逻辑，你可以在 Global.ascx 中使用相同的代码而不是 MembersController.cs

我希望它能帮助你解决问题.问我您是否还有任何问题，谢谢.

I am facing an issue while using SQL Server Notifications. I am developing a web application in ASP.net where one of the page needs to be notified about new entries in one of the tables in a SQL Server database. I am using SQL Server Notification services along with Signal R to achieve this functionality.All seems to work fine with my web page getting updates about new data entries.

The problem arises when the page using notification is refreshed. I find the no of notification for single entry in database go up by the number of refreshes. So if I refresh the page thrice, I get 3 notifications for one entry. I am bit concerned if this would be a burden on server when the no of connected users increases. Also if there is an error while processing the request to update the page with new entry, the user gets multiple error messages with same text. I tried debugging my code and found out that the on change event of SqlDependency object used is fired multiple time with different IDs every time. Below is brief overview of what my code is doing to use notifications -

1. I am using SQL Server 2012 and enable_broker is set for the database.

2. In global.asax, I am using application_start and application_stop events to start and stop SqlDependency.

3. In page code, I am setting a new SqlDependency object on page load using a command object to monitor the exact data field of the table.

4. When onchange of SqlDependency object fires, I am notifying the UI using Signal R hub class. Then I remove the OnChange handler of the SqlDependency object, call for SqlDependency.Stop(connectionstring), set SqlDependency object to nothing, call for SqlDependency.Start(connectionstring) and finally set up the SqlDependency object again using the command object for updated data. This whole set to nothing-stop-start-reset object is to continue monitoring the data for changes.

The above steps work fine but when I refresh the page, those are repeated for the number of refreshes. I tried a lot of things by changing code and debugging but nothing seems to resolve the issue. Now I am wondering if it is some setting somewhere that I missed.

Please help me resolve this issue. Also let me know if any other information such as environment, coding details etc are required.

Regards, Tanmay

解决方案

i have resolved the following problem by using the below code, its works me.

1. SingletonDbConnect.cs

public class SingletonDbConnect
{
    private static SingletonDbConnect dbInstance;
    private static string connString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
    private readonly SqlConnection conn = new SqlConnection(connString);

    private SingletonDbConnect()
    {
    }

    public static SingletonDbConnect getDbInstance()
    {
        if (dbInstance == null)
        {
            dbInstance = new SingletonDbConnect();
        }
        return dbInstance;
    }

    public SqlConnection getDbConnection()
    {
        try
        {
            conn.Close();
            conn.Open();
        }
        catch (SqlException e)
        {

        }
        finally
        {
        }
        return conn;
    }

}

2. SqlDependencyEvent.cs

public class SqlDependencyEvent
{
  internal static int PageLoadCounter = 0;
  public void getEmailMessagesByEmailId(Guid emailid)
    {

        SingletonDbConnect conn = SingletonDbConnect.getDbInstance();

        using (MembersController.command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), conn.getDbConnection()))
        {

            MembersController.command.Notification = null;

            if (MembersController.dependency == null)
            {
                MembersController.dependency = new SqlDependency(MembersController.command);
                MembersController.dependency.OnChange += new OnChangeEventHandler(emailMessages_OnChange);
            }
            var reader = MembersController.command.ExecuteReader();
        }

        PageLoadCounter++;
    }
    private void emailMessages_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {

            if (MembersController.dependency != null)
            {
                MembersController.dependency.OnChange -= emailMessages_OnChange;
            }

            NotificationHub.EmailUpdateRecords();

            SingletonDbConnect conn = SingletonDbConnect.getDbInstance();
            using (MembersController.command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), conn.getDbConnection()))
            {
                MembersController.command.Parameters.Add(new SqlParameter("@emailaccountid", defaultemailid));
                MembersController.command.Notification = null;

                MembersController.dependency = new SqlDependency(MembersController.command);
                MembersController.dependency.OnChange += new OnChangeEventHandler(emailMessages_OnChange);

                var reader = MembersController.command.ExecuteReader();

            }

            PageLoadCounter++;


        }
    }

}

3. MembersController.cs

public class MembersController : Controller
{
    SingletonDbConnect conn = SingletonDbConnect.getDbInstance();

    internal static SqlCommand command = null;
    internal static SqlDependency dependency = null;

    //
    // GET: /Members/
    public ActionResult Index()
    {
        SqlDependency.Stop(conn.getDbConnection().ConnectionString);
        SqlDependency.Start(conn.getDbConnection().ConnectionString);
        return View();
    }
 }

its resolved my problem and its working me, even we refresh page more than 1, but SqlDependency will call only once. i used one of the MembersController for SqlDependency start and stop, its your own logic, you can use the same code in Global.ascx instead of MembersController.cs

i hope it will help you and resolve issue. ask me if you have still any problem thanks.

这篇关于在 ASP.Net Web 应用程序中页面刷新时发出多个 SQL 通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！