sqldependency:Notification Self Updating Grid With DB Chnages

Posted: March 9, 2006 in Uncategorized

What I am going to post here is a  simple beauty of the SqlServer2005 Notification.I am goign to display How can you have a Self Updated Grid when the DB changes.We ll levearage the SqlServer Notification and Little Bit of thread scenario to ceate a simpe app here :-

However before proceeding with the code parts be sure to read the limitation of query notification.That you need to have your query confor to the Indexed view requisite and also always check to see the necessary permission requirement .Liek you might need to enable brokerif its not,also you might need to set the permission for the notification.

Another important point is the notification seems to be happens in a different thrad and once notification happens the same wiped out .Further as a last point you need to also consider taht notification not only happens for row updates.

Finally the thread techniques i am using here is mere to get the thing work and the recommnedation here would be to read JUVAL LOWY at idesign and MSDN .Further giving few ref here for the notification.Hope that would help too.

  SqlConnection

con; 

SqlCommand cmd; 

DataTable dt; 

SqlDataReader dr; 

SqlDependency depend; 

privatevoid Form1_Load(object sender, EventArgs e){

DataTable dt = newDataTable(); 

SqlDependency.Start(“Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True”);dt=configuredata();

dataGridView1.DataSource = dt;

}

privateDataTable configuredata(){

using (con = newSqlConnection(“Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True”)){

con.Open();

using (cmd = newSqlCommand(“select employeeid,lastname,city from dbo.employee”, con)){

depend =

newSqlDependency(cmd);depend.OnChange +=

newOnChangeEventHandler(MyOnChanged);dr = cmd.ExecuteReader(

CommandBehavior.CloseConnection);dt =

newDataTable();dt.Load(dr);

dr.Close();

}

return dt;}

}

delegatevoidsendmessage(DataTable msg); 

privatevoid MyOnChanged(object sender, SqlNotificationEventArgs e){

sendmessage handler = newsendmessage(this.ShowMessage); 

DataTable dt = newDataTable();dt = configuredata();

Object[] args = { dt }; 

if (this.InvokeRequired){

this.BeginInvoke(handler, args);}

}

privatevoid ShowMessage(DataTable dt){

dataGridView1.DataSource = dt;

}

privatevoid button1_Click(object sender, EventArgs e){

DataTable dt = newDataTable();dt = configuredata();

}

privatevoid Form1_FormClosing(object sender, FormClosingEventArgs e){

SqlDependency.Stop(“Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True”);}

Just Wait for my updates on the same using Web Scenario.Recommended reading on the Sqldependency is on :-

http://blogs.msdn.com/dataaccess/archive/2005/09/27/474447.aspx

http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp

 

http://www.sqljunkies.com/WebLog/nielsb/archive/2004/09/21/4292.aspx

http://www.thedatafarm.com/blog/PermaLink.aspx?guid=94659547-f5f7-44e9-ab57-1371d710d477

http://weblogs.asp.net/wallym/archive/2004/09/22/233120.aspx

http://www.thedatafarm.com/blog/PermaLink.aspx?guid=68701804-b4fb-41a9-a06f-09a503c6aea0

http://www.codeproject.com/useritems/SqlDependencyPermissions.asp

 

threading :-

http://www.idesign.net/idesign/uploads/Background%20with%201.1.zip

http://www.microsoft.com/belux/nl/msdn/community/columns/himschoot/backgroundprocessing2.mspx

http://blogs.msdn.com/brada/archive/2005/01/14/353132.aspx

http://www.yoda.arachsys.com/csharp/threads/

 http://www.code-magazine.com/article.aspx?quickid=0403071?

Advertisements
Comments
  1. Julie says:

    I wanted to point out to watch out for using Query Notification in Windows apps. If you must, then you should have no more than 10 users. All of those notificiaton regstratios and open SQL connections puts a big load on sql server. If you put the request at the middle tier (eg in a web app or  a web service) then you can centralize the notification for all of the end users and onlyhave one per application. Hope this helps! :-)

  2. Shreeman says:

    Agree with Julie regd the Design aspects of using the SqlDependency in a centraliszed LAyer and managing the same from there would provide a more Effective from t he performance and scalabiltiy point of view.Howvere the Key here is When all the Dendecy will be setup and wehen released and further when the Internal Sql Pools or Queues will be get Flushed.Thanks Julie for pointintg the same.
     
    Further I can see that i didn\’t have posted on the cachedependency which i ll post soon.
     
    shreeman

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s