如何在C#/ SQL Server中跟踪重复的日历事件？

CREATE TABLE CalendarEvents (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(255) NOT NULL,
    Description NVARCHAR(MAX),
    StartDateTime DATETIME NOT NULL,
    EndDateTime DATETIME NOT NULL,
    RecurrenceType NVARCHAR(50),
    RecurrenceInterval INT,
    Participants NVARCHAR(MAX)
);

using System;
using System.Data.SqlClient;

public class CalendarEvent
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public DateTime StartDateTime { get; set; }
    public DateTime EndDateTime { get; set; }
    public string RecurrenceType { get; set; }
    public int RecurrenceInterval { get; set; }
    public string Participants { get; set; }

    public void Save()
    {
        using (var connection = new SqlConnection("ConnectionString"))
        {
            connection.Open();

            var command = new SqlCommand("INSERT INTO CalendarEvents (Title, Description, StartDateTime, EndDateTime, RecurrenceType, RecurrenceInterval, Participants) VALUES (@Title, @Description, @StartDateTime, @EndDateTime, @RecurrenceType, @RecurrenceInterval, @Participants)", connection);
            command.Parameters.AddWithValue("@Title", Title);
            command.Parameters.AddWithValue("@Description", Description);
            command.Parameters.AddWithValue("@StartDateTime", StartDateTime);
            command.Parameters.AddWithValue("@EndDateTime", EndDateTime);
            command.Parameters.AddWithValue("@RecurrenceType", RecurrenceType);
            command.Parameters.AddWithValue("@RecurrenceInterval", RecurrenceInterval);
            command.Parameters.AddWithValue("@Participants", Participants);

            command.ExecuteNonQuery();
        }
    }

    // 添加修改和删除方法
}

private readonly Timer _timer;

public CalendarEventChecker()

{

    _timer = new Timer(60000); // 每分钟检查一次

    _timer.Elapsed += Timer_Elapsed;

    _timer.Start();

}

private void Timer_Elapsed(object sender, ElapsedEventArgs e)

{

    using (var connection = new SqlConnection("ConnectionString"))

    {

        connection.Open();

        var command = new SqlCommand("SELECT * FROM CalendarEvents WHERE RecurrenceType IS NOT NULL AND StartDateTime >= GETDATE()", connection);

        var reader = command.ExecuteReader();

        while (reader.Read())

        {

            var calendarEvent = new CalendarEvent

            {

                Id = reader.GetInt32(0),

                Title = reader.GetString(1),

                Description = reader.GetString(2),

                StartDateTime = reader.GetDateTime(3),

                EndDateTime = reader.GetDateTime(4),

                RecurrenceType = reader.GetString(5),

                RecurrenceInterval = reader.GetInt32(6),

                Participants = reader.GetString(7)

            };

            // 处理重复事件

            HandleRecurringEvent(calendarEvent);

        }

        reader.Close();

    }

}

private void HandleRecurringEvent(CalendarEvent calendarEvent)

{

    // 根据RecurrenceType和RecurrenceInterval计算下一个事件的开始时间

    var nextStartDateTime = CalculateNextStartDateTime(calendarEvent.StartDateTime, calendarEvent.RecurrenceType, calendarEvent.RecurrenceInterval);

    // 更新事件的开始和结束时间

    calendarEvent.StartDateTime = nextStartDateTime;

    calendarEvent.EndDateTime = nextStartDateTime.Add(calendarEvent.EndDateTime.Subtract(calendarEvent.StartDateTime));

    // 保存更新后的事件

    calendarEvent.Save();

    // 发送提醒邮件给参与者

    SendEmailToParticipants(calendarEvent);

}

private DateTime CalculateNextStartDateTime(DateTime startDateTime, string recurrenceType, int recurrenceInterval)

{

    // 根据RecurrenceType和RecurrenceInterval计算下一个事件的开始时