Function to return a mysqlconnection

Question

I am trying to create a Function that do a check if a checkbox is checked, and if so, then return the new MySqlConnection, else return the value of a different MySqlConnection. Actually should be simple, but there is no way for me that it works.

When i do it without the IF statement, and just return one value(conection) it works:

    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

But this is actualy what I need to do, mach an check and return a connection:

   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

When i do it like that, a have this error: System.InvalidOperationException: 'An error occurred creating the form. See Exception.InnerException for details. The error is: Object reference not set to an instance of an object.'

Inner Exception: NullReferenceException: Object reference not set to an instance of an object.

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — David, Jun 20 '19 at 11:18
This has nothing to do with a `MySQLConnection`. The `ExtLogCheckB` property is `null` (`Nothing` in VB) when you try to read it. Consider the logic of what you're doing... You try to check `If ExtLogCheckB.Checked = True` *while the class itself is being constructed*, which is *before the user has even seen the form*. A check box will not be checked, or even exist, before the form has been constructed. You need to re-think what you're trying to accomplish. — David, Jun 20 '19 at 11:20
hmm ok makes sense... My problem is that i have 2 diferent IP Adresses for my Database Server, and depending on it if i am Local in the building a use one IP Adresse and if I am working Remote than the oder IP Adress. So in my Program i create the connectionstring and then when I am runing some querys I open and close the connection (mycqlconn). So i wanted to add a checkbox and if I am working from remote to be abel to use the oder IP Adress but for the same connection (mysqlconn). — Acelando, Jun 20 '19 at 12:56
I suppose in that case you'd need to create your SQL connection after showing the form to the user, not before. Whatever data your code is fetching just to show the form can't depend on user input from the form. (Of course, if you're not fetching data just to show the form, then why create a SQL connection immediately when the form loads in the first place?) — David, Jun 20 '19 at 13:01
You are 100% right... Dim mysqlconn As MySqlConnection = LoginIP() Is just needed IN the form and thats it. Thx man — Acelando, Jun 20 '19 at 13:10

score 0 · Answer 1 · answered Jun 20 '19 at 13:22

The solution to your problem might be to create a property that decides which connection string to return and that is accessed in the button (or link or whatever next UI element the user has to invoke to proceed).

But also don't compile any connection strings into your code, please. Open the project properties, Settings, and add two lines there with your two connection strings, e.g.

Name    Type                 Scope        Value
------  -------------------  -----------  ----------------------------------------------------------------
ProdDB  (Connection string)  Application  server=100.XX.XX.XX;userid=idname;password=idpass;database=my_db
TestDB  (Connection string)  Application  server=200.XX.XX.XX;userid=idname;password=idpass;database=my_db

That adds them into your App.config and allows you to change them in future without the need to recompile your application. (Although if your connection string contains user name and password, it might be marginally safer to compile them into your executable.)

The code in your form could look like this:

Private ReadOnly Property ConnectionString As String
    Get
        With My.Settings
            Return If(ExtLogCheckB.Checked, .TestDB, .ProdDB)
        End With
    End Get
End Property

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim myConnection As New MySqlConnection(ConnectionString)
    'Do something with it...
End Sub

ok this is something new for me, but it's working perfectly. But I have this problem: my first form is an Login form and after the "login process" i am closing this form and opening a new form (my program form). So the "checkbox" information is gone, and then the "get" process is not working, how cann I pass the Retunr function in the nest form so that i can use there the ConnectionStrin as well? — Acelando, Jun 20 '19 at 13:47

score 0 · Accepted Answer · answered Jun 20 '19 at 20:24

To your follow up question:

You could add a module called StartUp.vb to your project that looks like this:

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

And open the project properties, Application and uncheck checkbox [ ] Enable Application Framework. After unchecking the checkbox, select StartUp (our newly added module) as Startup Object.

Also ensure that the compile options are set to Option explicit = On, Option strict = On and Option infer = On.

Then you might want to add a class called LoginDetails.vb containing the login details you need including a property with an actual database connection.

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

The code behind of the LoginForm could hardly be simpler:

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

Some extension methods to simplify data access:

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

A sample entity:

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

And now you can quite easily access data form within your application (e.g. 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

I apologize to everybody out there, I'm aware that it became now more of a tutorial than a simple answer to a question. I promise to reform.

Final hint: The code compiles but is otherwise untested.

Offtopic: Does anybody know a way for the syntax highlighting (the colors) to work on VB.NET code snippets? — Christoph, Jun 21 '19 at 18:11

Function to return a mysqlconnection

2 Answers2