当前位置：首页 > news >正文

SQL Server通过存储过程调用DLL程序集发送飞书卡片消息

news 2025/7/21 16:22:21

1. 引言

在现代企业应用开发中，数据库系统与即时通讯工具的集成变得越来越重要。SQL Server作为一款功能强大的关系型数据库管理系统，可以通过存储过程实现各种自动化任务，包括发送通知消息。本文将详细介绍如何在SQL Server中创建存储过程，通过HTTP请求与飞书开放平台API交互，实现发送富文本卡片消息的功能。

1.1 应用场景

SQL Server发送飞书卡片消息的应用场景非常广泛，包括但不限于：

数据库监控告警：当数据库出现性能问题、空间不足或关键作业失败时自动通知DBA团队
业务流程通知：在订单处理、库存变更等业务操作完成后通知相关人员
定时报表推送：定期将关键业务数据以卡片形式发送给管理层
审批流程触发：当数据库中的审批状态变更时通知审批人
系统集成：作为企业应用集成的一部分，连接数据库系统与协作平台

1.2 技术概览

实现这一功能主要涉及以下技术组件：

SQL Server CLR集成：允许在SQL Server中执行.NET代码
HTTP客户端：用于向飞书API发送请求
JSON处理：构建和解析飞书API所需的JSON格式消息
OAuth 2.0认证：获取访问飞书API所需的令牌
存储过程封装：提供简洁的数据库接口供其他应用调用

2. 准备工作

2.1 环境要求

在开始实现之前，请确保满足以下环境要求：

SQL Server 2012或更高版本（支持CLR集成）
.NET Framework 4.5或更高版本
飞书开发者账号及应用权限
数据库服务器能够访问互联网（与飞书API通信）

2.2 飞书应用配置

