mysql - 返回 mysqlconnection 的函数

Question

我正在尝试创建一个函数来检查是否选中了复选框，如果选中，则返回新的 MySqlConnection，否则返回不同 MySqlConnection 的值。其实应该很简单，但我没有办法让它工作。

当我在没有 IF 语句的情况下执行此操作，并且只返回一个值(连接)时，它会起作用:

    Dim mysqlconn As MySqlConnection = LoginIP()

    Public Function LoginIP() As MySqlConnection
        Return New MySqlConnection("server=100.XX.XX.XX; userid=idname; password=idpass; database=my_db")
     End Function

但这实际上是我需要做的，进行检查并返回连接:

   Dim mysqlconn As MySqlConnection = LoginIP()

    Public Function LoginIP() As MySqlConnection
        If ExtLogCheckB.Checked = True Then
        Return New MySqlConnection("server=100.XX.XX.XX; userid=idname; password=idpass; database=my_db")
        Else
        Return New MySqlConnection("server=200.XX.XX.XX; userid=idname; password=idpass; database=my_db")
        End If
    End Function

当我这样做时，出现以下错误:System.InvalidOperationException:“创建表单时发生错误。有关详细信息，请参阅 Exception.InnerException。错误是:对象引用未设置到对象的实例。'

内部异常:NullReferenceException:未将对象引用设置为对象的实例。

Answer 1

对于您的后续问题:

您可以将名为 StartUp.vb 的模块添加到您的项目中，如下所示:

Imports System
Imports System.Diagnostics
Imports System.Windows.Forms
Imports MySql.Data.MySqlClient

Module StartUp

    'Private Fields
    <DebuggerBrowsable(DebuggerBrowsableState.Never)>
    Private _LoginDetails As LoginDetails

    'Main entry point

    Public Sub Main(args As String())
        'Standard initialization, copied from a C# project
        Application.EnableVisualStyles()
        Application.SetCompatibleTextRenderingDefault(False)
        'Start login screen
        Dim myLoginDetails As LoginDetails = Nothing
        Using myLoginForm As New LoginForm()
            myLoginForm.ShowDialog()
            myLoginDetails = myLoginForm.LoginDetails
        End Using
        'Assign details
        If (myLoginDetails Is Nothing) Then
            Environment.ExitCode = -1 'Login cancelled
            Application.Exit()
        End If
        LoginDetails = myLoginDetails
        'Start main application
        Application.Run(New MainForm())
    End Sub

    'Public Properties

    Public Property LoginDetails As LoginDetails
        Get
            Return _LoginDetails
        End Get
        Private Set(value As LoginDetails)
            _LoginDetails = value
        End Set
    End Property

    Public ReadOnly Property FooDB As MySqlConnection
        Get
            Return LoginDetails?.FooDB
        End Get
    End Property

End Module

然后打开项目属性、应用程序并取消选中复选框[]启用应用程序框架。取消选中该复选框后，选择“StartUp”(我们新添加的模块)作为“启动对象”。

还要确保编译选项设置为Option Explicit = On、Option strict = On 和 Option Infer = On。

然后您可能需要添加一个名为 LoginDetails.vb 的类，其中包含您需要的登录详细信息，包括具有实际数据库连接的属性。

Imports System
Imports System.Data
Imports System.Diagnostics
Imports MySql.Data.MySqlClient

