作者在 2010-04-11 11:10:31 发布以下内容
参数化查询语句和parameter属性
在VB.NET中连接数据库时，SQL查询语句中（Select、Updata、Delete、Insert）可以使用参数来代替指定值，然后要在Command对象的parameters属性中给定参数值，或者在DataAdapter中的 SelectCommand 等一类属性中设置parameters，并给定参数值。
DbDataAdapter 具有四个用于从数据源检索数据和更新数据源中数据的属性：SelectCommand 属性返回数据源中的数据；InsertCommand、UpdateCommand 和 DeleteCommand 属性用于管理数据源中的更改。 在调用 DataAdapter 的 Fill 方法之前必须设置 SelectCommand 属性。 在调用 DataAdapter 的 Update 方法之前必须设置 InsertCommand、UpdateCommand 或 DeleteCommand 属性，具体取决于对 DataTable 中的数据做了哪些更改。 例如，如果已添加行，则在调用 Update 之前必须设置InsertCommand。 当 Update 正在处理已插入、已更新或已删除的行时，DataAdapter 将使用相应的 Command 属性来处理该操作。有关已修改行的当前信息将通过 Parameters 集合传递到 Command 象。
注意：参数占位符的语法取决于数据源。 此示例显示 SQL Server 数据源的占位符。 使用问号 (?) 占位符代表 System.Data.OleDb 和 System.Data.Odbc 参数。
使用 SqlClient 参数
下面的示例演示如何创建 SqlDataAdapter 并将 MissingSchemaAction 设置为 AddWithKey，以便从数据库中检索其他架构信息。 SelectCommand、InsertCommand、UpdateCommand 和 DeleteCommand 属性集及其相应的 SqlParameter 对象已添加到 Parameters 集合。 该方法返回一个 SqlDataAdapter 对象。
Public Function CreateSqlDataAdapter( _
ByVal connection As SqlConnection) As SqlDataAdapter
Dim adapter As SqlDataAdapter = New SqlDataAdapter
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' Create the commands.
adapter.SelectCommand = New SqlCommand( _
"SELECT CustomerID, CompanyName FROM CUSTOMERS", connection)
adapter.InsertCommand = New SqlCommand( _
"INSERT INTO Customers (CustomerID, CompanyName) " & _
"VALUES (@CustomerID, @CompanyName)", connection)
adapter.UpdateCommand = New SqlCommand( _
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = " & _
"@CompanyName WHERE CustomerID = @oldCustomerID", connection)
adapter.DeleteCommand = New SqlCommand( _
"DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)
' Create the parameters.
adapter.InsertCommand.Parameters.Add("@CustomerID", _
SqlDbType.Char, 5, "CustomerID")
adapter.InsertCommand.Parameters.Add("@CompanyName", _
SqlDbType.VarChar, 40, "CompanyName")
adapter.UpdateCommand.Parameters.Add("@CustomerID", _
SqlDbType.Char, 5, "CustomerID")
adapter.UpdateCommand.Parameters.Add("@CompanyName", _
SqlDbType.VarChar, 40, "CompanyName")
adapter.UpdateCommand.Parameters.Add("@oldCustomerID", _
SqlDbType.Char, 5, "CustomerID").SourceVersion = _
DataRowVersion.Original
adapter.DeleteCommand.Parameters.Add("@CustomerID", _
SqlDbType.Char, 5, "CustomerID").SourceVersion = _
DataRowVersion.Original
Return adapter
End Function
ByVal connection As SqlConnection) As SqlDataAdapter
Dim adapter As SqlDataAdapter = New SqlDataAdapter
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' Create the commands.
adapter.SelectCommand = New SqlCommand( _
"SELECT CustomerID, CompanyName FROM CUSTOMERS", connection)
adapter.InsertCommand = New SqlCommand( _
"INSERT INTO Customers (CustomerID, CompanyName) " & _
"VALUES (@CustomerID, @CompanyName)", connection)
adapter.UpdateCommand = New SqlCommand( _
"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = " & _
"@CompanyName WHERE CustomerID = @oldCustomerID", connection)
adapter.DeleteCommand = New SqlCommand( _
"DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)
' Create the parameters.
adapter.InsertCommand.Parameters.Add("@CustomerID", _
SqlDbType.Char, 5, "CustomerID")
adapter.InsertCommand.Parameters.Add("@CompanyName", _
SqlDbType.VarChar, 40, "CompanyName")
adapter.UpdateCommand.Parameters.Add("@CustomerID", _
SqlDbType.Char, 5, "CustomerID")
adapter.UpdateCommand.Parameters.Add("@CompanyName", _
SqlDbType.VarChar, 40, "CompanyName")
adapter.UpdateCommand.Parameters.Add("@oldCustomerID", _
SqlDbType.Char, 5, "CustomerID").SourceVersion = _
DataRowVersion.Original
adapter.DeleteCommand.Parameters.Add("@CustomerID", _
SqlDbType.Char, 5, "CustomerID").SourceVersion = _
DataRowVersion.Original
Return adapter
End Function
OleDb 参数占位符
对于 OleDbDataAdapter 对象和 OdbcDataAdapter 对象，必须使用问号 (?) 占位符来标识参数。参数化查询语句定义必须创建的输入和输出参数。 若要创建参数，请使用 Parameters.Add 方法或 Parameter 构造函数来指定列名称、数据类型和大小。 对于内部数据类型（如 Integer），无需包含大小，也可以指定默认大小。
Dim selectSQL As String = _
"SELECT CustomerID, CompanyName FROM Customers " & _
"WHERE CountryRegion = ? AND City = ?"
Dim insertSQL AS String = _
"INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"
Dim updateSQL AS String = _
"UPDATE Customers SET CustomerID = ?, CompanyName = ? " & _
WHERE CustomerID = ?"
Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
"SELECT CustomerID, CompanyName FROM Customers " & _
"WHERE CountryRegion = ? AND City = ?"
Dim insertSQL AS String = _
"INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"
Dim updateSQL AS String = _
"UPDATE Customers SET CustomerID = ?, CompanyName = ? " & _
WHERE CustomerID = ?"
Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
'下面的代码示例创建 SQL 语句的参数，然后填充 DataSet。
' Assumes that connection is a valid OdbcConnection object.
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter
Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD
' Add Parameters and set values.
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"
Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter
Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD
' Add Parameters and set values.
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"
Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")
注意：如果未为参数提供参数名称，则该参数将使用从“Parameter1”开始递增的默认名称 ParameterN。 建议在提供参数名称时避免使用 ParameterN 命名约定，因为所提供的名称可能会与 ParameterCollection 中现有的默认参数名称发生冲突。 如果提供的名称已经存在，将引发异常。
--VS2008MSDN文档