创建飞书应用：
- 登录飞书开放平台(https://open.feishu.cn/)
- 进入"开发者后台"，点击"创建应用"
- 填写应用名称、描述等基本信息
获取凭证信息：
- 应用凭证：App ID和App Secret
- 权限配置：确保已添加"发送消息"权限
- 启用机器人能力
获取webhook地址（可选）：
- 如果使用webhook方式发送消息，需在机器人配置中获取webhook地址
- 本文主要介绍通过API方式发送

2.3 SQL Server配置

启用CLR集成：

sp_configure 'clr enabled', 1
RECONFIGURE

设置TRUSTWORTHY ON（仅开发环境建议）：

ALTER DATABASE YourDatabaseName SET TRUSTWORTHY ON

创建非对称密钥和登录（生产环境推荐）：

CREATE ASYMMETRIC KEY CLRFeishuKey FROM EXECUTABLE FILE = 'C:\path\to\your\assembly.dll'
CREATE LOGIN CLRFeishuLogin FROM ASYMMETRIC KEY CLRFeishuKey
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRFeishuLogin

3. 实现方案设计

3.1 架构设计

整个解决方案的架构分为以下几个层次：

数据库层：存储过程作为入口点，处理业务逻辑
CLR集成层：.NET程序集处理HTTP通信和JSON序列化
API通信层：与飞书服务器交互，发送消息
安全层：处理认证和授权

3.2 消息流程

应用程序或SQL作业调用存储过程
存储过程调用CLR函数/方法
CLR代码构建请求，发送到飞书API
飞书API处理请求并返回结果
结果返回给调用方

3.3 错误处理设计

完善的错误处理机制应包括：

API请求失败的重试逻辑
详细的错误日志记录
返回有意义的错误信息给调用方
敏感信息的安全处理

4. CLR程序集实现

4.1 创建C#类库项目

使用Visual Studio创建新的类库项目，命名为"SQLFeishuIntegration"。

4.2 核心代码实现

以下是完整的C#实现代码：

using System;
using System.IO;
using System.Net;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Web.Script.Serialization;[Serializable]
[DataContract]
public class FeishuAccessTokenResponse
{[DataMember(Name = "code")]public int Code { get; set; }[DataMember(Name = "msg")]public string Message { get; set; }[DataMember(Name = "tenant_access_token")]public string TenantAccessToken { get; set; }[DataMember(Name = "expire")]public int Expire { get; set; }
}[Serializable]
[DataContract]
public class FeishuMessageResponse
{[DataMember(Name = "code")]public int Code { get; set; }[DataMember(Name = "msg")]public string Message { get; set; }[DataMember(Name = "data")]public MessageResponseData Data { get; set; }
}[Serializable]
[DataContract]
public class MessageResponseData
{[DataMember(Name = "message_id")]public string MessageId { get; set; }
}[Serializable]
[DataContract]
public class CardContent
{[DataMember(Name = "config")]public CardConfig Config { get; set; }[DataMember(Name = "header")]public CardHeader Header { get; set; }[DataMember(Name = "elements")]public List<CardElement> Elements { get; set; }
}[Serializable]
[DataContract]
public class CardConfig
{[DataMember(Name = "wide_screen_mode")]public bool WideScreenMode { get; set; } = true;[DataMember(Name = "enable_forward")]public bool EnableForward { get; set; } = true;
}[Serializable]
[DataContract]
public class CardHeader
{[DataMember(Name = "title")]public CardTitle Title { get; set; }[DataMember(Name = "template")]public string Template { get; set; }
}[Serializable]
[DataContract]
public class CardTitle
{[DataMember(Name = "tag")]public string Tag { get; set; } = "plain_text";[DataMember(Name = "content")]public string Content { get; set; }
}[Serializable]
[DataContract]
public class CardElement
{[DataMember(Name = "tag")]public string Tag { get; set; }[DataMember(Name = "text")]public CardText Text { get; set; }[DataMember(Name = "fields")]public List<CardField> Fields { get; set; }[DataMember(Name = "actions")]public List<CardAction> Actions { get; set; }
}[Serializable]
[DataContract]
public class CardText
{[DataMember(Name = "tag")]public string Tag { get; set; } = "lark_md";[DataMember(Name = "content")]public string Content { get; set; }
}[Serializable]
[DataContract]
public class CardField
{[DataMember(Name = "is_short")]public bool IsShort { get; set; }[DataMember(Name = "text")]public CardText Text { get; set; }
}[Serializable]
[DataContract]
public class CardAction
{[DataMember(Name = "tag")]public string Tag { get; set; } = "button";[DataMember(Name = "text")]public CardText Text { get; set; }[DataMember(Name = "type")]public string Type { get; set; }[DataMember(Name = "url")]public string Url { get; set; }
}public class FeishuMessageSender
{private const string TokenUrl = "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal";private const string MessageUrl = "https://open.feishu.cn/open-apis/im/v1/messages";private static Dictionary<string, Tuple<string, DateTime>> _tokenCache = new Dictionary<string, Tuple<string, DateTime>>();private static readonly object _lock = new object();[SqlProcedure]public static void SendFeishuCardMessage(SqlString appId, SqlString appSecret, SqlString receiveId, SqlString receiveIdType, SqlString title, SqlString content, SqlString buttonText, SqlString buttonUrl,out SqlString resultMessage,out SqlInt32 resultCode){resultMessage = "";resultCode = -1;try{// 获取访问令牌string accessToken = GetAccessToken(appId.Value, appSecret.Value);if (string.IsNullOrEmpty(accessToken)){resultMessage = "Failed to get access token";return;}// 构建卡片消息var cardContent = new CardContent{Config = new CardConfig(),Header = new CardHeader{Title = new CardTitle { Content = title.Value },Template = "blue"},Elements = new List<CardElement>{new CardElement{Tag = "div",Text = new CardText { Content = content.Value }}}};if (!string.IsNullOrEmpty(buttonText.Value) && !string.IsNullOrEmpty(buttonUrl.Value)){cardContent.Elements.Add(new CardElement{Tag = "action",Actions = new List<CardAction>{new CardAction{Text = new CardText { Content = buttonText.Value },Type = "default",Url = buttonUrl.Value}}});}var messageData = new{receive_id = receiveId.Value,content = new JavaScriptSerializer().Serialize(cardContent),msg_type = "interactive"};string jsonData = new JavaScriptSerializer().Serialize(messageData);// 发送消息var response = SendHttpRequest($"{MessageUrl}?receive_id_type={receiveIdType.Value}","POST",jsonData,new Dictionary<string, string>{{ "Authorization", $"Bearer {accessToken}" },{ "Content-Type", "application/json" }});var serializer = new DataContractJsonSerializer(typeof(FeishuMessageResponse));using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(response))){var messageResponse = (FeishuMessageResponse)serializer.ReadObject(ms);if (messageResponse.Code == 0){resultCode = 0;resultMessage = $"Message sent successfully. Message ID: {messageResponse.Data.MessageId}";}else{resultMessage = $"Failed to send message: {messageResponse.Message}";}}}catch (Exception ex){resultMessage = $"Error: {ex.Message}";}}private static string GetAccessToken(string appId, string appSecret){string cacheKey = $"{appId}_{appSecret}";lock (_lock){if (_tokenCache.ContainsKey(cacheKey)){var cachedToken = _tokenCache[cacheKey];if (DateTime.Now < cachedToken.Item2.AddMinutes(-5)) // 提前5分钟过期{return cachedToken.Item1;}_tokenCache.Remove(cacheKey);}var requestData = new{app_id = appId,app_secret = appSecret};string jsonData = new JavaScriptSerializer().Serialize(requestData);string response = SendHttpRequest(TokenUrl, "POST", jsonData, null);var serializer = new DataContractJsonSerializer(typeof(FeishuAccessTokenResponse));using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(response))){var tokenResponse = (FeishuAccessTokenResponse)serializer.ReadObject(ms);if (tokenResponse.Code == 0){_tokenCache[cacheKey] = new Tuple<string, DateTime>(tokenResponse.TenantAccessToken,DateTime.Now.AddSeconds(tokenResponse.Expire));return tokenResponse.TenantAccessToken;}else{throw new Exception($"Failed to get access token: {tokenResponse.Message}");}}}}private static string SendHttpRequest(string url, string method, string data, Dictionary<string, string> headers){HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);request.Method = method;request.Timeout = 30000; // 30秒超时if (headers != null){foreach (var header in headers){request.Headers.Add(header.Key, header.Value);}}if (method == "POST" || method == "PUT"){byte[] dataBytes = Encoding.UTF8.GetBytes(data);request.ContentType = "application/json";request.ContentLength = dataBytes.Length;using (Stream requestStream = request.GetRequestStream()){requestStream.Write(dataBytes, 0, dataBytes.Length);}}try{using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())using (Stream responseStream = response.GetResponseStream())using (StreamReader reader = new StreamReader(responseStream, Encoding.UTF8)){return reader.ReadToEnd();}}catch (WebException ex){if (ex.Response != null){using (Stream responseStream = ex.Response.GetResponseStream())using (StreamReader reader = new StreamReader(responseStream, Encoding.UTF8)){string errorResponse = reader.ReadToEnd();throw new Exception($"HTTP Error: {ex.Status}, Response: {errorResponse}");}}throw;}}
}

4.3 代码说明

数据模型类：定义了飞书API返回的数据结构
FeishuMessageSender类：核心功能类，包含发送消息的主要逻辑
GetAccessToken方法：获取飞书API访问令牌，带缓存机制
SendHttpRequest方法：通用的HTTP请求发送方法
SendFeishuCardMessage方法：SQL Server调用的入口点，构建卡片消息并发送

4.4 编译和部署

编译项目生成DLL文件

在SQL Server中注册程序集：

CREATE ASSEMBLY SQLFeishuIntegration
FROM 'C:\path\to\SQLFeishuIntegration.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

5. 存储过程实现

5.1 创建存储过程

基于CLR程序集创建存储过程：

CREATE PROCEDURE dbo.sp_SendFeishuCardMessage@AppId NVARCHAR(100),@AppSecret NVARCHAR(100),@ReceiveId NVARCHAR(100),@ReceiveIdType NVARCHAR(20) = 'open_id', -- 可以是 open_id, user_id, email, chat_id@Title NVARCHAR(200),@Content NVARCHAR(MAX),@ButtonText NVARCHAR(100) = NULL,@ButtonUrl NVARCHAR(500) = NULL,@ResultMessage NVARCHAR(MAX) OUTPUT,@ResultCode INT OUTPUT
AS
EXTERNAL NAME SQLFeishuIntegration.[SQLFeishuIntegration.FeishuMessageSender].SendFeishuCardMessage
GO

5.2 简化版存储过程

为了方便日常使用，可以创建一个简化版的存储过程：

CREATE PROCEDURE dbo.sp_SendSimpleFeishuAlert@Title NVARCHAR(200),@Content NVARCHAR(MAX),@IsSuccess BIT = 1,@ButtonText NVARCHAR(100) = NULL,@ButtonUrl NVARCHAR(500) = NULL
AS
BEGINDECLARE @AppId NVARCHAR(100) = 'your_app_id';DECLARE @AppSecret NVARCHAR(100) = 'your_app_secret';DECLARE @ReceiveId NVARCHAR(100) = 'default_receive_id';DECLARE @ResultMessage NVARCHAR(MAX);DECLARE @ResultCode INT;-- 根据成功/失败设置不同的标题样式IF @IsSuccess = 1SET @Title = '✅ ' + @Title;ELSESET @Title = '❌ ' + @Title;EXEC dbo.sp_SendFeishuCardMessage@AppId = @AppId,@AppSecret = @AppSecret,@ReceiveId = @ReceiveId,@ReceiveIdType = 'user_id',@Title = @Title,@Content = @Content,@ButtonText = @ButtonText,@ButtonUrl = @ButtonUrl,@ResultMessage = @ResultMessage OUTPUT,@ResultCode = @ResultCode OUTPUT;IF @ResultCode <> 0RAISERROR('Failed to send Feishu message: %s', 16, 1, @ResultMessage);
END
GO

5.3 使用示例

-- 示例1：发送简单通知
DECLARE @ResultMsg NVARCHAR(MAX);
DECLARE @ResultCode INT;EXEC dbo.sp_SendFeishuCardMessage@AppId = 'your_app_id',@AppSecret = 'your_app_secret',@ReceiveId = 'user_id_or_open_id',@Title = '数据库备份通知',@Content = '数据库备份已完成，耗时2小时15分钟。备份大小: 45GB',@ButtonText = '查看详情',@ButtonUrl = 'https://your-domain.com/backup-reports',@ResultMessage = @ResultMsg OUTPUT,@ResultCode = @ResultCode OUTPUT;SELECT @ResultCode AS ResultCode, @ResultMsg AS ResultMessage;-- 示例2：使用简化版存储过程
EXEC dbo.sp_SendSimpleFeishuAlert@Title = '每日销售报表',@Content = '今日销售额: ¥1,250,000\n订单数: 1,245\n平均客单价: ¥1,004',@IsSuccess = 1,@ButtonText = '下载完整报表',@ButtonUrl = 'https://your-domain.com/reports/daily-sales';

6. 高级功能实现

6.1 支持Markdown格式

飞书卡片消息支持Markdown格式，可以增强消息的可读性：

ALTER PROCEDURE dbo.sp_SendFeishuCardMessageWithMarkdown@AppId NVARCHAR(100),@AppSecret NVARCHAR(100),@ReceiveId NVARCHAR(100),@ReceiveIdType NVARCHAR(20) = 'open_id',@Title NVARCHAR(200),@MarkdownContent NVARCHAR(MAX),@ButtonText NVARCHAR(100) = NULL,@ButtonUrl NVARCHAR(500) = NULL,@ResultMessage NVARCHAR(MAX) OUTPUT,@ResultCode INT OUTPUT
AS
BEGIN-- 构建完整的卡片JSONDECLARE @CardJson NVARCHAR(MAX);SET @CardJson = N'{"config": {"wide_screen_mode": true,"enable_forward": true},"header": {"title": {"tag": "plain_text","content": "' + REPLACE(@Title, '"', '\"') + '"},"template": "blue"},"elements": [{"tag": "div","text": {"tag": "lark_md","content": "' + REPLACE(REPLACE(@MarkdownContent, '\', '\\'), '"', '\"') + '"}}';IF @ButtonText IS NOT NULL AND @ButtonUrl IS NOT NULLBEGINSET @CardJson = @CardJson + N',{"tag": "action","actions": [{"tag": "button","text": {"tag": "plain_text","content": "' + REPLACE(@ButtonText, '"', '\"') + '"},"type": "default","url": "' + REPLACE(@ButtonUrl, '"', '\"') + '"}]}';ENDSET @CardJson = @CardJson + N']}';-- 调用CLR方法发送消息EXEC dbo.sp_SendFeishuCardMessage@AppId = @AppId,@AppSecret = @AppSecret,@ReceiveId = @ReceiveId,@ReceiveIdType = @ReceiveIdType,@Title = @Title,@Content = @CardJson,@ButtonText = NULL, -- 已经在JSON中包含@ButtonUrl = NULL, -- 已经在JSON中包含@ResultMessage = @ResultMessage OUTPUT,@ResultCode = @ResultCode OUTPUT;
END
GO

6.2 支持多列布局

飞书卡片支持多列布局，可以更高效地展示信息：

CREATE PROCEDURE dbo.sp_SendFeishuMultiColumnCard@AppId NVARCHAR(100),@AppSecret NVARCHAR(100),@ReceiveId NVARCHAR(100),@ReceiveIdType NVARCHAR(20) = 'open_id',@Title NVARCHAR(200),@Column1Title NVARCHAR(100),@Column1Content NVARCHAR(MAX),@Column2Title NVARCHAR(100),@Column2Content NVARCHAR(MAX),@ButtonText NVARCHAR(100) = NULL,@ButtonUrl NVARCHAR(500) = NULL,@ResultMessage NVARCHAR(MAX) OUTPUT,@ResultCode INT OUTPUT
AS
BEGINDECLARE @CardJson NVARCHAR(MAX);SET @CardJson = N'{"config": {"wide_screen_mode": true,"enable_forward": true},"header": {"title": {"tag": "plain_text","content": "' + REPLACE(@Title, '"', '\"') + '"},"template": "wathet"},"elements": [{"tag": "div","fields": [{"is_short": true,"text": {"tag": "lark_md","content": "**' + REPLACE(@Column1Title, '"', '\"') + '**\n' + REPLACE(REPLACE(@Column1Content, '\', '\\'), '"', '\"') + '"}},{"is_short": true,"text": {"tag": "lark_md","content": "**' + REPLACE(@Column2Title, '"', '\"') + '**\n' + REPLACE(REPLACE(@Column2Content, '\', '\\'), '"', '\"') + '"}}]}';IF @ButtonText IS NOT NULL AND @ButtonUrl IS NOT NULLBEGINSET @CardJson = @CardJson + N',{"tag": "action","actions": [{"tag": "button","text": {"tag": "plain_text","content": "' + REPLACE(@ButtonText, '"', '\"') + '"},"type": "default","url": "' + REPLACE(@ButtonUrl, '"', '\"') + '"}]}';ENDSET @CardJson = @CardJson + N']}';EXEC dbo.sp_SendFeishuCardMessage@AppId = @AppId,@AppSecret = @AppSecret,@ReceiveId = @ReceiveId,@ReceiveIdType = @ReceiveIdType,@Title = @Title,@Content = @CardJson,@ButtonText = NULL,@ButtonUrl = NULL,@ResultMessage = @ResultMessage OUTPUT,@ResultCode = @ResultCode OUTPUT;
END
GO

