Skip to content

Instantly share code, notes, and snippets.

@benfoster
Created May 27, 2015 11:26
Show Gist options
  • Save benfoster/14cb8d99f6f47f9d274b to your computer and use it in GitHub Desktop.
Save benfoster/14cb8d99f6f47f9d274b to your computer and use it in GitHub Desktop.
SqlAzureEntityTagStore
private void ConfigureCaching()
{
var services = ConfigurationManager.ConnectionStrings[Constants.FabrikServicesConnectionStringKey];
var cacheControlPolicy = new AttributeBasedCacheControlPolicy(new CacheControlHeaderValue
{
Private = true,
MustRevalidate = true,
NoTransform = true,
MaxAge = TimeSpan.Zero
});
var retryStrategy = new FixedInterval("fixed", retryCount: 3, retryInterval: TimeSpan.FromSeconds(1), firstFastRetry: true);
var retryManager = new RetryManager(new[] { retryStrategy }, retryStrategy.Name);
RetryManager.SetDefault(retryManager);
var entityTagStore = new SqlAzureEntityTagStore(
services.ConnectionString,
connectionString => new ReliableSqlConnection(connectionString));
var handler = new CachingHandler(config, entityTagStore)
{
UriTrimmer = uri => uri.PathAndQuery.ToLowerInvariant(), // Lowercase all resource URLs
CacheControlHeaderProvider = (request, cfg) => cacheControlPolicy.GetCacheControl(request, cfg),
RoutePatternProvider = new FabrikRoutePatternProvider(config)
};
config.MessageHandlers.Add(handler);
}
using CacheCow.Common;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
namespace CacheCow.Server.EntityTagStore.SqlAzure
{
/// <summary>
/// Implements IEntityTagStore for SQL Server 2000 and above
///
/// Relies on a table and 4 store procedures (in scripts folder)
///
/// Uses plain ADO.NET. Not worth baking dependency for the sake of 5 calls.
/// </summary>
public class SqlAzureEntityTagStore : IEntityTagStore
{
private readonly string _schema;
private readonly string _connectionString;
private readonly Func<string, IDbConnection> _connectionStringFactory;
private const string ConnectionStringName = "EntityTagStore";
private const string DefaultSchema = "dbo";
public SqlAzureEntityTagStore()
{
if (!ConfigurationManager.ConnectionStrings.Cast<ConnectionStringSettings>()
.Any(x => ConnectionStringName.Equals(x.Name, StringComparison.CurrentCultureIgnoreCase)))
{
throw new InvalidOperationException(
string.Format(
"Connection string with name '{0}' could not be found. Please create one or explicitly pass a connection string",
ConnectionStringName));
}
this._schema = DefaultSchema;
this._connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
this._connectionStringFactory = cs => new SqlConnection(cs);
}
public SqlAzureEntityTagStore(string connectionString, Func<string, IDbConnection> connectionStringFactory = null)
: this(connectionString, DefaultSchema, connectionStringFactory) { }
public SqlAzureEntityTagStore(string connectionString, string schema, Func<string, IDbConnection> connectionStringFactory = null)
{
this._schema = schema;
this._connectionString = connectionString;
this._connectionStringFactory = connectionStringFactory ?? (cs => new SqlConnection(cs));
}
public bool TryGetValue(CacheKey key, out TimedEntityTagHeaderValue eTag)
{
eTag = null;
using (var connection = GetConnection())
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = this.GetStoredProcedureName(StoredProcedureNames.GetCache);
command.CommandType = CommandType.StoredProcedure;
command.AddParameter(ColumnNames.CacheKeyHash, key.Hash);
using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
if (!reader.Read())
{
return false;
}
// there must be only one record
eTag = new TimedEntityTagHeaderValue((string)reader[ColumnNames.ETag])
{
LastModified = DateTime.SpecifyKind((DateTime)reader[ColumnNames.LastModified], DateTimeKind.Utc)
};
return true;
}
}
}
public void AddOrUpdate(CacheKey key, TimedEntityTagHeaderValue eTag)
{
using (var connection = GetConnection())
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = this.GetStoredProcedureName(StoredProcedureNames.AddUpdateCache);
command.CommandType = CommandType.StoredProcedure;
command.AddParameter(ColumnNames.CacheKeyHash, key.Hash);
command.AddParameter(ColumnNames.RoutePattern, key.RoutePattern);
command.AddParameter(ColumnNames.ResourceUri, key.ResourceUri);
command.AddParameter(ColumnNames.ETag, eTag.Tag);
command.AddParameter(ColumnNames.LastModified, eTag.LastModified.ToUniversalTime());
command.ExecuteNonQuery();
}
}
public int RemoveResource(string resourceUri)
{
using (var connection = GetConnection())
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = this.GetStoredProcedureName(StoredProcedureNames.DeleteCacheByResourceUri);
command.CommandType = CommandType.StoredProcedure;
command.AddParameter(ColumnNames.ResourceUri, resourceUri);
return command.ExecuteNonQuery();
}
}
public bool TryRemove(CacheKey key)
{
using (var connection = GetConnection())
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = this.GetStoredProcedureName(StoredProcedureNames.DeleteCacheById);
command.CommandType = CommandType.StoredProcedure;
command.AddParameter(ColumnNames.CacheKeyHash, key.Hash);
return command.ExecuteNonQuery() > 0;
}
}
public int RemoveAllByRoutePattern(string routePattern)
{
using (var connection = GetConnection())
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = this.GetStoredProcedureName(StoredProcedureNames.DeleteCacheByRoutePattern);
command.CommandType = CommandType.StoredProcedure;
command.AddParameter(ColumnNames.RoutePattern, routePattern);
return command.ExecuteNonQuery();
}
}
public void Clear()
{
using (var connection = GetConnection())
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = this.GetStoredProcedureName(StoredProcedureNames.Clear);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
}
/*********
** Private methods
*********/
/// <summary>Prefixes a stored procedure name with the configured database schema name.</summary>
/// <param name="procedure">The stored procedure name to format.</param>
private string GetStoredProcedureName(string procedureName)
{
return String.Format("[{0}].[{1}]", this._schema, procedureName);
}
private IDbConnection GetConnection()
{
return this._connectionStringFactory(this._connectionString);
}
public void Dispose()
{
// nothing
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment