如何在MVC中用原生SQL实现FullCalendar的CRUD？解决仅显示当日问题

嘿，我来帮你搞定这个问题！在MVC里不用JSON，纯靠原生SQL给FullCalendar实现完整CRUD完全可行，咱们一步步来拆解，解决你现在只能显示日期的问题：

首先得有个存储日历事件的表，比如叫Events，字段建议这样设：

• EventId (INT, 主键自增)
• Title (VARCHAR(255), 事件标题，必填)
• StartDate (DATETIME, 开始时间，必填)
• EndDate (DATETIME, 结束时间，可选)
• Description (TEXT, 事件描述，可选)

先创建一个CalendarEvent模型类，用来在Controller和View之间传递数据：

public class CalendarEvent
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Start { get; set; } // 存ISO格式字符串，方便FullCalendar识别
    public string End { get; set; }
    public string Description { get; set; }
}

2.1 Read：加载日历和事件（解决你当前只能显示日期的问题） #

这个Action负责从数据库查所有事件，然后传到View渲染：

public ActionResult Calendar()
{
    var eventsList = new List<CalendarEvent>();
    // 原生SQL查询语句
    var query = "SELECT EventId, Title, StartDate, EndDate, Description FROM Events";

    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDbConn"].ConnectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(query, conn))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    eventsList.Add(new CalendarEvent
                    {
                        Id = reader.GetInt32(0),
                        Title = reader.GetString(1),
                        // 转成FullCalendar需要的ISO格式
                        Start = reader.GetDateTime(2).ToString("yyyy-MM-ddTHH:mm:ss"),
                        End = reader.IsDBNull(3) ? null : reader.GetDateTime(3).ToString("yyyy-MM-ddTHH:mm:ss"),
                        Description = reader.IsDBNull(4) ? "" : reader.GetString(4)
                    });
                }
            }
        }
    }

    return View(eventsList);
}

关键点：把查询到的DateTime转成yyyy-MM-ddTHH:mm:ss格式，这是FullCalendar能直接识别的日期格式。

2.2 Add：新增事件 #

用参数化SQL插入，绝对不能拼接字符串（防注入！）：

[HttpPost]
public ActionResult AddEvent(CalendarEvent newEvent)
{
    var query = @"INSERT INTO Events (Title, StartDate, EndDate, Description)
                  VALUES (@Title, @StartDate, @EndDate, @Description)";

    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDbConn"].ConnectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@Title", newEvent.Title);
            cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(newEvent.Start));
            // 处理可选的结束时间，为空就传DBNull
            cmd.Parameters.AddWithValue("@EndDate", string.IsNullOrEmpty(newEvent.End) ? (object)DBNull.Value : DateTime.Parse(newEvent.End));
            cmd.Parameters.AddWithValue("@Description", newEvent.Description ?? "");

            cmd.ExecuteNonQuery();
        }
    }

    // 新增后跳回日历页面刷新
    return RedirectToAction("Calendar");
}

2.3 Edit：修改事件 #

同样用参数化SQL更新：

[HttpPost]
public ActionResult EditEvent(CalendarEvent updatedEvent)
{
    var query = @"UPDATE Events 
                  SET Title = @Title, StartDate = @StartDate, EndDate = @EndDate, Description = @Description
                  WHERE EventId = @EventId";

    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDbConn"].ConnectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@EventId", updatedEvent.Id);
            cmd.Parameters.AddWithValue("@Title", updatedEvent.Title);
            cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(updatedEvent.Start));
            cmd.Parameters.AddWithValue("@EndDate", string.IsNullOrEmpty(updatedEvent.End) ? (object)DBNull.Value : DateTime.Parse(updatedEvent.End));
            cmd.Parameters.AddWithValue("@Description", updatedEvent.Description ?? "");

            cmd.ExecuteNonQuery();
        }
    }

    return RedirectToAction("Calendar");
}

2.4 Delete：删除事件 #

[HttpPost]
public ActionResult DeleteEvent(int eventId)
{
    var query = "DELETE FROM Events WHERE EventId = @EventId";

    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDbConn"].ConnectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@EventId", eventId);
            cmd.ExecuteNonQuery();
        }
    }

    return RedirectToAction("Calendar");
}

首先确保引入FullCalendar的CSS和JS（用CDN或者本地文件都行），然后写View：

@model List<YourProjectNamespace.Models.CalendarEvent>

<!-- 日历容器 -->
<div id="calendar"></div>

<!-- 新增事件模态框 -->
<div id="addModal" style="display:none; position:fixed; top:0; left:0; width:100%; height:100%; background:rgba(0,0,0,0.5); z-index:999;">
    <div style="background:white; margin:10% auto; padding:2rem; width:320px; border-radius:8px;">
        <h3>添加新事件</h3>
        @using (Html.BeginForm("AddEvent", "YourControllerName", FormMethod.Post))
        {
            <div class="mb-2">
                <label>标题:</label>
                <input type="text" name="Title" class="form-control" required />
            </div>
            <div class="mb-2">
                <label>开始时间:</label>
                <input type="datetime-local" name="Start" id="addStart" class="form-control" required />
            </div>
            <div class="mb-2">
                <label>结束时间:</label>
                <input type="datetime-local" name="End" id="addEnd" class="form-control" />
            </div>
            <div class="mb-2">
                <label>描述:</label>
                <textarea name="Description" class="form-control"></textarea>
            </div>
            <div class="d-flex justify-content-between">
                <button type="submit" class="btn btn-primary">保存</button>
                <button type="button" class="btn btn-secondary" onclick="document.getElementById('addModal').style.display='none'">取消</button>
            </div>
        }
    </div>
