If you're using Sql Server, you can return multiple "ResultSets" from one
database call.
For Simplicity, I will use 3 Select Statements.
Select PatientID, PName from dbo.Patient
Select SampleID, PatientID, SampleName from dbo.Sample
Select SasID, SampleID, SasName from dbo.Sas
Ok.
You're looking for an OO way.
Create 3 classes. These will mimick your database Entities. (Actually,
your db mimicks your objects, but you understand what I'm saying)
Familarize yourself with the IDataReader object.
The way I do it is:
I create a stored procedure, which returns the 3 resultsets above. Then I
loop on them, with an IDataReader, to "Serialize" my data, into concrete
objects.
It goes something like this.
private object SerializeIDataReader( IDataReader idr )
{
while (idr.Read)
{
string patientName = idr.GetString(0); // where 0 is the ordinal number
of the select
Patient p = new Patient (patientName);
}
idr.NextResult();
while (idr.Read)
{
string sampName = idr.GetString(0);
Sample sam = new Sample(sampName);
}
idr.NextResult();
while (idr.Read)
{
string sasName = idr.GetString(0);
Sas sa = new Sas(sasName);
}
}
return someObjectWhichWrapsUpAllTheseThings;
}
That's a very abbreviated, and buggy code sample. But you get the point.
someObjectWhichWrapsUpAllTheseThings is something you write up.
The goal of SerializeIDataReader is that it can take any IDataReader.
Perhaps you create an IDataReader for 1 Patient, 100 Patients. Now, you'll
always have to have 3 ResultSets, but it gives you some flexibility.
..
Additional, you want to check out the EnterpriseLibrary
http://msdn.microsoft.com/library/de...tml/entlib.asp
This bundles up alot of database access stuff. Without reinventing the
wheel.
You saw I used IDataReader, the interface, and not any concrete
implmentation. This is so you can switch out your backend database, and not
really affect your code ... which takes an IDataReader and turns it into
concrete objects.
...
I don't know your setup exactly, but let me go with this generic sample.
Customer
Order
OrderDetails
In this situation, I'd have these 3 objects. I'd also have a collection for
each one.
CustomerCollection : CollectionBase
OrderCollection : CollectionBase
OrderDetailsCollection : CollectionBase
Customer
string CustomerName
OrderCollection AllOrdersForThisCustomer
Order
DateTime OrderDate
OrderDetailsCollection AllOrderDetailsForThisOrder
My stored procedure would have:
Select CustomerID, CustomerName from Customer
Select OrderID, CustomerID, OrderDate from Orders
Select OrderDetailID, OrderID from OrderDetails.
My serializer would do this:
1. Create a new CustomerCollection
2. Loop over the first ResultSet, creating Customer objects, and then
adding them to the CustomerCollection
3. Loop over the second ResultSet, creating Order objects, and adding them
to the specific Customer (matching up CustomerID)
4. Loop over the third ResultSet, creating OrderDetail objects, adding them
to the correct Order object.
5. return the CustomerCollection, which now has all data in it.
Because I wrote a generic serializer, I could use multiple IDataReaders.
What I mean , is that I could populate one IDataReader for All Customers,
and then another one for a Single Customer.
...
This takes some work. Sometimes a defer to the "poor mans" business object
of using a strongly typed dataset. But more recently, I opt for the OO
method.
...
see
http://spaces.msn.com/sholliday/ for a few tidbits. you may want to use the
freebie sql server 2000, called msde.
"Tarun Mistry" <no****@nospam.com> wrote in message
news:ea**************@TK2MSFTNGP10.phx.gbl...
Hi all, I have posted this in both the c# and asp.net groups as it applies
to both (apologies if it breaks some group rules).
I am making a web app in asp.net using c#. This is the first fully OO
application I will be making, also my first .NET application, so im
looking for any help and guidance.
Ok, my problems are todo with object and database abstraction, what should
i do.
I have defined certain objects my system will need, they are all linked.
Lets say these are
Patient --- Sample --- Sas
An Sas contains a Sample and a Sample contains a Patient. A patient can
exist on its own however a Sample needs a valid patient and an Sas needs a
valid sample.
At the database level i simply store 3 tables, one for each of the
classes. However, what is the procedure for loading my data back into objects
before I use them? I know I can make a static method within the class that loads
data, creates an object and returns it (although I was told this wasnt
good) or to use Data Access Objects (DAO)s as helper classes for my main classes
that do this work, again a good idea but i cant see it providing a
solution to one of my problems, see 2 below.
A few scenarios that are valid within the system:
1) I simply want to search the patients within the system. I could search
by patient parameters such as name and patientid. Will the patient class need
to contain code that performs this SQL query and returns an array of
patient objects, alternatively do I use a separate DAO (Database Access Object )
to perform the patient queries? Now I need 2 classes to handle my patients
(and potentially 2 for Samples and 2 for Sas's etc etc).
2) I may want to search for all the samples created within a given date.
Remember each sample contains a patient. The actual SQL for this is
simple, I just need to perform a join, but where is the member function stored
that performs this SQL? How do i split the SQL to create Sample objects that
contain the correct patient objects?
3) The classes/objects will be viewed in a web form (i.e. i want to edit a
patient details, so I load the patient contents from the database into an
object which populates a web form) or in a pseudo data grid (i.e.
searching for a patient with the surname Smith and displaying all the results). I
didn't want to use a dataset as I would loose control over the appearance
of the search results/table displayed to the user. If I do use a dataset,
there is no need for an array of objects :S What am I to do?
These are the main issues im having developing the system. Coming from a
procedural PHP background, these issues seem very simple and easy to work
with, however being OO based makes them infinitely complex. The whole
running one query and the separate objects being created and nested
correctly it the real sticking point.
All advice and help appreciated. Please remember im a .net novice so if I
have missed a crucial facet of the technology, please do advice, i would
really appreciate it!
Thanks again, apologies for the long post.
Kind Regards
Taz
