将值列表和表的参数传递给存储过程 [英] passing parameter of list of values and tables to stored procedure

问题描述

我要实现一个 C# 应用程序，该应用程序需要使用接收 ID 或参数值的现有存储过程.我负责的任务分两步:

I'm in the point to implement an C# application that needs to consume already existing stored procedure that receive IDs or values in params. My task in charge is in two steps:

1- 迁移存储过程以接收 IDs(int) 列表和当前参数列表，就像一个表

1- migrate stored procedure in order to receive list of IDs(int) and list of current params, means like a table

2- 实现调用这个程序的层，将接收 List 和 KeyValuePair 或 KeyValuePair

2- implement the layer that cal this procedures and will receive List and KeyValuePair or KeyValuePair

执行此操作的最佳方法是什么?

What should be the best approach to do this ?

EntityFramework 来包装 SP 还是根本不包装 ORM?如何在 SP 端实现 List 和 KeyValuePair 参数?使用表值参数?

EntityFramework to wrap SPs or not ORM at alla? How to implement List and KeyValuePair params ob SP side ? with Table-Valued params ?

我使用 SQL 2012

I'm with SQL 2012

谢谢，

推荐答案

在 sql 端尝试用户定义的表类型功能，并在存储过程中将表作为参数传递.

Try in sql side User defined table type functionality and pass table as parameter in stored procedure.

例如:

CREATE TABLE Test
(
Id int NOT NULL IDENTITY (1, 1),
TestName varchar(50) NOT NULL,
Value int NULL
) ON [PRIMARY]

-- Create a table data type

CREATE TYPE [dbo].[TestType] As Table
(
--This type has structure similar to the DB table 
TestName varchar(50) NOT NULL,
Value int NULL
)

--This is the Stored Procedure

CREATE PROCEDURE [dbo].[TestProcedure]
(
    @Test As [dbo].[TestType] Readonly
)
AS
Begin
Insert Into Test(TestName,Value)
Select TestName, Value From @Test
End

传递数据的C#代码如下:

C# code passing the data as follows:

DataTable dataTable = new DataTable("SampleDataType");

// We create column names as per the type in DB 

dataTable.Columns.Add("TestName", typeof(string)); 
dataTable.Columns.Add("Value", typeof(Int32)); 

// And fill in some values 

dataTable.Rows.Add("Metal", 99); 
dataTable.Rows.Add("HG", null);
...
SqlParameter parameter = new SqlParameter(); 

// The parameter for the SP must be of SqlDbType.Structured 

parameter.ParameterName="@Test"; 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dataTable; 

command.Parameters.Add(parameter); 

这篇关于将值列表和表的参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！