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
Else
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
Next
End With
cn.Open()
If .ExecuteNonQuery Then ' executing the sql query
Return True
Else
Return False
End If
cn.Close()
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.")
Else
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