How can i pass an array as a parameter into a stored procedure?

Question

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.

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".

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

IDUSER | IDPRODUCT
777        1
777        2
777        3
777        4

I am using sql server 2016.

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

thanks

This should show you how to do it. https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows If you still can't get it let me know I will give u an answer. — DaniDev, Oct 23 '17 at 22:51
If you want to do it via a parameter in sql, look into using `STRING_SPLIT` as you're using 2016. — Simon, Oct 23 '17 at 22:53

Jairo Alfaro · Accepted Answer · 2017-10-26T19:38:07.557

I think you can try these two ways:

Without a stored procedure: You can try with SqlBulkCopy class.

C# Code:

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;
    }

With a stored procedure: Try with a table-valued parameter

SQL Code:

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:

    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();
            }
        }
    }

How can i pass an array as a parameter into a stored procedure?

1 Answers1