Geek Answers Handbook

Issuing multiple SQL notifications in an ASP.Net web application on an update page

I am facing a problem when using SQL Server notifications. I am developing a web application in ASP.net where one of the pages should be notified of new entries in one of the tables in the SQL Server database. I use SQL Server Notification Services with Signal R to achieve this functionality. Everything seems to be working fine with my webpage getting updates on new data records.

The problem occurs when the notification page refreshes. I found that the number of notifications about a single record in the database is growing in the number of updates. Therefore, if I refresh the page three times, I get 3 notifications for one record. I am a little worried that this will be a burden for the server when the number of connected users does not increase. In addition, if an error occurs while processing a request to update a page with a new record, the user receives several error messages with the same text. I tried debugging my code and found out that the on change event from the used SqlDependency object is fired several times with different identifiers each time. The following is a brief overview of what my code does to use notifications -

  • I am using SQL Server 2012, and enable_broker installed for the database.

  • In global.asax I use the application_start and application_stop events to start and stop SqlDependency .

  • In the page code, I set a new SqlDependency object when the page loads, using the command object to control the exact data field of the table.

  • When the onchange object of the SqlDependency object is onchange , I notify the user interface using the Signal R concentrator class. Then I onchange handler of the onchange object, call SqlDependency.Stop(connectionstring) , set the SqlDependency object to zero, call SqlDependency.Start(connectionstring) Finally, I create the SqlDependency object again, using the command object to update the data. This whole object does not matter-stop-start-reset to continue monitoring data for changes.

The above steps work fine, but when I refresh the page, they repeat for the number of updates. I tried many things, changing the code and debugging, but nothing seems to solve the problem. Now I wonder, somewhere, somewhere, where I missed.

Please help me solve this problem. Also let me know if any other information is required, such as environment, encoding data, etc.

Regards, Tanmay

+6
source share
2 answers

This is probably due to the connection pool. It returns a notification for each connection open in the pool. You can undo the union for this particular service by changing the Connection String property:

 Pooling = False;
0
source

I solved the following problem using the code below, its working me.

  • 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; } }
  1. 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++; } } }
  1. 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 solved my problem and its working, even we refresh the page more than 1, but SqlDependency will call only once. I used one of the StartController for SqlDependency and stopped its own logic, you can use the same code in Global.ascx instead of MembersController.cs

I hope this helps you solve the problem. ask me if you still have any problems.

0
source

More articles:


All Articles