[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.aspnet.caching

SQL Cache Dependency in application_start event.

masmith

8/7/2006 3:14:00 PM

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
2 Answers

Alvin Bruney [ASP.NET MVP]

8/8/2006 2:06:00 AM

0

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/b...
-------------------------------------------------------


"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


masmith

8/8/2006 3:01:00 PM

0

Thank you for your response!

After seeing your response that the Application instance stays around I took
another look at everything else that needed to be in place to make it work.
I found the stored procedure being used did not meet the requirements. After
removing the nocount and database name when referencing the table in the
select the SQL cache dependency now works.
--
Thanks,
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/b...
> -------------------------------------------------------
>
>
> "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
>
>
>