First of all: STOP concatenating together your SQL code!! This is an invitation to hackers everywhere to attack you with SQL injection! Use parametrized queries instead!
I would use this solution: create a single SqlCommand
with a parametrized query, and execute that:
string stmt = "INSERT INTO dbo.Test(id, name) VALUES(@ID, @Name)";
SqlCommand cmd = new SqlCommand(smt, _connection);
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
for (int i = 0; i < 10000; i++)
{
cmd.Parameters["@ID"].Value = i;
cmd.Parameters["@Name"].Value = i.ToString();
cmd.ExecuteNonQuery();
}
or use SqlBulkCopy
, especially if you're inserting even more than 10'000 rows.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…