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