Public Class LoginDetails

    'Private Fields

    <ThreadStatic>
    <DebuggerBrowsable(DebuggerBrowsableState.Never)>
    Private _OpenConnection As MySqlConnection

    'Constructors

    Public Sub New(extendedLogging As Boolean, userName As String, password As String)
        Dim myRawConnectionString As String = If(extendedLogging, My.Settings.TestDB, My.Settings.ProdDB)
        Dim myBuilder As New MySqlConnectionStringBuilder(myRawConnectionString)
        myBuilder.UserID = userName
        myBuilder.Password = password
        myBuilder.Pooling = False
        If (extendedLogging) Then
            myBuilder.UsePerformanceMonitor = True
            myBuilder.UseUsageAdvisor = True
        End If
        ConnectionString = myBuilder.ToString()
        Me.UserName = userName
        Me.DatabaseName = myBuilder.Database
        Me.ServerName = myBuilder.Server
    End Sub

    'Public Properties

    Public ReadOnly Property UserName As String

    Public ReadOnly Property DatabaseName As String

    Public ReadOnly Property ServerName As String

    Public ReadOnly Property ExtendedLogging As Boolean

    ''' <summary>Returns an open connection to the FooDB or throws an exception if not possible.</summary>
    Public ReadOnly Property FooDB As MySqlConnection
        Get
            'Return from cache
            Dim myResult As MySqlConnection = _OpenConnection
            If (myResult IsNot Nothing) Then
                'Return open conneciton
                If (myResult.State = ConnectionState.Open) Then Return myResult
                'Try to open it
                Try
                    myResult.Open()
                    If (myResult.State = ConnectionState.Open) Then Return myResult
                Catch
                End Try
                'Kill corrupted connection
                Try
                    myResult.Dispose()
                Catch
                End Try
            End If
            'Initialize and return
            Try
                myResult = New MySqlConnection(ConnectionString)
                myResult.Open()
                If (myResult.State = ConnectionState.Open) Then
                    _OpenConnection = myResult
                    Return myResult
                End If
            Catch
            End Try
            'Throw exception
            Throw New ApplicationException($"Unable to connect to database {DatabaseName} on server {ServerName}!")
        End Get
    End Property

    'Private Properties

    Private ReadOnly Property ConnectionString As String

End Class

LoginForm 背后的代码再简单不过了:

Imports System

Public Class LoginForm

    Public Property LoginDetails As LoginDetails

    Private Sub btnOkay_Click(sender As Object, e As EventArgs) Handles btnOkay.Click
        LoginDetails = New LoginDetails(ExtLogCheckB.Checked, txtLoginName.Text, txtPassword.Text)
        Close()
    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        LoginDetails = Nothing
        Close()
    End Sub

End Class

一些简化数据访问的扩展方法:

Imports System
Imports System.Data
Imports System.Reflection
Imports System.Runtime.CompilerServices
Imports System.Threading.Tasks
Imports MySql.Data.MySqlClient

'Warning: Don't use string based queries because of the danger of SQL-injection! I told you...

<Extension()>
Module ExtMySqlConnection

    <Extension()>
    Public Function ExecToString(connection As MySqlConnection, query As String) As String
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myCommand As New MySqlCommand(query, connection)
        Dim myResult As Object = myCommand.ExecuteScalar()
        Return CType(myResult, String)
    End Function

    <Extension()>
    Public Function ExecToInt32(connection As MySqlConnection, query As String) As Int32
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myCommand As New MySqlCommand(query, connection)
        Dim myResult As Object = myCommand.ExecuteScalar()
        Return CType(myResult, Int32)
    End Function

    <Extension()>
    Public Function ExecToInt32Nullable(connection As MySqlConnection, query As String) As Int32?
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myCommand As New MySqlCommand(query, connection)
        Dim myResult As Object = myCommand.ExecuteScalar()
        Return CType(myResult, Int32?)
    End Function

    <Extension()>
    Public Function ExecToDateTime(connection As MySqlConnection, query As String) As DateTime
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myCommand As New MySqlCommand(query, connection)
        Dim myResult As Object = myCommand.ExecuteScalar()
        Return CType(myResult, DateTime)
    End Function

    <Extension()>
    Public Function ExecToDateTimeNullable(connection As MySqlConnection, query As String) As DateTime?
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myCommand As New MySqlCommand(query, connection)
        Dim myResult As Object = myCommand.ExecuteScalar()
        Return CType(myResult, DateTime?)
    End Function

    <Extension()>
    Public Function ExecToDataTable(connection As MySqlConnection, ByVal query As String) As DataTable
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myDataSet As DataSet = ExecToDataSet(connection, query)
        Return myDataSet.Tables(0)
    End Function

    <Extension()>
    Public Function ExecToDataSet(connection As MySqlConnection, ByVal query As String) As DataSet
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myResult As New DataSet()
        Try
            Dim myCommand As New MySqlCommand(query, connection)
            Dim cmd As New MySqlDataAdapter(myCommand)
            cmd.Fill(myResult)
        Finally
            'CloseConnection()
        End Try
        Return myResult
    End Function

    ''' <summary>Takes the connection and executes the given query on it and returns the result as a single row of type 
    ''' <see cref="DataRow" />. If the query results in 0 rows, null is returned. If the query results in multiple rows, 
    ''' an <see cref="AmbiguousMatchException" /> is thrown.</summary>
    ''' <param name="connection">The connection on which to invoke the query (a <see cref="NullReferenceException" /> is thrown if the parameter is null to simulate instance method behavior).</param>
    ''' <param name="query">The SQL statement to be executed.</param>
    ''' <returns>The according <see cref="DataRow" /> or null (or an <see cref="AmbiguousMatchException" /> may be thrown).</returns>
    <Extension()>
    Public Function ExecToDataRow(connection As MySqlConnection, ByVal query As String) As DataRow
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Return ExecToDataRow(connection, query, False)
    End Function

    ''' <summary>Takes the connection and executes the given query on it and returns the result as a single row of type 
    ''' <see cref="DataRow" />. If the query results in 0 rows, null is returned. If the query results in multiple rows, 
    ''' it depends on parameter <paramref name="ignoreAdditionalRows"/> whether the first record is returned (true) or
    ''' an <see cref="AmbiguousMatchException" /> is thrown (false).</summary>
    ''' <param name="connection">The connection on which to invoke the query (a <see cref="NullReferenceException" /> is thrown if the parameter is null to simulate instance method behavior).</param>
    ''' <param name="query">The SQL statement to be executed.</param>
    ''' <param name="ignoreAdditionalRows">Determines whether additional rows should be silently ignored if more than one rows are returnd (true) or whether an <see cref="AmbiguousMatchException" /> should be thrown (false).</param>
    ''' <returns>The according <see cref="DataRow" /> or null (or an <see cref="AmbiguousMatchException" /> may be thrown).</returns>
    <Extension()>
    Public Function ExecToDataRow(connection As MySqlConnection, ByVal query As String, ignoreAdditionalRows As Boolean) As DataRow
        'Check args
        If (connection Is Nothing) Then Throw New NullReferenceException()
        If (String.IsNullOrWhiteSpace(query)) Then Throw New ArgumentNullException(NameOf(query), "The query is null or empty!")
        'Execute query
        Dim myDataTable As DataTable = ExecToDataTable(connection, query)
        'Handle 1 or 0 records
        Select Case myDataTable.Rows.Count
            Case 1
                Return myDataTable.Rows(0)
            Case 0
                Return Nothing
        End Select
        'Determine what to do if there are more than one record
        If (Not ignoreAdditionalRows) Then
            Throw New AmbiguousMatchException()
        End If
        Return myDataTable.Rows(0)
    End Function

End Module

示例实体:

Imports System
Imports System.Data
Imports System.Diagnostics

<DebuggerDisplay("{DebuggerDisplayValue}")>
Public Class SampleEntity

    'Private Fields
    Private ReadOnly _Record As DataRow

    'Constructors

    Public Sub New(record As DataRow)
        If (record Is Nothing) Then Throw New ArgumentNullException(NameOf(record))
        _Record = record
    End Sub

    'Public Properties

    Public ReadOnly Property RecID As Int32
        Get
            Return _Record.Field(Of Int32)("RecID")
        End Get
    End Property

    Public ReadOnly Property LastName As String
        Get
            Return _Record.Field(Of String)("LastName")
        End Get
    End Property

    Public ReadOnly Property FirstName As String
        Get
            Return _Record.Field(Of String)("FirstName")
        End Get
    End Property

    Public ReadOnly Property FullName As String
        Get
            Return If(LastName, "") & " " & If(FirstName, "").Trim()
        End Get
    End Property

    Public ReadOnly Property EntryDate As DateTime
        Get
            Return _Record.Field(Of DateTime)("EntryDate")
        End Get
    End Property

    Public ReadOnly Property LeavingDate As DateTime?
        Get
            Return _Record.Field(Of DateTime?)("LeavingDate")
        End Get
    End Property

    Public ReadOnly Property IsActive As Boolean
        Get
            Return _Record.Field(Of Boolean)("IsActive")
        End Get
    End Property

    'Private Properties

    Private ReadOnly Property DebuggerDisplayValue As String
        Get
            Return $"{RecID}: {FullName}"
        End Get
    End Property

End Class

现在您可以非常轻松地访问应用程序中的数据表单(例如 MainForm):

Imports System.Collections.Generic
Imports System.Data

Public Class MainForm

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim myAppUsers = GetUsers()
        'Bind the users to some control
    End Sub

    Private Iterator Function GetUsers() As IEnumerable(Of SampleEntity)
        Dim myDT = FooDB.ExecToDataTable("SELECT * FROM AppUser ORDER BY LastName, FirstName")
        For Each myRecord As DataRow In myDT.Rows
            Yield New SampleEntity(myRecord)
        Next
    End Function

End Class

我向所有人道歉，我知道它现在更像是一个教程，而不是对问题的简单回答。我保证改革。

最终提示:代码可以编译，但未经测试。