Using one connection when connection string is same

Question

I have a simple VB.net windows form application. I have a method for opening the connection to the database. I am using two MySql connections. In the method below it can be seen that My primary connection is opened and after that my secondary connection is opened. Both connections have different connection strings but in very rare cases these connection strings can be same so in that I don't want to open the connection twice.

Dim MySqlConnPrimary As New MySqlConnection
Dim MySqlConnSecondary As New MySqlConnection

Public Sub OpenConnection(ByVal strConnectionStringPrimary As String, ByVal strConnectionStringSecondary As String)

    Try
        With MySqlConnPrimary
            .ConnectionString = strConnectionStringPrimary
            .Open()
        End With

        With MySqlConnSecondary
            .ConnectionString = strConnectionStringSecondary
            .Open()
        End With

    Catch ex As Exception

    End Try
End Sub

Mostly the connection strings will be different but in case the connection strings are same then I don't want to open the connections multiple times. How should I achieve this? Should I assign MySqlConnPrimary to MySqlConnSecondary incase the connection strings are same?

You can check the connection State by using Property Named : MySqlConnPrimary.ConnectionState Check it using if..else block and code the necessary actions according to it. — Mahadev
– Mahadev, Commented Aug 11, 2015 at 8:03
@Mahadev Thank you for your quick response. What I want is that If the connection strings are same then both MySqlConnPrimary and MySqlConnSecondary should use 1 connection so that they are opened just once. — Adnan Yaseen
– Adnan Yaseen, Commented Aug 11, 2015 at 8:07
You should not keep a connection open; instead, open, do operation, close. That might eliminate the problem. (Connection pooling is used to make it efficient.) — Andrew Morton
– Andrew Morton, Commented Aug 11, 2015 at 8:09
Be wary of comparing connection strings. Two semantically identical connection strings could be unequal with regard to pure string comparison. — David Osborne
– David Osborne, Commented Aug 11, 2015 at 8:10

J Robson · Accepted Answer · 2015-08-11 08:50:24Z

1

The obvious and simple answer is to string compare the connection strings and reuse the connection if they are the same.

In pseudo-code:

If string.Equals(strConnectionStringPrimary,strConnectionStringSecondary)
    MySqlConnSecondary = MySqlConnPrimary
Else
    With MySqlConnSecondary
        .ConnectionString = strConnectionStringSecondary
        .Open()
    End With
End If

It doesn't feel like an ideal solution, however it is a mechanism to answer your immediate question.

edited Aug 11, 2015 at 8:50

answered Aug 11, 2015 at 8:05

J Robson

1492 silver badges12 bronze badges

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

2 Comments

Adnan Yaseen Over a year ago

@J R Thanks. Thats what I was looking for. I was thinking the same but was not sure whether it is the elegant way of achieving.

J Robson Over a year ago

I don't think there's an elegant way to solve the immediate problem that you're asking for help with, outside of a reconsideration of your architecture. The usual paradigm is to open a connection, perform some work, and then close the connection. The other note I'd leave is to not just use the "==" operator, but an operator appropriate for your workflow - perhaps the string.Compare(string,string) method, if you choose to keep your current methodology

Collectives™ on Stack Overflow

Using one connection when connection string is same

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