Thanks for your input Paul.. I'm currently building a DAL which I want to be able to transport between projects with no recoding required of the DAL, just configuration.. I've got it all working wonderfully, and is very fast too.. I've coupled this with a code generator which I point at a database (SQL or ORACLE) and can create stored procs (SQL or Oracle), model or entity classes (marked up with class and property decorations to work with the DAL) in the click of a few buttons. I'm also working on outputting forms, views, view model stubs, and listing and search pages for ASP.Net, WPF and MVC to start with..
The DAL can work with any app type really as it's just a DLL and the host app just needs some config settings to hook it all up. The idea of having updates through the DAL for the host app was something I was just thinking about.. I thought it would be a nice addition to the DAL you just hook up and database data changes are propagated back to the app for you.. It's looking like it may be too much trouble, or as you suggest, in the wrong layer of the app.. Maybe the following is a better solution.... - SQL Trigger on tables call a .Net assembly from Sql Server (see here<http://www.codeproject.com/KB/database/Managed_Code_in_SQL.aspx>) when data is modified. - The .Net assembly talks to a web service to notify of the db change. - Apps subscribe to the web service which is setup in the Observer pattern <http://en.wikipedia.org/wiki/Observer_pattern>. - The web service notifies the app of the change - The app responds to the change as it needs to.. again.. just thinking out aloud... On Tue, Mar 29, 2011 at 11:54 PM, Paul Stovell <[email protected]>wrote: > And note that System.Data.SqlClient.SqlDependency (which uses service > broker) is different to System.Web.Caching.SqlCacheDependency (which uses > triggers and polling, making it kind of useless): > > > > > http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency > > > > Paul > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Paul Stovell > *Sent:* Tuesday, 29 March 2011 11:50 PM > > *To:* ozDotNet > *Subject:* RE: Propagate Database changes to application > > > > This is what I was talking about (I think): > > > > http://msdn.microsoft.com/en-us/library/ms172133.aspx > > > > Personally I'd probably just poll unless my demand load suggested > notifications would work better. > > > > Paul > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Paul Stovell > *Sent:* Tuesday, 29 March 2011 11:45 PM > *To:* ozDotNet > *Subject:* RE: Propagate Database changes to application > > > > Correct, though once upon a time I did have an experiment that worked with > query notifications. From memory you attach some kind of SqlDependency > object to your SqlCommand (not the type that works with triggers - it used > the same code as clustered views from memory). > > > > There is a trade off either way you go. With a notification system you can > end up "drinking from a fire hose" if the events happen too frequently under > load (e.g., an Orders table probably isn't good for notifications). Systems > that rely on polling tend to scale better (for example: this little thing > called the internet) but that's a generalization. > > > > Out of curiosity, is this something that needs to happen at the database > level? Should more than one process really be writing to the database at > once? Could it be done using a messaging system above the database? > > > > Paul > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Grant Molloy > *Sent:* Tuesday, 29 March 2011 9:32 PM > *To:* ozDotNet > *Subject:* Re: Propagate Database changes to application > > > > Ok.. > > well it looks like bindable linq only propagates changes from what it's got > in memory already, not from changes in the database.. > > > > On Tue, Mar 29, 2011 at 9:26 PM, Grant Molloy <[email protected]> wrote: > > Hi Joseph, > > > > I wasn't really looking at any particular single client solution.. > > > > If Paul Stovell is listening, what did you use in Bindable Linq.. I am > downloading now to have a look, but thought you may be able to put into > words for larger audience.. > > > > thanks > > > > > > On Tue, Mar 29, 2011 at 9:59 AM, Joseph Cooney <[email protected]> > wrote: > > Re: client type - I was more wondering is it a web app? Windows app? > Silverlight? Windows intranet app could do udp broadcast of changes from the > server or something like that. > > Sent from my iPhone > > > On 29/03/2011, at 8:18 AM, Grant Molloy <[email protected]> wrote: > > Thanks everyone for feedback.. > > > > Joseph, > > SQL Server, yes, version either 2008 or 2008 R2.. More than likely R2, but > not nailed down yet. > > Client type is a DAL DLL.. I want the DAL to know when the data changes, > and the client will subscribe to DAL events, and then DAL can fire off event > when data is changed, and App can decide what to do about it. Kind of like > a publisher / subscriber model implementation. > > > > I'm not 100% sure I'm going about it the right way or have the events in > the right place (ie in the DAL).. > > > > On Tue, Mar 29, 2011 at 1:55 AM, Joseph Cooney <[email protected]> > wrote: > > What type of client is it? Do you control the means by which data is > written to the table? Are clients deployed to the internet or intranet? What > kind of database are we talking about here (I assume you mean SQL Server, > since you mention SqlDependency) but which version? > > > > Joseph > > > > On Mon, Mar 28, 2011 at 8:33 AM, Grant Molloy <[email protected]> wrote: > > Hi list, > > > > I'm looking to have an application automagically update it's view of the > data when the data in the connected database table changes. > > I've seen two main ways to do this so far. > > 1. Poll the database every n seconds > > 2. Use the System.Web.Caching.SqlDependancy object. > > > > Does anyone else know of any other better or smarter way of doing this ? > > > > thanks > > Grant > > > > -- > > > > w: http://jcooney.net > > t: @josephcooney > > > > > > > > >
