I have a console app that monitors a database using SqlDependency. The app is to monitor the database table and send an email once a new record is added in the table.
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.
Here is my code below:
SQLWatcher class:
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();
}
}
}
Implementation:
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<BenefitDocModel> r = db.GetTable<BenefitDocModel>().ToList<BenefitDocModel>();
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<BenefitDocModel>().FirstOrDefault(x => 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<BenefitDocModel>().FirstOrDefault(x => 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();
}
}
}
