How to redeclare an instance of a class?

Question

I'm dusting off vb.net needs to work and I have a question that I hope you can clarify. My application require a connection with a database, now when the form is load I execute this function:

Sub OpenConnection()
    Try
        Dim connection As New Connection
        Dim MysqlConn = connection.establishConnection()

        MysqlConn.Open()

    Catch myerror As MySqlException
        MessageBox.Show("Connection failed: " & myerror.Message)
    Finally
        MysqlConn.Dispose() 
    End Try
End Sub

The problem's that connection and MysqlConn variable is used also in another function so I've declared this two variable as global. How you can see this variable are re-declared in the OpenConnection() 'cause I've a form where the user can change the settings connection string. Now if the software can't connect to the database for a wrong credentials the user change this into the setting form but if I remove connection and MysqlConn the application use the past instance of a class so even if the credentials are correct, not declaring a new instance of the connection the application can't connect. For the moment I've solved with a re-declaration inside the OpenConnection() but is this a good move? Is there a way to have the two global variables and invoke a new instance without redefine the function?

Connection class - EstablishConnection function

Public Function establishConnection()

    Dim MysqlConn As MySqlConnection = New MySqlConnection()  
    Dim server_name = My.Settings.server_name
    Dim username = My.Settings.username
    Dim password = My.Settings.password
    Dim database = My.Settings.database

    MysqlConn.ConnectionString = "server=" & server_name & ";" _
    & "user id=" & username & ";" _
    & "password=" & password & ";" _
    & "database=" & database & ";"

    Return MysqlConn

End Function

You don't declare an instance of anything. You declare a variable, you create an instance of a type and you assign the instance to the variable. If you want to create a new instance then just create a new instance. Use the `New` keyword to create a new object and assign it to the variable to replace the old one. — jmcilhinney, Dec 09 '15 at 12:13
Instead of a Sub acting on a global Connection, you could make it a function *returning* a connection. [See this](http://stackoverflow.com/a/28216964/1070452) but I dont see a class in the question — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 12:17
I've update my code, how you can see I return the connection when establishConnection is called. — Dillinger, Dec 09 '15 at 12:39
A) Turn on Option Strict. B) Yes, establishconnection is a function which does what I described, but the method that calls it is still setting some global var. You dont need it. Each operation should create, use and dispose of the connection (see link). If there is more than one DB or ConnStr, then a class would be idea for storing the settings. — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 12:44
Why I should user Option strict? Okay I'm trying to user your hints, thanks. — Dillinger, Dec 09 '15 at 12:47
Option Strict reveals unwanted type conversions at compile time rather than runtime. Probably 30% of the questions here are about RT errors resulting from sloppy code that Option Strict would have prevented. — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 13:05

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2015-12-09T13:14:50.870

How to redeclare an instance of a class?

Variables are declared.
Objects are instanced (instantiated).

They are never redeclared; another new one is just created. Take this line (similar to yours):

Dim connection As MySQLConnection

It declares a variable, giving it a name and type.

Dim connection As MySQLConnection = New MySQLConnection

The second part creates an instance - the New operator is the key: it creates a new instance. The short hand form you used does both at once and perhaps makes it unclear that 2 different operations - declaring a variable and creating an instance - are going on.

Apparently, you have a class named Connection to "manage" connections. But the top method, OpenConnection is just using it to set/reset some global object var. dbConnections should generally be created, used and disposed of.

A connection maker-helper like you have can prevent having connection strings sprinkled all over the code. Eg:

Function GetMySQLConnection(Optional usr As String = "root",
                       Optional pw As String = "123456") As MySqlConnection
    Dim conStr As String
    conStr = String.Format("Server=localhost;Port=3306;Database={0};Uid={1}; Pwd={2};", 
         MySQLDBase, usr, pw)

    Return New MySqlConnection(constr)
End Function

This might be part of a small class to hold the DBname and other log in info. But using it doesnt require a global var:

Sub GetSomeData(...)
    Using dbCon As MySqlConnection = GetMySQLConnection()
        ...
        dbCon.Open()

        myDA.Fill(myDT)
        ...

    End Using
End Sub

At the start, a new connection object is created for this set of operations, which exists only locally (in that method). The Using block assures that the connection is closed (and returned to the pool) and disposed of when you are done with it.

That will be a problem if you are trying to manage/create connections to 2 different DBs — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 13:47
So I guess the same thing will happean for the connection close, right? I mean, now I'm using dbCon, but if I want close the connection inside another method I should call a function of the Connection class? Or redeclare Using dbCon as MySqlConnection = .... in the method where I want to close the connection? — Dillinger, Dec 09 '15 at 13:50
`Using` will close and dispose of the underlying resources for you. As described, connections should be created and used locally — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 13:50
So when method has completed the operation the connection will close automatically? — Dillinger, Dec 09 '15 at 13:57
No, when the code hits the `End Using` that Connection will be closed, returned to the connection pool and resources released...and the object variable will be `Nothing` — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 13:58

How to redeclare an instance of a class?

1 Answers1