Where to Open Database Connection - How many times

[vpekulas]

My questions is very simple (I think) I'd like your opinion where to open the DB connection. In classic ASP I'd open the connection near the top of the page where I'd need it and close it with the last trip to the DB near the bottom of the page. I'd only have 1 connection per page.

Can I, should I use the same scenario in .NET ? So far I'm using something like this: (2 connections per page)

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack = True Then
        Call BindData()
    End If
End Sub

Sub BindData()
    Dim myConnection As System.Data.SqlClient.SqlConnection
    Dim myCommand As System.Data.SqlClient.SqlDataAdapter
    Dim SQL As String = "SELECT fldUSERNAME, fldIP, fldDATE FROM tbl_adminlogs ORDER BY ID DESC"
    myConnection = New System.Data.SqlClient.SqlConnection(ConnectionString)
    myCommand = New System.Data.SqlClient.SqlDataAdapter(SQL, myConnection)
    Dim ds As DataSet = New DataSet()
    myCommand.Fill(ds)
    dGrid.DataSource = ds
    dGrid.DataBind()
    myConnection.Close()
    myConnection.Dispose()
    lblTotal.Text = "Total Entries Found: " & Return_TotalC.ToString
End Sub

Sub NewPage(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
    dGrid.CurrentPageIndex = e.NewPageIndex
    BindData()
End Sub


Function Return_TotalC() As Int32
    Dim strConn As String = ConnectionString
    Dim strSQL As String = "SELECT COUNT(ID) FROM tbl_adminlogs"
    Dim Conn As New System.Data.SqlClient.SqlConnection(strConn)
    Dim Cmd As New System.Data.SqlClient.SqlCommand(strSQL, Conn)
    Conn.Open()
    Return_TotalC = Cmd.ExecuteScalar()
    Conn.Close()
    Conn.Dispose()
End Function

"Taxes are the fees we pay for civilized society" G.W.

 

[Isadore]

vp: I think your code:

Conn.Open()
Return_TotalC = Cmd.ExecuteScalar()
Conn.Close()

..captures the essence of the argument, i.e., open and at the very last opportunity; close at the very first (at least this seems to be the argument in most cases).

..what advantages the following may have:

Conn.Open()
Return...
Conn.Close()
Conn = Nothing

..can't say for certain, have seen it both ways in many places -- not sure if setting the connection to nothing affects the "connection pooling" or not..

 

[vpekulas]

I think the "Connection = Nothing" argument was my ASP 3.0 talking

What you are saying is that if I need to tap into my DB say 4 times on 1 page, it doesn't matter that I have to open the DB connection 4 times as well. It's better then opening it once and closing it once. Right ?

"Taxes are the fees we pay for civilized society" G.W.

 

[Isadore]

Oh, I must have misread your argument.

No, I would leave the connection open (others may disagree) and go ahead and hit the tables but after hitting the last table I would immediately close the connection -- all of this based on the notion that you are not doing much in between -- i.e., just getting data out of the tables, and then closing the connection.

On several of my pages I leave the connection open while I execute 2 or 3 SQL statements against different tables in the same database, and then finally close the connection.

However, I am not totally certain about this -- good point you have brought up. With "connection pooling" (a dot NET feature) you could arguably close the connection each time yet not spend the resources to open a new connection as the connection will remain open on the server for some time afterwards.

Perhaps someone will drop by and a little more color to all of this -- good question vp.

 

[link9]

Isadore is correct on the connection pooling point. You can open and close your connections w/o the overhead of actually opening a connection to the database because of connection pooling.

Personally, I do like Isadore does... I have a data object that wraps the data access, and it opens the connection when you instantiate it, and then closes the connection when you .Dispose() the object. One open... one close per object.

However, opening and closing your connection would be a better model because of the pooling aspect of ADO.NET. The shorter the duration that your connection is open, the more opportunity other threads are going to have to access that connection.

If you have 1000000 data objects out there, and each are holding their connections open, then you obviously have problems. I don't deal w/ that type of volume, though, so I don't worry about it. Really, I should re-write my wrapper class to do things that way, but until I have thousands of concurrent users (which isn't ever going to happen on my reporting sites), then I just don't feel the urgency to do so.

As for setting something = nothing, you definitely don't have to do that anymore.

You should either .Close() or .Dispose() of your connection objects when you're done w/ them. This will release them back to the pool, and both are functionally equal. Do both if it makes you feel better.


paul

The answer to getting answered -- faq855-2992

 

[vpekulas]

Thanks guys, makes it clear. The connection pooling is done by default in .NET right ? No need to set-up anything ?

"Taxes are the fees we pay for civilized society" G.W.

 

[Isadore]

That is correct vp, post back if you need any additional help.

 

[cappmgr]

If you are writting C# the using statement is nice, as it opens and closes your conection.

using (SqlConnection myConnection = new SqlConnection(myConnString))
{
db operations
} //connection automatically closed

Marty