SQL database listener retuns the same data three times
Gerald Oakham
41
Reputation points
Hi all,
I am using SqlDependencyEx to listen for a change in a table on an SQL database, which works ok, however, when certain actions are performed, I get the same data 3 times, which means acting on it is problematic.
EG, When I set the s_id to "open" (6), or change its location, the XML gets produced x3 ( when I set s_id = "close" (7) I only get the XML once).
I'm pretty sure this is down to something in the database, but, is there a way to just get the first set of XML (and ignore the duplicates) ?
private void Form1_Load(object sender, EventArgs eg)
{
var connectionString = @"standardSQLConnectionString";
// See constructor optional parameters to configure it according to your needs
var listener = new SqlDependencyEx(connectionString, "dbaseName", "tableName", listenerType: SqlDependencyEx.NotificationTypes.Update); // Listen to Updates
// e.Data contains actual changed data in the XML format
listener.TableChanged += (o, e) =>
{
toolStripStatusLabel1.Text = "Seen change..";
var tableChange = e.Data.ToString();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(tableChange);
//XmlNodeList availChangeINS = xmlDoc.GetElementsByTagName("inserted");
XmlNodeList availChangeINS = xmlDoc.FirstChild.SelectNodes("inserted");
//// debug
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
XmlTextWriter xtw = null;
try
{
xtw = new XmlTextWriter(sw);
xtw.Formatting = System.Xml.Formatting.Indented;
xmlDoc.WriteTo(xtw);
}
finally
{
if (xtw != null)
xtw.Close();
}
sb.ToString();
SetText(sb + Environment.NewLine+ " ----------------------------------------------------------------- " + Environment.NewLine);
//// debug
foreach (XmlNode changedRow in availChangeINS)
{
XmlNodeList rowList = availChangeINS[0].SelectNodes("row");
foreach (XmlNode availDataINS in rowList)
{
if ((availDataINS["s_id"].InnerText.TrimEnd() == "6") || (availDataINS["s_id"].InnerText.TrimEnd() == "7"))
{
_res_id = availDataINS["res_id"].InnerText.TrimEnd();
_g_id = availDataINS["g_id"].InnerText.TrimEnd();
_s_id = availDataINS["s_id"].InnerText.TrimEnd();
_r_id = availDataINS["r_id"].InnerText;
}
else
{
_res_id = availDataINS["resn_id"].InnerText.TrimEnd();
_s_id = "0";
_s_name = "Not open ot close";
SetText1("Not a open or close - " + _res_id + Environment.NewLine);
}
}
if (_status_id != "0")
{
/// do some stuff
}
}
if (_status_name == "open")
{
SetText1(_s_name + " >>> " + _res_id + Environment.NewLine);
}
else if (_status_name == "close")
{
SetText1(_s_name + " <<< " + _resn_id + Environment.NewLine);
}
};
listener.Start();
}
/// FUNCTIONS
private void SetText(string text)
{
// InvokeRequired required compares the thread ID of the
// calling thread to the thread ID of the creating thread.
// If these threads are different, it returns true.
if (this.richTextBox1.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(SetText);
this.Invoke(d, new object[] { text });
}
else
{
this.richTextBox1.AppendText(text);
}
}
private void SetText1(string text)
{
if (this.richTextBox2.InvokeRequired)
{
SetTextCallback d = new SetTextCallback(SetText1);
this.Invoke(d, new object[] { text });
}
else
{
this.richTextBox2.AppendText(text);
}
}
XML output being read :
<root>
<inserted>
<row>
<res_id>2092139</re_id>
<g_id>264444</g_id>
<s_id>6</s_id>
<r_id>4</r_id>
</inserted>
<deleted>
<row>
<re_id>2092139</re_id>
<g_id>264444</g_id>
<s_id>6</s_id>
<r_id>4</r_id>
</row>
</deleted>
</root>
<root>
<inserted>
<row>
<res_id>2092139</re_id>
<g_id>264444</g_id>
<s_id>6</s_id>
<r_id>4</r_id>
</inserted>
<deleted>
<row>
<re_id>2092139</re_id>
<g_id>264444</g_id>
<s_id>6</s_id>
<r_id>4</r_id>
</row>
</deleted>
</root>
<root>
<inserted>
<row>
<res_id>2092139</re_id>
<g_id>264444</g_id>
<s_id>6</s_id>
<r_id>4</r_id>
</inserted>
<deleted>
<row>
<re_id>2092139</re_id>
<g_id>264444</g_id>
<s_id>6</s_id>
<r_id>4</r_id>
</row>
</deleted>
</root>
Thank you all in advance