Filling dynamic Objects using ADO.NET

Sometimes you need to use information of different types like strings, booleans or doubles in the same data structure. In that case you would like to be able to create your own objects to fulfill your needs. But if you had to create 5 thousand different objects you wouldn't is too happy to do that by coding each one of them in a static way. You would like to fill the data in each one of the objects dynamically and most probably using databases.

If you create your own object and you can fill it with the data you want, then you are ready to get your job done. The use of the OleDbDataReader is sometimes better than using OleDbDataAdapter because you have better control of what you are doing. Follow the next steps and enjoy using your own data types.

Step # 1: Add the following statement at the top of your source code

Step # 2: Declare the following things you will use to read data from a database

Dim cmd As New OleDbCommand
Dim reader As OleDbDataReader

Step # 3 : Open the database connection

myConnection.Open()

Step # 4:Configure the OleDbCommand properties

cmd.Connection = myConnection
cmd.CommandText = "SELECT A, B, C, D FROMaTable WHERE A = Yes"

Step # 5:Instantiate the reader with the data returned by the

OleDbCommandreader = cmd.ExecuteReader()

Step # 6:Create an array of objects of the class you defined

Imports System
Namespace myNamespace
Public Class [myClass]
Public A As Integer
Public B As [String]
Public C As Boolean
Public D As Boolean
Public Sub New()
A = 0
B = "0"
C = False
D = False
End Sub 'New
End Class '[myClass]
End Namespace 'myNamespace
Dim arrMyClass(150) As [myClass]

Step # 7: Loop through the OleDbDataReader to get the information you need and fill the object array

Dim row As Integer = 0
While reader.Read()
Dim getA As Integer = reader.GetInt32(0)
Dim getB As [String] = reader.GetString(1)
Dim getC As Boolean = reader.GetBoolean(2)
Dim getD As Boolean = reader.GetBoolean(3)
arrMyClass(row).A = getA
arrMyClass(row).B = getB
arrMyClass(row).C = getC
arrMyClass(row).D = getD
row += 1 ' Counts how many rows are being read
End While
reader.Close() ' It is important to close the OleDbDataReader