0
\$\begingroup\$

I work on a project that is dealing with two databases (Mysql Db on Internet + Sqlite Local Db). I will explain what I do with my project to build the codes:

  • firstly I have (Class "ClsMysql") that contains the following methods and I have the same for "SQLite" (Class "ClsSqlite"):

     Public Sub DtFill(ByVal SqlStr As String, ByVal xDt As DataTable, ByVal xPar() As 
     MySqlParameter)
         Dim xCmd As New MySqlCommand() With {
         .CommandType = CommandType.Text,
         .CommandText = SqlStr,
         .Connection = _ServerConnStr
         }
    
         If xPar IsNot Nothing Then
             For i As Integer = 0 To xPar.Length - 1
                 xCmd.Parameters.Add(xPar(i))
             Next
         End If
    
         Dim xDa As New MySqlDataAdapter(xCmd)
         xDa.Fill(xDt)
    
         xCmd.Dispose()
         xDa.Dispose()
     End Sub
    
     'Execute NoN Query Data
     Public Sub ExecuteNonQSql(ByVal SqlStr As String, ByVal xPar() As MySqlParameter)
         Dim xCmd As New MySqlCommand() With {
         .CommandType = CommandType.Text,
         .CommandText = SqlStr,
         .Connection = _ServerConnStr
         }
    
         If xPar IsNot Nothing Then
             For i As Integer = 0 To xPar.Length - 1
                 xCmd.Parameters.Add(xPar(i))
             Next
         End If
    
         xCmd.ExecuteNonQuery()
         xCmd.Dispose()
     End Sub
    
     'Execute Scalar Data
     Public Function ExecuteScalarSql(ByVal SqlStr As String, ByVal xPar() As MySqlParameter) As Integer
         Dim xCmd As New MySqlCommand() With {
         .CommandType = CommandType.Text,
         .CommandText = SqlStr,
         .Connection = _ServerConnStr
         }
    
         If xPar IsNot Nothing Then
             For i As Integer = 0 To xPar.Length - 1
                 xCmd.Parameters.Add(xPar(i))
             Next
         End If
    
         ExecuteScalarSql = xCmd.ExecuteScalar()
         xCmd.Dispose()
     End Function
    
  • and I have a class for the "Currencies Table" contains the following methods:

    Public Sub UpdateRecord()
     Try
         Select Case _ConnType
                     'Server
             Case 0
                 'Check Conn
                 If CheckServerConn() = False Then
                     xBgwStatus = False
                 Else
                     'Check if there is duplication
                     Select Case CheckExistUpdate_MySql()
                         Case True
                             xBgwStatus = False
                         'Start Update
                         Case False
                             Dim xPar(4) As MySqlParameter
                             xPar(0) = New MySqlParameter("@CurrencyID", MySqlDbType.Int32) With {
                                 .Value = CurrencyID}
                             xPar(1) = New MySqlParameter("@CurrencyName", MySqlDbType.VarChar) With {
                                 .Value = CurrencyName}
                             xPar(2) = New MySqlParameter("@CurrencyType", MySqlDbType.VarChar) With {
                                 .Value = CurrencyType}
                             xPar(3) = New MySqlParameter("@CurrencyPrice1", MySqlDbType.Double) With {
                                 .Value = CurrencyPrice1}
                             xPar(4) = New MySqlParameter("@CurrencyPrice2", MySqlDbType.Double) With {
                                 .Value = CurrencyPrice2}
                             xClsMySql.ExecuteNonQSql(xSqlUpdate, xPar)
                             xBgwStatus = True
                     End Select
                 End If
    
                     'Local
             Case 1
                 'Check Conn
                 If CheckLocalConn() = False Then
                     xBgwStatus = False
                 Else
                     'Check if there is duplication
                     Select Case CheckExistUpdate_Sqlite()
                         Case True
                             xBgwStatus = False
                         'Start Update
                         Case False
                             Dim xPar(4) As SQLiteParameter
                             xPar(0) = New SQLiteParameter("@CurrencyID", MySqlDbType.Int32) With {
                                 .Value = CurrencyID}
                             xPar(1) = New SQLiteParameter("@CurrencyName", MySqlDbType.VarChar) With {
                                 .Value = CurrencyName}
                             xPar(2) = New SQLiteParameter("@CurrencyType", MySqlDbType.VarChar) With {
                                 .Value = CurrencyType}
                             xPar(3) = New SQLiteParameter("@CurrencyPrice1", MySqlDbType.Double) With {
                                 .Value = CurrencyPrice1}
                             xPar(4) = New SQLiteParameter("@CurrencyPrice2", MySqlDbType.Double) With {
                                 .Value = CurrencyPrice2}
                             xClsSqlite.ExecuteNonQSql(xSqlUpdate, xPar)
                             xBgwStatus = True
                     End Select
                 End If
         End Select
    
     Catch ex As Exception
         xBgwStatus = False
         MsgBox(Me_MsgErrorStr + vbNewLine + vbNewLine + ex.Message, Me_MsgInfoStyle, Me_MsgCaptionStr)
     Finally
         _LocalConnStr.Close()
         _ServerConnStr.Close()
     End Try
     End Sub
    
    
    
    'SQLite check the duplications
    Public Function CheckExistInsert_Sqlite() As Boolean
     Dim xPar(0) As SQLiteParameter
     xPar(0) = New SQLiteParameter("@CurrencyName", MySqlDbType.VarChar) With {
                             .Value = CurrencyName}
     If xClsSqlite.ExecuteScalarSql(xSqlExistInsert, xPar) > 0 Then
         MsgBox("The Record is already exist", Me_MsgInfoStyle, Me_MsgCaptionStr)
         Return True
     Else
         Return False
     End If
     End Function
    
    'MySQL check the duplications
    Public Function CheckExistUpdate_MySql() As Boolean
     Dim xPar(1) As MySqlParameter
     xPar(0) = New MySqlParameter("@CurrencyID", MySqlDbType.Int32) With {
                             .Value = CurrencyID}
     xPar(1) = New MySqlParameter("@CurrencyName", MySqlDbType.VarChar) With {
                             .Value = CurrencyName}
     If xClsMySql.ExecuteScalarSql(xSqlExistInsert, xPar) > 0 Then
         MsgBox("The Record is already exist", Me_MsgInfoStyle, Me_MsgCaptionStr)
         Return True
     Else
         Return False
     End If
     End Function
    
  • _ConnType will be either "Local" neither "Server".

my question is my way to build the classes and the methods are right or there is better than that way?

\$\endgroup\$
1
  • \$\begingroup\$ Hi. Welcome to Code Review! It would be helpful if you could provide more details about what your code is supposed to do. What problem is the code meant to solve? What input does it take? What output should it produce? Also, you have plenty of room to post more code. Ideally you'd post your entire program. But at minimum given your question, I think that we should have the entirety of all three classes and any interfaces that they implement. Titles should reflect what the code does, not what is wanted from review. \$\endgroup\$
    – mdfst13
    Aug 29 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.