none
SQL-Dependency not working with Online SQL-Databse

    Question

  • 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();
                
                
            }

    Wednesday, August 31, 2016 9:22 AM

Answers