在ASP.NET C＃中不触发Sqldependency [英] Sqldependency not triggering in ASP.NET C#

问题描述

我希望在数据库中有任何更改时显示通知。我在控制台应用程序（C＃）中尝试了SQLDependency（作为试用版），它运行完美。

现在当我在WebForm（ASP.Net）中尝试相同时，事件没有触发。我不知道我哪里错了



我尝试过：

< pre>名称空间WebMessage 
 {
  public   partial   class  WebForm1：System.Web.UI.Page 
 {
  static   string  connectionString =  @ 数据源= PBA-PC\SQLEXPRESS;初始目录= MyDB;集成安全性= SSPI;; 
 
  public  WebForm1（）
 {
 
} 
  public   void  Page_Load（ object  sender，EventArgs e）
 {
 SqlDependency.Start（connectionString）; 
 getDataWithSqlDependency（）; 
 HttpContext.Current.Response.Write（ 等待数据更改）; 
  //   Console.WriteLine（等待数据更改）;  
  //   Console.WriteLine（按Enter键退出）;  
  //   Console.WriteLine（CanRequestNotifications（）。ToString（））;  
  //   Console.ReadLine（）;  
 SqlDependency.Stop（connectionString）; 
} 
 静态 DataTable getDataWithSqlDependency（）
 {
  //   Console.WriteLine（getDataWithSqlDependency）;  
 使用（ var  connection =  new  SqlConnection（connectionString））
 使用（ var  cmd =  new  SqlCommand（  SELECT ID，UserName，Email FROM dbo。[myUser]，connection））
 {
  var  dt =  new  DataTable（）; 
  //  为此命令创建依赖项并添加事件处理程序 
  var  dependency =  new  SqlDependency（cmd）; 
 dependency.OnChange + =  new  OnChangeEventHandler（OnChange）; 
  //  执行命令以获取数据 
 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; 
} 
 
} 
 静态  void  OnChange（  object  sender，SqlNotificationEventArgs e）
 {
 HttpContext.Current.Response.Write（$   OnChange事件已触发.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（$  数据已更改。{dt.Rows.Count}行已返回。）; 
} 
  else  
 {
 HttpContext.Current.Response.Write（  SqlDependency未重新启动）; 
} 
 
 
} 
} 
} 

解决方案

< blockquote> 触发OnChange事件.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（

数据已更改返回了{dt.Rows.Count}行。）;
}
else
{
HttpContext.Current.Response.Write（ SqlDependency未重新启动）;
}


}
}
}

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:

<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");
            }
            

        }
    }
}

解决方案

"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"); } } } }

这篇关于在ASP.NET C＃中不触发Sqldependency的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！