Create provider-agnostic data parameters (usually used in calling stored procedures) is quite cumbersome, unless you use a provider-agnostic data layer framework (i.e. MS Enterprise Library Data Access Application Block). Each provider uses it's own data parameters naming conventions. For instance System.Data.SqlClient requires data parameters names to be prefixed by '@' (single quotes are excluded).
For more details regarding data parameters naming conventions used in stored procedures, refer to this MSDN article.
Creating a data parameter can be accomplished either explicitly, by creating a new instane of a specific parameter class (i.e. System.Data.SqlClient.SqlParameter), or implicitly through a data command object.
Indirect data parameter creation is shown below:
|
Public Shared Function CreateDataParameter(ByVal ParameterName As String, _
ByVal ParameterValue As Object, _
ByVal ParameterType As System.Data.DbType, _
ByVal DefineParameterType As Boolean, _
ByVal AddParameter As Boolean, _
ByVal Command As System.Data.IDbCommand) As System.Data.IDbDataParameter
Dim Parameter As System.Data.IDbDataParameter
Parameter = Command.CreateParameter()
Parameter.ParameterName = ParameterName
Parameter.Value = ParameterValue
If DefineParameterType Then
Parameter.DbType = ParameterType
End If
If AddParameter Then
Command.Parameters.Add(Parameter)
End If
Return Parameter
End Function |
The above function returns the newly created parameter, by assigning all the necessary values (parameter name, value and type) and adding the data parameter to its command creator parameters collection. Not all these function parameters are required, but it gives you full control over the creation and assignment of new data parameters.
You can you use several function overloads, to simplify data parameter creation:
|
Public Shared Function CreateDataParameter(ByVal ParameterName As String, _
ByVal ParameterValue As Object, _
ByVal Command As System.Data.IDbCommand) As System.Data.IDbDataParameter
Return CreateDataParameter(ParameterName, ParameterValue, Nothing, False, False, _
Command)
End Function
Public Shared Function CreateDataParameter(ByVal ParameterName As String, _
ByVal ParameterValue As Object, _
ByVal AddParameter As Boolean, _
ByVal Command As System.Data.IDbCommand) As System.Data.IDbDataParameter
Return CreateDataParameter(ParameterName, ParameterValue, Nothing, False, _
AddParameter, Command)
End Function |
Have in mind that when no data parameter type is defined, it's the underlined data provider's responsibility to discover the correct System.Data.DbType. Not all data providers support data parameter type discovery.
Specific data parameters names conventions
Creating the proper data parameter isn't always suffice, the parameter name should follow certain naming conventions (i.e. prefixing parameter name). Some providers don't use parameter names at all, all parameters show up in SQL statements as '?' (question marks), it is required though, that data parameters are added to data command's parameters collection in the exact same order, as defined in SQL statement.
The below function handles these peculiarities:
|
Public Shared Function GetProviderSpecificParameterLiteral(ByVal ParameterName As String, _
ByVal Connection As System.Data.IDbConnection) As String
Dim Literal As String = ""
If TypeOf Connection Is System.Data.SqlClient.SqlConnection Then
Literal = "@" & ParameterName
ElseIf TypeOf Connection Is System.Data.OleDb.OleDbConnection Then
Literal = "?"
ElseIf TypeOf Connection Is System.Data.Odbc.OdbcConnection Then
Literal = "?"
End If
Return Literal
End Function |
The above implementation doesn't cover all available data providers, but it can be extended very easily.
To complete this walkthrough, the below snippet demonstrates how to combine the above function calls:
|
Dim DBConn As System.Data.IDbConnection
Dim DBCommand As System.Data.IDbCommand
…
DBCommand = DBConn.CreateCommand
DBCommand.Connection = DBConn
DBCommand.CommandText = "update Table set Field = " & _
GetProviderSpecificParameterLiteral("Field", DBConn) & _
" where ID = " & _
GetProviderSpecificParameterLiteral("ID", DBConn)
DBParameter = CreateDataParameter(GetProviderSpecificParameterLiteral("Field", DBConn), _
FieldValue, DbType.String, True, True, DBCommand)
DBParameter = CreateDataParameter(GetProviderSpecificParameterLiteral("ID", DBConn), _
IDValue, DbType.Int32, True, True, DBCommand)
RecordsAffected = DBCommand.ExecuteNonQuery() |