6.3 定时消息发送

结合SQL Server Agent作业，可以实现定时消息发送：

-- 创建存储过程用于定时发送日报
CREATE PROCEDURE dbo.sp_SendDailyDatabaseReport
AS
BEGINDECLARE @ReportDate NVARCHAR(20) = CONVERT(NVARCHAR(10), GETDATE(), 120);DECLARE @Title NVARCHAR(200) = '数据库日报 - ' + @ReportDate;DECLARE @Content NVARCHAR(MAX);DECLARE @ResultMessage NVARCHAR(MAX);DECLARE @ResultCode INT;-- 获取数据库状态信息DECLARE @DbSize NVARCHAR(50);DECLARE @BackupStatus NVARCHAR(100);DECLARE @JobStatus NVARCHAR(MAX);SELECT @DbSize = CONVERT(NVARCHAR(50), SUM(size * 8 / 1024)) + ' MB'FROM sys.master_filesWHERE database_id = DB_ID();SELECT @BackupStatus = CASE WHEN MAX(backup_finish_date) > DATEADD(DAY, -1, GETDATE()) THEN '✅ 最近24小时内有备份' ELSE '❌ 最近24小时内无备份' ENDFROM msdb.dbo.backupsetWHERE database_name = DB_NAME();-- 获取作业状态SET @JobStatus = '';SELECT @JobStatus = @JobStatus + CASE WHEN run_status = 0 THEN '❌ 失败 - ' WHEN run_status = 1 THEN '✅ 成功 - ' WHEN run_status = 2 THEN '🔄 重试 - ' WHEN run_status = 3 THEN '⏸️ 取消 - ' ELSE '❓ 未知 - ' END +name + '\n最后运行: ' + CONVERT(NVARCHAR(20), last_run_outcome_date, 120) + '\n\n'FROM (SELECT j.name, h.run_status, MAX(h.run_date) AS last_run_outcome_dateFROM msdb.dbo.sysjobs jINNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_idWHERE h.run_date >= CONVERT(VARCHAR(8), GETDATE(), 112)GROUP BY j.name, h.run_status) AS JobStatus;-- 构建消息内容SET @Content = '**数据库大小**: ' + @DbSize + '\n' +'**备份状态**: ' + @BackupStatus + '\n\n' +'**今日作业执行情况**:\n' + @JobStatus;-- 发送消息EXEC dbo.sp_SendSimpleFeishuAlert@Title = @Title,@Content = @Content,@IsSuccess = 1,@ButtonText = '查看详细报表',@ButtonUrl = 'https://your-domain.com/db-reports/daily';
END
GO

