如何将列表值传递给存储过程插入查询 [英] How to pass list value to a stored procedure insert query

问题描述

public void InsertDataToDb()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["connection"].
            ConnectionString;
        var records = GetRecords();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            SqlCommand cmd =
                new SqlCommand(
                    "INSERT INTO TableName (param1, param2, param3) " +
                    " VALUES (@param1, @param2, @param3)");
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            cmd.Parameters.Add("@param1", DbType.String);
            cmd.Parameters.Add("@param2", DbType.String);
            cmd.Parameters.Add("@param3", DbType.String);

            foreach (var item in records)
            {
                cmd.Parameters[0].Value = item.param1;
                cmd.Parameters[1].Value = item.param2;
                cmd.Parameters[2].Value = item.param3;

                cmd.ExecuteNonQuery();
            }

            conn.Close();
        }
    }

我想在存储过程中执行此操作。如何将列表作为参数传递给SP。任何人都建议



我尝试了什么：



无需转换列表到xml作为参数。需要直接将列表传递给SP。

I want to do this in stored procedure . how to pass list to SP as parameter. anyone pls suggest

What I have tried:

With out converting list to xml as parameter. Need to directly pass list to a SP.

推荐答案

我个人喜欢表类型参数，主要是因为它易于使用并且能够定义精确的结构。 />


例如，看看如何将多个记录传递给存储过程 [ ^ ]

Personally I like table typed parameters, mostly because of the ease of usage and being able to define exact structure.

For an example, have a look at How to pass multiple records to a Stored Procedure[^]

将列表传递给SQL的唯一方法服务器是XML，如果你有2016年的JSON，或者作为CSV，最后一个选项是噩梦。您无法传入List< t>，SQL Server没有这个概念。您的XML或JSON转换为表，表是SQL Server知道的

The only ways to pass a list through to SQL Server are as XML, JSON if you have 2016, or as a CSV, the last option being a nightmare. You can't pass in a List<t>, SQL Server has no concept of that. Your XML or JSON gets converted to a table, tables are what SQL Server knows about

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