Issue of multiple SQL notifications in ASP.Net web

Question

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

Answer 1

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.

Answer 2

This is probably caused by connection pooling. It reurns a notification for each connection open in the pool. You can cancel the pooling for this specific service by changing the Connection String property:

Pooling = False;