I'm a bit skeptical whether to go full OO on a application I'm
designing.

Keep in mind, this is just an example.

Basically, I have a User class. This class loads/saves a user.

Method 1
In this user class, there is a static method that retrieves all users
in the database as a datatable/sqldatareader.

Method 2
In this user class namespace, there is another class called
UserCollectionClass. User class has a static method that gets all users
in the database. But instead of returning a datatable/sqldatareader, it
returns a UserCollectionClass containing each user as a user class.


Which method is better for performance,maintainbility? Which is better
for data binding? Will there be a problem returning UserCollectionClass
as the collection gets bigger vs datatable?

If I go method 2, should the User class be INSIDE the
UserCollectionClass?? Also, should the GetUsers method be in the User
class or UserCollectionClass?

Also that using method 2 will probably result in 2 SQL HITS.

For an example,
the first hit will get the "user ids" and return them to a
sqldatareader. The reader would loop through the user ids and intialize
each User class (SECOND hit) and add to the collection.

You want 3 different classes.


class User

class UserCollection

...

then, you want a third class.


class UserMaker (or soemthing like that , I call mine UserController , the
name doesn't matter that much)


User encapsulates the properties, methods and (sometimes) events of a User
UserCollection is a CollectionBase ( dot net 1.1) of User's.  Look at
generics if you're in 2.0

UserMaker is a biz layer object.

It will contain a private method, which will take a IDataReader as the
argument, and return a UserCollection.


Here is the code where I create a PubsTitle (which is a title entry .. in
the pubs database)

PubsTitle equates to User in your scenario.
PubsTitleColllection equates to UserCollection in your scenario.

Fyi, Titles.Layouts.TitlesAllLayout.XXXXXXXXXX
are just constants referring to the ordinal position on the ResultSet.




// this is private, because you reuse this method ......
private BusinessObjectCollections.PubsTitleCollection
SerializeTitles(IDataReader dataReader)
{
BusinessObjects.PubsTitle item = new BusinessObjects.PubsTitle();
BusinessObjectCollections.PubsTitleCollection coll = new
BusinessObjectCollections.PubsTitleCollection();
try {
   while (dataReader.Read()) {
     if (!(dataReader.IsDBNull(Titles.Layouts.TitlesAllLayout.TITLEID))) {
       item = new
BusinessObjects.PubsTitle(dataReader.GetString(Titles.Layouts.TitlesAllLayou
t.TITLEID), dataReader.GetString(Titles.Layouts.TitlesAllLayout.PUBID),
dataReader.GetString(Titles.Layouts.TitlesAllLayout.PUBNAME));
       if (!(dataReader.IsDBNull(Titles.Layouts.TitlesAllLayout.TITLE))) {
         item.Title =
dataReader.GetString(Titles.Layouts.TitlesAllLayout.TITLE);
       }

       coll.Add(item);
     }
   }
     return coll;
} finally {
   if (!((dataReader == null))) {
     try {
       dataReader.Close();
     } catch {
     }
   }
}
}


By doing it this way, you can populate the IDataReader with
1 User
10 Users
100 Users
(aka, it doesn't matter, you'll always get a UserCollection back)



The biz layer will have multipe methods, here is a sample which gets a
single Title, based in the TitleID

public BusinessLogic.BusinessObjects.PubsTitle GetSingleTitle(string
titleId)
{
try {
   Data.Titles.PubsTitlesData dataObject = new Data.Titles.PubsTitlesData();
   BusinessLogic.BusinessObjects.PubsTitle returnValue=null;
     returnValue =
this.SerializeTitles(dataObject.GetSingleTitleDataReader(titleId)[0];
//notice I use the indexer to return the first item of the collection, since
I expect 1 title
   }
   return returnValue;
} finally {
}
}

public BusinessLogic.BusinessObjects.PubsTitle GetTitlesAll()
{
try {
   Data.Titles.PubsTitlesData dataObject = new Data.Titles.PubsTitlesData();
   BusinessObjectCollections.PubsTitleCollection returnValue=null;
     returnValue = this.SerializeTitles(dataObject.GetAllTitlesDataReader();
   }
   return returnValue;
} finally {
}
}

You can have several of these methods.

The object not defined is the
Data.Titles.PubsTitlesData

this is the datalayer object, which returns IDataReaders. (it also returns
DataSets, scalars, and voids, but those aren't germane to this discussion)


If you want a single User (or title from my example), then create an
IDataReader with 1 User in it.
If you want all Users, then create (in the datalayer) an IDataReader with
all Users in it.
If you want all Users with Lastname Of "Smith", then create an IDataReader
with Smith Users in it.
(you get the picture... you create an IDataReader for whatever need you
have)

...

The biz method will call the DataLayer, to get the appropriate IDataReader.

It will pass in into the generic "SerializeTitles" method, which will return
a Collection (UserCollection in your case) of User's.



see also
http://groups.google.com/group/microsoft.public.dotnet.framework.aspnet/browse_thread/thread/3d5e9b7539d5c033/b6af85a2e6593dfa?lnk=st&q=IDataReader+idr&
rnum=2&hl=en#b6af85a2e6593dfa



By building a generic datalayer, you can switch databases without too much
heartache.... because any db can get a IDataReader created against it.

You biz layer uses IDataReaders, to loop over and create your business
objects and objectCollections.


This is a tried and true method for me.  I can't imagine ever going back.


............




<vze1r***@verizon.net> wrote in message
Show quoteHide quote

As far as "multiple sql statements" or "multiple resultsets".


You should famaliarize yourself with the

     IDataReader.NextResult();  method

The code below responds to two select statements inside a stored procedure.
(see farther down for the tsql stored procedure)

I use a "deep" boolean to signal to check for the second resultset.


private BusinessObjectCollections.PubsTitleCollection
SerializeTitles(IDataReader dataReader, bool deep)


{
BusinessObjects.PubsTitle item = new BusinessObjects.PubsTitle();
BusinessObjectCollections.PubsTitleCollection coll = new
BusinessObjectCollections.PubsTitleCollection();
try {
   while (dataReader.Read()) {
     if (!(dataReader.IsDBNull(Titles.Layouts.TitlesAllLayout.TITLEID))) {
       item = new
BusinessObjects.PubsTitle(dataReader.GetString(Titles.Layouts.TitlesAllLayou
t.TITLEID), dataReader.GetString(Titles.Layouts.TitlesAllLayout.PUBID),
dataReader.GetString(Titles.Layouts.TitlesAllLayout.PUBNAME));
       if (!(dataReader.IsDBNull(Titles.Layouts.TitlesAllLayout.TITLE))) {
         item.Title =
dataReader.GetString(Titles.Layouts.TitlesAllLayout.TITLE);
       }

       coll.Add(item);
     }
   }

///////////////////  Here we go, lets check the 2nd ResultSet
   if ((deep)) {
     dataReader.NextResult();
     BusinessObjectCollections.PublisherCollection allPubs = new
BusinessObjectCollections.PublisherCollection();
     while (dataReader.Read()) {
       if
(!(dataReader.IsDBNull(Publishers.Layouts.PublisherDefaultLayout.PUBID))) {
         BusinessObjects.Publisher currentpub = new
BusinessObjects.Publisher(dataReader.GetString(Publishers.Layouts.PublisherD
efaultLayout.PUBID),
dataReader.GetString(Publishers.Layouts.PublisherDefaultLayout.PUBNAME));
         allPubs.Add(currentpub);
       }
     }
     foreach (BusinessObjects.PubsTitle titleItem in coll) {
       titleItem.AllPublishers = allPubs;  /////// the PubsTitle objects, as
a property called "AllPublishers", which is a CollectionBase of Publishers
(or PublisherCollection )
     }
   }
   return coll;
} finally {
   if (!((dataReader == null))) {
     try {
       dataReader.Close();
     } catch {
     }
   }
}
}





if exists (select * from sysobjects
where id = object_id('uspTitleGetSingleForId') and sysstat & 0xf = 4)
BEGIN
  drop procedure dbo.uspTitleGetSingleForId
END
GO

--  uspTitleGetSingleForId 'PS3333'

CREATE  PROCEDURE dbo.uspTitleGetSingleForId  (
@title_id varchar(6)
)

AS

SET NOCOUNT ON



select
title_id     ,
pub_id   ,
title

from
titles WITH (NOLOCK)
WHERE
LTRIM(RTRIM(UPPER(title_id))) = LTRIM(RTRIM(UPPER(@title_id))) -- this is a
sucky way, but unforunately, the primarykey of a title ,... is a string
value
ORDER BY
title




select
pub_id ,
pub_name
from
publishers WITH (NOLOCK)
ORDER BY
pub_name


SET NOCOUNT OFF

GO


GRANT EXECUTE on dbo.uspTitleGetSingleForId TO pubsuser

GO



If you find this info useful, please post a "thank you".







Show quoteHide quoteBusinessObjects.PubsTitle(dataReader.GetString(Titles.Layouts.TitlesAllLayou
Show quoteHide quoteData.Titles.PubsTitlesData();
Show quoteHide quoteData.Titles.PubsTitlesData();
this.SerializeTitles(dataObject.GetAllTitlesDataReader();
Show quoteHide quotehttp://groups.google.com/group/microsoft.public.dotnet.framework.aspnet/browse_thread/thread/3d5e9b7539d5c033/b6af85a2e6593dfa?lnk=st&q=IDataReader+idr&
Show quoteHide quote

Very interesting!!!!

Now, wont this create a performance hit because of all the object
creation etc?

Basically is returning 100 user records in a datatable or 100 records
as a UserCollection object faster?

You should test it yourself.....

In my case:
My TitlesCollection objects **always** out perform a strongly typed DataSet
....... by a large percentage.
I haven't tested a DataTable object.

But you won't know until you test it yourself.

But I think you'll be very surprised by the results.


Object Creation time ( compared to Database connectivity time)  is
miniscule.

And remember, just because there's a nice wrapper function from Microsoft,
they are doing something under the covers.
Aka, a DataTable has to be populated somehow.  MS isn't using magic fairies,
they use code like most of us do.
And sometimes, very few sometimes, but sometimes, writing your own stuff is
faster, because you're not dealing with 1,000 "what ifs" like Microsoft has
to do, with things like
filling DataSets, etc.

http://dotnet.di.unipi.it/Content/sscli/docs/doxygen/fx/bcl/classes.html
That's not a great one, but you can still kinda see the code.

While I'm not a (production code) java user or fan, when I used java in grad
school, it was interesting to be able to look at the code of the methods of
the java-framework.


...



<vze1r***@verizon.net> wrote in message
Show quoteHide quote

Ok great. Thank you for showing me the light. I will use your
methodlogy from now on.


Another question I had was naming conventions in SQL 2000.

Currently I prefix my columns like
User ID would be int_userid_fk or int_userid_pk
User Name would be var_username

etc

I seen Microsoft do UserID as UserID

What naming convention should I use?

You need to post to:
microsoft.public.sqlserver.programming

...


<vze1r***@verizon.net> wrote in message
Show quoteHide quote

Microsoft's recommendation is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconnamingguidelines.asp.
You appear to be using a variant of Hungarian notation which is no longer
the prefered convention. In general, use Pascal casing in most cases and
camel casing in parameters. In addition, do not preface the variable with
the type.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

Hi sloan,

Your solution seems really nice, I was alreaddy thinking of using such an
approach, so your experience is really helpfull to me. Thanks a lot!

Just one question: How do you do inserts/updates/deletes? Also via such a
Maker/Controller-class? Or do you put them inside the objects?

Thanks a lot,

Pieter

Plugin System Recommendations
Detect if compiling as a console application
Nested iterator problem
Connection getting closed in between.
.net threads