[2005] Problem with opening / closing connections

Hello,

I have a bit of a problem with my application in as much as when I open it and it connects to the SQL Server DB, instead of opening just 1 process in SQL Enterprise Manager it opens about 10.

This means I obviously am opening connections and not closing them.
The trouble is, I didn't create the code so I don't know where to put this right.

If I can give you an idea of how the app works, I'm hoping maybe someone can help me?

It is a 3 tier application, with an app layer, a data layer and a web service layer.
When you log in, it connects to the Web Service layer and brings back user information. (See code below)

Code:

---

UserID = txtEERef.Text

ds = New DataSet

ds = Users.GetUserInfo(txtEERef.Text)
If ds.Tables(0).Rows.Count > 0 Then
  If UCase(txtPassword.Text) <> UCase(ds.Tables(0).Rows(0).Item("Password".ToString)) Then
      MessageBox.Show("Invalid Password", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Authorised = False
      txtPassword.SelectAll()
      txtPassword.Clear()
      txtPassword.Focus()
      Exit Sub
  End If
Else
      MessageBox.Show("You are not in the List of Users for the Database." & vbNewLine & vbNewLine & "Please see your Administrator for this Application.", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Authorised = False
      txtEERef.SelectAll()
      txtEERef.Clear()
      txtEERef.Focus()
      Exit Sub
End If

UserAccessLevel = ds.Tables(0).Rows(0).Item("AccessLevel".ToString)
UserID = ds.Tables(0).Rows(0).Item("EmployeeNumber".ToString)

ds = EEList.Populate_Personal_Details(UserID)
If ds.Tables(0).Rows.Count > 0 Then
    UserName = ds.Tables(0).Rows(0).Item("FullName".ToString)
Else
    MessageBox.Show("You do not have an Employee Record in the Database" & vbNewLine & vbNewLine & "You must have your Administrator set up an Employee Record for you.", "No Employee Record Found.", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Authorised = False
    Exit Sub
End If

ds.Dispose()
Users.Dispose()
EEList.Dispose()
Authorised = True

Users.UpdateLoggedInDate(UserID, Date.Today)

Me.Update()
Me.Close()

---

This alone creates about 10 processes in Enterprise Manager.
Obviously for anyone to help me they would need more information / code, but to be honest I'm not sure exactly what code will help the most - therefore if anyone can help me, it would great if they could let me know what code they would need to see in order to help me with my problem.

Thanks,

Well, that code isn't opening ANY connections so it obviously isn't the problem. This line looks like it gets the data:

vb.net Code:

ds = Users.GetUserInfo(txtEERef.Text)

so you'd need to go into that GetUserInfo method and see what's happening in there. This is another possible culprit:

vb.net Code:

ds = EEList.Populate_Personal_Details(UserID)

Can I also point out that if you aren't going to use an object then you shouldn't be creating one. This code:

vb.net Code:

ds = New DataSet
 
ds = Users.GetUserInfo(txtEERef.Text)

creates a new DataSet and then simply discards it by getting another DataSet the very next line. If you don't want to create a new object then don't use the 'New' key word.

Thanks jmc - I've been into my Users Web Service and looked at the method. It shows :

Code:

---

<WebMethod()> _
        Public Function GetUserInfo(ByVal m_EERef As String) As DataSet

        Dim ds As New Database.DataAccess.UsersTDS
        Dim Users As New Database.DataAccess.cls_Users(ConfigurationManager.AppSettings.Get("dbConn"))
        ds = Users.get_USER_Info(m_EERef)
        Return ds

        End Function

---

I've then gone into the class which is being called by this method and it shows :

Code:

---

Public Function get_USER_Info(Optional ByVal m_EERef As String = "") As DataSet

        Try
            AdaptSql = New Data.UsersAdapter(SQL_ConnectionString)
            m_TDS = New UsersTDS
            If Val(m_EERef) = 0 Then
                AdaptSql.Fill(m_TDS)
            Else
                AdaptSql.Fill(m_TDS, m_EERef)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            m_TDS = Nothing
        End Try

        Return m_TDS

    End Function

---

As I mentioned this isn't my code, so apologies if it doesn't tell the whole story, I'm just trying to piece it together the best I can.

Does any of this make sense to you, and maybe point in the direction of the opening of connections ?

Thanks in advance.

To open a connection, a .Open() needs to be issued against your connection object.

Do a project wide search for .Open and see what code is revealed.

Thanks Hack,

There seems to be a .open() in every class as follows :

Code:

---

Public Sub New(ByVal sqlConn As String)

        Try
            cn_SQLConn.ConnectionString = sqlConn
            SQL_ConnectionString = sqlConn
            cn_SQLConn.Open()
        Catch ex As SqlException
            Throw New System.Exception(ex.Message)
        End Try

End Sub

---

There are your multiple database connections.

What to do about them?

I can't answer that. As you look at this project, what would make sense to you?

I wouldn't actually remove any code just yet, but play around with just commenting out some of the things you think you might not need, and then run the project.

This will probably take some trial and error testing to get it just right.

Thanks Hack,

Can I just ask you something ?

In these classes I am using Data Adapters, which if I understand it correctly, automatically take care of the opening and closing of connections.
Firstly, is this correct ?
If so, I'd guess I don't need the .open() in the new procedures outlined above.

You are correct and incorrect. When you call the Fill or Update method of a DataAdapter it first checks whether the appropriate Connection(s) is already open. If it's not then the DataAdapter will call the Connection's Open method. Once the data access is complete the DataAdapter will call the Connection's Close method IF AND ONLY IF it previously called its Open method. In short, if a connection is open then it stays open, but if it's closed then it will be opened first and then closed again. The code in post #5 is creating a Connection and opening it, so your DataAdapter is not going to close it for you.

Cheers jmc - so in my case, as the code in post #5 is opening a connection which the Adapter WON'T close, I'd be better off getting rid of the 'cn_SQLConn.Open()' line, and letting the adapter take care of opening and closing the connection.

Have I got that right ?

Yes you do. The drawback with letting the adapter handle the opening and closing of the connection is that if you have multiple adapters using the same connection and you use more than one in quick succession, you will be unnecessarily closing and reopening the connection between each pair. If that is the case though, you'd still be better off without the Open call where it is. In that case you'd just do something like this:

vb.net Code:

connection.Open()
adapter1.Fill(table1)
'There will be no unnecessary close and reopen of the connection here.
adapter2.Fill(table2)
connection.Close()

Thanks jmc and Hack - that's helped a lot.