SQL語句的使用非常靈活，通過各種SQL語句，可以實現(xiàn)不同功能的操作，下面將為您介紹參數(shù)化SQL語句，供您參考，希望對您有所幫助。
SQL注入的方法有兩種：
一是所有的SQL語句都存放在存儲過程中，這樣不但可以避免SQL注入，還能提高一些性能，并且存儲過程可以由專門的數(shù)據(jù)庫管理員(DBA)編寫和集中管理，不過這種做法有時候針對相同的幾個表有不同條件的查詢，SQL語句可能不同，這樣就會編寫大量的存儲過程，所以有人提出了第二種方案：參數(shù)化SQL語句。例如我們在本篇中創(chuàng)建的表UserInfo中查找所有女性用戶，那么通常情況下我們的SQL語句可能是這樣：
1 |
select * from UserInfo where sex=0 |
在參數(shù)化SQL語句中我們將數(shù)值以參數(shù)化的形式提供，對于上面的查詢，我們用參數(shù)化SQL語句表示為：
1 |
select * from UserInfo where sex=@sex |
再對代碼中對這個SQL語句中的參數(shù)進行賦值，假如我們要查找UserInfo表中所有年齡大于30歲的男性用戶，這個參數(shù)化SQL語句可以這么寫：
1 |
select * from UserInfo where sex=@sex and age>@age |
下面是執(zhí)行這個查詢并且將查詢結果集以DataTable的方式返回的代碼：
02 |
SqlConnection connection = new SqlConnection("server=localhost;database=pubs;uid=sa;pwd=''"); |
04 |
SqlCommand command = new SqlCommand("select * from UserInfo where sex=@sex and age>@age", connection); |
06 |
command.Parameters.AddWithValue("@sex", true); |
08 |
SqlParameter parameter = new SqlParameter("@age", SqlDbType.Int); |
10 |
command.Parameters.Add(parameter); |
12 |
SqlDataAdapter adapter = new SqlDataAdapter(command); |
13 |
DataTable data = new DataTable(); |
#p#
上面的代碼是訪問SQL Server數(shù)據(jù)庫的代碼。如果本文中提到的數(shù)據(jù)分別在Access、MySQL、Oracle數(shù)據(jù)庫，那么對應的參數(shù)化SQL語句及參數(shù)分別如下：
| 數(shù)據(jù)庫 |
Access |
MySQL |
Oracle |
| SQL語句 |
select * from UserInfo
where sex=? and age>? |
select * from UserInfo
where sex=?sex and age>?age |
select * from UserInfo
where sex=:sex and age>:age |
| 參數(shù) |
OleDbParameter |
MySqlParameter |
OracleParameter |
| 實例化參數(shù) |
OleDbParameter p=new OleDbParameter(“?”, OleDbType. Boolean); |
MySqlParameter p=new MySqlParameter(“?sex”, MySqlDbType.Bit); |
OracleParameter p=new OracleParameter(“:sex”, OracleType.Byte); |
| 賦值 |
p.Value=true; |
p.Value=1; |
p.Value=1; |
通過上面的實例代碼我們可以看出盡管SQL語句大體相似，但是在不同數(shù)據(jù)庫的特點，可能參數(shù)化SQL語句不同，例如在Access中參數(shù)化SQL語句是在參數(shù)直接以“?”作為參數(shù)名，在SQL Server中是參數(shù)有“@”前綴，在MySQL中是參數(shù)有“?”前綴，在Oracle中參數(shù)以“:”為前綴。
注意：因為在Access中參數(shù)名都是“?”，所以給參數(shù)賦值一定要按照列順序賦值，否則就有可能執(zhí)行出錯。
Command對象傳參效率測試
在.net平臺，普通的insert語句有兩種寫法，不帶參數(shù)insert into test(c1,c2) values(var1,var2)和帶參數(shù)insert into test(c1,c2) values(:c1,:c2)，它們的執(zhí)行效率如何呢？
做了個試驗，代碼如下：(數(shù)據(jù)庫是oracle)
01 |
public partial class WebForm1 : System.Web.UI.Page |
03 |
protected void Page_Load(object sender, EventArgs e) |
10 |
OracleConnection con = new OracleConnection(); |
11 |
con.ConnectionString = "Data Source=oracl;User Id=xxx;Password=xxx;Persist Security Info=True;"; |
12 |
System.Random r = new Random((int)System.DateTime.Now.Ticks); |
13 |
string strCommand = "insert into test(c1,c2) values({0},{1})"; |
14 |
OracleCommand com = new OracleCommand(); |
17 |
DateTime dt = DateTime.Now; |
18 |
Label1.Text = "不傳參:"+DateTime.Now.ToLongTimeString(); |
19 |
for (int i = 0; i < 50000; i++) |
22 |
com.CommandText = string.Format(strCommand, r.Next(), r.Next()); |
23 |
com.ExecuteNonQuery(); |
25 |
com.CommandText = "truncate table test"; |
26 |
com.ExecuteNonQuery(); |
28 |
Label2.Text = DateTime.Now.ToLongTimeString(); |
32 |
OracleConnection con = new OracleConnection(); |
34 |
con.ConnectionString = "Data Source=bocodb;User Id=hljyd;Password=hljyd;Persist Security Info=True;"; |
35 |
System.Random r = new Random((int)System.DateTime.Now.Ticks); |
36 |
string strCommand = "insert into test(c1,c2) values(:c1,:c2)"; |
37 |
OracleCommand com = new OracleCommand(); |
38 |
com.Parameters.Add(":c1", OracleType.Number); |
39 |
com.Parameters.Add(":c2", OracleType.Number); |
40 |
com.CommandText = strCommand; |
43 |
Label1.Text = "傳參:"+DateTime.Now.ToLongTimeString(); |
44 |
for (int i = 0; i < 50000; i++) |
46 |
com.Parameters[":c1"].Value = r.Next(); |
47 |
com.Parameters[":c2"].Value = r.Next(); |
49 |
com.ExecuteNonQuery(); |
51 |
com.Parameters.Clear(); |
52 |
com.CommandText = "truncate table test"; |
53 |
com.ExecuteNonQuery(); |
55 |
Label2.Text = DateTime.Now.ToLongTimeString(); |
執(zhí)行結果：
10000記錄：
不傳參數(shù)?5:46:19 15:46:34 15秒
傳參數(shù)：??5:50:51 15:51:01 10秒
50000記錄：
不傳參數(shù) 16:09:03 16:10:24 81秒
傳參數(shù)：：16:15:43 16:16:36 53秒
這只是2個參數(shù)的情況，如果參數(shù)很多會不會影響更大呢？
10000記錄，7個參數(shù)：
不傳參數(shù)：17:11:01 17:11:18 17秒
傳參數(shù)：17:13:46 17:13:59 13秒
50000記錄：7個參數(shù)：
不傳參數(shù)：17:19:02 17:20:25 1分23秒
傳參數(shù)：17:15:09 17:16:10 1分1秒
需要相差不大，但是向command對象傳遞參數(shù)既可以避免sql注入問題，也可以提高性能。
【編輯推薦】
SQL中的分頁查詢語句介紹
批處理SQL語句的執(zhí)行效率提高的方法
SQL語句中特殊字符的處理方法
教您用SQL語句進行模糊查詢
為您講解SQL動態(tài)語句的語法
