I am currently playing around with VB.Net using Visual Studio 2010 (.Net 4.0) and I need your help to create a collection class (let's call it 'cl_MyCollection') with a constructor that accepts two arguments:
- An
SqlDataReaderobject - Some means of pointing to a custom class I have created
The scenario is; I have five database tables in SQL 2000 and five VB.Net custom classes.
Each custom class contains a variable for each field of the table the class represents.
For example: "tbl_User" has 2 fields "User_ID" and "User_Name" and tbl_Family" has 3 fields "Family_ID", "Family_Name" and "Family_Location".
Here is the VB.Net custom class I have created intended to read and store a single record of the "tbl_User" table:
Imports System.Data.SqlClient
Public Class cl_table_User
Property User_ID As Integer
Property User_Name As String
Public Sub New(ByRef rdr As SqlDataReader)
Me.User_ID = rdr("User_ID)
Me.User_Name = rdr("User_Name")
End Sub
End Class
I have created similar classes for the other database tables.
The idea is to perform an SQL statement and use the results to instantiate my intended collection class ('cl_MyCollection'). I was thinking something like this: ('conn' is an 'SqlConnection' object)
Dim cmd As SqlCommand = New SqlCommand("Select * From tbl_Users", conn)
Dim rdr As SqlDataReader = cmd.ExecuteReader()
Dim aCollection as new cl_MyCollection(rdr, "tbl_Users")
and later when I want to use "tbl_Family":
Dim cmd As SqlCommand = New SqlCommand("Select * From tbl_Family", conn)
Dim rdr As SqlDataReader = cmd.ExecuteReader()
Dim aCollection as new cl_MyCollection(rdr, "tbl_Family")
As you can see the third line is passing two arguments, the SQLDataReader and some means of pointing to a custom class I have created. The constructor of this class will then iterate through the results and create an object of the custom class I created.
I want the second argument to (somehow) make it decide which custom class to use. The 'somehow' is why I am here asking for your help.
The only way I can think this will work is by setting up a select case and list each custom class and then create an object of the custom class but I am hoping there is a better way. Currently I have it set up like this:
Public Class cl_MyCollection
Private records As New Collection
Public Sub New(rdr As SqlDataReader, selectedClass as string)
Select Case selectedClass
Case "tbl_User"
While (rdr.Read())
Dim thisRecord as new cl_table_User(rdr)
records.Add(thisRecord)
End While
Case "tbl_Family"
While (rdr.Read())
dim thisRecord as new cl_table_Family(rdr)
records.Add(thisRecord)
End While
End Select
End Sub
End Class
Ideally I would like the following code to work, or at least the idea it represents:
Public Class cl_MyCollection
Private records As New Collection
Public Sub New(rdr As SqlDataReader, selectedClass as TYPE)
While (rdr.Read())
Dim thisRecord as new selectedClass(rdr)
records.Add(thisRecord)
End While
End Sub
End Class
Is this possible? or do I need to stick with the select case?
P.S Sorry for the length of this post. Edit: Fixed typo, the select case now points to the second argument ('selectedClass') as originally intended.