然后创建SQL Server Agent作业，每天定时执行此存储过程。

7. 安全考虑

7.1 敏感信息保护

加密存储凭证：

-- 创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword123!';-- 创建证书
CREATE CERTIFICATE FeishuCert WITH SUBJECT = 'Feishu API Credentials';-- 创建对称密钥
CREATE SYMMETRIC KEY FeishuSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE FeishuCert;-- 加密存储App Secret
OPEN SYMMETRIC KEY FeishuSymmetricKey DECRYPTION BY CERTIFICATE FeishuCert;INSERT INTO dbo.FeishuCredentials (AppId, EncryptedAppSecret)
VALUES ('your_app_id',ENCRYPTBYKEY(KEY_GUID('FeishuSymmetricKey'), 'your_app_secret')
);CLOSE SYMMETRIC KEY FeishuSymmetricKey;

安全获取凭证：

CREATE PROCEDURE dbo.sp_GetFeishuCredentials@AppId NVARCHAR(100) OUTPUT,@AppSecret NVARCHAR(100) OUTPUT
AS
BEGINOPEN SYMMETRIC KEY FeishuSymmetricKey DECRYPTION BY CERTIFICATE FeishuCert;SELECT @AppId = AppId,@AppSecret = CONVERT(NVARCHAR(100), DECRYPTBYKEY(EncryptedAppSecret))FROM dbo.FeishuCredentialsWHERE IsActive = 1;CLOSE SYMMETRIC KEY FeishuSymmetricKey;
END
GO

