cmd.Parameters的Add方法是增加一个参数,增加多个参数的的时候使用一个foreach循环,cmd.Parameters的AddRange方法是增加一个参数的数组。
Add:将指定的对象添加到……中
AddRange:向……末尾,添加数组
在群体操作时,使用AddRange取代Add
用AddRange可以让我们要加入的东西一次性加入,而不要每次只加一个,这样显然可以加快速度。几乎所有的windows control都支持Add和AddRange两种方法。
Add:
For i = 0 To param.Length - 1
comm.Parameters.Add(param(i))
Next i
AddRange:
Comm.Parameters.AddRange(Param)
支持Add和AddRange的类:
StringCollection, TraceCollection, SqlParameter, etc.
HttpWebRequest
UserControl
ColumnHeader
原代码:
- ''' ExecuteQuery,查询数据操作
- ''' </summary>
- ''' <param name="strSql">sql语句</param>
- ''' <param name="param">参数集合</param>
- ''' <param name="ct">命令类型</param>
- ''' <returns>DataTable类型,数据表</returns>
- ''' <remarks></remarks>
- Public Function ExecuteQuery(ByVal strSql As String, ByVal param() As SqlParameter, ByVal ct As CommandType) As DataTable
- Dim dt As New DataTable
- comm = New SqlCommand(strSql, Me.GetConn)
- comm.CommandType = ct
- If param IsNot Nothing Then
- For i = 0 To param.Length - 1
- comm.Parameters.Add(param(i))
- Next i
- End If
- Try
- dr = comm.ExecuteReader()
- dt.Load(dr)
- Catch ex As Exception
- Throw ex
- Finally
- dr.Close()
- conn.Close()
- End Try
- Return dt
- End Function
''' ExecuteQuery,查询数据操作
''' </summary>
''' <param name="strSql">sql语句</param>
''' <param name="param">参数集合</param>
''' <param name="ct">命令类型</param>
''' <returns>DataTable类型,数据表</returns>
''' <remarks></remarks>
Public Function ExecuteQuery(ByVal strSql As String, ByVal param() As SqlParameter, ByVal ct As CommandType) As DataTable
Dim dt As New DataTable
comm = New SqlCommand(strSql, Me.GetConn)
comm.CommandType = ct
If param IsNot Nothing Then
For i = 0 To param.Length - 1
comm.Parameters.Add(param(i))
Next i
End If
Try
dr = comm.ExecuteReader()
dt.Load(dr)
Catch ex As Exception
Throw ex
Finally
dr.Close()
conn.Close()
End Try
Return dt
End Function
修改后:
- ''' <summary>
- ''' 查询方法
- ''' </summary>
- ''' <param name="myCommandType">sql语句类型:区别于存储过程</param>
- ''' <param name="Param">参数数组</param>
- ''' <param name="strSql">sql语句</param>
- Public Function ExecuteQuery(ByVal myCommandType As CommandType, ByVal Param() As SqlParameter, ByVal strSql As String) As DataTable
- Dim dt As New DataTable
- Comm = New SqlCommand(strSql, Me.GetConn)
- Comm.CommandType = myCommandType
- If Param IsNot Nothing Then
- Comm.Parameters.AddRange(Param)
- End If
- Try
- myDataReader = Comm.ExecuteReader
- dt.Load(myDataReader)
- Catch ex As Exception
- Throw ex
- Finally
- myDataReader.Close()
- Conn.Close()
- End Try
- Return dt
- End Function