Function returning a Datareader to populate a DropDownList

[tmt]

Good Afternoon:

I have a function that returns a datareader to populate a DropDownList.
Below is the code I'm using. Which one is more efficient and more
importantly correct. Any input/opinion will be greatly appreciated.

My concern is that I'm cleaning up the Datareader, connection and
command objects correctly and in the most efficient way. etc.

Thank you in advance.

*** Method 1 ***

Function GetCompanyList() As SqlDataReader
'Populate Drop Down Lists
Dim myConn As New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("SELECT Company from Offices",
myConn)
Try
myConn.Open()
Catch e As Exception
'Catch the error and display it.
Response.Write("An Error Occurred: " & e.ToString())
Finally
GetBehindList =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Try
End Function

Page_Load

'Fill the Company DropDown Lists
CompanyList.DataSource = GetCompanyList()
CompanyList.DataTextField = "Company"
CompanyList.DataValueField = "Company"
CompanyList.DataBind()
CompanyList.Items.Insert(0, New ListItem(vCompany,
vCompany))
GetCompanyList.Close()

---------------------------------------------------------------------------------------------------

*** Method 2 ***
Function GetCompanyList() As SqlDataReader
'Populate Drop Down Lists
Dim myConn As New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("SELECT Company from Offices",
myConn)
Try
myConn.Open()
GetCompanyList =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Catch e As Exception
'Catch the error and display it.
Response.Write("An Error Occurred: " & e.ToString())
Finally
myCommand.Dispose()
End Try
End Function

Page_Load

'Fill the Company DropDown Lists
CompanyList.DataSource = GetCompanyList()
CompanyList.DataTextField = "Company"
CompanyList.DataValueField = "Company"
CompanyList.DataBind()
CompanyList.Items.Insert(0, New ListItem(vCompany,
vCompany))
GetCompanyList.Close()

 

[Scott M.]

You really shouldn't be passing a DataReader at all since it represents an
open (live) connection to your data source. Although you have the
CommandBehavior.CloseConnection set, the connection won't get closed until
you close your DataReader, meaning that until you have finished using the
DataReader, the connection will stay open. Because you are passing the
DataReader, it will not get closed as soon as it could get closed.

If i were you, I would make a separate data class that exposes methods that
return whatever data you need in, perhaps, XML or a DataTable form. Then
you can bind your UI control to that data.