Sql依赖onchange事件不会每次c#都触发 [英] Sql Dependency onchange event not firing every time c#
问题描述
我在windows服务中实现了sql依赖.当表中的数据发生更改时，将触发 onchange 事件，然后我将调用 Web 服务.
I have implemented sql dependency in windows service. when data will be changed in table then onchange event will fire and from there I am invoking a web service.
我将分享我的完整代码.我在我的 PC 上测试了很多次，然后在生产 PC 上安装 Windows 服务它可以工作.假设如果我今天在生产 PC 上安装，那么它今天可以使用，但是当我第二天测试时，它的 onchange 事件没有触发.
I will share my full code. I tested many time on my PC before installing the windows service on production PC it works. Suppose if I install on production PC today then it works for today but when I test next day then its onchange event was not firing.
所以我发现 onchange 事件只在第一天触发，从第二天开始 onchange 事件没有触发.可能是我在代码中犯了一些错误.所以这是我的要求，请有人详细查看我的代码，并帮助我解决无法正常工作的错误.
so I found onchange event firing only first day and from the next day onchange event not firing. May be I have made some mistake in code. so it is my request please some one see my code in details and help me where I have made the mistake for which it is not working properly.
public partial class PartIndexer : ServiceBase
{
static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
SqlDependency dep;
public PartIndexer()
{
InitializeComponent();
}
private string GetLoggedInUser()
{
string userName = "";
if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
{
userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
}
return userName;
}
#region OnStart
protected override void OnStart(string[] args)
{
BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
RegisterNotification();
MailSend(); // notification mail send
BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion
#region RegisterNotification
/// <summary>
/// RegisterNotification
/// this is main routine which will monitor data change in ContentChangeLog table
/// </summary>
private void RegisterNotification()
{
string tmpdata = "";
BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TestTable FROM [dbo].ContentChangeLog";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
dr.Read();
tmpdata = dr[0].ToString();
}
}
dr.Dispose();
cmd.Dispose();
}
}
catch (Exception ex)
{
BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
}
finally
{
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
}
}
#endregion
#region OnDataChange
/// <summary>
/// OnDataChange
/// OnDataChange will fire when after data change found in ContentChangeLog table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
if (e.Source == SqlNotificationSource.Timeout)
{
MailSend(); // notification mail send
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
MailSend(); // notification mail send
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
StartIndex();
BBALogger.Write("PartIndexer Service Data changed", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
RegisterNotification();
}
#endregion
#region StartIndex
/// <summary>
/// StartIndex
/// this routine will call web service in bba reman website which will invoke routine to re-index data
/// </summary>
void StartIndex()
{
// calling web service if change is notified
}
#endregion
#region MailSend
/// <summary>
/// MailNotify
/// fire mail when apps start & exit
/// </summary>
/// <param name="strStatus"></param>
void MailSend()
{
// mail send code
}
#endregion
#region OnStop
protected override void OnStop()
{
BBALogger.Write("PartIndexer Service StartIndex called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
}
#endregion
}
另一个问题，我注意到当我启动 Windows 服务并让它运行一天时，当我尝试停止或重新启动 Windows 服务时，我无法停止 Windows 服务.这意味着我的代码中肯定存在一些我无法指出的缺陷.所以请帮助我.谢谢
Another issue I noticed that when I start my windows service and leave it run for one day then when I try to stop or restart the the windows service then I windows service cannot be stopped. It means definitely there is some flaw in my code which I am not being able to point out. so please help me. thanks
推荐答案
我刚刚注意到你用过
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
sql 依赖在RegisterNotification() 函数内启动和停止，但它的方式不正确，可能会导致下次调用事件时出现问题.它可能会正常工作一次.
sql dependency start and stop inside RegisterNotification() function, but its not correct way, and it may cause the problem to call event next time. it may work one time correctly.
你只是尝试在windows中使用启动功能
you just try to use in windows start function
#region OnStart
protected override void OnStart(string[] args)
{
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
RegisterNotification();
MailSend(); // notification mail send
BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion
希望它能解决您的问题，谢谢.
i hope it will resolve your issue, thanks.
这篇关于Sql依赖onchange事件不会每次c#都触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
