I have implemented SQL-Dependency on my Local SQL-Server but when i tried to configured it on online SQL-Server, it is not properly responding. not giving notification,
SqlDependencyOnChange is not firing
//SqlConnection sampleSqlConnection;
SqlConnection sampleSqlConnection =
new SqlConnection(
ConfigurationManager.ConnectionStrings["DbConnectionStr"].ConnectionString);
private async void Form1_Load(object sender, EventArgs e)
{
SqlCommand sampleSqlCommand; SqlDependency sampleSqlDependency;
//this.sampleSqlConnection =
//new SqlConnection(
//ConfigurationManager.ConnectionStrings["DbConnectionStr"].ConnectionString);
SqlDependency.Start(sampleSqlConnection.ConnectionString);
sampleSqlCommand = new SqlCommand();
sampleSqlCommand.Connection = this.sampleSqlConnection;
if (sampleSqlConnection.State != ConnectionState.Open) sampleSqlConnection.Open();
sampleSqlCommand.CommandType = CommandType.StoredProcedure;
sampleSqlCommand.CommandText = "uspGetSampleInformation";
sampleSqlCommand.Notification = null;
sampleSqlDependency = new SqlDependency(sampleSqlCommand);
sampleSqlDependency.OnChange += this.SqlDependencyOnChange;
var dr= sampleSqlCommand.ExecuteReader();
dr.Close();
}
private void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs)
{
if (eventArgs.Info == SqlNotificationInfo.Invalid)
{
MessageBox.Show("The above notification query is not valid.");
}
else
{
MessageBox.Show("Notification Info: " + eventArgs.Info + " Notification source: " + eventArgs.Source +
" Notification type: " + eventArgs.Type);
}
SqlCommand sampleSqlCommand; SqlDependency sampleSqlDependency;
sampleSqlCommand = new SqlCommand();
sampleSqlCommand.Connection = this.sampleSqlConnection;
if (sampleSqlConnection.State != ConnectionState.Open) sampleSqlConnection.Open();
sampleSqlCommand.CommandType = CommandType.StoredProcedure;
sampleSqlCommand.CommandText = "uspGetSampleInformation";
sampleSqlCommand.Notification = null;
sampleSqlDependency = new SqlDependency(sampleSqlCommand);
sampleSqlDependency.OnChange += this.SqlDependencyOnChange;
var dr = sampleSqlCommand.ExecuteReader();
dr.Close();
}
