Setting database class at run time in VB.Net

Question

I have the follow classes for Sqlite and SqlServer:

Class for SQLite:

Imports System.Data.SQLite
Public Class clsOperDB_SQLite

 Public Shared Function getValue(sql As String) As String

        Try
            Using conn As New SQLiteConnection(strConn_SQLITE)
                Using cmd As New SQLiteCommand()
                    cmd.Connection = conn
                    conn.Open()
                    cmd.CommandText = sql
                    Return cmd.ExecuteScalar
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Return ""

    End Function

End Class

Class for SQLSERVER:

Imports System.Data.SqlClient
Public Class clsOperDB_SQLSERVER

            Public Shared Function getValue(sql As String) As String

                Try
                    Using conn As New SqlConnection(strConn_SQLSERVER)
                        Using cmd As New SqlCommand()
                            cmd.Connection = conn
                            conn.Open()
                            cmd.CommandText = sql
                            Return cmd.ExecuteScalar
                        End Using
                    End Using
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

                Return ""

            End Function

End Class

this is my test form:

Public Class Form1

    'form level variable
    Dim dbConnector

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim connectionType As String = "SQLITE"

        ' need something like this or any way to set form level variable

        If connectionType = "SQLITE" Then
            dbConnector = clsOperDB_SQLite
        Else
            dbConnector = clsOperDB_SQLSERVER
        End If


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'get value from SQLITE
        Dim ValueFromDatabase As String = dbConnector.getValue("select .....")
    End Sub
End Class

I need help to define dbConnector variable and set its value, also intellisense should show me class methods, using a parameter I want to change database and avoid using a conditional for every time I want to use one or the other database :

   Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim Query As String = "Select ..."
        Dim ValueFromDatabase As String = ""

        ' dont want to use if for each sql query

        If connectionType = "SQLITE" Then
            ValueFromDatabase = clsOperDB_SQLite.getValue(Query)
        Else
            ValueFromDatabase = clsOperDB_SQLSERVER.getValue(Query)
        End If

    End Sub

The rest of methods and params for both classes are the same only change class data objects (SQLiteConnection, SqlConnection, and so)

Thanks

This is not directly related to your question but you might benefit from reading this. — jmcilhinney
– jmcilhinney, Commented Nov 30, 2018 at 2:33

jmcilhinney · Accepted Answer · 2018-11-30 02:31:00Z

1

You should define an interface that species all the common members. You can then create a class for each data source that implements that interface. In your application code, you can then declare a variable of that interface type and assign an instance of any class that implements it to that variable. You can then just use that variable and invoke any member of the interface without caring what type the actual class instance is.

The interface and the implementing classes would look something like this:

Public Interface IDataAccessProvider
    Function GetValue(sql As String) As String
End Interface

Public Class SqliteDataAccessProvider
    Implements IDataAccessProvider

    Public Function GetValue(sql As String) As String Implements IDataAccessProvider.GetValue
        'Provide SQLite-specific implementation here.
    End Function

End Class

Public Class SqlServerDataAccessProvider
    Implements IDataAccessProvider

    Public Function GetValue(sql As String) As String Implements IDataAccessProvider.GetValue
        'Provide SQL Server-specific implementation here.
    End Function

End Class

Your application code might then look like this:

Private dataAccessProvider As IDataAccessProvider

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Read a value that identifies the data source and store it here.
    'The value might be stored in the config file or wherever else is appropriate.
    Dim dataSourceIdentifier As String

    Select Case dataSourceIdentifier
        Case "SQLite"
            dataAccessProvider = New SqliteDataAccessProvider()
        Case "SQL Server"
            dataAccessProvider = New SqlServerDataAccessProvider()
    End Select
End Sub

You can then just call dataAccessProvider.GetValue in your code without any care for what the data source actually is, except to ensure that your SQL syntax is valid for that data source.

Please note that, while what you do is up to you, I have chosen to use a sane naming convention in this code. No one would last long working for me using class names like clsOperDB_SQLSERVER. There's a reason you don't see any types with names like that in the .NET Framework.

edited Nov 30, 2018 at 2:31

answered Nov 30, 2018 at 2:19

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eduardo Espinosa Over a year ago

works like a charm, also added my other methods, last question: If I only have a method for SQLite, just add a dummy method to SQL Server part?

jmcilhinney Over a year ago

You can do that, but I would suggest throwing an InvalidOperationExcpetion in that dummy implementation. That way, you'll be notified if your code accidentally calls a method that it shouldn't be. If you just have an empty method then you might be calling it when you shouldn't and never know, in which case your application might be doing something with the result that it shouldn't.

Collectives™ on Stack Overflow

Setting database class at run time in VB.Net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related