vb.net - 在 VB.Net 运行时设置数据库类

Question

我有以下 Sqlite 和 SqlServer 类：

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

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

这是我的测试表格：

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

我需要帮助来定义 dbConnector 变量并设置它的值，intellisense 也应该向我展示类方法，使用我想更改数据库的参数并避免在每次我想使用一个或另一个数据库时使用条件：

   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

两个类的其余方法和参数相同，仅更改类数据对象（SQLiteConnection、SqlConnection 等）

谢谢

标签： vb.net

Answer 1

您应该定义一个接口来指定所有常见成员。然后，您可以为实现该接口的每个数据源创建一个类。然后，在您的应用程序代码中，您可以声明该接口类型的变量并将实现它的任何类的实例分配给该变量。然后，您可以只使用该变量并调用接口的任何成员，而无需关心实际的类实例是什么类型。

接口和实现类看起来像这样：

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

您的应用程序代码可能如下所示：

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

然后，您可以直接调用dataAccessProvider.GetValue您的代码，而不用关心数据源实际上是什么，除非确保您的 SQL 语法对该数据源有效。

请注意，虽然您的工作取决于您，但我选择在此代码中使用合理的命名约定。没有人会为我使用像clsOperDB_SQLSERVER. 在 .NET Framework 中看不到任何具有类似名称的类型是有原因的。