[RESOLVED] Database connections in VB

[dclamp]

So I am just getting back into VB after a long hiatus. I spend a lot of time with web development, specifically PHP w/MySQL.

When I connect to MySQL in PHP, the connection is opened when the page is loaded, and closed at the bottom of the page. Basically the connection is opened and closed on every single page load and all the queries I need are in between those connections.

Getting into VB now will be my first time using databases with VB, I looked at JMC's tutorial for connecting to DBs and I was able to load what I wanted into my datagrid view, and into text boxes, etc. So thats all bueno.

My question is, at what point are the database connections open. Do I connect to the database when I first load the program and keep it open until I close out. Or do I open and close the connection as needed when I need to interact with the db?

[jmcilhinney]

It sounds like you are creating a Windows Forms app so the answer is that you open the connection as late as possible and close it as early as possible. It's important to realise that ADO.NET uses connection pooling so there are two levels of connection. The DbConnection object that you create is a fairly lightweight object. The actual database connection exists at a lower level. If you open a DbConnection and then close it, then open another DbConnection with the same connection string soon after, the same low-level database connection will be used both times. ADO.NET creates one database connection per pool, where a pool is created for each unique connection string. When you open your first DbConnection, a database connection is opened and that database connection persists after you close your DbConnection. If you go for some time without opening another DbConnection then ADO.NET will close the underlying connection, otherwise it will get used repeatedly.

So, to answer your question, you open the database when you need to retrieve or save data and then you close it immediately afterwards. If you want to execute multiple queries in succession then you would open a connection before the first and close it after the last. You also don't have to use the same DbConnection object each time. Feel free to create and destroy DbConnection objects where they are used.

[dclamp]

OK that makes sense. So basically every time I need to query from the database I would need to have the following (taken from your example):

As I am replying I am answering my own questions... I think I am going to play around a little bit and get back if I have any questions. Before I ask how, I'm going to try to create functions to keep my form code clean and keep database interaction in the class.

Thanks!

Using connection As New SqlConnection("connection string here")
    Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
                                    connection)
        connection.Open()
 
        Using reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.", _
                                              reader("Quantity"), _
                                              reader("Unit"), _
                                              reader("Name")))
            End While
        End Using
    End Using
End Using

[jmcilhinney]

That's basically correct. For instance, if you had a DAL that included a repository for the StockItem entity, it might have a GetData method that included code just like that and, as it read each record, packaged it into some other form and then returned a list containing those objects. Alternatively, the GetData method of the repository might use a data adapter and just return a populated DataTable, with a higher layer doing the repackaging. Either way, the lifetime of the DbConnection object would be that method alone.