question

GeraldOakham-8014 avatar image
0 Votes"
GeraldOakham-8014 asked GeraldOakham-8014 commented

SQL database listener retuns the same data three times

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

dotnet-sqlclient
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GeraldOakham-8014, Welcome to Microsoft Q&A, I am not clear about your example about set the s_id to "open" (6) and set s_id = "close" (7) . Could you provide your database design?

0 Votes 0 ·

Hi JackJJun-MSFT,

applogies for my late reply.
turns out there is a better table in the database to listen to, which then doesn't generate the extra event, so I am using that instead.

thank you though

0 Votes 0 ·

0 Answers