Hi Roman,
The problem here is Datasets , I used to be a great fan but they create
poor designs and result in a tight DB Binding .. This is why you get
Complex difficult to maintain code and SQl . Great for small quick
projects though.
I now prefer Custom Collections and lazy initialisation , for < 100,000
Contacts you would keep all in memory ( Select * ) . For more than this
I would have something like this ..
ContacsCollection ContacsCollection.GetActiveWidgets ( string
widgetType , string widgetType2)
{
ContactsCollection contacts = new COntactsCollection();
// TODO check cache ..if (Cache["GetByWidgets"+ widgetType ] !=
null... )
Contacts.Add(DBClient.ContactsSchema.GetByWidgets(widgetType);
//uses data reader and simple select all contacts for that Widget via
inner join
Contacts.Add(DBClient.ContactsSchema.GetByWidgets(widgetType2);
// if and and instead of a unit do it in code
Return contacts;
}
2 transactions and a union would add negligible time but would use 2
simple , reusable queries .. less code , less complexity ...More
flexible . In addition on larger systems such simple queries are more
likely to be in cache and hence avoid a DB transaction altogether.
If Attributes is a small table... you can store the whole table in a
singleton ( note if it is used infrequently use a Select *) .
ContacsCollection ContacsCollection.GetActiveWidgets ( string
widgetType , string widgetType2)
{
ContactsCollection contacts = new ContactsCollection();
IList list AttributesCollection.GetByWidget(widgetType);
List.Add(AttributesCollection.GetByWidget(widgetType));
foreach ( Attribute attribute in list )
contacts.Add(attribute.Contact)
// if and and instead of a unit do it in code
Return contacts;
}
Now in the case of both tables being huge it gets messy but the idea is
to avoid complicated SQL and use simple reusable components.
The one exception to this is reports which will always be messy . I have
given up trying to get all reports working nicely and for some just use
painful SQL.
Ben
> -----Original Message-----
> From: Unmoderated discussion of advanced .NET topics.
[mailto:ADVANCED-
> [EMAIL PROTECTED] On Behalf Of Roman Antchik
> Sent: Friday, 8 October 2004 3:21 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [ADVANCED-DOTNET] Abstracting SQL
>
> >> Don't forget you can also put complicated and important
> >> tables in memory
> >> on middle tiers this allows you to scale to many users and
> >> makes the DB
> >> performance irrelevant. These middle tiers also allows your
> >> OO design
> >> to capture business logic that requires sorting , joins , groupings
,
> >> exclusions etc and allows you to have much simpler SQL . ( ie
just
> >> simple selects and updates). In this case your SQL grammar
> >> is so simple
> >> it will be DB independent.
>
> Thanks Ben. Unfortunately that is not an option:
>
> 1. Sheer volume of data.
>
> 2. Straightforward O/R mapper is not hard to implement - this is where
we
> stand now. It is the more complex stuff, like subqueries that got us
> stuck.
>
> Regardless, there is no support for subqueries in .NET framework
> DataTable / DataSet classes anyway.
>
> For example the only way to execute the query below - without
subqueries -
> would be to have two queries and a union.
>
> Extracting all contacts that either have Attribute 1 or Attribute 2
> (assuming there are three tables - Contacts, Attributes &
> ContactAttributes):
>
> SELECT * FROM Contacts
> WHERE EXISTS
> (
> SELECT * FROM ContactAttributes
> WHERE
> Contact.Id = ContactAttributes.ContactId
> AND
> ContactAttributes.AttributeId = <Some Value>
> )
> OR EXISTS
> (
> SELECT * FROM ContactAttributes
> WHERE
> Contact.Id = ContactAttributes.ContactId
> AND
> ContactAttributes.AttributeId = <Some Other Value>
> )
>
>
> Thanks,
> Roman.
>
> ===================================
> This list is hosted by DevelopMentor(r) http://www.develop.com
> Some .NET courses you may be interested in:
>
> Essential .NET: building applications and components with CSharp
> August 30 - September 3, in Los Angeles
> http://www.develop.com/courses/edotnet
>
> View archives and manage your subscription(s) at
> http://discuss.develop.com
===================================
This list is hosted by DevelopMentor� http://www.develop.com
Some .NET courses you may be interested in:
Essential .NET: building applications and components with CSharp
August 30 - September 3, in Los Angeles
http://www.develop.com/courses/edotnet
View archives and manage your subscription(s) at http://discuss.develop.com
