ExecuteSqlDataReader Module1 (VB.NET)

Imports System.Data.SqlClient
Imports System.Runtime.Serialization

Module Module1

    Sub Main()
        ' not using any Params for this example but left it in
        Dim Params As New List(Of Parameter)()
        ' Get our first list of data
        Dim testlist1 = New List(Of TestClass_1)()
        testlist1 = ExecuteSqlDataReader(Of TestClass_1)("<your SQL query here>", "<your SQL connection here>", Params)
        ' Get our second list of data
        Dim testlist2 = New List(Of TestClass_2)()
        testlist2 = ExecuteSqlDataReader(Of TestClass_2)("<your SQL query here>", "<your SQL connection here>", Params)
    End Sub

    ''' <summary>
    '''  Generic function that uses SqlClient to connect and retrieve data from a DB
    ''' </summary>
    ''' <typeparam name="T"></typeparam>
    ''' <param name="SQLText"></param>
    ''' <param name="SQLConnectionStrong"></param>
    ''' <param name="params"></param>
    ''' <returns>Return a collection of T></returns>
    ''' <remarks></remarks>
    Public Function ExecuteSqlDataReader(Of T)(SQLText As String, SQLConnectionStrong As String, params As List(Of Parameter)) As List(Of T)

        Dim ListOfT As List(Of T) = New List(Of T)

        Using SQLcon = New SqlConnection(SQLConnectionStrong)

            Using SQLCMD As New SqlCommand(SQLText, SQLcon)

                SQLCMD.CommandText = SQLText          ' set the SQL command (SP or in line SQL)
                SQLCMD.CommandType = CommandType.Text       ' set the command type (StoredProcedure or Text)
                'SQLCMD.CommandTimeout = mySQLSection.CommandTimeout ' set the command time out

                For Each p In params
                    SQLCMD.Parameters.AddWithValue(p.Name, p.Value)
                Next

                If SQLcon.State = ConnectionState.Closed Then
                    SQLcon.Open() ' open the connection
                End If

                Using myReader As SqlDataReader = SQLCMD.ExecuteReader()                     ' create the Data Reader and get the Data

                    Dim dt As DataTable = myReader.GetSchemaTable()         ' Get the Table Schema (Needed to identify each column type)

                    Do While myReader.Read()        ' for each record in the data set

                        Dim tmp = CreateInstanceOf_T(Of T)() ' create an instance of T
                        Dim index As Integer = 0    ' reset the column indexer

                        For Each row As DataRow In dt.Rows                              ' for each column in the table

                            Dim v = GetType(T).GetProperty(row(0).ToString())    ' get the type
                            Try
                                If v <> Nothing Then

                                    Select Case DirectCast(v.PropertyType, System.Type).FullName ' identify the Column data type and convert the contents
                                        Case "System.Int32"
                                            GetType(T).GetProperty(row(0).ToString()).SetValue(tmp, myReader.GetInt32(index))       ' to Int32
                                        Case "System.Guid"
                                            GetType(T).GetProperty(row(0).ToString()).SetValue(tmp, myReader.GetGuid(index))        ' to Guid
                                        Case "System.String"
                                            If Not IsDBNull(myReader(index)) Then
                                                GetType(T).GetProperty(row(0).ToString()).SetValue(tmp, myReader.GetString(index))      ' to String
                                            End If
                                        Case "System.Boolean"
                                            GetType(T).GetProperty(row(0).ToString()).SetValue(tmp, myReader.GetBoolean(index))     ' to Boolean
                                        Case "System.Decimal"
                                            GetType(T).GetProperty(row(0).ToString()).SetValue(tmp, myReader.GetDecimal(index))     ' to Decimal
                                            ' todo :: complete the convertion for oter types
                                        Case Else
                                    End Select

                                End If

                            Catch ex As Exception
                                ' todo :: should log any errors here
                            End Try
                            index = index + 1   ' increment the column indexer
                        Next
                        ListOfT.Add(tmp)        ' add the temp object (T) to the ListOfT
                    Loop
                End Using
            End Using

        End Using

        Return ListOfT      ' return the list of T objects

    End Function

    Public Function CreateInstanceOf_T(Of T)() As T
        Dim tmp As T = GetType(T).GetConstructor(New System.Type() {}).Invoke(New Object() {})
        Return tmp
    End Function

End Module