本文介绍了SqlDependency 代码监听来自 Windows 服务的表中的插入/更新的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的 SqlDependency 代码正在监听来自 Windows 服务的表中的插入/更新，当我启动该服务时，它会在接下来的 2/3 天内工作，然后它停止工作.
my SqlDependency code is listening to inserts/update in a table from windows service and when i start the service then it works for next 2/3 days and after that it stopped working.
我在这里粘贴我的完整代码.
here i am pasting my full code.
public partial class PartIndexer : ServiceBase
{
static string connectionString = "MyConnection String;Pooling=true;Connect Timeout=20;";
SqlDependency dep;
public PartIndexer()
{
InitializeComponent();
}
#region OnStart
protected override void OnStart(string[] args)
{
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
RegisterNotification();
MailNotify("STARTED");
}
#endregion
#region RegisterNotification
/// <summary>
/// RegisterNotification
/// this is main routine which will monitor data change in ContentChangeLog table
/// </summary>
private void RegisterNotification()
{
string tmpdata = "";
//eventLog1.WriteEntry("RegisterNotification invoked");
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ActivityDate FROM [bba-reman].ContentChangeLog";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
tmpdata = dr[0].ToString();
}
}
}
dr.Dispose();
cmd.Dispose();
}
}
finally
{
//SqlDependency.Stop(connStr);
}
}
#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 dep = sender as SqlDependency;
dep.OnChange -= new OnChangeEventHandler(OnDataChange);
SendMailNotification();
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 SendMailNotification()
{
// does some job
}
#endregion
#region MailNotify
/// <summary>
/// MailNotify
/// fire mail when apps start & exit
/// </summary>
/// <param name="strStatus"></param>
void MailNotify(string strStatus)
{
if (strStatus == "STARTED")
{
var template = new MailTemplate()
.WithBody("HI,<br><br>Part Indexer Started Date " + DateTime.Now.ToLongDateString())
.WithSubject("Part Indexer Started")
.WithSender("xxxxx")
.WithRecepient("xxxx")
.Send();
}
else if (strStatus == "STOPPED")
{
var template = new MailTemplate()
.WithBody("HI,<br><br>Part Indexer stopped Date " + DateTime.Now.ToLongDateString())
.WithSubject("Part Indexer Stopped")
.WithSender("xxx")
.WithRecepient("xxx")
.Send();
}
}
#endregion
#region OnStop
protected override void OnStop()
{
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
}
#endregion
}
我从这个网址看到另一个人面临同样的问题 SqlDependency 错误很久以后
i saw another guy face the same problem from this url SqlDependency error after a long time
他说发生超时错误.根据他的建议，我将代码更改为
he said timeout error occur. as per his suggestion i change my code bit like
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;
//SqlDependency dep = sender as SqlDependency;
//dep.OnChange -= new OnChangeEventHandler(OnDataChange);
if (e.Source == SqlNotificationSource.Timeout)
{
// just restart notification
RegisterNotification();
return;
}
else if (e.Source != SqlNotificationSource.Data)
{
ReStartService();
}
SendMailNotification();
RegisterNotification();
}
看看这行代码
if (e.Source == SqlNotificationSource.Timeout)
{
// just restart notification
RegisterNotification();
return;
}
else if (e.Source != SqlNotificationSource.Data)
{
Environment.Exit(1);
}
如果发生超时，我将再次调用 RegisterNotification() 函数.我在阅读该 url 后写了这些行，但不确定上面的行会做什么?
if timeout occur then i am calling RegisterNotification() function again.i wrote these line after reading that url but not sure what the above line will do?
所以请指导我我是否在正确的轨道上?请帮助我使我的应用程序没有错误，以便它可以长时间聆听而没有任何问题.谢谢
so just guide me am i on right track? please help me make my apps bug free so it can listen for long without any problem. thanks
推荐答案
获取完整代码.
#region all using
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
#endregion
namespace PartIndexerService
{
public partial class PartIndexer : ServiceBase
{
static string connectionString = "server=sql08-2.orcsweb.com;uid=bba-reman;password=_bba_1227_kol;database=BBAreman;Pooling=true;Connect Timeout=20;";
SqlDependency dep;
public PartIndexer()
{
InitializeComponent();
}
#region OnStart
protected override void OnStart(string[] args)
{
RegisterNotification();
MailNotify("STARTED");
}
#endregion
#region RegisterNotification
/// <summary>
/// RegisterNotification
/// this is main routine which will monitor data change in ContentChangeLog table
/// </summary>
private void RegisterNotification()
{
string tmpdata = "";
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 ActivityDate FROM [bba-reman].ContentChangeLog";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
tmpdata = dr[0].ToString();
}
}
}
dr.Dispose();
cmd.Dispose();
}
}
finally
{
//SqlDependency.Stop(connStr);
}
}
#endregion
public void ReStartService()
{
ServiceController service = new ServiceController("PartIndexer");
if ((service.Status.Equals(ServiceControllerStatus.Stopped)) || (service.Status.Equals(ServiceControllerStatus.StopPending)))
{
service.Start();
}
else
{
service.Stop();
service.WaitForStatus(ServiceControllerStatus.Stopped);
service.Start();
service.WaitForStatus(ServiceControllerStatus.Running);
}
}
#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;
if (e.Source == SqlNotificationSource.Timeout)
{
var template = new MailTemplate()
.WithBody("HI,<br><br>Part Indexer Service Exception Timeout occur " + DateTime.Now.ToLongDateString())
.WithSubject("Part Indexer Service Exception Timeout occur")
.WithSender("[email protected]")
.WithRecepient("[email protected]")
.Send();
RegisterNotification();
return;
}
else if (e.Source != SqlNotificationSource.Data)
{
var template = new MailTemplate()
.WithBody("HI,<br><br>Part Indexer Service Exception SqlNotificationSource.Data " + DateTime.Now.ToLongDateString())
.WithSubject("Part Indexer Service Exception SqlNotificationSource.Data")
.WithSender("[email protected]")
.WithRecepient("[email protected]")
.Send();
Environment.Exit(1);
}
StartIndex();
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()
{
//eventLog1.WriteEntry("Web Service called start for indexing data");
PartIndexerWS.AuthHeader oAuth = new PartIndexerWS.AuthHeader();
oAuth.Username = "Admin";
oAuth.Password = "Admin";
PartIndexerWS.SearchDataIndex DataIndex = new PartIndexerWS.SearchDataIndex();
DataIndex.AuthHeaderValue = oAuth;
try
{
DataIndex.StartIndex();
//eventLog1.WriteEntry("Web Service called stop for indexing data");
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message.ToString());
//eventLog1.WriteEntry("Web Service call error "+ex.Message.ToString());
}
}
#endregion
#region MailNotify
/// <summary>
/// MailNotify
/// fire mail when apps start & exit
/// </summary>
/// <param name="strStatus"></param>
void MailNotify(string strStatus)
{
if (strStatus == "STARTED")
{
var template = new MailTemplate()
.WithBody("HI,<br><br>Part Indexer Started Date " + DateTime.Now.ToLongDateString())
.WithSubject("Part Indexer Started")
.WithSender("[email protected]")
.WithRecepient("[email protected]")
.Send();
//eventLog1.WriteEntry("mail fired ");
}
else if (strStatus == "STOPPED")
{
var template = new MailTemplate()
.WithBody("HI,<br><br>Part Indexer stopped Date " + DateTime.Now.ToLongDateString())
.WithSubject("Part Indexer Stopped")
.WithSender("[email protected]")
.WithRecepient("[email protected]")
.Send();
//eventLog1.WriteEntry("mail fired ");
}
}
#endregion
#region OnStop
protected override void OnStop()
{
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
//eventLog1.WriteEntry("Part Indexer stopped Date : " + DateTime.Now.ToLongDateString());
}
#endregion
}
}
这篇关于SqlDependency 代码监听来自 Windows 服务的表中的插入/更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！