7.2 权限控制

最小权限原则：

-- 创建专门的角色
CREATE ROLE FeishuMessageSender;-- 只授予必要的执行权限
GRANT EXECUTE ON dbo.sp_SendSimpleFeishuAlert TO FeishuMessageSender;-- 不直接授予基础存储过程的权限
DENY EXECUTE ON dbo.sp_SendFeishuCardMessage TO PUBLIC;

审计日志：

CREATE TABLE dbo.FeishuMessageLog (LogId INT IDENTITY(1,1) PRIMARY KEY,AppId NVARCHAR(100),ReceiveId NVARCHAR(100),ReceiveIdType NVARCHAR(20),Title NVARCHAR(200),Content NVARCHAR(MAX),ButtonText NVARCHAR(100) NULL,ButtonUrl NVARCHAR(500) NULL,ResultCode INT,ResultMessage NVARCHAR(MAX),SentBy NVARCHAR(128) DEFAULT SUSER_SNAME(),SentTime DATETIME DEFAULT GETDATE(),ClientHost NVARCHAR(128) DEFAULT HOST_NAME(),ClientApp NVARCHAR(128) DEFAULT APP_NAME()
);-- 修改存储过程添加日志记录
ALTER PROCEDURE dbo.sp_SendFeishuCardMessage-- 原有参数...
AS
BEGIN-- 原有逻辑...-- 记录日志INSERT INTO dbo.FeishuMessageLog (AppId, ReceiveId, ReceiveIdType, Title, Content, ButtonText, ButtonUrl, ResultCode, ResultMessage)VALUES (@AppId, @ReceiveId, @ReceiveIdType, @Title, @Content,@ButtonText, @ButtonUrl, @ResultCode, @ResultMessage);
END
GO

8. 性能优化

8.1 令牌缓存优化

在CLR代码中已经实现了内存缓存，还可以添加数据库层面的缓存：

CREATE TABLE dbo.FeishuTokenCache (AppId NVARCHAR(100) NOT NULL,AccessToken NVARCHAR(500) NOT NULL,ExpireTime DATETIME NOT NULL,LastUpdated DATETIME NOT NULL DEFAULT GETDATE(),PRIMARY KEY (AppId)
);-- 修改获取令牌的存储过程
ALTER PROCEDURE dbo.sp_GetFeishuAccessToken@AppId NVARCHAR(100),@AppSecret NVARCHAR(100),@AccessToken NVARCHAR(500) OUTPUT,@IsNewToken BIT OUTPUT
AS
BEGINSET @IsNewToken = 0;-- 检查缓存中是否有未过期的令牌SELECT @AccessToken = AccessTokenFROM dbo.FeishuTokenCacheWHERE AppId = @AppId AND ExpireTime > DATEADD(MINUTE, 5, GETDATE());IF @AccessToken IS NULLBEGIN-- 调用CLR方法获取新令牌DECLARE @ResultMessage NVARCHAR(MAX);DECLARE @ResultCode INT;DECLARE @ExpireIn INT;-- 这里需要扩展CLR方法以返回过期时间-- 假设有一个新的CLR方法可以返回完整响应-- 更新缓存DELETE FROM dbo.FeishuTokenCache WHERE AppId = @AppId;INSERT INTO dbo.FeishuTokenCache (AppId, AccessToken, ExpireTime)VALUES (@AppId, @AccessToken, DATEADD(SECOND, @ExpireIn, GETDATE()));SET @IsNewToken = 1;END
END
GO

