.net 2.0 SqlDependency

.net 2.0 SqlDependency

• Chris Palmer

  Hello,

  can you offer the code in VB.NET I understand how this example works but can't write it in VB.NET.

  Thank you

  
  

• Michael madan

  I've been having all manner of problems getting this working. I've followed all the rules when it comes to forming my select statement, and I'm using the constructor listed above, but the only notifications I'm getting are:
  
  Info: Error
  Source: Timeout
  Type: Change
  
  If I instead use the constructor that takes only the command parameter I get a constant stream of:
  
  Info: Options
  Source: Statement
  Type: Subscribe
  
  I used this article as a template for my code:
  
  http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx
  
  

• SenthilNathan

  Hi,
  I read the link. Thanks
  Please have a lok at the code below. This does not produce an error now. But the DataChanged seems to occur all the time.

  private delegate void UICallback();

          private void GetData()
          {
              try
              {
                  ds.Clear();

                  SqlConnection conn = new SqlConnection(txtConnect.Text);
                  conn.Open();
                  SqlCommand cmd = new SqlCommand(txtSelect.Text, conn);

                  SqlDataAdapter da = new SqlDataAdapter(cmd);
                 
                  SqlDependency.Start(txtConnect.Text);
                  SqlDependency dep = new SqlDependency(cmd);
                 
                  dep.OnChange += new OnChangeEventHandler(DataChanged);

                  da.Fill(ds, "Employees");
                                
                  grdDemo.DataSource = ds;
                  grdDemo.DataMember = "Employees";
              }

              catch (Exception ex)
              {
                  MessageBox.Show(ex.Message);
              }
          }

  private void RebindOnUIThread()
          {
              GetData();
              lblStatus.Text = string.Format("{0} changes have occured.", changeCount);
          }

          private void DataChanged(object sender, SqlNotificationEventArgs e)
          {
              changeCount++;
              Invoke(new UICallback(RebindOnUIThread));
          }

  
  

• blogg

  I'm glad to hear that you got it working.

  There are quite a few. If you're interested in working with ADO.NET 2.0 closely to SQL Server, you can use Bob Beauchemin's book; if you want  general ADO.NET 2.0 content you can look at Sahil Malik's or Glenn Johnson's. There are several others as well.

  Pablo Castro
  Program Manager - ADO.NET Team
  Microsoft Corporation
  

  
  
  

• dalek9

  This is now Ok.
  
  Can you recommend a good .NET 2.0 (ado.net or asp.net or winforms) book please

  Thanks alot

  
  

• Demonica

  Hi,

  You need to call the static method SqlDependency.Start before you can execute a command that's bound to a SqlDependency object. In the code that you posted the call to Start was commented out. Did you try uncommenting that line That should be enough to get it working.

  When using SqlDependency you also need to make sure that you have the database permissions that are required by SqlDependency setup appropriately. Here is a blog entry by Sushil that describes that in detail:

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

  Pablo Castro
  Program Manager - ADO.NET Team
  Microsoft Corporation
  

  
  
  

• Bryan Cooper

  fmardani wrote:

  Are these two books (Sahil Malik's or Glenn Johnson's) only for windows or do they go through ASP.NET too Thanks

  Thanks for recommending my book Pablo, I owe it to you for it being decent quality.

  Regards your question fmardani,

  My book solely concentrates on all it's examples as ConsoleApplications (mostly), unless the concept I wished to demonstrate was specific to ASP.NET or WinForms. Sorry if that dissappoints you, but keep reading.

  In my opinion, ADO.NET is the one technology that spans both winforms and asp.net. Barring exceptions, your architecture that surrounds data access should be affected in a very minimal manner with what it is being wrapped in. Sure there are issues of a single user app - which an ASP.NET app will never be. But I feel if you really wish to master ASP.NET + ADO.NET, you should look at two books - not one.

  Sure one book could be written that focusses on both ASP.NET and ADO.NET, but chances are it will either be 2000 pages thick, or too cursory and basic.

  I would suggest that you should pick up the Mat Macdonald and Mario Spuzta book for core ASP.NET, and combine it with what I have to say in ADO.NET and put your architect hat on and make the right decisions for your application.

  Just my view :)

  Sahil Malik [MVP C#]
  Author: Pro ADO.NET 2.0
  http://www.winsmarts.com

  
  
  

• treble1999

  Both are really focused on ADO.NET itself, and do a bit of the environment only so it gives some context to the explanations. I don't remember how much ASP.NET/WinForms they cover, but it most likely won't be much as the books are on ADO.NET.

  There are some books on combined technologies such as ADO.NET + ASP.NET. I'd recommend that you look at an online book store to find out what's available.

  Pablo Castro
  Program Manager - ADO.NET Team
  Microsoft Corporation
  

  
  
  

• ctwalker

  Thanks
  

• jennifer.wu

  I found out the problem. When we create the SqlDependency object, the service name and the timeout values has to be passed as an argument to the constructor.

  Earlier I was trying with the default constructor with no arguments.

  SqlDependency dependency =new SqlDependency(command,"Service=.;",1);

  
  
  

• gmeh

  Hi,

  I am also having a similair problem related to the SqlNotificationEventArgs error. The SQL is a pretty simple one (like SELECT Description FROM dbo.CustomValues) When I execute the application, immediately the OnChange event is fired and the Info property of SqlNotificationEventArgs is coming as 'Invalid', the Source property is 'Statement' and the type property is 'Subscribe'. Can anyone help me to solve this error

  Thanks,

  Shree

   

  
  
  

• jhazucha

  Are these two books (Sahil Malik's or Glenn Johnson's) only for windows or do they go through ASP.NET too
  Thanks 
  

• Gibson

  Try using a constructor such as:

  SqlDependency mDependency = new SqlDependency(objCommand, null, 31557600); // listen for changes for 1 year

  The third parameter is the number of seconds after which the listener will expire (timeout). Thus, using a value '1' for the third parameter will cause an expiry message to be sent almost immediately!

  In the above constructor, the expiry is being set to 1 year (365.25 days) which is a reasonable period (within which your site/db will likely be restarted anyway).

  - Nalin J

  
  
  

• Antony Kumar

  If you're seeing change notifications coming immediately after you execute the command, it might be that the SQL statements that you have in the command are not "notifiable". Not every SQL construct can be used with query notifications.

  For example, the following things will cause an invalidation to be sent immediately:

  • Use of *, as in "SELECT * FROM Table"
  • Use of non-qualified table names, such as "SELECT a, b FROM Table"; instead, you should qualify the table with its owning schema. For example, if the owning schema is 'dbo', the statement should read "SELECT a, b FROM dbo.Table"
  • Use of blobs in the SELECT list

  Note that you can use the SqlNotificationEventArgs object properties (Info, Source and Type) to tell why the change notification was fired (e.g. an actual notification or an invalid statement for example).

  This page of SQL Server books online describes in detail the rules and restrictions for queries that will be used with query notifications:

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

  Pablo Castro
  Program Manager - ADO.NET Team
  Microsoft Corporation

  
  
  
• .net 2.0 SqlDependency