Fill a List(of T) from a Reader

I’ve come to love List(of T).  It makes it so much easier to work with data.  Here’s a simple overview of working with data in a list.  The following code is vb.Net, but can be easily converted to C# using the telerik converter, or developerFusion.

1. Create the list class

This is just a simple definition that should reflect an existing table in your database.  Add as many fields of varying types as you’d like.  In this use case the fields and field datatypes must match the table. Every field from the table should be include in the list class.

Public Class MyList_Info
  ' local property declarations
  Property MyListID As Int64
  Property MyListName As String
       
  ' initialization
  Public Sub New()
  End Sub
End Class

2.Build the database support class

In this class I’m storing the connection string in the app.config, but it can also be stored in the web.config, or hard coded into the class if you really want.

This class makes the call to the database and returns the database command object.

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Reflection

Public Class DB_Support
#Region "Properties"
  Property DBConnString As String = ""
#End Region

#Region "Constructors"
  Public Sub New()
    Dim app As New AppSettingsReader()
    DBConnString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
  End Sub
#End Region

#Region "Helpers"
  Function GetCom(sql) As SqlCommand
    ' create a DB connection
    Dim con As SqlConnection = New SqlConnection(DBConnString)
    con.Open()

    ' the query to grab all the files.
    Dim sqlString As String = sql
    Dim com As SqlCommand = con.CreateCommand()
    com.CommandText = sqlString

    Return com
  End Function
#End Region

3. Build the business layer class

In this class we’re calling the database to fill our list with the specific data that we want.

Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Public Class MyListClass
Public Function Get_MyList_All () As List(Of MyList_Info)
  Try
    Dim db As New DB_Support
    Dim reader As IDataReader
    Dim oMyList As New List(Of MyList_Info)

    reader = CType(SqlHelper.ExecuteReader(db.DBConnString, CommandType.Text, "Select * from MyListTable"), IDataReader)

    oMyList = db.ReaderToList(Of MyList_Info)(reader)

    Return oMyList
  Catch ex As Exception              
    Return Nothing
  End Try
End Function
End Class

4. Build the Reader To List function

Add the following function to the DB_Support class.

Function ReaderToList(Of T)(ByVal dr As IDataReader) As List(Of T)
  Try
    Dim list As New List(Of T)
    Dim obj As T

    While dr.Read()
      obj = Activator.CreateInstance(Of T)()

      For Each prop As PropertyInfo In obj.GetType().GetProperties()
        Try
          If Not Object.Equals(dr(prop.Name), DBNull.Value) Then
            prop.SetValue(obj, dr(prop.Name), Nothing)
          End If
        Catch ex As Exception
          'property doesn't exist in reader, so skip it
        End Try
      Next
      
      list.Add(obj)
    End While

    Return list
  Catch ex As Exception
    Return Nothing
  End Try
End Function

4. Call the Get_MyList_All function

In your application, or website, call the Get_MyList_All function and fill a list with it.  Without being able to account for the specifics of your application here’s the general way to do it.

Dim dbMyList As List(Of MyList_Info) = MyListClass.Get_MyList_All()

Now you should be able to filter, sort, display, and run all kinds of tests against the dbMyList List.