Database Connection Problems.

I have a question about data base connections I need to know best practices on how to use them within method. I have a project that is running out of connections to the database and I’m not sure why. so here is a sample of what I’m doing in my database methods. Please point me in the right direction.





Question 1: Now then say I have a method called getBrokerID in a class called brokers, and this method returns a string value. Now then do I need to put the dim cnn as new sqlConnection, and the dim resultSet as sqldatareader above the Try statement? And add the cnn.close(), and resultSet.close in my exception statement? Or will the connection be closed at the end of the End function block without any extra code. Which is what I always thought happened but it seems not to be the case. Cause if I take out all my cnn.Close, and resultSet.close statements I start running out of connections to the connection pool even with the CommandBehavior.closeconnection. So to me it seems like the calling object is leaving that connection open. For example if I had a form that loaded broker data like this do these questions happen?








Public sub Init()


Dim broker as new brokers


Broker.getBrokerID() ‘Question 1a. if the cnn.close is removed does the connection cnn stay open here?


Broker.doSomethingElse() ‘Question 1b. Now a new cnn is created now do I have two connections open to my pool?


End sub





Public class Brokers





Public Function GetBrokerID(ByVal Broker As String) As String


Try


'check to see if a ' is in the broker string


If Broker.IndexOf("'") > 0 Then


Broker = Broker.Insert(Broker.IndexOf("'"), "'")


End If





Broker = Trim(Broker)


Dim cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))


Dim sqlSelectCmd As SqlCommand = New SqlCommand("BrokerRecord", cnn)


sqlSelectCmd.CommandText = "select SalespersonNumber FROM QueryMaster..ARD_SalespersonMasterfile where name = '" & Broker & "'"


cnn.Open()


Dim resultSet As SqlDataReader = sqlSelectCmd.ExecuteReader(CommandBehavior.CloseConnection)





Dim brokerID As String





If resultSet.HasRows = True Then


resultSet.Read()


brokerID = resultSet("SalespersonNumber")


Else


cnn.Close()


resultSet.Close()


Throw New Exception("BrokerID was not found for the Broker, function GetBrokerID")


End If


cnn.Close()


resultSet.Close()


Return brokerID





Catch ex As Exception


Throw ex


End Try


End Function


End class








Question 2. Returning an sqlDataReader. Now then is this a good practice to follow? Because it seems when I do this it really does leave my connection to my connection pool open, and I don’t know how to close the connection from the calling object. Because the commandbehavior.closeconnection does not seem to be closing the connection.








‘calling method for getBrokerList


public sub init()


dim broker as new broker


dim sqlReader as SqlDataReader


sqlReader = broker.getBrokerList


while sqlreader.read


…do something


wend


end sub





Public Function getBrokerList() As SqlClient.SqlDataReader


Dim sql As String


Dim cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))


Dim sqlSelectCmd As SqlCommand = New SqlCommand("BrokerListing", cnn)





sql = "SELECT BrokerName, BrokerID FROM tbl_CC_Broker where visibleFlag = 'T'"


sqlSelectCmd.CommandText = sql





cnn.Open()


Dim resultSet As SqlDataReader = sqlSelectCmd.ExecuteReader(CommandBehavior.CloseConnection)


Return resultSet


End Function





Question 3. would it be better to structure my broker class with a global database connection, and a global resultSet?





thanks for any help!