I was trying to implement a simple SQLCacheDependency to cache objects in my asp.net application.
After breaking my head for quite a while, I seem to have hit a dead end and thought an outside view would be help.
SqlCommand cmd = new SqlCommand("SELECT UserID,FirstName,MiddleName,LastName,Mobile#,EmailID,FriendlyName,Phone#,AboutMe,Scrap#,JobPosting#,Testimonial#,Blog#,Views#,LastVisitedOn,CurrentAddress,City,State,Country,PermanentAddress,HomeTown,Occupation,CurrentEmployer,LanguageSpeak,LanguageWrite,CreatedOn,NeedJob,ProfileRank,Status,CurrentEmployerID,PictureID,ReferredBy,lat,Long,Zoom,Gender,PinCode,PersonalStatus,DOB,PreferredLanguage,EmploymentHistory,AboutFamily,LastSchoolName,HasCertificate,CertificateMonthLength,CertificateDescription,CertificateSchoolUserId,CallTimes,CertificateType,CertificateTypes,Skills FROM mydb.dbo.UserInfo where UserID=10277",con);
System.Web.Caching.SqlCacheDependency dependency = new System.Web.Caching.SqlCacheDependency(cmd);
con.Open();
try
{
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
seUserInfo = new UserInfo(reader, false);
this.Context.Cache.Add("Sean", seanUserInfo, dependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, new CacheItemRemovedCallback(ItemRemovedCallBack));
}
}
But this item is removed from the cache as soon as it is added. Now after having spent a lot of time fixing other possible causes (Setting ANSI NULLS ON etc. for the database) I have now hit a wall. From the SQL Server Profiler, I see that the subscription is fired with the following text data
<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler">
<qnev:EventText>subscription fired</qnev:EventText>
<qnev:SubscriptionID>0</qnev:SubscriptionID>
<qnev:NotificationMsg><qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="0" type="subscribe" source="statement" info="invalid" database_id="0" sid="0x2EB2AC37F2E7FF468D5DE0B591029EE7"><qn:Message>26119019-fef7-47ee-ac82-3cb56313670d;9fbb5459-bde4-494b-9b7d-8347be2ee4cb</qn:Message></qn:QueryNotification></qnev:NotificationMsg><qnev:BrokerDlg>9B1E5573-A52F-E111-8152-005056C00008</qnev:BrokerDlg></qnev:QNEvent>
You will notice that the type = subscribe and info = invalid. This is what surprises me. According to http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/ and http://msdn.microsoft.com/en-us/library/ms189308.aspx that happens when "The command submitted contained a statement that does not support notifications (for example, an INSERT or UPDATE)" where as clearly it is a simple select statement which conforms to the conditions specified for SqlDependency creation
So what am I missing here? This is the most simple scenario and it does not work!
Ok I asked this on msdn forums as well and it was answered there. My problem was that I was using a three part qualifier mydb.dbo.tablename instead of two part qualifier dbo.tablename which is a requirement.