如何使用ASP.NET Core将Outlook邮件数据存储到SQL Server数据库

1. 选择邮件数据获取方式 #

优先使用Microsoft Graph API（替代旧版EWS），它适配云端/本地Outlook，且服务器部署友好。避免使用Office Interop（仅适合桌面客户端，服务器环境有依赖兼容性问题）。

配置Graph API权限 #

• 在Azure AD注册应用，添加Mail.Read应用权限（需管理员同意）。
• 将租户ID、客户端ID、客户端密钥存入ASP.NET Core的appsettings.json：

"GraphApiSettings": {
  "TenantId": "your-tenant-id",
  "ClientId": "your-client-id",
  "ClientSecret": "your-client-secret",
  "ApiEndpoint": "https://graph.microsoft.com/v1.0"
}

2. SQL Server数据库表设计 #

创建两张关联表存储邮件与附件：

Emails表（邮件主表） #

CREATE TABLE Emails (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    SenderId NVARCHAR(255) NOT NULL,
    RecipientIds NVARCHAR(MAX) NOT NULL, -- 多收件人用逗号分隔或JSON格式存储
    Subject NVARCHAR(500),
    Body NVARCHAR(MAX),
    ReceivedDateTime DATETIME2 NOT NULL,
    CreatedDateTime DATETIME2 DEFAULT GETUTCDATE()
)

Attachments表（附件关联表） #

CREATE TABLE Attachments (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    EmailId UNIQUEIDENTIFIER FOREIGN KEY REFERENCES Emails(Id),
    FileName NVARCHAR(255) NOT NULL,
    ContentType NVARCHAR(100),
    Content VARBINARY(MAX) NOT NULL, -- 小附件直接存库，大附件建议存文件系统并记录路径
    Size INT
)

3. ASP.NET Core项目基础配置 #

• 安装必要NuGet包：Microsoft.Graph、Microsoft.EntityFrameworkCore.SqlServer、Azure.Identity
• 在Program.cs注册Graph服务与EF Core上下文：

builder.Services.AddDbContext<EmailDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddSingleton<GraphServiceClient>(sp =>
{
    var settings = builder.Configuration.GetSection("GraphApiSettings").Get<GraphApiSettings>();
    var credential = new ClientSecretCredential(settings.TenantId, settings.ClientId, settings.ClientSecret);
    return new GraphServiceClient(credential);
});

// 配置映射类
public class GraphApiSettings
{
    public string TenantId { get; set; }
    public string ClientId { get; set; }
    public string ClientSecret { get; set; }
    public string ApiEndpoint { get; set; }
}

4. 核心业务逻辑实现 #

4.1 从Outlook拉取邮件数据 #

public async Task<List<EmailDto>> FetchEmailsAsync(GraphServiceClient graphClient)
{
    var emails = new List<EmailDto>();
    // 拉取指定时间范围内的收件箱邮件（可根据需求调整过滤条件）
    var messages = await graphClient.Me.Messages
        .Request()
        .Select(m => new { m.Id, m.Sender, m.ToRecipients, m.Subject, m.Body, m.ReceivedDateTime })
        .Filter("ReceivedDateTime ge 2024-01-01T00:00:00Z")
        .GetAsync();

    foreach (var msg in messages)
    {
        emails.Add(new EmailDto
        {
            SenderId = msg.Sender.EmailAddress.Address,
            RecipientIds = string.Join(",", msg.ToRecipients.Select(r => r.EmailAddress.Address)),
            Subject = msg.Subject,
            Body = msg.Body.Content,
            ReceivedDateTime = msg.ReceivedDateTime.Value.DateTime,
            GraphMessageId = msg.Id // 用于后续拉取对应附件
        });
    }
    return emails;
}

4.2 保存邮件与附件到数据库 #

public async Task SaveEmailsToDbAsync(List<EmailDto> emailDtos, GraphServiceClient graphClient, EmailDbContext dbContext)
{
    foreach (var dto in emailDtos)
    {
        var email = new Email
        {
            SenderId = dto.SenderId,
            RecipientIds = dto.RecipientIds,
            Subject = dto.Subject,
            Body = dto.Body,
            ReceivedDateTime = dto.ReceivedDateTime
        };
        dbContext.Emails.Add(email);
        await dbContext.SaveChangesAsync();

        // 拉取并保存当前邮件的附件
        var attachments = await graphClient.Me.Messages[dto.GraphMessageId].Attachments
            .Request()
            .GetAsync();

        foreach (var att in attachments)
        {
            if (att is FileAttachment fileAtt)
            {
                var attachment = new Attachment
                {
                    EmailId = email.Id,
                    FileName = fileAtt.Name,
                    ContentType = fileAtt.ContentType,
                    Content = fileAtt.ContentBytes,
                    Size = fileAtt.Size
                };
                dbContext.Attachments.Add(attachment);
            }
        }
        await dbContext.SaveChangesAsync();
    }
}

4.3 定义实体与DTO类 #

// 数据库实体类
public class Email
{
    public Guid Id { get; set; }
    public string SenderId { get; set; }
    public string RecipientIds { get; set; }
    public string Subject { get; set; }
    public string Body { get; set; }
    public DateTime ReceivedDateTime { get; set; }
    public DateTime CreatedDateTime { get; set; }
    public ICollection<Attachment> Attachments { get; set; }
}

public class Attachment
{
    public Guid Id { get; set; }
    public Guid EmailId { get; set; }
    public string FileName { get; set; }
    public string ContentType { get; set; }
    public byte[] Content { get; set; }
    public int Size { get; set; }
    public Email Email { get; set; }
}

// 数据传输类
public class EmailDto
{
    public string SenderId { get; set; }
    public string RecipientIds { get; set; }
    public string Subject { get; set; }
    public string Body { get; set; }
    public DateTime ReceivedDateTime { get; set; }
    public string GraphMessageId { get; set; }
}

5. 关键注意事项 #

• 权限验证：确保Azure AD应用的Mail.Read权限已获得管理员同意，避免出现403权限错误。
• 大附件优化：如果附件超过10MB，建议使用Graph API分块下载功能，或改为将附件存储到文件系统（数据库仅记录文件路径），防止数据库性能下降。
• 增量同步：记录每次同步的最后ReceivedDateTime，下次仅拉取新增邮件，避免重复处理。
• 错误处理：引入Polly等重试库，处理Graph API或数据库的临时异常，提升系统稳定性。

内容的提问来源于stack exchange，提问作者swapnil sawant