"masmith" <masmith@discussions.microsoft.com> wrote in message
news:C6F9EFE3-1C21-4501-AB48-F5BE713D473B@microsoft.com...
>I am trying to find the best way to get SQL Dependency Caching to work in
>the
> Global.asax. In the Application_Start event I open a connection to the
> SQL
> Server database and get data from several tables that will change very
> infrequently. This data is then saved in Application variables. What I
> am
> looking for is to be notified if the table has been changed so I can
> reload
> the Application variables with the new information. I have SQL Cache
> Dependency working for the database and the connection used to read the
> tables does have the appropriate permissions to get notifications working.
> I
> tried adding the following Global.asax:
>
> //Note the following is called by Application_Start
> Procedure_to_get_data.{
> .
> SqlDependency sqlDep = new SqlDependency(sqlCmd);
> sqlDep.OnChange += new OnChangeEventHandler(sqlDep_IllnessList_OnChange);
> .
> }
>
> private void sqlDep_IllnessList_OnChange(object caller,
> SqlNotificationEventArgs e) {
>
> Debug.WriteLine("sqlDep_OnChange");
> const string LF = "\n\r";
> if (e.Type == SqlNotificationType.Change) {
> Debug.WriteLine(e.Info + LF + e.Source + LF + e.Type);
> GetSQLConnStart();
> GetIllnessList();
> }
>
> }//
>
> The sqlDep_IllnessList_OnChange gets the initial Subscribe event but no
> Change event when the data changes. I believe this is because after the
> Global.asax is executed it is detroyed and the event I regestered no
> longer
> exist.
>
> Is there any way of making this work at the application level?
>
>
>
> --
> Mark

"Alvin Bruney [MVP]" wrote:

> have sqlDep be a static, that will rule out collection. However, I dont
> think that is the issue because the global class stays around as long as the
> application does.
>
> --
> ________________________
> Warm regards,
> Alvin Bruney [MVP ASP.NET]
>
> [Shameless Author plug]
> Professional VSTO.NET - Wrox/Wiley
> The O.W.C. Black Book with .NET
> www.lulu.com/owc, Amazon
> Blog: http://www.msmvps.com/blogs/alvin
> -------------------------------------------------------
>
>
> "masmith" <masmith@discussions.microsoft.com> wrote in message
> news:C6F9EFE3-1C21-4501-AB48-F5BE713D473B@microsoft.com...
> >I am trying to find the best way to get SQL Dependency Caching to work in
> >the
> > Global.asax. In the Application_Start event I open a connection to the
> > SQL
> > Server database and get data from several tables that will change very
> > infrequently. This data is then saved in Application variables. What I
> > am
> > looking for is to be notified if the table has been changed so I can
> > reload
> > the Application variables with the new information. I have SQL Cache
> > Dependency working for the database and the connection used to read the
> > tables does have the appropriate permissions to get notifications working.
> > I
> > tried adding the following Global.asax:
> >
> > //Note the following is called by Application_Start
> > Procedure_to_get_data.{
> > .
> > SqlDependency sqlDep = new SqlDependency(sqlCmd);
> > sqlDep.OnChange += new OnChangeEventHandler(sqlDep_IllnessList_OnChange);
> > .
> > }
> >
> > private void sqlDep_IllnessList_OnChange(object caller,
> > SqlNotificationEventArgs e) {
> >
> > Debug.WriteLine("sqlDep_OnChange");
> > const string LF = "\n\r";
> > if (e.Type == SqlNotificationType.Change) {
> > Debug.WriteLine(e.Info + LF + e.Source + LF + e.Type);
> > GetSQLConnStart();
> > GetIllnessList();
> > }
> >
> > }//
> >
> > The sqlDep_IllnessList_OnChange gets the initial Subscribe event but no
> > Change event when the data changes. I believe this is because after the
> > Global.asax is executed it is detroyed and the event I regestered no
> > longer
> > exist.
> >
> > Is there any way of making this work at the application level?
> >
> >
> >
> > --
> > Mark
>
>