CODEWITHSUNDEEP
mysqlvb.netmysql-connector
Theveloper
Theveloper

Reputation: 806

VB.NET MySqlCommand Stored Procedure Parameter Order?

I create a dictionary in a function which I pass to the function executing the sproc.

    Dim PParamDict As New Dictionary(Of String, String)
    PParamDict.Add("sname", name)
    PParamDict.Add("sdescription", description)
    PParamDict.Add("sLoggedInID", LoggedInID)
    PParamDict.Add("sCompanyID", CompanyID)

The dictionary gets passed to the function PParamDict -> ParameterDict

    Dim dbComm As New MySqlCommand(ProcedureName, PConnection)

    Dim pair As KeyValuePair(Of String, String)
    For Each pair In ParameterDict
        dbComm.Parameters.AddWithValue(pair.Key, pair.Value)
    Next

Parameters are added from the dictionary.

This is a straightforward sproc, nothing special about it, a simple insert.

CREATE PROCEDURE `NewCollection`(
IN `sLoggedInID` INT(5), 
IN `sCompanyID` INT(5), 
IN `sname` VARCHAR(20),  
IN `sdescription` VARCHAR(500))

BEGIN
INSERT INTO `collection`
(`userid`, `companyid`, `name`, `description`, `generated`) 
VALUES 
(sLoggedInID, sCompanyID, sname, sdescription, CURRENT_TIMESTAMP);
END

This works as long as the PParamDict.Add statements are in that order. If they're in a different order they get passed as they come in. This is the most ridiculous thing I've ever seen, I'm passing the damn keys to the MySqlCommand which are defined letter for letter in the sproc. I must be missing something, please help!

Upvotes: 0

Views: 5880

Answers (2)

Dave Becker
Dave Becker

Reputation: 1433

I have a similar problem, however my list of parameter values has come via reflection so I don't build a list manually and have no control of the order.

In Pete's answer, the binding of the parameters will always be in the correct order because it is not a stored procedure, but a text command where the parameter names are used as place holders so it won't matter which order they are added.

To get around the ordering problem I simply explicity call the procedure as text (like Pete's INSERT) rather than use command.CommandType = System.Data.CommandType.StoredProcedure like this:

command.CommandText = "call procname (@param1, @param2, @param3 ... );"

Then I can add my parameters in any order i want

command.Parameters.AddWithValue("@param3", 123)
command.Parameters.AddWithValue("@param2", 456)
command.Parameters.AddWithValue("@param1", 789)

Hope this helps.

EDIT: This method won't work if you have output parameters

Upvotes: 1

pete
pete

Reputation: 25091

Perhaps this will help. A StringDictionary "implements a hash table with the key and the value strongly typed to be strings rather than objects".

A HashTable "represents a collection of key/value pairs that are organized based on the hash code of the key".

As you add pairs to your StringDictionary, it gets reorganized by the hash code of the key string.

If you build a SqlParameterCollection instead of a StringDictionary, your parameters are named and a for each iterator should match the parameters in your sproc nicely.

UPDATE

Adding code example.

Private Function GetParameters(ByVal name As String, ByVal description As String, ByVal LoggedInID As Integer, ByVal CompanyID As Integer) As SqlParameterCollection
    Dim cmd As SqlCommand = New SqlCommand()
    Dim pc As SqlParameterCollection = cmd.Parameters 'SqlParameterCollection constructor is marked as "Friend" so it has to be instantiated this way.'

    pc.AddWithValue("sname", name)
    pc.AddWithValue("sdescription", description)
    pc.AddWithValue("sLoggedInID", LoggedInID)
    pc.AddWithValue("sCompanyID", CompanyID)

    Return pc
End Function

Private Sub ExecuteStoredProcedure(ByVal pc As SqlParameterCollection)
    Dim sp As String = String.Empty
    Dim conn As SqlConnection = Nothing
    Dim cmd As SqlCommand = Nothing
    Dim da As SqlDataAdapter = Nothing
    Dim ds As DataSet = Nothing
    Dim p As SqlParameter = Nothing

    Try
        sp = "INSERT INTO `collection` (`user_id`, `company_id`, `name`, `description`, `generated`) VALUES (`sLoggedInID`, `sCompanyID`, `sname`, `sdescription`, CURRENT_TIMESTAMP)"
        conn = New SqlConnection("your connection string here")
        cmd = New SqlCommand(sp, conn)
        For Each p In pc
            cmd.Parameters.Add(p)
        Next

        da = New SqlDataAdapter(cmd)
        ds = New DataSet
        da.Fill(ds)

    Catch ex As SqlException
        'handle exception'
    Catch ex As Exception
        'handle exception'
    Finally
        If conn IsNot Nothing Then
            conn.Dispose()
        End If
        If cmd IsNot Nothing Then
            cmd.Dispose()
        End If
        If da IsNot Nothing Then
            da.Dispose()
        End If
    End Try
End Sub

Upvotes: 0

Related Questions