阅读 大约需要 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
如对本文有疑问， 点击进行留言回复！！
MongoDB中数据的替换方法实现类Replace()函数功能详解
理解Redis持久化，RDB持久化和AOF持久化的不同处理方式
网友评论