Prompt, in C # there is a parameterized where you can list the parameter lists, and then behind the scenes when passing parameters, the parameters will be escaped and all that.

Are there any means that would be able to pull a collection into such a request

INSERT INTO T1 (F1,F2) VALUES (@F1,@F2)

and would be generated: INSERT INTO T1 (F1,F2) VALUES (1,1),(2,2),(3,3)...(n,n) ?

Or is this done only with pens?

    1 answer 1

    You can pull down the inserted records in the form of a Table-Valued Parameter, like this:

    Create a table type for the passed parameter:

     CREATE TYPE dbo.CategoryTableType AS TABLE ( CategoryID int, CategoryName nvarchar(50) )

    And pass a parameter from C # in the form DataTable , IEnumerable<SqlDataRecord> , or DbDataReader :

     string sqlInsert = @"INSERT INTO dbo.Categories (CategoryID, CategoryName) SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc"; SqlCommand insertCommand = new SqlCommand(sqlInsert, connection); SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories); tvpParam.SqlDbType = SqlDbType.Structured; tvpParam.TypeName = "dbo.CategoryTableType";

    A full example is in MSDN: Table-Valued Parameters

    If there is a lot of data (more than 1000 lines), it is better to insert data through a push through bulk insert, by calling SqlBulkCopy.WriteToServer .

    • And without creating a type in any way? Just write a self-made solution? - iluxa1810 2:01 pm
    • @ iluxa1810 without creating a type - SqlBulkCopy.WriteToServer - PashaPash ♦
    • but this solution is not universal = (For example, in MySql there is no such thing and you can only insert it via an intermediary in the form of a CSV file. I thought that there is something universal. - iluxa1810
    • @ iluxa1810 universal - insert one line at a time, separate inserts. because even the approach with "VALUES (1,1), (2,2)" is not universal - for example, it does not work in SQL Server 2005. - PashaPash ♦