8.2 批量消息发送

对于需要发送多条消息的场景，可以实现批量发送：

CREATE PROCEDURE dbo.sp_SendFeishuCardsBatch@AppId NVARCHAR(100),@AppSecret NVARCHAR(100),@MessageList NVARCHAR(MAX), -- JSON格式的消息列表@ResultSummary NVARCHAR(MAX) OUTPUT
AS
BEGINDECLARE @AccessToken NVARCHAR(500);DECLARE @IsNewToken BIT;EXEC dbo.sp_GetFeishuAccessToken @AppId, @AppSecret, @AccessToken OUTPUT, @IsNewToken OUTPUT;IF @AccessToken IS NULLBEGINSET @ResultSummary = 'Failed to get access token';RETURN;END-- 解析JSON消息列表-- 这里需要SQL Server 2016+支持JSON功能DECLARE @Results TABLE (MessageId INT IDENTITY(1,1),ReceiveId NVARCHAR(100),Title NVARCHAR(200),ResultCode INT,ResultMessage NVARCHAR(MAX));INSERT INTO @Results (ReceiveId, Title)SELECT receive_id, titleFROM OPENJSON(@MessageList)WITH (receive_id NVARCHAR(100) '$.receive_id',receive_id_type NVARCHAR(20) '$.receive_id_type',title NVARCHAR(200) '$.title',content NVARCHAR(MAX) '$.content',button_text NVARCHAR(100) '$.button_text',button_url NVARCHAR(500) '$.button_url');-- 遍历发送每条消息DECLARE @CurrentId INT = 1;DECLARE @MaxId INT = (SELECT MAX(MessageId) FROM @Results);DECLARE @CurrentReceiveId NVARCHAR(100);DECLARE @CurrentReceiveIdType NVARCHAR(20);DECLARE @CurrentTitle NVARCHAR(200);DECLARE @CurrentContent NVARCHAR(MAX);DECLARE @CurrentButtonText NVARCHAR(100);DECLARE @CurrentButtonUrl NVARCHAR(500);DECLARE @CurrentResultMessage NVARCHAR(MAX);DECLARE @CurrentResultCode INT;WHILE @CurrentId <= @MaxIdBEGINSELECT @CurrentReceiveId = r.ReceiveId,@CurrentTitle = r.Title,@CurrentReceiveIdType = m.receive_id_type,@CurrentContent = m.content,@CurrentButtonText = m.button_text,@CurrentButtonUrl = m.button_urlFROM @Results rCROSS APPLY OPENJSON(@MessageList)WITH (receive_id NVARCHAR(100) '$.receive_id',receive_id_type NVARCHAR(20) '$.receive_id_type',title NVARCHAR(200) '$.title',content NVARCHAR(MAX) '$.content',button_text NVARCHAR(100) '$.button_text',button_url NVARCHAR(500) '$.button_url') mWHERE r.MessageId = @CurrentId AND m.receive_id = r.ReceiveId;EXEC dbo.sp_SendFeishuCardMessage@AppId = @AppId,@AppSecret = @AppSecret,@ReceiveId = @CurrentReceiveId,@ReceiveIdType = @CurrentReceiveIdType,@Title = @CurrentTitle,@Content = @CurrentContent,@ButtonText = @CurrentButtonText,@ButtonUrl = @CurrentButtonUrl,@ResultMessage = @CurrentResultMessage OUTPUT,@ResultCode = @CurrentResultCode OUTPUT;UPDATE @ResultsSET ResultCode = @CurrentResultCode,ResultMessage = @CurrentResultMessageWHERE MessageId = @CurrentId;SET @CurrentId = @CurrentId + 1;END-- 生成汇总结果SELECT @ResultSummary = (SELECT Title AS '消息标题',CASE WHEN ResultCode = 0 THEN '成功' ELSE '失败' END AS '发送状态',ResultMessage AS '结果详情'FROM @ResultsFOR JSON PATH);
END
GO

9. 错误处理与监控

9.1 完善的错误处理

