C#SQL INSERT命令 [英] C# SQL insert command
本文介绍了C#SQL INSERT命令的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
谁能告诉我插入纪录创造了更好的性能以下2种方式?
案例1
CMD的SqlCommand =新的SqlCommand();的for(int i = 0; I< 10000;我++)
{
CMD =新的SqlCommand(插入测试(ID,姓名)值('+ I +,+ I +'));
cmd.ExecuteNonQuery();
}
案例2
SQL字符串= NULL;的for(int i = 0; I< 10000;我++)
{
SQL + =插入测试(ID,姓名)值('+ I +,+ I +');
}CMD的SqlCommand =新的SqlCommand(SQL,conn);在
cmd.ExecuteNonQuery();
解决方案
中首先为 STOP 串联起来您的SQL code!这是一个邀请黑客处处与SQL注入攻击你!使用参数化查询,而不是!
我会用这个解决方案:创建一个单的SqlCommand
与参数化查询,并执行:
字符串语句=INSERT INTO dbo.Test(ID,姓名)VALUES(@ID,@Name);CMD的SqlCommand =新的SqlCommand(SMT,_connection);
cmd.Parameters.Add(@ ID中,SqlDbType.Int);
cmd.Parameters.Add(@名,SqlDbType.VarChar,100);的for(int i = 0; I< 10000;我++)
{
cmd.Parameters [@ ID]值=我。
cmd.Parameters [@名]值= i.ToString()。 cmd.ExecuteNonQuery();
}
或使用 SqlBulkCopy的
,特别是如果你插入甚至超过了10,000行。
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();
解决方案
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.
这篇关于C#SQL INSERT命令的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文