参数化查询与拼接SQL代码执行测试

环境:xp(sp3),vs2010,sql2005,
代码：

public partial class Form1 : Form
    {
        private string ConnString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        public Form1()
        {

            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            Stopwatch sw = new Stopwatch();
            sw.Start();
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();
                string cmdText = @"Insert Into ScanStatus(LblSn,Status,Msg,AddTime)
                               Values(@LblSn,@Status,@Msg,@AddTime)";
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                cmd.Parameters.AddRange(new SqlParameter[]{new SqlParameter("@LblSn", SqlDbType.NVarChar,20),
                                      new SqlParameter("@Status", SqlDbType.NVarChar,20),
                                      new SqlParameter("@Msg", SqlDbType.NVarChar,100),
                                      new SqlParameter("@AddTime", SqlDbType.DateTime,8)});
                cmd.Prepare();
                for (int i = 0; i < 500; i++)
                {
                    cmd.Parameters["@LblSn"].Value = "123" + i;
                    cmd.Parameters["@Status"].Value = "";
                    cmd.Parameters["@Msg"].Value = string.Empty;
                    cmd.Parameters["@AddTime"].Value = DateTime.Now;

                    cmd.ExecuteNonQuery();
                }
            }
            sw.Stop();
            label1.Text = sw.ElapsedMilliseconds.ToString();
            MessageBox.Show("OK!");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();
                string cmdText = @"Insert Into ScanStatus(LblSn,Status,Msg,AddTime)
                               Values(@LblSn,@Status,@Msg,@AddTime)";
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                cmd.Parameters.AddRange(new SqlParameter[]{new SqlParameter("@LblSn", SqlDbType.NVarChar),
                                      new SqlParameter("@Status", SqlDbType.NVarChar),
                                      new SqlParameter("@Msg", SqlDbType.NVarChar),
                                      new SqlParameter("@AddTime", SqlDbType.DateTime)});

                for (int i = 0; i < 500; i++)
                {
                    cmd.Parameters["@LblSn"].Value = "123" + i;
                    cmd.Parameters["@Status"].Value = "";
                    cmd.Parameters["@Msg"].Value = string.Empty;
                    cmd.Parameters["@AddTime"].Value = DateTime.Now;

                    cmd.ExecuteNonQuery();
                }
            }
            sw.Stop();
            label2.Text = sw.ElapsedMilliseconds.ToString();
            MessageBox.Show("OK!");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (int i = 0; i < 500; i++)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    conn.Open();
                    string cmdText = @"Insert Into ScanStatus(LblSn,Status,Msg,AddTime)
                               Values('{0}','{1}','{2}','{3}')";
                    cmdText=string.Format(cmdText,"123" + i,"","",DateTime.Now);
                    SqlCommand cmd = new SqlCommand(cmdText, conn);
    

                    cmd.ExecuteNonQuery();

                }
            }
            sw.Stop();
            label3.Text = sw.ElapsedMilliseconds.ToString();
            MessageBox.Show("OK!");
        }
    }

结论:3种写法没多少区别Prepare快个1%左右，基本可以忽略,默认情况下ado.net使用连接池

测试二,wince,vs2005, sql server compact3.1
代码类似上面
结果：使用同一个连接执行多个命令语句性能要比每次打开关闭速度快一个数量级，原因是没连接池存在