On Wed, Mar 30, 2011 at 9:46 AM, Grant Molloy <[email protected]> wrote: > 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) 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. > The web service notifies the app of the change > The app responds to the change as it needs to..
Haven't read all the responses on this thread but I don't see how for a web app a web page can be updated on this notification when the client's (browser's) connection to the server is already closed. Unless the client side script polls the server. > > 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 >> >> >> >> >> >> >> >> >
