SQL database listener retuns the same data three times

Gerald Oakham 21 Reputation points
2022-09-29T17:28:54.49+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,557 questions
{count} votes