In my VB .Net winforms application, how can I maintain a connection to my database?

I have a VB .NET Windows form application(Not a web application). When I press a button on the form, it will connect to the database and load data into a GridView control. That works great, but several other forms also need a database connection. So here is my question. Is there a way to make my database connection more persistent so that it can be accessed anywhere in the application? If so, how would I do it?

Thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi brgdotnet;

One way is to make a module that is global like this

Module GlobalModule
    Public connectionString As String
End Module

Then you can access it from anywhere in your application like this.

'' Assign aa value to the connectionString variable
GlobalModule.connectionString = "Data Source= ...."

'' Or when you need to read it you use this
Dim conStr As String = GlobalModule.connectionString

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor Commented:
Thank you Fernando? Is this like a static class where I just reference the values via the class name?
Why not use settings? Double click on My Project, choose "Settings" and add your connection string
Call it
Dim conn = New SqlConnection(My.Settings.MyConnectionString)
Jacques Bourgeois (James Burger)PresidentCommented:
In .NET, it is not recommended to keep a Connection opened. All ADO.NET has been built around the fact that you work in a disconnected fashion, same as web developers do, even in a desktop application. One of the only situations where a connection might stay open for a little while is when you use a DataReader.

Most other objects such as the DataAdapter and DataTable work with the idea that you connect, load the data, then disconnect. You work with the data in memory (in the DataTable, in a grid, in a collection). When ready, you reconnect, save the changes and disconnect.

Tools that hide what is happening, such as LINQ and Data Entities also work that way.

Connections are managed by a mechanism called Connection Pooling (look to the left side of the page if you are not with SQL Server). Look at the Caution in the Adding Connections section of the page: We strongly recommend that you always close the connection when you are finished using it.

With today's tool, this means open the connection (unless you use an object such as the DataAdapter that does it for you), grab your data (in a DataTable or other container) and Close the connection.

In the background, the system leaves the real connection opened for a while on the server, even if you called Close on your object (the object is not the Connection, it's only a way to control it). If you reopen another Connection somewhere else in the application, with the exact same ConnectionString (saved in a constant or the application Settings so that it is always exactly the same), the system reuses the same connection. If however you do not use the connection for a while, it is closed and will be recreated the next time you open any Connection object with the same ConnectionString.

Although this seems to run against what was done in the past with databases, it makes more sense with today's applications, Windows or otherwise, that always work with a copy of the data in memory instead of working directly on the database.
Is this like a static class where I just reference the values via the class name
Module GlobalModule
    Public connectionString As String
End Module

Open in new window

NotInheritable Class GlobalModule
    Public Shared connectionString As String
End Module
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.