SQL Cache Dependency - Guru question Extensions

sbornstein2
sbornstein2 used Ask the Experts™
on
Hello all,

I found an example that I can use SQL Cache Dependency online.  I have the following static list that can handle a table coming in from my LINQ dbml and it enables table notifications etc.  Well this is great in a simple world but I need to also be able to handle a query with JOINS in it and multiple tables.   Anyone know a way to do something similar to my pasted code but instead using the IQueryable or even stored procedure etc?  There are some links out there but the problem is I need to be able to enabletablenotification on all tables within the query not just the first one.  Attached is my code for handling a table which works great.  I just load a dropdown on my form such as:

  using (AWDataContext dc = new AWDataContext())
            {
                List<Employee> employee = dc.Employees.LinqCache();
                cboEmployee.DataSource = employee;
                cboEmployee.DataTextField = "NationalIDNumber";
                cboEmployee.DataValueField = "EmployeeID";
                cboEmployee.DataBind();
            }          

I want to be able to use a LINQ query or a stored procedure that will have joined tables to so something similar.
public static List<T> LinqCache<T>(this Table<T> query) where T : class
        {
            string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName;
            string conn = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;

            List<T> result = HttpContext.Current.Cache[tableName] as List<T>;

            if (result == null)
            {
                using (SqlConnection cn = new SqlConnection(conn))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);
                    cmd.Notification = null;
                    cmd.NotificationAutoEnlist = true;
                    SqlCacheDependencyAdmin.EnableNotifications(conn);
                    if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(conn).Contains(tableName))
                    {
                        SqlCacheDependencyAdmin.EnableTableForNotifications(conn, tableName);
                    }

                    SqlCacheDependency dependency = new SqlCacheDependency(cmd);
                    cmd.ExecuteNonQuery();

                    result = query.ToList();
                    HttpContext.Current.Cache.Insert(tableName, result, dependency);
                }
            }
            return result;
        }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I need to grade this but the solution provided is the exact code I already posted that does it by table.  I was looking for how to handle it using a query or stored procedures.  The dotnetcurry link is the code I pasted that is what I started with.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial