Click here to Skip to main content
15,890,506 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I wanted notification should be shown, when there is any change in Database. I tried SQLDependency in Console Application(C#)(as a trial), it works perfect.
Now when I tried the same in WebForm(ASP.Net), the event is not triggering. I don't know where I am going wrong

What I have tried:

C#
<pre>namespace WebMessage
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        static string connectionString = @"Data Source=PBA-PC\SQLEXPRESS; Initial Catalog = MyDB; Integrated Security=SSPI;";
        
        public WebForm1()
        {
           
        }
        public void Page_Load(object sender, EventArgs e)
        {
            SqlDependency.Start(connectionString);
            getDataWithSqlDependency();
            HttpContext.Current.Response.Write("Waiting for Data Changes");
            // Console.WriteLine("Waiting for data changes");
            //Console.WriteLine("Press enter to quit");
            // Console.WriteLine(CanRequestNotifications().ToString());
            //Console.ReadLine();
            SqlDependency.Stop(connectionString);
        }
        static DataTable getDataWithSqlDependency()
        {
            //Console.WriteLine("getDataWithSqlDependency");
            using (var connection = new SqlConnection(connectionString))
            using (var cmd = new SqlCommand("SELECT ID,UserName,Email FROM dbo.[myUser]", connection))
            {
                var dt = new DataTable();
                // Create dependency for this command and add event handler
                var dependency = new SqlDependency(cmd);
                dependency.OnChange += new OnChangeEventHandler(OnChange);
                // execute command to get data
                connection.Open();
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                HttpContext.Current.Response.Write(dt.Rows.Count.ToString());
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    HttpContext.Current.Response.Write(dt.Rows[i]["UserName"].ToString());
                    HttpContext.Current.Response.Write(dt.Rows[i]["Email"].ToString());
                }
              
                return dt;
            }

        }
        static void OnChange(object sender, SqlNotificationEventArgs e)
        {
            HttpContext.Current.Response.Write($"OnChange Event fired. SqlNotificationEventArgs: Info={e.Info}, Source={e.Source}, Type={e.Type}.");
            if ((e.Info != SqlNotificationInfo.Invalid) && (e.Type != SqlNotificationType.Subscribe))
            {
                //resubscribe
                var dt = getDataWithSqlDependency();
                HttpContext.Current.Response.Write($"Data changed. {dt.Rows.Count} rows returned.");
            }
            else
            {
                HttpContext.Current.Response.Write("SqlDependency not restarted");
            }
            

        }
    }
}
Posted
Comments
Richard Deeming 29-May-19 11:51am    
Maybe something to do with the fact that you're calling SqlDependency.Stop immediately, without executing any code that would trigger a change notification?
SqlDependency in an ASP.NET Application | Microsoft Docs[^]
Priya Karthish 29-May-19 12:31pm    
In ConsoleApplication, it works perfect. only in Webform, not working. So only I am confused

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900