ALTER PROCEDURE dbo.sp_SendFeishuCardMessage-- 原有参数...
AS
BEGINBEGIN TRYBEGIN TRANSACTION;-- 参数验证IF @AppId IS NULL OR @AppSecret IS NULL OR @ReceiveId IS NULL OR @Title IS NULL OR @Content IS NULLBEGINRAISERROR('Required parameters are missing', 16, 1);RETURN;END-- 验证receive_id_typeIF @ReceiveIdType NOT IN ('open_id', 'user_id', 'email', 'chat_id')BEGINSET @ReceiveIdType = 'open_id';END-- 调用CLR方法DECLARE @CLRResultMessage NVARCHAR(MAX);DECLARE @CLRResultCode INT;EXEC dbo.CLR_SendFeishuCardMessage@AppId = @AppId,@AppSecret = @AppSecret,@ReceiveId = @ReceiveId,@ReceiveIdType = @ReceiveIdType,@Title = @Title,@Content = @Content,@ButtonText = @ButtonText,@ButtonUrl = @ButtonUrl,@ResultMessage = @CLRResultMessage OUTPUT,@ResultCode = @CLRResultCode OUTPUT;-- 处理结果IF @CLRResultCode <> 0BEGIN-- 记录详细错误INSERT INTO dbo.ErrorLog (ProcedureName, ErrorMessage, ErrorDetails)VALUES ('sp_SendFeishuCardMessage', @CLRResultMessage, 'AppId: ' + ISNULL(@AppId, 'NULL') + ', ReceiveId: ' + ISNULL(@ReceiveId, 'NULL'));-- 根据错误类型决定是否重试IF @CLRResultMessage LIKE '%token expired%' OR @CLRResultMessage LIKE '%invalid token%'BEGIN-- 令牌过期，清除缓存并重试一次DELETE FROM dbo.FeishuTokenCache WHERE AppId = @AppId;EXEC dbo.CLR_SendFeishuCardMessage@AppId = @AppId,@AppSecret = @AppSecret,@ReceiveId = @ReceiveId,@ReceiveIdType = @ReceiveIdType,@Title = @Title,@Content = @Content,@ButtonText = @ButtonText,@ButtonUrl = @ButtonUrl,@ResultMessage = @CLRResultMessage OUTPUT,@ResultCode = @CLRResultCode OUTPUT;IF @CLRResultCode <> 0RAISERROR('Retry failed: %s', 16, 1, @CLRResultMessage);ENDELSEBEGINRAISERROR('%s', 16, 1, @CLRResultMessage);ENDENDSET @ResultMessage = @CLRResultMessage;SET @ResultCode = @CLRResultCode;COMMIT TRANSACTION;END TRYBEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;SET @ResultMessage = ERROR_MESSAGE();SET @ResultCode = -1;-- 记录未处理异常INSERT INTO dbo.ErrorLog (ProcedureName, ErrorMessage, ErrorDetails)VALUES ('sp_SendFeishuCardMessage', ERROR_MESSAGE(), 'Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)) + ', State: ' + CAST(ERROR_STATE() AS NVARCHAR(10)));END CATCH
END
GO

9.2 监控与报警

创建监控存储过程，检查消息发送失败情况：

CREATE PROCEDURE dbo.sp_MonitorFeishuMessageFailures@HoursToCheck INT = 24,@AlertThreshold INT = 5
AS
BEGINDECLARE @FailureCount INT;DECLARE @LastError NVARCHAR(MAX);SELECT @FailureCount = COUNT(*),@LastError = MAX(ResultMessage)FROM dbo.FeishuMessageLogWHERE ResultCode <> 0AND SentTime > DATEADD(HOUR, -@HoursToCheck, GETDATE());IF @FailureCount >= @AlertThresholdBEGINDECLARE @AlertTitle NVARCHAR(200) = '飞书消息发送失败警报';DECLARE @AlertContent NVARCHAR(MAX) = '过去' + CAST(@HoursToCheck AS NVARCHAR) + '小时内共发生' + CAST(@FailureCount AS NVARCHAR) + '次消息发送失败。\n\n' +'最后一次错误信息:\n' + @LastError;-- 发送给管理员，使用不同的接收IDEXEC dbo.sp_SendSimpleFeishuAlert@Title = @AlertTitle,@Content = @AlertContent,@IsSuccess = 0;END
END
GO

10. 完整示例与测试

10.1 数据库监控报警示例

-- 监控数据库空间并发送警报
CREATE PROCEDURE dbo.sp_CheckDatabaseSpaceAndAlert@SpaceThresholdMB INT = 1024 -- 1GB
AS
BEGINDECLARE @FreeSpaceMB DECIMAL(10,2);DECLARE @DbName NVARCHAR(128) = DB_NAME();DECLARE @Title NVARCHAR(200);DECLARE @Content NVARCHAR(MAX);DECLARE @IsCritical BIT = 0;-- 获取数据库文件空间信息SELECT @FreeSpaceMB = SUM(CAST(available_bytes AS DECIMAL(10,2)) / 1024 / 1024)FROM sys.dm_os_volume_stats(DB_ID(), NULL) vCROSS APPLY sys.database_files fWHERE f.file_id = 1; -- 主要数据文件IF @FreeSpaceMB < @SpaceThresholdMBBEGINSET @IsCritical = 1;SET @Title = '⚠️ 数据库空间不足警报 - ' + @DbName;SET @Content = '数据库 **' + @DbName + '** 剩余空间仅剩 ' + CAST(@FreeSpaceMB AS NVARCHAR(20)) + ' MB，低于阈值 ' + CAST(@SpaceThresholdMB AS NVARCHAR(20)) + ' MB。\n\n' +'**建议立即处理**，否则可能导致数据库操作失败。';ENDELSEBEGINSET @Title = '✅ 数据库空间正常 - ' + @DbName;SET @Content = '数据库 **' + @DbName + '** 剩余空间充足: ' + CAST(@FreeSpaceMB AS NVARCHAR(20)) + ' MB，' +'阈值: ' + CAST(@SpaceThresholdMB AS NVARCHAR(20)) + ' MB。';END-- 添加详细信息DECLARE @DetailInfo NVARCHAR(MAX) = '';SELECT @DetailInfo = @DetailInfo + '文件: ' + name + '\n' +'类型: ' + CASE WHEN type = 0 THEN '数据' ELSE '日志' END + '\n' +'大小: ' + CAST(CAST(size * 8 / 1024 AS DECIMAL(10,2)) AS NVARCHAR(20)) + ' MB\n' +'使用率: ' + CAST(CAST((size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS DECIMAL(10,2)) AS NVARCHAR(20)) + ' MB 空闲\n\n'FROM sys.database_files;SET @Content = @Content + '\n\n**详细文件信息**:\n' + @DetailInfo;-- 发送警报EXEC dbo.sp_SendSimpleFeishuAlert@Title = @Title,@Content = @Content,@IsSuccess = CASE WHEN @IsCritical = 1 THEN 0 ELSE 1 END,@ButtonText = CASE WHEN @IsCritical = 1 THEN '立即处理' ELSE NULL END,@ButtonUrl = CASE WHEN @IsCritical = 1 THEN 'https://your-domain.com/db-admin?action=cleanup' ELSE NULL END;
END
GO

10.2 业务通知示例

-- 订单处理完成通知
CREATE PROCEDURE dbo.sp_SendOrderProcessedNotification@OrderId INT,@CustomerName NVARCHAR(100),@OrderAmount DECIMAL(18,2),@ProcessedBy NVARCHAR(100),@ProcessTime DATETIME
AS
BEGINDECLARE @Title NVARCHAR(200) = '订单处理完成通知 #' + CAST(@OrderId AS NVARCHAR(10));DECLARE @Content NVARCHAR(MAX) = '**订单编号**: #' + CAST(@OrderId AS NVARCHAR(10)) + '\n' +'**客户名称**: ' + @CustomerName + '\n' +'**订单金额**: ¥' + CAST(@OrderAmount AS NVARCHAR(20)) + '\n' +'**处理人员**: ' + @ProcessedBy + '\n' +'**处理时间**: ' + CONVERT(NVARCHAR(20), @ProcessTime, 120) + '\n\n' +'订单已处理完成，请相关人员进行后续跟进。';EXEC dbo.sp_SendSimpleFeishuAlert@Title = @Title,@Content = @Content,@IsSuccess = 1,@ButtonText = '查看订单详情',@ButtonUrl = 'https://your-domain.com/orders/' + CAST(@OrderId AS NVARCHAR(10));
END
GO

10.3 测试脚本

-- 测试简单消息
DECLARE @ResultMsg NVARCHAR(MAX);
DECLARE @ResultCode INT;EXEC dbo.sp_SendFeishuCardMessage@AppId = 'your_app_id',@AppSecret = 'your_app_secret',@ReceiveId = 'user_id_or_open_id',@Title = '测试消息',@Content = '这是一条测试消息，用于验证SQL Server发送飞书卡片的功能。',@ButtonText = '确认收到',@ButtonUrl = 'https://your-domain.com/confirm',@ResultMessage = @ResultMsg OUTPUT,@ResultCode = @ResultCode OUTPUT;SELECT @ResultCode AS ResultCode, @ResultMsg AS ResultMessage;-- 测试Markdown格式消息
DECLARE @MarkdownContent NVARCHAR(MAX) = 
'### 数据库性能报告\n' +
'**服务器**: DB-PROD-01\n' +
'**时间**: ' + CONVERT(NVARCHAR(20), GETDATE(), 120) + '\n\n' +
'#### 关键指标\n' +
'- CPU使用率: 78%\n' +
'- 内存压力: 65%\n' +
'- 活动连接数: 142\n\n' +
'```sql\n' +
'SELECT TOP 5 query_text, execution_count\n' +
'FROM sys.query_stats\n' +
'ORDER BY total_worker_time DESC;\n' +
'```';EXEC dbo.sp_SendFeishuCardMessageWithMarkdown@AppId = 'your_app_id',@AppSecret = 'your_app_secret',@ReceiveId = 'user_id_or_open_id',@Title = '数据库性能报告',@MarkdownContent = @MarkdownContent,@ButtonText = '查看详细报告',@ButtonUrl = 'https://your-domain.com/db-reports/performance',@ResultMessage = @ResultMsg OUTPUT,@ResultCode = @ResultCode OUTPUT;SELECT @ResultCode AS ResultCode, @ResultMsg AS ResultMessage;-- 测试多列布局
EXEC dbo.sp_SendFeishuMultiColumnCard@AppId = 'your_app_id',@AppSecret = 'your_app_secret',@ReceiveId = 'user_id_or_open_id',@Title = '服务器状态概览',@Column1Title = '数据库服务器',@Column1Content = '**状态**: 正常运行\n' +'**CPU**: 45%\n' +'**内存**: 62%\n' +'**磁盘**: 78%空闲',@Column2Title = '应用服务器',@Column2Content = '**状态**: 警告\n' +'**CPU**: 85%\n' +'**内存**: 90%\n' +'**响应时间**: 2.4s',@ButtonText = '查看所有服务器',@ButtonUrl = 'https://your-domain.com/server-monitor',@ResultMessage = @ResultMsg OUTPUT,@ResultCode = @ResultCode OUTPUT;SELECT @ResultCode AS ResultCode, @ResultMsg AS ResultMessage;