How can i implement push based sql dependency.

Abhijit barua

I want to implement push based sql dependecy for auto update of gridview data when database have some changes. I change database(sql server 2008) broker now it is enable, i also grant priviledge. I write code to test in .cs page like below.

if (Cache["test"] == null)

{

string connectionString = ConfigurationManager.ConnectionStrings["book"].ConnectionString;

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter ad = new SqlDataAdapter("SELECT * from test", myConnection);

DataSet ds = new DataSet();

ad.Fill(ds);

SqlCacheDependency sqlDepend = new SqlCacheDependency(ad.SelectCommand);

Cache.Insert("test", ds, sqlDepend);

}

GridView2.DataSource = Cache["test"] as DataSet;

GridView2.DataBind();

And in web config i put like below

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhijit barua14y ago
I am using sql server 2008 enterprise edition not express one. I will try and let u know. Thanking you Abhijit Barua
brunda k14y ago
i am not very clear with "create new user in sql server 2008( Not sql server 2008 express)."

but "ASPNET" user account is windows user account, u can get this user account into sql server and grant the permission, while granting login as administrator

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "SUSANTA-PC\ASPNET"

brunda
Abhijit barua14y ago
Respected sir, when i using GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "SUSANTA-PC\ASPNET" then i get Cannot find the user 'SUSANTA-PC\ASPNET', because it does not exist or you do not have permission. then i going to create user SUSANTA-PC\ASPNET it showing like SUSANTA-PC\ASPNET at create new user in sql server 2008( Not sql server 2008 express). please reply.
Abhijit barua14y ago
Thank you.
brunda k14y ago
http://www.dotnetcurry.com/ShowArticle.aspx?ID=263
Abhijit barua14y ago
Thank you, actually is any way where database send notification of the changes. I have done using timer and polling method. where we have to check time to time is their any changes. please guide me for push based method. Thank you.
NIlesh Patel14y ago
dear friend
you have to use Threading For Checking Changing in Database. And Call Fill Grid Function So Got Latest Data From Database.