如何将数组作为参数传递到存储过程中?(复制)

This question already has an answer here:

这个问题已经有了答案:

• Turning a Comma Separated string into individual rows 11 answers
• 将逗号分隔的字符串转换为单个行11的答案。
• Adding multiple parameterized variables to a database in c# 2 answers
• 在c# 2中向数据库添加多个参数化变量

I am trying to send a string from C# into SQL server but I have no idea how to insert that string into another table using a stored procedure.

我正在尝试从c#向SQL server发送一个字符串，但是我不知道如何使用存储过程将该字符串插入到另一个表中。

The table where I want the data to be inserted only has 2 columns, @IDUser (a parameter that I am also passing from C# and that will be the same for all idproduct) and the @IDProduct string that will be something like "1,2,3,4".

我希望插入数据的表只有2个列，@IDUser(我也从c#中传递一个参数，对于所有的idproduct都是相同的)和@IDProduct字符串，类似于“1、2、3、4”。

So what I want is a stored procedure to insert the @idUser and the array values individually like

我需要一个存储过程来插入@idUser和数组值

IDUSER | IDPRODUCT
777        1
777        2
777        3
777        4

I am using sql server 2016.

我正在使用sql server 2016。

I have found possible answers for this issue but they look very complex compared to my level of SQL Server knowledge.

我已经为这个问题找到了可能的答案，但是与我的SQL Server知识水平相比，它们看起来非常复杂。

thanks

谢谢

1 个解决方案

#1

I think you can try these two ways:

我想你可以试试这两种方法:

1. Without a stored procedure: You can try with SqlBulkCopy class.
2. 没有存储过程:可以尝试使用SqlBulkCopy类。

C# Code:

c#代码:

static void Main(string[] args)
    {
        Console.WriteLine("Inserting ...");

        var userId = 777;
        var productIds = new List<int> { 1, 2, 3, 4 };
        var dto = new Dictionary<int, List<int>>
        {
            { userId, productIds }
        };

        ExecuteBulkInsert(dto);
        // ExecuteProcedure(dto);

        Console.WriteLine("Done! ...");
        Console.ReadLine();
    }

    public static void ExecuteBulkInsert( Dictionary<int, List<int>> dto)
    {
        string connectionString = GetConnectionString();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            DataTable newProducts = CreateDataTable(dto);

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "dbo.UserProducts";
                bulkCopy.WriteToServer(newProducts);
            }
        }
    }

    private static DataTable CreateDataTable(Dictionary<int, List<int>> dto)
    {
        const string IdUserColumnName = "IdUser";
        const string IdProductColumnName = "IdProduct";

        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn(IdUserColumnName, typeof(int)));
        table.Columns.Add(new DataColumn(IdProductColumnName, typeof(int)));

        foreach (var product in dto)
        {
            foreach (var productId in product.Value)
                table.Rows.Add(product.Key, productId);
        }

        return table;
    }

2. With a stored procedure: Try with a table-valued parameter
3. 使用存储过程:尝试使用表值参数

SQL Code:

SQL代码:

CREATE TABLE dbo.UserProducts
(
    IdUser INT NOT NULL,
    IdProduct INT NOT NULL
);
GO

CREATE TYPE dbo.UserProductsType AS TABLE
(
    IdUser INT NOT NULL,
    IdUser INT NOT NULL
);
GO

CREATE PROCEDURE dbo.UserProductsInsert
    @userProductsType dbo.UserProductsType READONLY
AS
BEGIN

    INSERT INTO UserProducts
    SELECT * FROM @userProductsType 
END

C# Code:

c#代码:

    private static void ExecuteProcedure( Dictionary<int, List<int>> dto)
    {
        string connectionString = GetConnectionString();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "dbo.UserProductsInsert";
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter parameter = command.Parameters.AddWithValue("@userProductsType", CreateDataTable(dto));
                parameter.SqlDbType = SqlDbType.Structured;
                parameter.TypeName = "dbo.UserProductsType";

                command.ExecuteNonQuery();
            }
        }
    }