Which is more efficient?

[NH]

Hi, I am building a windows forms app (vb.net, .net 2.0).

For basic loading of a combo box which of the two options below is best
practice or more efficient. The main difference is should you use a
datareader and load the results into a table or use a sqldataAdapter and fill
a dataTable?

OPTION 1:
Public Function GetData(ByVal commandText As String, ByVal connectionString
As String, ByVal commandType As CommandType) As DataTable
Using connection As New SqlConnection(connectionString)
connection.Open()
Using command As New SqlCommand(commandText, connection)
command.CommandType = commandType
Using reader As SqlDataReader = command.ExecuteReader()
Dim table As New DataTable
table.Load(reader)
Return table
End Using
End Using
End Using
End Function

Usage:
Me.ListBox1.DataSource = GetData("Select UnitID,Name from Unit", "connection
string", CommandType.Text)
Me.ListBox1.DisplayMember = "Name"
Me.ListBox1.ValueMember = "UnitID"

OPTION 2:
Dim myCmd1 As SqlCommand = myconn.CreateCommand
myCmd1.CommandText = "Select UnitID,Name from Unit"
myCmd1.Connection = myconn

Dim da As New SqlDataAdapter()
da.SelectCommand = myCmd1

Dim dt As New DataTable()
da.Fill(dt)

Me.cmbUnitID.DisplayMember = "Name"
Me.cmbUnitID.ValueMember = "UnitID"
Me.cmbUnitID.DataSource = dt

 

[Göran Andersson]

Hi, I am building a windows forms app (vb.net, .net 2.0).

For basic loading of a combo box which of the two options below is best
practice or more efficient. The main difference is should you use a
datareader and load the results into a table or use a sqldataAdapter and fill
a dataTable?

OPTION 1:
Public Function GetData(ByVal commandText As String, ByVal connectionString
As String, ByVal commandType As CommandType) As DataTable
Using connection As New SqlConnection(connectionString)
connection.Open()
Using command As New SqlCommand(commandText, connection)
command.CommandType = commandType
Using reader As SqlDataReader = command.ExecuteReader()
Dim table As New DataTable
table.Load(reader)
Return table
End Using
End Using
End Using
End Function

Usage:
Me.ListBox1.DataSource = GetData("Select UnitID,Name from Unit", "connection
string", CommandType.Text)
Me.ListBox1.DisplayMember = "Name"
Me.ListBox1.ValueMember = "UnitID"

OPTION 2:
Dim myCmd1 As SqlCommand = myconn.CreateCommand
myCmd1.CommandText = "Select UnitID,Name from Unit"
myCmd1.Connection = myconn

Dim da As New SqlDataAdapter()
da.SelectCommand = myCmd1

Dim dt As New DataTable()
da.Fill(dt)

Me.cmbUnitID.DisplayMember = "Name"
Me.cmbUnitID.ValueMember = "UnitID"
Me.cmbUnitID.DataSource = dt

I would expect the data adapter to use the Load method to load the data
into the table that it puts in the data set, so the difference between
the two methods is minimal.

If you want better performance, don't use a DataSet, don't use a
DataTable and don't use data binding. Read the data from the data reader
and create ListItem objects to put in the list box.

 

[Gregory A. Beamer]

Underneath the hood, the adapter uses a DataReader. Of the two, depending on
your algorithm, you might see slightly higher performance with the reader,
but it will be marginal.

Don't get performance myopia. While it is good to know more efficient
algorithms, you can get so caught up in them that you make a solution
completely unmaintainable.