how to use sqlcachedependency for each user in asp.net mvc

Question

I have an application in asp.net mvc (C#), in which each user have different products sharing a common table separated by user id.

I need to implement SqlCacheDependency for each user's products.

The Scenario is: say there are two users (user1 and user2) and products assigned for them based on the user id.

I need to cache the products using SqlCacheDependency for each user as they wont update/change it frequently. I need to maintain the cache of the user1 even when the products belonging to user2 are changed.

How can i maintain the SqlCacheDependency based on the User id for products sharing a common table?

Answer 1

Maybe it's not the SqlCacheDependency that needs to be cached by user id, but the HttpContextCache entry.

Would this work for you? (I'm assuming userProducts to be your DataTable or List<Product> as appropriate.

var cmd = new SqlCommand("select * from product where user_id = @user_id");
cmd.Parameters.Add("@user_id", userId);
HttpContext.Current.Cache.Insert(string.Format("products:{0}", userId), userProducts, new SqlCacheDependency(cmd));