在MVC中将EntityFramework代码转换为原生SQL查询

嗨，我来帮你把这段Entity Framework的CRUD代码转成原生SQL的实现~ 先理清楚原代码的核心逻辑：根据EventID的值判断是更新已有事件还是新增新事件，完成操作后返回执行状态。下面是对应的原生SQL实现方案：

一、对应的原生SQL语句 #

新增事件的SQL #

INSERT INTO Events (Subject, Start, [End], Description, IsFullDay, ThemeColor)
VALUES (@Subject, @Start, @End, @Description, @IsFullDay, @ThemeColor)

注意：End是SQL的关键字，所以需要用方括号[]包裹起来避免语法错误

更新事件的SQL #

UPDATE Events
SET Subject = @Subject,
    Start = @Start,
    [End] = @End,
    Description = @Description,
    IsFullDay = @IsFullDay,
    ThemeColor = @ThemeColor
WHERE EventID = @EventID

二、替换后的C#实现代码 #

我们用ADO.NET的SqlConnection和SqlCommand来执行原生SQL，同时使用参数化查询防止SQL注入：

[HttpPost]
public JsonResult SaveEvent(Event e)
{
    var status = false;
    // 替换成你的数据库连接字符串
    string connectionString = "Your_Database_Connection_String";

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand cmd;

        if (e.EventID > 0)
        {
            // 更新事件
            cmd = new SqlCommand(@"
                UPDATE Events
                SET Subject = @Subject,
                    Start = @Start,
                    [End] = @End,
                    Description = @Description,
                    IsFullDay = @IsFullDay,
                    ThemeColor = @ThemeColor
                WHERE EventID = @EventID", conn);
            cmd.Parameters.AddWithValue("@EventID", e.EventID);
        }
        else
        {
            // 新增事件
            cmd = new SqlCommand(@"
                INSERT INTO Events (Subject, Start, [End], Description, IsFullDay, ThemeColor)
                VALUES (@Subject, @Start, @End, @Description, @IsFullDay, @ThemeColor)", conn);
        }

        // 添加公共参数
        cmd.Parameters.AddWithValue("@Subject", e.Subject);
        cmd.Parameters.AddWithValue("@Start", e.Start);
        cmd.Parameters.AddWithValue("@End", e.End);
        cmd.Parameters.AddWithValue("@Description", e.Description ?? DBNull.Value); // 处理可能的空值
        cmd.Parameters.AddWithValue("@IsFullDay", e.IsFullDay);
        cmd.Parameters.AddWithValue("@ThemeColor", e.ThemeColor);

        // 执行SQL并判断是否成功
        int rowsAffected = cmd.ExecuteNonQuery();
        status = rowsAffected > 0;
    }

    return new JsonResult { Data = new { status = status } };
}

几点说明 #

• 记得把Your_Database_Connection_String替换成你实际的数据库连接字符串
• 用DBNull.Value处理可能为null的字段（比如Description），避免数据库报错
• 参数化查询能有效防止SQL注入，比拼接字符串的方式更安全
• ExecuteNonQuery()返回受影响的行数，通过判断行数是否大于0来确定操作是否成功

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