Azure Document DB 存储过程、触发器、自定义函数的实现

阅读 大约需要 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