title: SQL Dependency queries the whole table
body: <p>I have a console app that monitors a database using <code>SqlDependency</code>. The app is to monitor the database table and send an email once a new record is added in the table. </p>
  
  <p>Everything works fine, just that each time a new record is added the whole query is executed again and all the records in the table are returned. What I actually want is to return only the newly added record in the table. </p>
  
  <p>Here is my code below:</p>
  
  <p>SQLWatcher class:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.SqlClient;
  using System.Linq;
  using System.Text;
  using System.Threading;
  using System.Threading.Tasks;
  
  namespace TestApp
  {
      public enum SqlWatcherNotificationType
      {
          Blocking,
          Threaded // Launch in another thread so SqlWatcher can immediately start monitoring again.
      }
  
      public class SqlWatcher : IDisposable
      {
          private string ConnectionString;
          private SqlConnection Connection;
          private SqlCommand Command;
          private SqlDataAdapter Adapter;
          private DataSet Result;
          private SqlWatcherNotificationType NotificationType;
  
          public SqlWatcher(string ConnectionString, SqlCommand Command, SqlWatcherNotificationType NotificationType)
          {
              this.NotificationType = NotificationType;
              this.ConnectionString = ConnectionString;
  
              SqlDependency.Start(this.ConnectionString);
  
              this.Connection = new SqlConnection(this.ConnectionString);
              this.Connection.Open();
              this.Command = Command;
              this.Command.Connection = this.Connection;
  
              Adapter = new SqlDataAdapter(this.Command);
          }
  
          public void Start()
          {
              RegisterForChanges();
          }
  
          public void Stop()
          {
              SqlDependency.Stop(this.ConnectionString);
          }
  
          public delegate void SqlWatcherEventHandler(DataSet Result);
  
          public event SqlWatcherEventHandler OnChange;
  
          public DataSet DataSet
          {
              get { return Result; }
          }
  
          private void RegisterForChanges()
          {
              // Remove old dependency object
              this.Command.Notification = null;
  
              // Create new dependency object
              SqlDependency dep = new SqlDependency(this.Command);
              dep.OnChange += new OnChangeEventHandler(Handle_OnChange);
  
              // Save data
              Result = new DataSet();
              Adapter.Fill(Result);
  
              // Notify client of change to DataSet
              switch (NotificationType)
              {
                  case SqlWatcherNotificationType.Blocking:
                      OnChange(Result);
                      break;
  
                  case SqlWatcherNotificationType.Threaded:
                      ThreadPool.QueueUserWorkItem(ChangeEventWrapper, Result);
                      break;
              }
          }
  
          public void ChangeEventWrapper(object state)
          {
              DataSet Result = (DataSet)state;
              OnChange(Result);
          }
  
          private void Handle_OnChange(object sender, SqlNotificationEventArgs e)
          {
              if (e.Type != SqlNotificationType.Change)
                  throw new ApplicationException("Failed to create queue notification subscription!");
  
              //Clean up the old notification
              SqlDependency dep = (SqlDependency)sender;
              dep.OnChange -= Handle_OnChange;
  
              //Register for the new notification
              RegisterForChanges();
          }
  
          public void Dispose()
          {
              Stop();
          }
      }
  }
  </code></pre>
  
  <p>Implementation:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Linq;
  using System.Data.SqlClient;
  using System.Linq;
  using System.Net.Mail;
  using System.Text;
  using System.Threading.Tasks;
  
  namespace TestApp
  {
      class Program
      {
          private static SqlWatcher SqlQueueWatcher;
  
          // string pin = string.Empty;
          string siteURL = "http://docapp/sites/nlpc";
  
          public string benefitCheck()
          {
              DataContext db = new DataContext("Data Source=;Initial Catalog=ServiceTest;User ID=;password =  ;Integrated Security=true");
              BenefitDocModel RSAName = new BenefitDocModel();
              CustomerCareContextDataContext LandingDb = new CustomerCareContextDataContext(siteURL);
              IEnumerable&lt;BenefitDocModel&gt; r = db.GetTable&lt;BenefitDocModel&gt;().ToList&lt;BenefitDocModel&gt;();
  
              var query = r.ToList();
  
              var rsa = from rr in query
                        select ( new { rr.PIN , rr.Document_Name, rr.Firstname, rr.Surname, rr.URL});
  
              foreach (var rsapin in rsa)
              {
                  Console.WriteLine(rsapin);
                  // sendEmail(rsapin.PIN);
              }
                  /*pin = RSAName.PIN;
                  RSAsLibraryDocument test = new RSAsLibraryDocument();
                  BenefitDocModel RSAName1 = db.GetTable&lt;BenefitDocModel&gt;().FirstOrDefault(x =&gt; x.PIN == pin);
              if (pin == RSAName1.PIN)
                  {
                      test.PIN = RSAName.PIN;
                      test.UserID = RSAName.UserID;
                      test.Firstname = RSAName.Firstname;
                      test.Surname = RSAName.Surname;
                      test.Document_Name = RSAName.Document_Name;
                      test.Document_URL = RSAName.URL;
                      test.UserType = RSAName.UserType;
                      test.Name = RSAName.PIN + RSAName.Document_Name;
                  }*/
              return "success";
  
              }//
  
          public void SQLServiceStartForLog()
          {
              //BenefitDocModel rsapin = db.GetTable&lt;BenefitDocModel&gt;().FirstOrDefault(x =&gt; x.PIN == pin);
              //cmd.Notification = null;
              string connS = "Data Source=;Initial Catalog=ServiceTest;User ID=;password =  ;Integrated Security=true";
              SqlCommand cmd = new SqlCommand();
              //cmd.Notification = null;
              cmd = new SqlCommand("SELECT UserID, Surname, Firstname, PIN, URL, Document_Name FROM dbo.BenefitDoc");
              cmd.CommandType = CommandType.Text;
              SqlQueueWatcher = new SqlWatcher(connS, cmd, SqlWatcherNotificationType.Blocking);
              SqlQueueWatcher.OnChange += new SqlWatcher.SqlWatcherEventHandler(QueueSQLWatcher_OnChangeForLog);
              cmd.Notification = null;
              SqlQueueWatcher.Start();   
          }
          private void QueueSQLWatcher_OnChangeForLog(DataSet Result)
          {
              try
              {
                  Console.WriteLine("Database monitoring is starting....");
                  benefitCheck();
                  Console.WriteLine("Database monitoring completed.");
  
              }
              catch (Exception e)
              {
                  Console.WriteLine(e.ToString());
              }
          }
          public void sendEmail(string pin)
          {
              MailMessage mail = new MailMessage("", "");
              SmtpClient client = new SmtpClient();
              client.Port = 25;
              client.DeliveryMethod = SmtpDeliveryMethod.Network;
              client.UseDefaultCredentials = false;
              client.Host = "smtp.gmail.com";
              client.EnableSsl = true;
              client.Credentials = new System.Net.NetworkCredential("", "");
              mail.Subject = "New Record added for RSA with " + pin;
              mail.Body = "The benefit application for user has been created.";
              client.Send(mail);
          }
  
          public static void Stop()
          {
              SqlQueueWatcher.Dispose();
          }
          static void Main(string[] args)
          {
              Program n = new Program();
              n.SQLServiceStartForLog();
              Console.ReadLine();
          }
      }
  }
  </code></pre>