參數化查詢與拼接SQL代碼執行測試

環境:xp(sp3),vs2010,sql2005,
代碼：

View Code

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
代碼類似上面
結果：使用同一個串連執行多個命令語句效能要比每次開啟關閉速度快一個數量級，原因是沒串連池存在