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