i created a dll file using vb.net then it has a shared function that will execute a stored procedure of mysql but my code is something long and when accessing it, it has a big risk that it can produce an error if there is an incorrect string input, i am asking an advice how i can make it much better or can point me something much better than my code as my reference.
here is my code

first i created a function named mysqlParamDatatype wiith 1 parameter, this function will detect the mysqldatatype of the mysql sproc parameter so it can pass the value with the correct datatype. but it is to long.

 Shared Function mysqlParamDataType(ByVal i As Integer) As MySqlDbType
        If i = 1 Then
            Return MySqlDbType.Binary
        ElseIf i = 2 Then
            Return MySqlDbType.Bit
        ElseIf i = 3 Then
            Return MySqlDbType.Blob
        ElseIf i = 4 Then
            Return MySqlDbType.Byte
        ElseIf i = 5 Then
            Return MySqlDbType.Date
        ElseIf i = 6 Then
            Return MySqlDbType.DateTime
        ElseIf i = 7 Then
            Return MySqlDbType.Decimal
        ElseIf i = 8 Then
            Return MySqlDbType.Double
        ElseIf i = 9 Then
            Return MySqlDbType.Enum
        ElseIf i - 10 Then
            Return MySqlDbType.Float
        ElseIf i = 11 Then
            Return MySqlDbType.Geometry
        ElseIf i = 12 Then
            Return MySqlDbType.Guid
        ElseIf i = 13 Then
            Return MySqlDbType.Int16
        ElseIf i = 14 Then
            Return MySqlDbType.Int24
        ElseIf i = 15 Then
            Return MySqlDbType.Int32
        ElseIf i = 16 Then
            Return MySqlDbType.Int64
        ElseIf i = 17 Then
            Return MySqlDbType.LongBlob
        ElseIf i = 18 Then
            Return MySqlDbType.LongText
        ElseIf i = 19 Then
            Return MySqlDbType.MediumBlob
        ElseIf i = 20 Then
            Return MySqlDbType.MediumText
        ElseIf i = 21 Then
            Return MySqlDbType.Newdate
        ElseIf i = 22 Then
            Return MySqlDbType.NewDecimal
        ElseIf i = 23 Then
            Return MySqlDbType.Set
        ElseIf i = 24 Then
            Return MySqlDbType.String
        ElseIf i = 25 Then
            Return MySqlDbType.Text
        ElseIf i = 26 Then
            Return MySqlDbType.Time
        ElseIf i = 27 Then
            Return MySqlDbType.Timestamp
        ElseIf i = 28 Then
            Return MySqlDbType.TinyBlob
        ElseIf i = 29 Then
            Return MySqlDbType.TinyText
        ElseIf i = 30 Then
            Return MySqlDbType.UByte
        ElseIf i = 31 Then
            Return MySqlDbType.UInt16
        ElseIf i = 32 Then
            Return MySqlDbType.UInt24
        ElseIf i = 33 Then
            Return MySqlDbType.UInt32
        ElseIf i = 34 Then
            Return MySqlDbType.UInt64
        ElseIf i = 35 Then
            Return MySqlDbType.VarBinary
        ElseIf i = 36 Then
            Return MySqlDbType.VarChar
        ElseIf i = 37 Then
            Return MySqlDbType.VarString
            Return MySqlDbType.Year
        End If
    End Function

then after that i have also this function to execute the stored procedure

this is the main function that will be call to the program.

it has 3 parameters first the connection string then the storeprocedure name that will be call to the mysql and last is the string where we can find the parameters of the sproc the value of the data and the datatype. the format of the string is something like this "paramname|7|valueid|13|100n..|n..|n.." the string will be splited by pipe " | " this will include the 3 the paramname, datatype and value then if the stored procedure has more than 1 parameters it will just splited by asterisk " * ".

Shared Function sqlQuerySproc(ByVal Connection As String, ByVal Sproc As String, ByVal Param As String) As Boolean
        Const splt1 = "*", splt2 As String = "|" ' declaration of the splitter
        Dim spltd1() As String = Param.Split(splt1) ' if the sproc has more than 1 param then it will split it
        Dim spltd2() As String
        Dim spltd1Count As Integer = spltd1.Length ' counting the splitted string
        Dim i As Integer = 0
        Dim ii As Integer

        Using cn As New MySqlConnection(Connection)
            Using cmd As New MySqlCommand(Sproc, cn) With {.CommandType = CommandType.StoredProcedure}
                With cmd
                    With .Parameters
                        For i = 0 To spltd1Count - 1 Step 1 ' looping until all the parameters will be satisfied
                            spltd2 = spltd1(i).Split(splt2) ' splitting the splitted string to 3 values 
                            ii = CInt(spltd2(1))
                            .Add(spltd2(0), mysqlParamDataType(ii)).Value = spltd2(2) ' splitted string will be pass to the parameters
                    End With
                    If .ExecuteNonQuery Then ' executing the sql query
                        Return True
                        Return False
                    End If
                End With
            End Using
        End Using
    End Function

then the functions can be accessed like this

Private Sub SimpleButton1_Click(sender As System.Object, e As System.EventArgs) Handles SimpleButton1.Click
        Dim value As String = String.Format("registration|7|{0}*referal1|7|{1}*pairing1|7|{2}*covered|13|{3}",
                                            txt_registrationFee.Text, txt_referalCommision.Text, txt_pairingCommission.Text, txt_daysCovered.Text) ' at this line of code is has a great risk of getting errors
        If sqlQuerySproc(_connectionString, "insertPricing", value) Then ' this line of code also has a big risk of getting errors
            MessageBox.Show("Saving New Pricing Settings Successful.")
            MessageBox.Show("An Internal Error Found, Please Contact the Server Administrator. ")
        End If
    End Sub

now i want this code simplier and risk free functions

I will appreciate for any help, Thank You

i am sorry for may bad english

4 Years
Discussion Span
Last Post by thines01

Why don't you put those parameter types in an array, List or Dictionary so you can have a more efficient search.
You could also reorder them to look for the most common data type first.

The structure containing the parameter data types would be at the module level (instead of in a function).

Edited by thines01

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.