Re:Issue with SQL Cache Dependency when passing a SqlCommand as input parameter

microsoft.public.dotnet.framework

25 Oct 2007 3:25 PM

param

Hey Guys,

I have been struggling with this for few days. when i use

SqlCacheDependency sqlDependency=new
SqlCacheDependency("test","dbo.USStates");

where test is the name of connectionstring and dbo.USStates is the table
name everything works fine.

but when i use

using (SqlConnection conn=new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("[dbo].[USStatesGet]", conn);

command.CommandType = CommandType.StoredProcedure;

SqlCacheDependency sqlDependency = new SqlCacheDependency(command);

}

HttpRuntime.Cache.Insert(cacheName, ds, sqlDependency,
Cache.NoAbsoluteExpiration,Cache.NoSlidingExpiration);

then the cache is not being invalidated when the values are changed in the
table

the following is the stored procedure i am using

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[USStatesGet]

-- Add the parameters for the stored procedure here

AS

BEGIN

--SET NOCOUNT OFF;

SELECT [stateid]

,[statename]

FROM [dbo].[USStates]

END

Thanks in Advance

Param