SQL Server 缓存依赖关系
里面有各种机制 NCache 以保持数据在缓存中同步。 可能存在这样的场景:用户希望保持数据与数据库同步,因此每次数据库更新都会通知缓存。 这样,缓存中的数据就会保持最新,并且操作会在更新的数据集上进行。
NCache 提供 SQL 依赖功能,以与 Microsoft SQL Server 2005 及以上版本同步缓存。 添加了一个项目 SqlCacheDependency (由...提供 NCache),指定代表数据库中查询结果集的 SQL 语句。 NCache 然后根据结果集建立与数据库的链接。 如果应用程序更新,SQL Server 会触发事件通知,这 NCache 从分布式缓存中捕获并删除相应的项目。
在使用 SQL Dependency 之前,请先设置 SQL Server 环境。 创建 SQL 依赖关系:
详情请参阅 设置 SQL Server 环境 管理员指南中的部分。
使用 SQL Server 缓存依赖项的先决条件
通过 API 添加具有 SQL Server 依赖项的数据
要使用 SQL 依赖项在缓存中添加数据, NCache 提供 SqlCacheDependency
班级。 这会使用 SQL 依赖项将项目添加到缓存中。
以下示例将 SQL 依赖项添加到某个项目,然后使用以下命令将该项目添加到缓存: Insert
方法。 的 Insert
方法添加一个具有依赖性的新项目,如果该数据已存在于缓存中,则会覆盖它。
// Creating connection string to get connected with database.
string connectionString = "your_connection_string_here";
// Getting products from database.
List<Product> products = FetchProductFromDB();
foreach (Product product in products)
{
string productKey = $"Product: {product.ProductID}";
// Creating an SQL dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache.
string query = $"SELECT UnitPrice FROM dbo.Products WHERE ProductID = {product.ProductID}";
// Creating dependency.
SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query);
CacheItem productItem = new CacheItem(product);
// Adding Dependency to product item
productItem.Dependency = dependency;
// Adding CacheItem in cache
_cache.Add(productKey, productItem);
}
// Creating connection spring to get connected with database
String connectionString = "your_connection_string_here";
// Getting products from the database
List<Product> products = FetchProductFromDB();
for (Product product : products) {
String productKey = "Product:" + product.getProductID();
// Creating an SQL dependency on the UnitPrice of the product. Whenever the UnitPrice changes, the product is removed from the cache.
String query = "SELECT UnitPrice FROM dbo.Products WHERE ProductID = " + product.getProductID();
// Creating a dependency
SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query);
CacheItem productItem = new CacheItem(product);
// Adding Dependency to the product item
productItem.setDependency(dependency);
// Adding CacheItem to the cache
cache.add(productKey, productItem);
System.out.println("Product added to cache: " + productKey);
}
// This is an async method
// Creating a connection string to establish connection with the database
var connection = new ActiveXObject("adodb.connection");
let connString = "your_connection_string_here";
let query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = ?";
let queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("CustomerID", this.customerId);
connection.Open(query, connString);
// Creating SQL dependency
let sqlDependency = new ncache.SqlCacheDependency(connString, query);
// Get orders against customerId
let orders = await this.fetchOrdersByCustomerId(this.customerId);
for (let order in orders)
{
// Generate a unique key for this order
let key = "Order:" + order.OrderId;
// Create a new cache item and add sql dependency to it
let cacheItem = new ncache.CacheItem(order);
cacheItem.setDependency(sqlDependency);
// Add cache item in the cache with sql dependency
await this.cache.insert(key, cacheItem);
// For successful addition of item with SQL Dependency
// Update the record in the database and check if key is present
}
connection.close();
# Creating a connection string to establish connection with the database
conn_string = "your_connection_string_here"
query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = ?"
query_command = ncache.QueryCommand(query)
query_command.set_parameters({"CustomerID": "ALFKI"})
# Creating SQL dependency
sql_dependency = ncache.SqlCacheDependency(conn_string, query)
# Get orders against Customer ID
orders = fetch_orders_by_customer_id("ALFKI")
for order in orders:
# Generate a unique key for this order
key = "Order:" + order.get_order_id()
# Create a new cache item and add sql dependency to it
cache_item = ncache.CacheItem(order)
cache_item.set_dependency(sql_dependency)
# Add cache item in the cache with sql dependency
cache.insert(key, cache_item)
# For successful addition of item with SQL Dependency
# Update the record in the database and check if key is present
备注
为确保操作是故障安全的,建议处理应用程序中的任何潜在异常,如中所述 处理故障.
使用存储过程添加具有 SQL 依赖关系的数据
NCache 允许您使用存储过程提供对项目的 SQL 依赖关系。 您还可以使用以下命令指定要随此过程一起传递的参数 SqlCacheDependency
方法。
以下示例通过存储过程使用 SQL 依赖项将项目添加到缓存 插页 方法。 的 Insert
方法添加一个具有依赖性的新项目,如果该数据已存在于缓存中,它将覆盖其属性。
// Creating connection string to get connected with database.
string connectionString = "your_connection_string_here";
string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";
// Getting products from database.
List<Product> products = FetchProductFromDB();
// Creating dictionary of CacheItems.
Dictionary<string, CacheItem> cacheItems = new Dictionary<string, CacheItem>();
foreach (Product product in products)
{
string productKey = $"Product: {product.ProductID}";
// Creating Param to be passed in stored procedure dictionary.
SqlCmdParams paramProductID = new SqlCmdParams
{
Type = CmdParamsType.Int,
Value = product.ProductID
};
// Creating stored procedure params.
Dictionary<string, SqlCmdParams> parameters = new Dictionary<string, SqlCmdParams>();
parameters.Add("@ProductID", paramProductID);
CacheItem productItem = new CacheItem(product);
// Creating an SQL dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache.
SqlCacheDependency dependency = new SqlCacheDependency(connectionString, spGetUnitPriceByProductID, SqlCommandType.StoredProcedure, parameters);
// Adding Dependency to product item
productItem.Dependency = dependency;
cacheItems.Add(productKey, productItem);
}
// Adding CacheItems in cache
_cache.AddBulk(cacheItems);
String connectionString = "your_connection_string_here";
String spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";
// Getting products from the database
List<Product> products = FetchProductFromDB();
// Create a map to store CacheItems.
Map<String, CacheItem> cacheItems = new HashMap<>();
for (Product product : products) {
String productKey = "Product:" + product.getProductID();
// Creating Param to be passed in the stored procedure dictionary.
SqlCmdParams paramProductID = new SqlCmdParams();
paramProductID.setCmdParamsType(CmdParamsType.Int);
paramProductID.setValue(product.getProductID());
// Creating stored procedure params.
Map<String, SqlCmdParams> parameters = new HashMap<>();
parameters.put("@ProductID", paramProductID);
CacheItem productItem = new CacheItem(product);
// Creating an SQL dependency on the UnitPrice of the product. Whenever the UnitPrice changes, the product is removed from the cache.
SqlCacheDependency dependency = new SqlCacheDependency(connectionString, spGetUnitPriceByProductID, SqlCommandType.StoredProcedure, parameters);
// Adding Dependency to the product item.
productItem.setDependency(dependency);
cacheItems.put(productKey, productItem);
System.out.println("Added product to cache items: " + productKey);
}
// Adding CacheItems in cache using addBulk method.
cache.addBulk(cacheItems);
System.out.println("Products added to cache.");
// This is an async method
// Creating a connection string to establish connection with the database
var connection = new ActiveXObject("adodb.connection");
let connString = "your_connection_string_here";
connection.Open(connString);
// Name of the stored procedure item is dependent un
let storedProcName = "GetCustomerByID";
// Specify the customerID passed as parameters
let sqlCmdParams = new Map();
sqlCmdParams.set("CustomerID", this.customerId);
// Create sql dependency
// In case the stored procedure has no parameters, pass null as the SqlCmdParams value
let sqlCacheDependency = new ncache.SqlCacheDependency(connString, storedProcName,
ncache.SqlCommandType.StoredProcedure, sqlCmdParams);
// Get orders from database against customerId
let orders = await this.fetchOrdersByCustomerId(this.customerId);
for (let order in orders)
{
// Generate a unique cache key for this order
let key = "Order:" + order.OrderId;
// Create a new cache item and add sql dependency to it
let item = new ncache.CacheItem(order);
item.setDependency(sqlCacheDependency);
// Add cache item in the cache with sql dependency
this.cache.insert(key, item);
// For successful addition of item with SQL Dependency
// Update the record in the database and check if key is present
}
connection.close();
# Creating a connection string to establish connection with the database
conn_string = "your_connection_string_here"
# Name of the stored procedure item is dependent upon
stored_proc_name = "GetCustomerByID"
# Specify the customerID passed as parameters
sql_cmd_params = {"CustomerID": "ALFKI"}
# Create sql dependency
# In case the stored procedure has no parameters, pass null as the SqlCmdParams value
sql_cache_dependency = ncache.SqlCacheDependency(conn_string, stored_proc_name, ncache.SqlCommandType.STORED_PROCEDURE, sql_cmd_params)
# Get orders from database against customerId
orders = fetch_orders_by_customer_id("ALFKI")
for order in orders:
# Generate a unique cache key for this order
key = "Order:" + order.get_order_id()
# Create a new cache item and add sql dependency to it
item = ncache.CacheItem(order)
item.set_dependency(sql_cache_dependency)
# Add cache item in the cache with sql dependency
cache.insert(key, item)
# For successful addition of item with SQL Dependency
# Update the record in the database and check if key is present
重要
- 在用户对数据库的访问受到更多限制的环境中,用户应该为每个数据库创建 SQL 服务和队列 NCache 通过检查“使用自定义 SQL 通知模式”复选框中的 附加选项 选项卡中 NCache 管理中心。
- 您还可以使用脚本创建所需的 SQL 服务和队列 NCache服务队列.sql 位于 %NCHOME%\bin\资源.
更多资讯
NCache 提供了 SQL 依赖项的示例应用程序 GitHub上.
参见
.NET: Alachisoft.NCache.运行时.依赖项 命名空间。
Java的: COM。alachisoft.ncache.runtime.dependency 命名空间。
节点.js: SqlCacheDependency 类。
Python: ncache.runtime.dependency 类。