Hi,
I am using SqlDependency in web application(asp.net), here when i m chenging something in database I am getting the notification in dependency_OnChange(object sender, SqlNotificationEventArgs e)event …but when I am trying to do some work according to that notification…..,it is not happening…..here in the following code I am trying to display a simple string in label… and then trying to populate the grid with changed values from the database automatically, without externally refreshing the page.....for this purpose i m using Ajax update panel control and calling its update() method in OnChange event,but this is not working………..Please help me to solve this problem...

using System.Data.SqlClient;

public partial class Notification : System.Web.UI.Page
{
    private DataSet myDataSet = null;
    private SqlConnection connection;
    private SqlCommand command = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        EnoughPermission();
        string ssql = "select Id,Name from dbo.Customer ";

        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrin gs["ASPNETDemoConnectionString"].ConnectionString.ToString());
        if (command == null)
            command = new SqlCommand(ssql, connection);

        if (myDataSet == null)
            myDataSet = new DataSet();
        GetAdvtData();
    }

     private bool EnoughPermission()
     {
        SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.Pe rmissionState.Unrestricted);
        try
        {
            perm.Demand();
            return true;
        }
        catch (System.Exception)
        {
            return false;
        }
    }
    SqlDependency dependency;
    private void GetAdvtData()
    {
        myDataSet.Clear();
        command.Notification = null;
        dependency = new SqlDependency(command);
        Label1.Text = System.DateTime.Now.ToString();
        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(myDataSet, "dbo.Customer");
            GridView1.DataSource = myDataSet;
            GridView1.DataMember = "dbo.Customer";
            GridView1.DataBind();
        }
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        try
        {
            RefreshData();
            UpdatePanel1.Update();
        }
        catch (Exception)
        {
        }
        SqlDependency dependency = (SqlDependency)sender;
        dependency.OnChange -= dependency_OnChange;
    }
    private void RefreshData()
    {
        Label1.Text = "Database had some changes and are applied in the Grid";
        GetAdvtData();
    }

}

Thanks,

So are you saying that you expect an event triggered by a SQL dependency to "push" HTML back to a client page when the event fires? It doesn't work this way.

Doing anything against page control with a dependency event is essentially pointless. Unless the dependency happens to fire precisely between the point at which you "wire up" to the dependency event and the end of the event handling portion of the page life cycle, you'll never affect the rendered page output.

The only way you are going to be able to get updates to the page is to poll it from the browser.

-Peter
compiledthoughts.com