ASP.NET, SQL Server, WPF, C#, VB.NET

i hope someone can help me out on this. i am trying to get the sqlcache  
  
dependency to work using the sqlcommand sqlcachedependency.  
  
when i set the sqldependency like this everything works fine:  
  
SqlCacheDependency dependency = new SqlCacheDependency("testdb",  
  
"dbo.Profiles");  
  
when i try to use the sqldependency using the sqlcommand (using the  
  
same settings/rest of the code) the cache seems to be invalidated  
  
immidiately and database is accessed every time when i try to retrieve  
  
the dataset:  
  
SqlCacheDependency dependency = new SqlCacheDependency(cmd);  
  
how can i make this work?  
  
i've copied the code that i am using below.  
  
protected void Button1_Click(object sender, EventArgs e)  
  
{  
  
DataSet ds = (DataSet)Cache["ds"];  
  
if (ds == null)  
  
{  
  
try  
  
{  
  
SqlDependency.Start(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString);  
  
ds = this.GetData();  
  
}  
  
finally  
  
{  
  
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString);  
  
}  
  
Response.Write("DataSet retrieved from the DB.");  
  
}  
  
else  
  
{  
  
Response.Write("DataSet retrieved from the ASP.NET  
  
Cache.");  
  
}  
  
GridView1.DataSource = ds.Tables[0].DefaultView;  
  
GridView1.DataBind();  
  
}  
  
private DataSet GetData()  
  
{  
  
string connString =  
  
ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString;  
  
using (SqlConnection cn = new SqlConnection(connString))  
  
{  
  
SqlCommand cmd = new SqlCommand("[dbo].[GetProfile]", cn);  
  
cmd.CommandType = CommandType.StoredProcedure;  
  
cmd.Parameters.AddWithValue("ProfileId",  
  
"7e24d275-725f-4264-a36d-733b44382776");  
  
// When using SqlCommand in the SqlCacheDependency the sql  
  
dependency  
  
// does not work.  
  
// SqlCacheDependency dependency = new  
  
SqlCacheDependency(cmd);  
  
SqlCacheDependency dependency = new  
  
SqlCacheDependency("testdb", "dbo.Profiles");  
  
DataSet ds = new DataSet();  
  
SqlDataAdapter da = new SqlDataAdapter(cmd);  
  
da.Fill(ds);  
  
Cache.Remove("ds");  
  
Cache.Insert("ds", ds, dependency);  
  
return ds;  
  
}  
  
}  
  
here is the stored procedure called:  
  
ALTER PROCEDURE [dbo].[GetProfile]  
  
@ProfileId uniqueidentifier  
  
AS  
  
BEGIN  
  
-- SET NOCOUNT ON added to prevent extra result sets from  
  
-- interfering with SELECT statements.  
  
SET NOCOUNT ON;  
  
-- Insert statements for procedure here  
  
select  
  
p.FirstName,  
  
p.LastName,  
  
c.Name as Color  
  
from  
  
dbo.Profiles p inner join dbo.Colors c on p.ColorCode = c.ColorCode  
  
where  
  
p.ProfileId = @ProfileId  
  
END  
  
setting in the web.config:  
  
<connectionStrings>  
  
<add  
  
connectionString="Server=(local)\sqlexpress;Database=testdb;Trusted_Connection=true;"  
  
name="TestDatabase"  
  
providerName="System.Data.SqlClient"/>  
  
</connectionStrings>  
  
<system.web>  
  
<caching>  
  
<sqlCacheDependency enabled="true" pollTime="3000">  
  
<databases>  
  
<add name="testdb" connectionStringName="TestDatabase"  
  
pollTime="500" />  
  
</databases>  
  
</sqlCacheDependency>  
  
</caching>  
  
i can't find anything on the web that addresses this issue.  
  
thanks

Hi Kent,  
  
thanks for your reply.  
  
i have commented out the SET NOCOUNT ON in my SP and also commented out  
  
the SqlDependency.Stop() and now everything works fine.  
  
here is the code:  
  
private DataSet GetData()  
  
{  
  
string connString =  
  
ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString;  
  
using (SqlConnection cn = new SqlConnection(connString))  
  
{  
  
using (SqlCommand cmd = new SqlCommand("dbo.GetProfile",  
  
cn))  
  
{  
  
cmd.CommandType = CommandType.StoredProcedure;  
  
cmd.Parameters.AddWithValue("ProfileId",  
  
"7be94c7c-66cf-4029-b13d-99d93c63f943");  
  
SqlCacheDependency dependency = new  
  
SqlCacheDependency(cmd);  
  
DataSet ds = new DataSet();  
  
SqlDataAdapter da = new SqlDataAdapter(cmd);  
  
da.Fill(ds);  
  
Cache.Remove("ds");  
  
Cache.Insert("ds", ds, dependency);  
  
return ds;  
  
}  
  
}  
  
}  
  
protected void ButtonGetData_Click(object sender, EventArgs e)  
  
{  
  
DataSet ds = (DataSet)Cache["ds"];  
  
if (ds == null)  
  
{  
  
string connString =  
  
ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString;  
  
try  
  
{  
  
System.Data.SqlClient.SqlDependency.Start(connString);  
  
ds = GetData();  
  
Response.Write("DB");  
  
}  
  
finally  
  
{  
  
//System.Data.SqlClient.SqlDependency.Stop(connString);  
  
}  
  
}  
  
GridView1.DataSource = ds.Tables[0].DefaultView;  
  
GridView1.DataBind();  
  
}

Hello emer,  
  
With a SqlDependency SET NOCOUNT ON is known to cause this behavior according  
  
to some accounts. The official list of non-dependables is at: http://msdn2.microsoft.com/en-us/library/aewzkxxh(VS.80).aspx  
  
Thank you,  
  
Kent Tegels  
  
DevelopMentor  
  
http://staff.develop.com/ktegels/