通过实体框架将表值类型传递给 SQL Server 存储过程 [英] Pass table value type to SQL Server stored procedure via Entity Framework

问题描述

我在 SQL Server 中创建了一个用户定义的表类型:

I created a user-defined table type in SQL Server:

CREATE TYPE dbo.TestType AS TABLE 
(
    ColumnA int,
    ColumnB nvarchar(500)
)

我正在使用存储过程将记录插入到数据库中:

And I'm using a stored procedure to insert records into the database:

create procedure [dbo].[sp_Test_CustomType]
   @testing TestType READONLY
as
    insert into [dbo].[myTable]
        select ColumnA, ColumnB 
        from @testing

我想用EF来执行这个存储过程，但问题来了:如何将用户定义的表传递给存储过程?

And I would like to use EF to execute this stored procedure, but here's the problem: how can I pass a user defined table to the stored procedure?

我尝试将存储过程添加到模型中，但我无法在更新的上下文中找到所需的存储过程.

I tried adding the stored procedure to the model, but I'm unable to find the desired stored procedure in the updated context.

我想要做的是对表执行批量插入，这是我目前使用的方法:

What I'm trying to do is to execute a bulk insert to a table, here's the method that I'm currently using:

List<items> itemToInsertToDB = //fetchItems;

foreach(items i in itemToInsertToDB)
{
     context.sp_InsertToTable(i.ColumnA, i.ColumnB)
}

目前，我使用 foreach 循环遍历列表以将项目插入数据库，但是如果列表中有很多项目，那么就会出现性能问题，所以，我'我正在考虑将列表传递给存储过程并在其中进行插入.

Currently, I use a foreach loop to loop through the list to insert item to DB, but if the list have a lot of items, then there will be a performance issue, so, I'm thinking of passing a list to the stored procedure and do the insert inside.

那么如何解决这个问题呢?或者有没有更好的方法来做到这一点?

So how to solve this problem? or are there any better ways to do this?

推荐答案

假设您想发送一个包含单列 GUID 的表格.

Lets say you want to send a table with a single column of GUIDs.

首先我们需要使用 SqlMetaData 表示表(列)的模式.

First we need to create a structure using SqlMetaData which represents the schema of the table (columns).

下面的代码演示了一个名为Id"的列GUID的就是SQL存储过程参数表类型

The below code demonstrates one column named "Id" of the GUID is the SQL stored procedure parameter table type

var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

接下来，您使用 SqlDataRecord.

下面的代码演示了如何使用上面创建的架构在列表中添加项目.为列表中的每个项目创建一个新的 SqlDataRecord.将 SetGuid 替换为相应的类型，并将 Guid.NewGuid() 替换为相应的值.对每个项目重复新的 SqlDataRecord 并将它们添加到 List

The below code demonstrates how to add the items inside a list using the above created schema. Create a new SqlDataRecord for each of the items in the list. Replace SetGuid with the corresponding type and Replace Guid.NewGuid() as the corresponding value. Repeat new SqlDataRecord for each item and add them to a List

var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

然后创建SqlParameter:

var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds"; //@UserIds is the stored procedure parameter name
parameter.TypeName = "{Your stored procedure type name}"
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

然后只需使用 Database.SqlQuery.

IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers @UserIds", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

在 SQL Server 中，创建您的用户定义表类型(我将它们后缀为 TTV，表类型值):

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):

CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

然后将类型指定为参数(不要忘记，表类型值必须是只读的！):

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):

CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

这篇关于通过实体框架将表值类型传递给 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！