Hi Ben,
I've just realised what is going on here - you are talking about a web
app! So we have been talking cross-purposes for some time. Our BL/DAL code
is primarily targeted at WinForms, so the notion of caching transactional
tables would not be of the same significance in our case. It does make a
lot of sense for WebForms. Although you would need to keep the cache
synchronised with Db, how do you manage inserts/updates?
Caching static tables works well in our case as well.
As far as unions - that is a good idea. As long as the 'Add' method checks
for duplicates the concept would work quite well. Simplicity comes at the
expense of fetching some extra rows from Db, of course, but in some cases
that's a small price to pay.
However, I am not giving up yet on the Query Object pattern - Frans' post
gave me a lot to think about.
Cheers,
Roman.
On Fri, 8 Oct 2004 17:10:22 +1000, Ben Kloosterman
<[EMAIL PROTECTED]> wrote:
>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
===================================
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
