新手还是带着昨天没解决的问题来请教下 sql优化问题

Question

求解,昨天在网上找的事务例子,照着做了根本没用啊.求好心人教教吧
 using (SqlTransaction trans = objcon.BeginTransaction())
                    {
                        try
                        {
                            for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++) 
                            {
                                 itmp++;
                                 tmp = "insert into TEimpdatas Values('" + this.label3.Text + "','"
                                    + this.dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" 
                                    + this.dataGridView1.Rows[i].Cells[1].Value.ToString() + "','"
                                    + this.dataGridView1.Rows[i].Cells[2].Value.ToString() + "','"
                                    + this.dataGridView1.Rows[i].Cells[3].Value.ToString() + "','"
                                    + DateTime.Now.ToLocalTime().ToString() + "','')";
                                 if (itmp == 500)
                                 {
                                     itmp = 0;
                                     SqlCommand objcom2 = new SqlCommand(tmp, objcon, trans);
                                     objcom2.ExecuteNonQuery();
                                 }
                                 SqlCommand objcom = new SqlCommand(tmp, objcon, trans); 
                              objcom.ExecuteNonQuery();
                          
                          }
                            trans.Commit();
                            MessageBox.Show("OK!");                        }
                        catch (Exception)
                        {
                            trans.Rollback();
                            //MessageBox.Show(ex.ToString());
                        }
                        finally
                        {
                            objcon.Close();
                        }

Answer 1

从你的代码中可以看出，你的记录只做插入操作，没有更新操作，你可以去网上查查SqlBulkCopy批量入库的相关资料，效率比你这样的要快很多。

Answer 2

求指导优化这sql语句.. 好心人有好报哟

Answer 3

不说其他的 你的临时变量完全没必要 直接i%500就可以了另外既然这个操作全是insert 可以考虑先组装SQL 最后执行（注：避免出现GO 语句分割用";"）事务操作最好是做成环境事务 最后还要考虑分布式事务（一个以上的数据库操作）

Answer 4

第一：你不应该吧 SqlCommand 放到 循环体内。第二：
你直接拼接一个SQL 然后执行用事务执行就可以了。第三：
例如:StringBuiler sb = new StringBuilder();for(int ....................)
{
sb.Append(你的SQL )
}最后你在 BeginTransaction()
cmd.ExecuteNonQuery(); 然后 Commint();catch
{
  RollBack();
}

Answer 5

你的意思这段代码不要咯?
  if (itmp == 500)                                 {                                     itmp = 0;                                     SqlCommand objcom2 = new SqlCommand(tmp, objcon, trans);                                     objcom2.ExecuteNonQuery();                                 }用StringBuiler 保存,一次性提交?