SQL Cache Dependency not working with Stored Procedure
- by pjacko
Hello,
I can't get SqlCacheDependency to work with a simple stored proc (SQL Server 2008):
create proc dbo.spGetPeteTest
as
set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set NUMERIC_ROUNDABORT OFF
set ARITHABORT ON
select Id, Artist, Album
from dbo.PeteTest
And here's my ASP.NET code (3.5 framework):
-- global.asax
protected void Application_Start(object sender, EventArgs e)
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
System.Data.SqlClient.SqlDependency.Start(connectionString);
}
-- Code-Behind
private DataTable GetAlbums()
{
string connectionString =
System.Configuration.ConfigurationManager.ConnectionStrings["UnigoConnection"].ConnectionString;
DataTable dtAlbums = new DataTable();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
// Works using select statement, but NOT SP with same text
//SqlCommand command = new SqlCommand(
// "select Id, Artist, Album from dbo.PeteTest", connection);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.spGetPeteTest";
System.Web.Caching.SqlCacheDependency new_dependency =
new System.Web.Caching.SqlCacheDependency(command);
SqlDataAdapter DA1 = new SqlDataAdapter();
DA1.SelectCommand = command;
DataSet DS1 = new DataSet();
DA1.Fill(DS1);
dtAlbums = DS1.Tables[0];
Cache.Insert("Albums", dtAlbums, new_dependency);
}
return dtAlbums;
}
Anyone have any luck with getting this to work with SPs?
Thanks!
