用DbCommand來產生暫時的Store Procedure

2009-11-23

Store Procedure的效能比較好,這點就不再討論,但有時不是想用就能用,這時可以用DbCommand的Prepare功能,來產生一個暫時的SP,藉此來提升效能.

這個例子是使用MS SQL 2005資料庫,而同一段語法,只傳不同的值去異動資料,看看它有什麼不同.

先來看看平時一般的寫法,與Profiler所抓到的Script.

程式 :


SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=SSPI;");
SqlCommand cmd = conn.CreateCommand();
try
{
	cmd.CommandText = "update customers set companyname=1 where customerid=@id";
	cmd.Parameters.Add("@id", SqlDbType.NChar, 5);
	conn.Open();
	for (int i = 0; i < 3; i++)
	{
		cmd.Parameters["@id"].Value = i;
		cmd.ExecuteNonQuery();//每次均傳完整的Script及Value
	}
}
catch (Exception ex)
{
	MessageBox.Show(ex.Message);
}
finally
{
	cmd.Dispose();
	conn.Dispose();
}

可以看到,跑了三次迴圈去異動DB的資料,在Profiler裡所看到的是三個一樣的完整Script.

使用SqlCommand.Prepare()來產生暫存的Store Procedure


SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=SSPI;");
SqlCommand cmd = conn.CreateCommand();
try
{
	cmd.CommandText = "update customers set companyname=1 where customerid=@id";
	cmd.Parameters.Add("@id", SqlDbType.NChar,5);
	conn.Open();
	cmd.Prepare();//產生暫存Store Procedure,但必需連往DB動作時,才會真正產生,此時並不會
	for (int i = 0; i < 3; i++)
	{
		cmd.Parameters["@id"].Value = i;
		cmd.ExecuteNonQuery();//只有第一次會產生SP,之後的異動,都只會傳參數,使用暫存的SP,所以Script就不會再傳.
	}
}
catch (Exception ex)
{
	MessageBox.Show(ex.Message);
}
finally
{
	cmd.Dispose();
	conn.Dispose();
}