Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

C# SQL insert command

Can anyone tell me the following 2 ways of inserting record creates better performance?

Case 1

SqlCommand cmd = new SqlCommand();

for (int i = 0; i < 10000; i++)
{
  cmd = new SqlCommand("insert into test(id, name) value('" + i + "', '" + i + "')");
  cmd.ExecuteNonQuery();
}

Case 2

string sql = null;

for (int i = 0; i < 10000; i++)
{
  sql += "insert into test(id, name) value('" + i + "', '" + i + "')";
}

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
like image 859
RoyT Avatar asked Nov 21 '11 21:11

RoyT


2 Answers

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.

like image 125
marc_s Avatar answered Sep 26 '22 05:09

marc_s


The second approach looks faster than #1 because you send the INSERT commands at once. In the first there's a round trip to the SQL server for each ExecuteNonQuery.

But you should try the bulk insert command: BULK INSERT (Transact-SQL), I guess you'll get a better performance than any one of the options you provided.

[]'s

like image 25
Fabio Avatar answered Sep 24 '22 05:09

Fabio