Hello,
I am wondering about best practices for class hierarchies and using
ADO.Net, especially the DataSet update/delete commands and the data
relations... this needs to package/unpackage to xml for client/server
sending. The class properties pretty much correspond to database tables.
This is a pretty long question so thanks in advance for reading and
commenting!
Clinics class - collection of Clinic objects
Clinic
Guid
Name
(a few other properties)
Exams class - collection of Exam objects
Exam
Guid
CreateDate
CreateByUserGuid (User class)
SpecialistUserGuid (User class)
Captures class - collection of Captures
Capture
Guid
Date
CaptureTypeCode
etc.
Users class
User
Guid
Name
Email
In VB6 for the singular classes (Exam, Clinic, Capture), I used a
private adodb recordset fields variable (with the schema from the table)
to hold most of the variables, and the get and let methods getting and
letting from the rs field. eg,
private m_fields as ADODB.Fields
Public Property Get Name() As String
Name = m_fields("Name")
End Property
Friend Property Let Name(vData As String)
m_fields("Name") = vData
End Property
I also had an update method which updated the table directly from the
fields variable or sent in the values of the fields to an AddUpdate
stored proc. The collection classes (Exams, Clinics, Captures) also had
an update method which went through all the singular objects and called
that update method.
The main advantages of this was that having a recordset was more
efficient then having local variables for each property and having to
set all of them vs just setting a recordset. also clean and easy code,
and extensible by adding new fields to the db table. The disadvantage
was that each there was a lot of database hits to build the whole object
(even with lazy loading) - because as we get further building the
hierarchy, we can query for the same user guid for example. ie,
Exam instance #1 does a query for UserGuid 123
Exam instance #2 does a query for UserGuid 222
Exam instance #3 does another query for UserGuid 123
Exam instance #5 does another query for userGuid 222
So, to get to the heart of my question, I would like to use ado.net's
new features to do two things across class boundaries...
(1) from a collection class (Exams) with a dataset and add/update/delete
commands defined, to send individual datarows to each individual class
(exam), sort of like pass in the datarow by reference. Thus, I can do
an update from the collection class level (Exams) with those
add/update/delete commands. I would also like to be able to do an
add/update/delete from the individual class level too (Exam), because I
would like to be able to easily create a new Exam or whatever. Also, I
would like to package it up as xml and then open it on another computer
and save to db.
(2) use ado.net's data relation to reduce the amount of sql queries...
for example with my object model above, I would like to do one query for
all Clinics that meet a criteria, then one query for all the exams in
those clinics, then all the captures for those exams, then all users for
the exams and clinics, etc. if i had to do it in a similar way i id it
in vb6, i guess i could use some caching techniques and checking to see
if i have gotten that guid for a particlar class already?
Note that I don't want to do anything like use static varibles to hold
DataSets with relations, because I want to be able to have multiple
instances obstantiated without interfering with each other.
Any ideas, comments, pointers or references highly appreciated... most
of the ado.net books and articles seem to be for a more two tiered
approach with using ado.net in the presentation layer, i would like to a
more n-tiered approach.
many thanks,
-ed
