Notify winforms app with Sql Server database changes
I created a Sql server database in which I added a table named user
. Then I ran this script
ALTER DATABASE [TestNotification] SET ENABLE_BROKER
I would like to use a class SqlDependency
to notify a winforms application when a table user
has changed.
namespace Watcher
{
public partial class Form1 : Form
{
private int changeCount = 0;
private const string statusMessage = "{0} changes have occurred.";
private DataSet dataToWatch = null;
private SqlConnection connection = null;
private SqlCommand command = null;
public Form1()
{
InitializeComponent();
button1.Enabled = CanRequestNotifications();
this.FormClosed += Form1_FormClosed;
}
void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
SqlDependency.Stop(GetConnectionString());
if (connection != null)
{
connection.Close();
}
}
private bool CanRequestNotifications()
{
// In order to use the callback feature of the
// SqlDependency, the application must have
// the SqlClientPermission permission.
try
{
SqlClientPermission perm =
new SqlClientPermission(
PermissionState.Unrestricted);
perm.Demand();
return true;
}
catch
{
return false;
}
}
private void button1_Click(object sender, EventArgs e)
{
changeCount = 0;
label1.Text = String.Format(statusMessage, changeCount);
//SqlDependency.Stop(GetConnectionString());
SqlDependency.Start(GetConnectionString());
if (connection == null)
{
connection = new SqlConnection(GetConnectionString());
}
if (command == null)
{
command = new SqlCommand(GetSQL(), connection);
}
if (dataToWatch == null)
{
dataToWatch = new DataSet();
}
GetData();
}
private string GetConnectionString()
{
return @"Data Source=BILOG-PRT-12\SQLEXPRESS; Initial Catalog=TestNotification;Integrated Security=True";
}
private string GetSQL()
{
return "Select [id],[nom],[prenom],[age] from [dbo].[user]";
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
MessageBox.Show("modification Occurred");
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
OnChangeEventHandler tempDelegate =new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
++changeCount;
label1.Text = String.Format(statusMessage, changeCount);
GetData();
}
private void GetData()
{
//dataToWatch.Clear();
//command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
if (connection.State != ConnectionState.Open) connection.Open();
using (var dr = command.ExecuteReader())
{
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
}
}
}
All brokerage services work:
I launched the application. Then I clicked the button and finally I went to Sql Server management studio . And I inserted a new line. The problem is that the message box in the application is not showing, so the C # application is not notified by SQL Server !!!
So I need to know:
- Why is this happening?
- Which step did I forget?
- How can I solve this problem?
source to share
There SqlDependency
are quite a few restrictions. To quote one pressing issue:
The projected columns in the SELECT statement must be explicitly specified, and the table names must be qualified with two-part names. Note that this means that all tables referenced by the statement must be in the same database.
(see https://msdn.microsoft.com/library/ms181122.aspx for a complete list)
You must explicitly use the two-part name (for example, dbo.user
instead of just user
).
Also, you need to execute the command. It doesn't just start working automatically :) Adding a simple one using (var dr = command.ExecuteReader()) {}
should be enough.
source to share