阅读 大约需要 4 分钟
在上一篇随笔中记录的是关于Azure Cosmos DB 中SQL API (DocumentDB) 的简介和Repository 的实现。本随笔是Document DB 中存储过程(Stored Procedure)、触发器(Triggers)、用户自定义函数(User Defined Functions)的实现方式。
存储过程(Stored Procedure)
1. 创建存储过程，需要四个参数，以此分别为数据库名，collection名，需要创建的存储过程名，存储过程的内容（内容使用的语言请参照官网：https://docs.microsoft.com/zh-cn/azure/cosmos-db/how-to-write-stored-procedures-triggers-udfs）
注：catch execption只是简写，抛出异常。
public async Task<bool> CreateStoredProcedureAsync(string databaseName, string collectionName, string storedProId, string body)
{
try
{
var sproc = new StoredProcedure()
{
Id = storedProId,
Body = body
};
var uri = UriFactory.CreateDocumentCollectionUri(databaseName, collectionName);
await TryDeleteStoredProcedure(uri, storedProId);
var result = await _client.Value.CreateStoredProcedureAsync(uri, sproc);
return result.StatusCode == HttpStatusCode.OK || result.StatusCode == HttpStatusCode.Created;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
// 为避免重复的存储过程名 而引发的的异常（以下触发器，自定义函数类同）。
private async Task TryDeleteStoredProcedure(Uri uri, string storedProId)
{
var sproc = _client.Value.CreateStoredProcedureQuery(uri).Where(x => x.Id == storedProId).AsEnumerable().FirstOrDefault();
if (sproc != null)
{
await _client.Value.DeleteStoredProcedureAsync(sproc.SelfLink);
}
}
2. 读取存储过程，第四个参数(procedureParams)为存储过程可能需要的参数.
public async Task<T> ExecuteStoredProcedureAsync<T>(string databaseName, string collectionName, string storedProId, params object[] procedureParams) where T : new()
{
StoredProcedureResponse<dynamic> result = await _client.Value.ExecuteStoredProcedureAsync<dynamic>(UriFactory.CreateStoredProcedureUri(databaseName, collectionName, storedProId), procedureParams);
if (result.StatusCode == HttpStatusCode.OK)
{
return JsonConvert.DeserializeObject<T>(result.Response?.ToString());
}
throw new ArgumentException("Execute stored ptocedure failed");
}
触发器(Triggers)
1. 创建触发器
public async Task<bool> CreateTriggerAsync(string databaseName, string collectionName, string triggerId, string triggerBody, TriggerOperation triggerOperation, TriggerType triggerType)
{
try
{
var trigger = new Trigger()
{
Id = triggerId,
Body = triggerBody,
TriggerOperation = triggerOperation,
TriggerType = triggerType
};
var uri = UriFactory.CreateDocumentCollectionUri(databaseName, collectionName);
await TryDeleteTrigger(uri, triggerId);
var result = await _client.Value.CreateTriggerAsync(uri, trigger);
return result.StatusCode == HttpStatusCode.OK || result.StatusCode == HttpStatusCode.Created;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private async Task TryDeleteTrigger(Uri uri, string triggerId)
{
var trigger = _client.Value.CreateTriggerQuery(uri).Where(x => x.Id == triggerId).AsEnumerable().FirstOrDefault();
if (trigger != null)
{
await _client.Value.DeleteTriggerAsync(trigger.SelfLink);
}
}
以上代码中有两个传入参数TriggerOperation 和 TriggerType。
TriggerOperation 参数有五种类型，分别为：
All = 0,
Create = 1,
Update = 2,
Delete = 3,
Replace = 4
TriggerType 参数有两种类型，分别为前触发器 TriggerType(Pre = 0)和 后触发器(Post = 1)。
2. 使用触发器
public async Task<bool> CreateDocumentAsync<T>(string databaseName, string collectionName, T document, RequestOptions requestOptions) where T : class
{
try
{
await CreateDocumentCollectionAsync(collectionName, databaseName);
var uri = UriFactory.CreateDocumentCollectionUri(databaseName, collectionName);
var response = await _client.Value.CreateDocumentAsync(uri, document, requestOptions);
bool result = (response != null && (response.StatusCode == HttpStatusCode.Created || response.StatusCode == HttpStatusCode.OK));
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
其中RequestOptions 参数可以作为触发器Id/name传入，其中传入的方式需要注意下，传入的类型按照前触发器和后触发器两种参入，如图：
怎么传，举个例子：var option = new RequestOptions() { PreTriggerInclude = new List<string>() { "GetBrithDate" } };
用户自定义函数(User Defined Functions)
1. 创建用户自定义函数
public async Task<bool> CreateUserDefinedFunctionAsync(string databaseName, string collectionName, string udfId, string body)
{
try
{
var udf = new UserDefinedFunction()
{
Id = udfId,
Body = body
};
var uri = UriFactory.CreateDocumentCollectionUri(databaseName, collectionName);
await TryDeleteUserDefinedFunction(uri, udfId);
var result = await _client.Value.CreateUserDefinedFunctionAsync(uri, udf);
return result.StatusCode == HttpStatusCode.OK || result.StatusCode == HttpStatusCode.Created;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private async Task TryDeleteUserDefinedFunction(Uri uri, string udfId)
{
var udf = _client.Value.CreateUserDefinedFunctionQuery(uri).Where(x => x.Id == udfId).AsEnumerable().FirstOrDefault();
if (udf != null)
{
await _client.Value.DeleteUserDefinedFunctionAsync(udf.SelfLink);
}
}
2. 运用自定义函数
public async Task<IEnumerable<T>> GetDocumentByUDF<T>(string databaseName, string collectionName, string sqlExpression, FeedOptions feedOptions = null) where T : new()
{
try
{
var uri = UriFactory.CreateDocumentCollectionUri(databaseName, collectionName);
var query = _client.Value.CreateDocumentQuery<T>(uri, sqlExpression, feedOptions).AsDocumentQuery();
var results = new List<T>();
while (query.HasMoreResults)
{
results.AddRange(await query.ExecuteNextAsync<T>());
}
return results;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
sqlExpression 为传入的sql 语句，具体 这个sql 语句怎么写，例如：
其中 数据库中用户自定义函数是这样定义的：
function getTitle(profession) {
if (profession === undefined)
throw 'no input';
if (profession === "Angular" || profession === "Vue" || profession === "React")
return "FrontEnd";
else if (profession === "Net" || profession === "Java")
return "BackEnd";
else
return "FullStack";
}
那么 sqlExpression = $"SELECT * FROM c where udf.getTitle(c.Profession) = '{换成插入值}'";
简单笔记，还有待继续挖掘，正在继续努力，想要了解和学习的 还请 切到Cosmos DB官网：https://docs.microsoft.com/zh-cn/azure/cosmos-db/how-to-write-stored-procedures-triggers-udfs
本随笔链接：https://www.cnblogs.com/OneManStep/p/10266217.html