如何用C#实现Outlook会议请求状态自动同步至SQL数据库

核心思路 #

放弃Active Directory的方式，直接通过Outlook COM Interop或**Exchange Web Services (EWS)**监听会议请求的响应事件，捕获参会者的接受/拒绝状态，再将状态写入SQL数据库。

方法一：使用Outlook COM Interop（本地客户端监听） #

适用于本地安装Outlook的场景，直接监听Outlook客户端的日历事件变化。

1. 引入依赖 #

通过NuGet安装Microsoft.Office.Interop.Outlook包，或在项目中添加对应的COM组件引用。

2. 编写监听与同步代码 #

using Microsoft.Office.Interop.Outlook;
using System;
using System.Runtime.InteropServices;

namespace OutlookMeetingSync
{
    public class MeetingStatusSync
    {
        private Application _outlookApp;
        private Items _calendarItems;

        public void StartMonitoring()
        {
            _outlookApp = new Application();
            var calendarFolder = _outlookApp.Session.GetDefaultFolder(OlDefaultFolders.olFolderCalendar);
            _calendarItems = calendarFolder.Items;
            _calendarItems.ItemChange += OnCalendarItemUpdated;
            Console.WriteLine("已启动Outlook日历监听...");
        }

        private void OnCalendarItemUpdated(object item)
        {
            if (item is AppointmentItem meeting && meeting.MeetingStatus == OlMeetingStatus.olMeeting)
            {
                // 仅处理当前用户发起的会议
                if (meeting.Organizer.Address == _outlookApp.Session.CurrentUser.Address)
                {
                    foreach (Recipient attendee in meeting.Recipients)
                    {
                        string status = attendee.MeetingResponseStatus switch
                        {
                            OlMeetingResponseStatus.olResponseAccepted => "已接受",
                            OlMeetingResponseStatus.olResponseDeclined => "已拒绝",
                            OlMeetingResponseStatus.olResponseTentative => "暂定接受",
                            _ => "未响应"
                        };

                        // 同步状态到SQL
                        UpdateSqlDatabase(meeting.Subject, attendee.Name, attendee.Address, status);
                    }
                }
            }
        }

        private void UpdateSqlDatabase(string meetingTitle, string attendeeName, string attendeeEmail, string status)
        {
            // 替换为你的SQL连接字符串
            string connStr = "Data Source=你的SQL服务器;Initial Catalog=你的数据库;Integrated Security=True";
            string sql = @"
                MERGE INTO MeetingAttendeeStatus t
                USING (VALUES (@MeetingTitle, @AttendeeEmail)) s (MeetingTitle, AttendeeEmail)
                ON t.MeetingTitle = s.MeetingTitle AND t.AttendeeEmail = s.AttendeeEmail
                WHEN MATCHED THEN UPDATE SET Status = @Status, UpdateTime = GETDATE()
                WHEN NOT MATCHED THEN INSERT (MeetingTitle, AttendeeName, AttendeeEmail, Status, UpdateTime)
                VALUES (@MeetingTitle, @AttendeeName, @AttendeeEmail, @Status, GETDATE());";

            using (var conn = new System.Data.SqlClient.SqlConnection(connStr))
            {
                conn.Open();
                using (var cmd = new System.Data.SqlClient.SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@MeetingTitle", meetingTitle);
                    cmd.Parameters.AddWithValue("@AttendeeName", attendeeName);
                    cmd.Parameters.AddWithValue("@AttendeeEmail", attendeeEmail);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 停止监听时释放COM对象，避免内存泄漏
        public void StopMonitoring()
        {
            _calendarItems.ItemChange -= OnCalendarItemUpdated;
            Marshal.ReleaseComObject(_calendarItems);
            Marshal.ReleaseComObject(_outlookApp);
        }
    }
}

3. 注意事项 #

• 运行程序时需保持Outlook处于打开状态，或通过代码启动Outlook进程。
• 在Outlook信任中心授予程序访问权限，避免安全拦截。
• 务必处理COM对象的释放，防止内存泄漏。

方法二：使用Exchange Web Services (EWS)（无本地Outlook场景） #

无需依赖本地Outlook客户端，直接连接Exchange服务器监听会议状态变化。

1. 引入依赖 #

通过NuGet安装Microsoft.Exchange.WebServices包。

2. 编写EWS订阅与同步代码 #

using Microsoft.Exchange.WebServices.Data;
using System;

namespace EwsMeetingSync
{
    public class EwsMeetingStatusSync
    {
        private ExchangeService _ewsService;
        private StreamingSubscriptionConnection _subscriptionConn;

        public void InitializeEws()
        {
            _ewsService = new ExchangeService(ExchangeVersion.Exchange2016);
            _ewsService.Credentials = new WebCredentials("你的邮箱账号", "你的密码");
            _ewsService.AutodiscoverUrl("你的邮箱地址", RedirectionValidator);

            // 订阅日历文件夹的修改事件
            var subscription = _ewsService.SubscribeToStreamingNotifications(
                new FolderId[] { WellKnownFolderName.Calendar },
                EventType.Modified);

            _subscriptionConn = new StreamingSubscriptionConnection(_ewsService, 30);
            _subscriptionConn.AddSubscription(subscription);
            _subscriptionConn.OnNotificationEvent += OnMeetingStatusChanged;
            _subscriptionConn.Open();
            Console.WriteLine("已通过EWS启动会议状态监听...");
        }

        private bool RedirectionValidator(string redirectUrl)
        {
            // 可添加URL安全验证逻辑，默认返回true
            return true;
        }

        private void OnMeetingStatusChanged(object sender, NotificationEventArgs args)
        {
            foreach (var notification in args.Events)
            {
                if (notification is ItemEvent itemEvent && itemEvent.ItemType == ItemType.Appointment)
                {
                    var meeting = Appointment.Bind(_ewsService, itemEvent.ItemId);
                    if (meeting.IsMeeting && meeting.Organizer.Address == "你的邮箱地址")
                    {
                        foreach (var attendee in meeting.RequiredAttendees)
                        {
                            string status = attendee.ResponseType switch
                            {
                                MeetingResponseType.Accepted => "已接受",
                                MeetingResponseType.Declined => "已拒绝",
                                MeetingResponseType.Tentative => "暂定接受",
                                _ => "未响应"
                            };

                            UpdateSqlDatabase(meeting.Subject, attendee.Name, attendee.Address, status);
                        }
                    }
                }
            }
        }

        // 同步SQL的方法与COM方式一致，可直接复用
        private void UpdateSqlDatabase(string meetingTitle, string attendeeName, string attendeeEmail, string status)
        {
            // 替换为你的SQL逻辑
        }
    }
}

3. 注意事项 #

• 确保Exchange服务器开启EWS服务，且账号拥有相应的访问权限。
• 实现订阅断开后的重连逻辑，避免丢失事件通知。

为什么不用Active Directory？ #

AD仅用于用户身份管理与权限控制，无法直接获取Outlook会议的实时响应状态。会议状态数据存储在Exchange服务器或本地Outlook客户端中，直接通过Outlook COM或EWS获取才是正确路径。

内容的提问来源于stack exchange，提问作者Akash A