DataAdapter: Is it possible to fill a Collection-Type instead of a DataTable/DataSet?

Question

Hello,

this is more a theoretical question i asked myself. I remembered that BinarySearch of an ordered List(Collection in general) is faster than finding Rows with Datatable.Rows.Find or DataTable.FindByPK with a primary key value.

Hence i fill a Datatable from Database in a shared constructor and immediately after that a List(of Int32) that contains all primary keys from that table. Later i will check with BinarySearch if the List contains primary-key values. But because the datatable contains only the PK-Column anyway, i asked myself if there is a way to avoid the huge overhead of filling a Datatable and after that adding all Rows to a List. Is it possible to fill a generic List(or other collection-type) instead of a Datatable/Dataset directly from a Dataadapter? Maybe i'm off the track and there is another way to avoid the Extra-Loop that i'm missing.

The code of filling the DataTable in a strong typed Dataset and the List:

   Private Shared w205CorrectSWUpgrades As New List(Of Int32)

   Shared Sub New()
        Dim da As New dsDatabaseTableAdapters.W205SWUpgradesTableAdapter
        For Each row As dsDatabase.W205SWUpgradesRow In da.Get_W205CorrectSWUpgrades.Rows
           w205CorrectSWUpgrades.Add(row.idData)
        Next
    End Sub

---

UPDATE: For the sake of completeness my solution (thanks TheCloudlessSky): Because DataAdapter itself uses a DataReader to fill a Datatable or a Dataset, the best way was to extend the (from VS) generated partial Class of the DataAdapter with a new function that returns the List(of Int32) filled directly from the database. Remember that this partial class must be in an other file than the generated Class, otherwise your sourcecode will be overwritten on changes in the Dataset. Also remember that it must be in the same Namespace(ends with TableAdapters) and of course has the same name.

 Namespace dsDatabaseTableAdapters
    Partial Public Class W205SWUpgradesTableAdapter

        Public Function GetListOfW205CorrectSWUpgrades() As System.Collections.Generic.List(Of System.Int32)
            Dim list As New System.Collections.Generic.List(Of System.Int32)
            Dim command As System.Data.SqlClient.SqlCommand = Me.CommandCollection(0)   

            Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
            Try
               If ((command.Connection.State And Global.System.Data.ConnectionState.Open) _
                        <> Global.System.Data.ConnectionState.Open) Then
                   command.Connection.Open()
               End If
               Using reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader
                   While reader.Read
                      list.Add(reader.GetInt32(0))
                   End While
               End Using
            Finally
                If (previousConnectionState = System.Data.ConnectionState.Closed) Then
                    command.Connection.Close()
                End If
            End Try

            Return list
        End Function

    End Class
End Namespace

Now the business logic and the data access layer are still strictly separated (in separate projects):

    Private Shared w205CorrectSWUpgrades As List(Of Int32)

    Shared Sub New()
        Dim da As New dsDatabaseTableAdapters.W205SWUpgradesTableAdapter
        w205CorrectSWUpgrades = da.GetListOfW205CorrectSWUpgrades
    End Sub

Answer 1

Why don't you use a DataReader instead since this is pretty trivial? In C# you'd do the following:

List<int> primaryKeys = new List<int>();

using (SqlConnection conn = new SqlConnection("your connection string"))
{
    SqlCommand command = new SqlCommand("SELECT Id FROM Table", conn);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Loop through each record.
        while (reader.Read())
        {
            primaryKeys.Add(reader.GetInt32(0));
        }
    }
}