Search

Create Provider-Agnostic Data Parameters

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()