如何在存储过程中使用表值参数并在MVC C#中调用及解决EF报错

没问题，我来帮你把这些问题一一理顺：

你创建的表值类型是正确的，下面给你补充完整的CRUD存储过程示例，适配这个表值类型：

插入（你已写的版本可以保留，这里再确认下） #

CREATE TYPE Employeetable AS TABLE (
    FirstName varchar(50),
    LastName varchar(50),
    States varchar(50),
    City varchar(50),
    AddressLine1 varchar(100),
    AddressLine2 varchar(100)
)
GO

CREATE PROCEDURE InsertEmployees
    @employeetable Employeetable READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Testing.Employees (FirstName, LastName, States, City, AddressLine1, AddressLine2)
    SELECT FirstName, LastName, States, City, AddressLine1, AddressLine2 FROM @employeetable;
END
GO

查询（用表值类型筛选员工） #

CREATE PROCEDURE GetEmployeesByLocations
    @employeetable Employeetable READONLY -- 传入要筛选的州/城市信息
AS
BEGIN
    SET NOCOUNT ON;
    SELECT e.*
    FROM Testing.Employees e
    JOIN @employeetable et ON e.States = et.States AND e.City = et.City;
END
GO

更新（注意：如果要更新，表值类型需要包含员工ID，这里给你调整下类型示例） #

首先修改表值类型（如果需要更新）：

ALTER TYPE Employeetable ADD EmployeeId INT;
GO

CREATE PROCEDURE UpdateEmployees
    @employeetable Employeetable READONLY
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE e
    SET e.FirstName = et.FirstName,
        e.LastName = et.LastName,
        e.States = et.States,
        e.City = et.City,
        e.AddressLine1 = et.AddressLine1,
        e.AddressLine2 = et.AddressLine2
    FROM Testing.Employees e
    JOIN @employeetable et ON e.EmployeeId = et.EmployeeId;
END
GO

删除（同样依赖员工ID） #

CREATE PROCEDURE DeleteEmployees
    @employeetable Employeetable READONLY
AS
BEGIN
    SET NOCOUNT ON;
    DELETE e
    FROM Testing.Employees e
    JOIN @employeetable et ON e.EmployeeId = et.EmployeeId;
END
GO

你遇到的Error 6005是因为旧版本的Entity Framework（比如EF 6之前或某些特定版本）不支持自动生成包含表值参数的存储过程模型。解决这个问题的最优方案是直接用ADO.NET原生方式调用存储过程，绕开EF的模型生成限制。

首先，你需要在项目中创建一个对应SQL表值类型的C#类：

public class EmployeeTableType
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string States { get; set; }
    public string City { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    // 如果要做更新/删除，需要添加EmployeeId字段：
    // public int EmployeeId { get; set; }
}

然后在控制器中编写HttpPost方法，用ADO.NET调用存储过程：

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;

// ... 控制器类代码 ...

[HttpPost]
public ActionResult AddEmployees(List<EmployeeTableType> employees)
{
    // 从配置文件获取数据库连接字符串
    string connectionString = ConfigurationManager.ConnectionStrings["YourDbConnectionName"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("InsertEmployees", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            // 将C#列表转换为DataTable，适配SQL表值参数
            DataTable employeeTable = ConvertEmployeeListToDataTable(employees);

            // 添加表值参数
            SqlParameter tableParam = cmd.Parameters.AddWithValue("@employeetable", employeeTable);
            tableParam.SqlDbType = SqlDbType.Structured;
            tableParam.TypeName = "Employeetable"; // 必须和SQL中创建的表值类型名称完全一致

            // 执行存储过程
            int rowsInserted = cmd.ExecuteNonQuery();
            
            // 这里可以根据rowsInserted做日志或提示信息
        }
    }

    return RedirectToAction("EmployeeList"); // 跳转到员工列表页
}

// 辅助方法：将EmployeeTableType列表转换为DataTable
private DataTable ConvertEmployeeListToDataTable(List<EmployeeTableType> employees)
{
    DataTable dt = new DataTable();
    // 按SQL表值类型的字段顺序添加列
    dt.Columns.Add("FirstName", typeof(string));
    dt.Columns.Add("LastName", typeof(string));
    dt.Columns.Add("States", typeof(string));
    dt.Columns.Add("City", typeof(string));
    dt.Columns.Add("AddressLine1", typeof(string));
    dt.Columns.Add("AddressLine2", typeof(string));
    // 如果有EmployeeId，添加对应的列：
    // dt.Columns.Add("EmployeeId", typeof(int));

    foreach (var emp in employees)
    {
        dt.Rows.Add(emp.FirstName, emp.LastName, emp.States, emp.City, emp.AddressLine1, emp.AddressLine2);
        // 如果有EmployeeId：
        // dt.Rows.Add(emp.FirstName, emp.LastName, emp.States, emp.City, emp.AddressLine1, emp.AddressLine2, emp.EmployeeId);
    }

    return dt;
}

补充说明 #

• 如果你的项目用的是EF Core 2.0及以上版本，可以尝试使用EF的表值参数支持，但需要手动配置，不过对于简单的CRUD场景，ADO.NET的方式更直接且兼容性更好。
• 确保连接字符串在Web.config（或appsettings.json，如果是.NET Core）中正确配置。

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