C # mssql sql concatenation and multiple inserts

Question

I recently came across a situation where I was shown code similar to the following:

string insertSql = "";
foreach (int animalType in new[] { 1,2,3 })
    insertSql += String.Format("INSERT INTO `animalType` (type) VALUES ({0}); ", animalType);

The number of inserts each time is small (1-3).

It feels “bad,” but I can't say why. Any thoughts?

Basically, many sql inserts are combined in one line. Afaik there are 3 reasons not to add values ​​to sql by concatenating strings: security, formatting problems, execution speed. Since parameters are ints, there can be no sql injection. No formatting issues. In addition, since these are inserts, the sql server will not reuse the descriptor (afaik it stores and, possibly, reuses query handles).

In addition, there are no triggers in the table.

Typically, this can be improved by performing a single insertion using join INSERT INTO animalType (type) (SELECT 1 UNION SELECT 2 UNION SELECT 3).

I know that you can use a data table added to a parameterized query or xml, but can it just make the code longer and harder to read? I simply have no real arguments against this.

And is there any point in dynamically creating a parameterized query this way:

string insertSql = "";
SqlCommand command = new SqlCommand();
var types = new[] { 1, 2, 3 };
for (int i = 0; i < types.Length; i++)
{
    insertSql += String.Format("INSERT INTO `animalType` (type) VALUES (@typeParam{0}); ", i);
    command.Parameters.AddWithValue("typeParam" + i, types[i]);
}

What can I say to a person who binds integer sql values ​​and doesn't use parameterized queries because his method is simpler? :)

Answer 1

, . , . SQL Server , ( ...).

:

• . .
• . UNION ALL TVP . .
• , . , .

, - , .

Answer 2

, , , :

string insertSql = "INSERT INTO animalType (type) VALUES (@type)";
var types = new[] { 1, 2, 3 };
using(SqlCommand command = new SqlCommand(insertSql, conn))
{
    foreach(var type in types)
    {
        command.Parameters.AddWithValue("@type", type);
        command.ExecuteNonQuery();
    }

}

, sql , ?

, , SQL- . , , , , , , , , .