如何在ASP.NET MVC的DbContext与控制器中使用通用CRUD存储过程

我在开发ASP.NET MVC应用时，想用一个能和任意表交互的通用CRUD存储过程，现在卡在了DbContext和控制器里怎么调用它的环节。下面是我已有的存储过程代码和Student模型类，求指导调用方法。

现有存储过程代码 #

CREATE PROCEDURE CRUDOperation
    @OperationType NVARCHAR(10), -- 'CREATE', 'READ', 'UPDATE', 'DELETE'
    @TableName NVARCHAR(50),
    @PrimaryKeyColumn NVARCHAR(50),
    @PrimaryKeyValue INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    IF @OperationType = 'CREATE'
    BEGIN
        SET @sql = 'INSERT INTO ' + QUOTENAME(@TableName) + ' VALUES (/* Add values here */)';
    END

    IF @OperationType = 'READ'
    BEGIN
        SET @sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' = ' + CAST(@PrimaryKeyValue AS NVARCHAR);
    END

    IF @OperationType = 'UPDATE'
    BEGIN
        SET @sql = 'UPDATE ' + QUOTENAME(@TableName) + ' SET /* Update columns here */ WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' = ' + CAST(@PrimaryKeyValue AS NVARCHAR);
    END

    IF @OperationType = 'DELETE'
    BEGIN
        SET @sql = 'DELETE FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' = ' + CAST(@PrimaryKeyValue AS NVARCHAR);
    END

    EXEC sp_executesql @sql;
END  

Student模型类代码 #

using System.ComponentModel.DataAnnotations;

namespace WebApplication13.Models
{
    public class Student
    {
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        [Phone]
        public string Contact { get; set; }
    }
}

调用方法指导 #

1. 先修改存储过程支持动态列值 #

原存储过程的CREATE和UPDATE部分无法接收动态列值，需要先调整：

ALTER PROCEDURE CRUDOperation
    @OperationType NVARCHAR(10), -- 'CREATE', 'READ', 'UPDATE', 'DELETE'
    @TableName NVARCHAR(50),
    @PrimaryKeyColumn NVARCHAR(50),
    @PrimaryKeyValue INT,
    @ColumnValues NVARCHAR(MAX) = NULL -- 新增参数，用于传递CREATE/UPDATE的列值（如"Name='张三', Contact='13800138000'"）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    IF @OperationType = 'CREATE'
    BEGIN
        SET @sql = 'INSERT INTO ' + QUOTENAME(@TableName) + ' VALUES (' + @ColumnValues + ')';
    END

    IF @OperationType = 'READ'
    BEGIN
        SET @sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' = ' + CAST(@PrimaryKeyValue AS NVARCHAR);
    END

    IF @OperationType = 'UPDATE'
    BEGIN
        SET @sql = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + @ColumnValues + ' WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' = ' + CAST(@PrimaryKeyValue AS NVARCHAR);
    END

    IF @OperationType = 'DELETE'
    BEGIN
        SET @sql = 'DELETE FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@PrimaryKeyColumn) + ' = ' + CAST(@PrimaryKeyValue AS NVARCHAR);
    END

    EXEC sp_executesql @sql;
END  

2. 在DbContext中封装调用逻辑 #

在你的DbContext类里添加通用调用方法，统一处理存储过程的参数传递：

using Microsoft.EntityFrameworkCore;
using WebApplication13.Models;

namespace WebApplication13.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }

        public DbSet<Student> Students { get; set; }

        // 通用CRUD存储过程调用方法
        public async Task<IEnumerable<T>> ExecuteCRUD<T>(string operationType, string tableName, string primaryKeyColumn, int primaryKeyValue, string? columnValues = null)
        {
            var parameters = new[]
            {
                new Microsoft.Data.SqlClient.SqlParameter("@OperationType", operationType),
                new Microsoft.Data.SqlClient.SqlParameter("@TableName", tableName),
                new Microsoft.Data.SqlClient.SqlParameter("@PrimaryKeyColumn", primaryKeyColumn),
                new Microsoft.Data.SqlClient.SqlParameter("@PrimaryKeyValue", primaryKeyValue),
                new Microsoft.Data.SqlClient.SqlParameter("@ColumnValues", columnValues ?? DBNull.Value)
            };

            return await this.Set<T>().FromSqlRaw($"EXEC CRUDOperation @OperationType, @TableName, @PrimaryKeyColumn, @PrimaryKeyValue, @ColumnValues", parameters).ToListAsync();
        }
    }
}

3. 控制器中的调用示例 #

以StudentController为例，实现各CRUD操作的调用：

using Microsoft.AspNetCore.Mvc;
using WebApplication13.Data;
using WebApplication13.Models;

namespace WebApplication13.Controllers
{
    public class StudentController : Controller
    {
        private readonly ApplicationDbContext _context;

        public StudentController(ApplicationDbContext context)
        {
            _context = context;
        }

        // 读取单个学生
        public async Task<IActionResult> Details(int id)
        {
            var student = await _context.ExecuteCRUD<Student>("READ", "Students", "Id", id);
            return View(student.FirstOrDefault());
        }

        // 创建学生
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create(Student student)
        {
            if (ModelState.IsValid)
            {
                // 构造列值字符串（可封装成通用方法简化操作）
                string columnValues = $"'{student.Name}', '{student.Contact}'";
                await _context.ExecuteCRUD<Student>("CREATE", "Students", "Id", 0, columnValues);
                return RedirectToAction(nameof(Index));
            }
            return View(student);
        }

        // 更新学生
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Update(Student student)
        {
            if (ModelState.IsValid)
            {
                string columnValues = $"Name='{student.Name}', Contact='{student.Contact}'";
                await _context.ExecuteCRUD<Student>("UPDATE", "Students", "Id", student.Id, columnValues);
                return RedirectToAction(nameof(Index));
            }
            return View(student);
        }

        // 删除学生
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            await _context.ExecuteCRUD<Student>("DELETE", "Students", "Id", id);
            return RedirectToAction(nameof(Index));
        }
    }
}

关键注意事项 #

• SQL注入风险：虽然用了QUOTENAME处理表名和列名，但动态拼接列值仍有注入风险，建议将列值也改为参数化传递，或封装更安全的动态参数处理逻辑。
• 类型安全：通用存储过程会牺牲部分类型安全，调用时需确保表名、主键列名与模型类的映射关系完全匹配。
• 自增主键处理：如果表主键是自增类型，CREATE语句需移除主键列，对应的参数传递也要同步调整。

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