SqlCommand中參數與SqlParameter的關聯
使用ADO.NET新增資料至資料庫時，為了避免SQL Injection必須使用SqlParameter來作為SQL Command的參數，有時候我們在INSERT動作前會先使用DELETE將資料清除，因此會寫類似下列的程式碼：
cmd.CommandText = "DELETE FROM Members WHERE ConferenceDate = @ConferenceDate"
Dim ConferenceDate As New SqlParameter With {.ParameterName = "@ConferenceDate", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = ConferenceData.Rows(CbConferenceDate.SelectedIndex)(0)}
Dim SerialNo As New SqlParameter With {.ParameterName = "@SerialNo", .SqlDbType = SqlDbType.Char, .Size = 8}
Dim BrokerId As New SqlParameter With {.ParameterName = "@BrokerId", .SqlDbType = SqlDbType.Char, .Size = 4}
Dim BrokerName As New SqlParameter With {.ParameterName = "@BrokerName", .SqlDbType = SqlDbType.NVarChar, .Size = 50}
Dim MemberName As New SqlParameter With {.ParameterName = "@MemberName", .SqlDbType = SqlDbType.NVarChar, .Size = 5}
cmd.Parameters.AddRange(New SqlParameter() {ConferenceDate, SerialNo, BrokerId, BrokerName, MemberName})
cmd.ExecuteNonQuery()
cmd.CommandText = "INSERT INTO Members(ConferenceDate,SerialNo,BrokerId,BrokerName,MemberName" & _
",UpdateDate) VALUES (@ConferenceDate,@SerialNo,@BrokerId,@BrokerName,@MemberName" & _
",GETDATE())"
For i As Integer = 0 To Members.Rows.Count - 1
SerialNo.Value = Members.Rows(i)("MemberNo")
BrokerId.Value = Members.Rows(i)("BrokerId")
BrokerName.Value = Members.Rows(i)("BrokerName")
MemberName.Value = Members.Rows(i)("MemberName")
cmd.ExecuteNonQuery()
Next
若一次就把INSERT和DELETE所需要的SqlParameter準備好，在執行到第一個ExecuteNoQuery時，便會遇到【參數化查詢 '(@ConferenceDate char(8),@SerialNo char(8),@BrokerId char(4),@Br' 必須有參數 '@SerialNo'，但未提供。】的錯誤。
透過SQL Server Profiler得到下列的SQL命令：
exec sp_executesql N'DELETE FROM Members WHERE ConferenceDate = @ConferenceDate',N'@ConferenceDate char(8),@SerialNo char(8),@BrokerId char(4),@BrokerName nvarchar(50),@MemberName nvarchar(5)',@ConferenceDate='20110217',@SerialNo=default,@BrokerId=default,@BrokerName=default,@MemberName=default
經過實驗得到【若在執行SqlCommand時，應該只宣告CommandText所用到的參數】的結論，所以程式改成下列的程式碼片段就可以順利執行了。
cmd.CommandText = "DELETE FROM Members WHERE ConferenceDate = @ConferenceDate"
Dim ConferenceDate As New SqlParameter With {.ParameterName = "@ConferenceDate", .SqlDbType = SqlDbType.Char, .Size = 8, .Value = ConferenceData.Rows(CbConferenceDate.SelectedIndex)(0)}
cmd.Parameters.Add(ConferenceDate)
cmd.ExecuteNonQuery()
Dim SerialNo As New SqlParameter With {.ParameterName = "@SerialNo", .SqlDbType = SqlDbType.Char, .Size = 8}
Dim BrokerId As New SqlParameter With {.ParameterName = "@BrokerId", .SqlDbType = SqlDbType.Char, .Size = 4}
Dim BrokerName As New SqlParameter With {.ParameterName = "@BrokerName", .SqlDbType = SqlDbType.NVarChar, .Size = 50}
Dim MemberName As New SqlParameter With {.ParameterName = "@MemberName", .SqlDbType = SqlDbType.NVarChar, .Size = 5}
cmd.Parameters.AddRange(New SqlParameter() {SerialNo, BrokerId, BrokerName, MemberName})
cmd.CommandText = "INSERT INTO Members(ConferenceDate,SerialNo,BrokerId,BrokerName,MemberName" & _
",UpdateDate) VALUES (@ConferenceDate,@SerialNo,@BrokerId,@BrokerName,@MemberName" & _
",GETDATE())"
For i As Integer = 0 To Members.Rows.Count - 1
SerialNo.Value = Members.Rows(i)("MemberNo")
BrokerId.Value = Members.Rows(i)("BrokerId")
BrokerName.Value = Members.Rows(i)("BrokerName")
MemberName.Value = Members.Rows(i)("MemberName")
cmd.ExecuteNonQuery()
Next
trans.Commit()