</div>

<!-- 编辑/删除事件模态框 -->
<div id="editModal" style="display:none; position:fixed; top:0; left:0; width:100%; height:100%; background:rgba(0,0,0,0.5); z-index:999;">
    <div style="background:white; margin:10% auto; padding:2rem; width:320px; border-radius:8px;">
        <h3>编辑事件</h3>
        @using (Html.BeginForm("EditEvent", "YourControllerName", FormMethod.Post))
        {
            <input type="hidden" name="Id" id="editEventId" />
            <div class="mb-2">
                <label>标题:</label>
                <input type="text" name="Title" id="editTitle" class="form-control" required />
            </div>
            <div class="mb-2">
                <label>开始时间:</label>
                <input type="datetime-local" name="Start" id="editStart" class="form-control" required />
            </div>
            <div class="mb-2">
                <label>结束时间:</label>
                <input type="datetime-local" name="End" id="editEnd" class="form-control" />
            </div>
            <div class="mb-2">
                <label>描述:</label>
                <textarea name="Description" id="editDescription" class="form-control"></textarea>
            </div>
            <div class="d-flex justify-content-between mb-2">
                <button type="submit" class="btn btn-primary">保存修改</button>
                <button type="button" class="btn btn-secondary" onclick="document.getElementById('editModal').style.display='none'">取消</button>
            </div>
        }
        @using (Html.BeginForm("DeleteEvent", "YourControllerName", FormMethod.Post))
        {
            <input type="hidden" name="eventId" id="deleteEventId" />
            <button type="submit" class="btn btn-danger w-100" onclick="document.getElementById('deleteEventId').value = document.getElementById('editEventId').value;">删除事件</button>
        }
    </div>
</div>

<script>
    document.addEventListener('DOMContentLoaded', function() {
        const calendarEl = document.getElementById('calendar');
        // 把Controller传过来的模型转成JS数组
        const events = @Html.Raw(Json.Encode(Model));

        const calendar = new FullCalendar.Calendar(calendarEl, {
            initialView: 'dayGridMonth',
            events: events, // 加载事件数据，解决你只能显示日期的问题
            editable: true, // 允许拖拽修改事件时间
            selectable: true, // 允许选择日期区域添加事件
            headerToolbar: {
                left: 'prev,next today',
                center: 'title',
                right: 'dayGridMonth,timeGridWeek,timeGridDay'
            },
            // 点击事件弹出编辑框
            eventClick: function(info) {
                document.getElementById('editEventId').value = info.event.id;
                document.getElementById('editTitle').value = info.event.title;
                document.getElementById('editStart').value = info.event.start.toISOString().slice(0, 16);
                if (info.event.end) {
                    document.getElementById('editEnd').value = info.event.end.toISOString().slice(0, 16);
                } else {
                    document.getElementById('editEnd').value = '';
                }
                document.getElementById('editDescription').value = info.event.extendedProps.description;
                document.getElementById('editModal').style.display = 'block';
            },
            // 选择日期弹出添加框
            select: function(info) {
                document.getElementById('addStart').value = info.startStr.slice(0, 16);
                document.getElementById('addEnd').value = info.endStr.slice(0, 16);
                document.getElementById('addModal').style.display = 'block';
                // 取消默认的选择高亮
                calendar.unselect();
            },
            // 拖拽修改时间后自动提交更新
            eventDrop: function(info) {
                const form = document.createElement('form');
                form.method = 'POST';
                form.action = '@Url.Action("EditEvent", "YourControllerName")';

                // 构造表单字段
                const fields = [
                    {name: 'Id', value: info.event.id},
                    {name: 'Title', value: info.event.title},
                    {name: 'Start', value: info.event.start.toISOString().slice(0, 16)},
                    {name: 'End', value: info.event.end ? info.event.end.toISOString().slice(0, 16) : ''},
                    {name: 'Description', value: info.event.extendedProps.description}
                ];

                fields.forEach(field => {
                    const input = document.createElement('input');
                    input.type = 'hidden';
                    input.name = field.name;
                    input.value = field.value;
                    form.appendChild(input);
                });

                document.body.appendChild(form);
                form.submit();
            }
        });

        calendar.render();
    });
</script>

1. 参数化SQL是底线：绝对不能直接把用户输入拼到SQL语句里，不然会有严重的SQL注入风险。
2. 日期格式要匹配：FullCalendar只认ISO8601格式的日期字符串，所以Controller里一定要转对格式。
3. 替换占位符：把代码里的YourDbConn、YourProjectNamespace、YourControllerName换成你自己项目里的实际值。
4. 样式可以优化：我给模态框加了点基础样式，你可以用Bootstrap或者自己的CSS调整得更美观。

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