CODEWITHSUNDEEP
c#asp.net.netsqldependency
C Sharper
C Sharper

Reputation: 8646

Cache is not getting refreshed in sqldependancy

I want to refresh the data automatically when change to the database is made.

I used this documentation:

And made the code on page load as:

 protected void Page_Load(object sender, EventArgs e)
    {
        conString = "Data Source=MITEJ5-PC\\MITEJTECHONOLY;Initial Catalog=SSISTestDatabase;Integrated Security=SSPI;";

        SqlDependency.Start(conString);
        using (SqlConnection connection =
       new SqlConnection(conString))
        {
            using (SqlCommand command =
                new SqlCommand(GetSQL(), connection))
            {
                SqlCacheDependency dependency =
                    new SqlCacheDependency(command);
                // Refresh the cache after the number of minutes 
                // listed below if a change does not occur. 
                // This value could be stored in a configuration file. 
                int numberOfMinutes = 1;
                DateTime expires =
                    DateTime.Now.AddMinutes(numberOfMinutes);

                Response.Cache.SetExpires(expires);
                Response.Cache.SetCacheability(HttpCacheability.Public);
                Response.Cache.SetValidUntilExpires(true);

                Response.AddCacheDependency(dependency);

                connection.Open();

                gv.DataSource = command.ExecuteReader();
                gv.DataBind();
            }
        }
    }

 private string GetSQL()
    {
        return "select Name,Age,Address from tlbStudent;";
    }

But when I run this , and made changes in SQL table data, it is not reflecting it on grid automatically.

Where can I be wrong in above code???

Please help me.

Upvotes: 0

Views: 422

Answers (2)

Rana
Rana

Reputation: 1218

The problem in 

private string GetSQL()
{
    return "select Name,Age,Address from tlbStudent;";
}

the table name should be 2 parts ""

private string GetSQL()
{
    return "select Name,Age,Address from dbo.tlbStudent;";
}

according to the documentation

The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294407

First you need to understand how SqlDependency works. Read The Mysterious Notification for a short introduction. Once you learn that the real feature at work is Query Notifications, you can learn about the restrictions in place for queries using notifications, see Creating a Query for Notification. Once such restriction is:

The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

For future problems read Troubleshooting Query Notifications.

Upvotes: 2

Related Questions