Thanks Ben. I never thought to cache data in BL/DAL based on query. For me it has always been the UI responsible for persisting relevant data, be it WebForms Cache or local form variable. I was so used to doing it I never even gave it a second thought.
I agree that it makes a lot more sense to do it your way - at the end of the day UI should not know where the data is coming from. Then again, an optional flag could tell the DAL to force reload from db. In our case we must implement Object Query - long story, business requerement. However, I see these two ideas complementing each other; there is no conflict. What strategy do you use to keep the cache in sync with the database, especially in the context of a Winforms App? Cheers, Roman. On Mon, 11 Oct 2004 17:16:18 +1000, Ben Kloosterman <[EMAIL PROTECTED]> wrote: >Not necessarily web apps but 3+ tier apps .... The cached tables reside >on the mid Tier. > >IMHO The cost of implementing such an O/R layer would only be worth it >on a large app and most of them are n tier . > >Ben > >> -----Original Message----- >> From: Unmoderated discussion of advanced .NET topics. >[mailto:ADVANCED- >> [EMAIL PROTECTED] On Behalf Of Roman Antchik >> Sent: Monday, 11 October 2004 10:02 AM >> To: [EMAIL PROTECTED] >> Subject: Re: [ADVANCED-DOTNET] Abstracting SQL >> >> 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(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(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
