Databases
StackOverflow2013
Postsdbo
PODoes it suck or not - Simple DAL with caching using Linq to SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODoes it suck or not - Simple DAL with caching using Linq to SQL
    primarykey
    data
    text
    <p>I created a simple Caching Data Access Layer that has caching using the Enterprise Library Caching Application Block and also makes use of SQL Query Notification - therefore not supporting any queries which are not valid for query notification.</p> <p>Background: This was put in place after the application was developed in order to lighten the load on the database and speed up the application. The main use of this DAL is for pulling data that is not expected to change very often such as data in Look Up Tables (presented in drop downs on the UI, etc).</p> <p>It is mainly used like the following example:</p> <pre><code>var cachingDal = new CachingDataAccessLayer(); var productTypes = cachingDal.LoadData&lt;ProductType&gt;(); </code></pre> <p>Where ProductType is a Linq to SQL table. I am curious to see what people think of the implementation I came up with and if it is horrible or amazing.</p> <p>Here's the code. Looking for any suggestions, criticisms, etc etc. Keep in mind I didn't choose the technology and am building on top of an existing system so switching data access stories is not really my call.</p> <pre><code>using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using Microsoft.Practices.EnterpriseLibrary.Caching; using Microsoft.Practices.EnterpriseLibrary.Logging; using MyDatabase; public class CachingDataAccessLayer { #region Cache Keys private const string CacheManagerName = "CachingDataAccessLayer"; #endregion #region Database /// &lt;summary&gt; /// Instantiate new MyDataContext /// &lt;/summary&gt; /// &lt;returns&gt;&lt;/returns&gt; private MyDataContext DatabaseConnection() { // instantiate database connection var database = new MyDataContext(Constants.DatabaseConnectionString); // set transaction isolation level to read committed database.ExecuteQuery(typeof(string), "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"); return database; } #endregion #region Generic Data Access with Caching /// &lt;summary&gt; /// Calls .Exists on list using predicate and if it evaluates to false, adds records to list using predicate. /// &lt;/summary&gt; /// &lt;typeparam name="TEntity"&gt;Database table&lt;/typeparam&gt; /// &lt;param name="list"&gt;List to add records to&lt;/param&gt; /// &lt;param name="predicate"&gt;The delagate that defines the conditions of elements to search for.&lt;/param&gt; public void AddRecordsIfNeeded&lt;TEntity&gt;(ref List&lt;TEntity&gt; list, Predicate&lt;TEntity&gt; predicate) where TEntity : class { // check if items are in list based on predicate and if not, add them to the list if (!list.Exists(predicate)) { list.AddRange(LoadData&lt;TEntity&gt;(predicate.Invoke)); } } /// &lt;summary&gt; /// Retrieve all records of type TEntity from the cache if available with filter Active = true (if Active property exists).&lt;br/&gt; /// If data is not available in cache go directly to the database.&lt;br/&gt; /// In addition, sets up query notification and refreshes cache on database change. /// &lt;/summary&gt; /// &lt;typeparam name="TEntity"&gt;Database table to retrieve.&lt;/typeparam&gt; /// &lt;returns&gt;returns List of TEntity&lt;/returns&gt; public List&lt;TEntity&gt; LoadData&lt;TEntity&gt;() where TEntity : class { // default filter is no filter Func&lt;TEntity, bool&gt; predicate = delegate { return true; }; // check for active property var activeProperty = typeof (TEntity).GetProperty("Active"); // if active property exists and is a boolean, set predicate to filter Active == true if (activeProperty != null) if (activeProperty.PropertyType.FullName == typeof (bool).FullName) predicate = (x =&gt; (bool) activeProperty.GetValue(x, null)); // load data &amp; return return LoadData(predicate); } /// &lt;summary&gt; /// Retrieve all records of type TEntity from the cache if available.&lt;br/&gt; /// If data is not available in cache go directly to the database.&lt;br/&gt; /// In addition, sets up query notification and refreshes cache on database change. /// &lt;/summary&gt; /// &lt;typeparam name="TEntity"&gt;Database table to retrieve.&lt;/typeparam&gt; /// &lt;param name="predicate"&gt;A function to test each element for a condition.&lt;/param&gt; /// &lt;returns&gt;returns List of TEntity&lt;/returns&gt; public List&lt;TEntity&gt; LoadData&lt;TEntity&gt;(Func&lt;TEntity, bool&gt; predicate) where TEntity : class { // default is to not refresh cache return LoadData(predicate, false); } /// &lt;summary&gt; /// Retrieve all records of type TEntity from the cache if available.&lt;br/&gt; /// If data is not available in cache or refreshCache is set to true go directly to the database.&lt;br/&gt; /// In addition, sets up query notification and refreshes cache on database change. /// &lt;/summary&gt; /// &lt;typeparam name="TEntity"&gt;Database table to retrieve.&lt;/typeparam&gt; /// &lt;param name="predicate"&gt;A function to test each element for a condition.&lt;/param&gt; /// &lt;param name="refreshCache"&gt;If true, ignore cache and go directly to the database and update cache.&lt;/param&gt; /// &lt;returns&gt;&lt;/returns&gt; public List&lt;TEntity&gt; LoadData&lt;TEntity&gt;(Func&lt;TEntity, bool&gt; predicate, bool refreshCache) where TEntity : class { // instantiate database connection using (var database = DatabaseConnection()) { // instantiate the cache var cache = CacheFactory.GetCacheManager(CacheManagerName); // get cache key name var cacheKey = typeof(TEntity).Name; // if the value is in the cache, return it if (cache.Contains(cacheKey) &amp;&amp; !refreshCache) // get data from cache, filter it and return results return (cache.GetData(cacheKey) as List&lt;TEntity&gt;).Where(predicate).ToList(); // retrieve the data from the database var data = from x in database.GetTable&lt;TEntity&gt;() select x; // if value is in cache, remove it if (cache.Contains(cacheKey)) cache.Remove(cacheKey); // add unfiltered results to cache cache.Add(cacheKey, data.ToList()); Logger.Write(string.Format("Added {0} to cache {1} with key '{2}'", typeof(TEntity).Name, CacheManagerName, cacheKey)); // set up query notification SetUpQueryNotification&lt;TEntity&gt;(); // return filtered results return data.Where(predicate).ToList(); } } #endregion #region Query Notification public void SetUpQueryNotification&lt;TEntity&gt;() where TEntity : class { // get database connection var database = DatabaseConnection(); // set up query notification using (var sqlConnection = new SqlConnection(Constants.DatabaseConnectionString)) { // linq query var query = from t in database.GetTable&lt;TEntity&gt;() select t; var command = database.GetCommand(query); // create sql command using (var sqlCommand = new SqlCommand(command.CommandText, sqlConnection)) { // get query parameters var sqlCmdParameters = command.Parameters; // add query parameters to dependency query foreach (SqlParameter parameter in sqlCmdParameters) { sqlCommand.Parameters.Add(new SqlParameter(parameter.ParameterName, parameter.SqlValue)); } // create sql dependency var sqlDependency = new SqlDependency(sqlCommand); // set up query notification sqlDependency.OnChange += sqlDependency_OnChange&lt;TEntity&gt;; // open connection to database sqlConnection.Open(); // need to execute query to make query notification work sqlCommand.ExecuteNonQuery(); } } Logger.Write(string.Format("Query notification set up for {0}", typeof(TEntity).Name)); } /// &lt;summary&gt; /// Calls LoadData of type TEntity with refreshCache param set to true. /// &lt;/summary&gt; /// &lt;typeparam name="TEntity"&gt;Database table to refresh.&lt;/typeparam&gt; void RefreshCache&lt;TEntity&gt;() where TEntity : class { // refresh cache LoadData&lt;TEntity&gt;(delegate { return true; }, true); } /// &lt;summary&gt; /// Refreshes data in cache for type TEntity if type is Delete, Insert or Update.&lt;br/&gt; /// Also re-sets up query notification since query notification only fires once. /// &lt;/summary&gt; /// &lt;typeparam name="TEntity"&gt;Database table&lt;/typeparam&gt; void sqlDependency_OnChange&lt;TEntity&gt;(object sender, SqlNotificationEventArgs e) where TEntity : class { var sqlDependency = sender as SqlDependency; // this should never happen if (sqlDependency == null) return; // query notification only happens once, so remove it, it will be set up again in LoadData sqlDependency.OnChange -= sqlDependency_OnChange&lt;TEntity&gt;; // if the data is changed (delete, insert, update), refresh cache &amp; set up query notification // otherwise, just set up query notification if (e.Info == SqlNotificationInfo.Delete || e.Info == SqlNotificationInfo.Insert || e.Info == SqlNotificationInfo.Update) { // refresh cache &amp; set up query notification Logger.Write(string.Format("sqlDependency_OnChange (Info: {0}, Source: {1}, Type: {2}). Refreshing cache for {3}", e.Info, e.Source, e.Type, typeof(TEntity).Name)); RefreshCache&lt;TEntity&gt;(); } else { // set up query notification SetUpQueryNotification&lt;TEntity&gt;(); } } #endregion } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWill
    1. USNate Pinchot
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODoes it suck or not - Simple DAL with caching using Linq to SQL
      1. USfearofawhackplanet
      1. VTFavorite
    2. VO
      singulars
      1. PODoes it suck or not - Simple DAL with caching using Linq to SQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODoes it suck or not - Simple DAL with caching using Linq to SQL
      1. USNathan
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload