Architecture Question!

[Antzz]

Hi Guys,
I have a question and am not sure if this is the right forum. I am going to post it anyways and let you respond.

Problem::
We are developing an application that almost works on the concept of an Email Inbox. A user has a Windows Client which displays a worklist. The worklist is displayed in a GridUI which is bound to a collection object which is populated via a dataset from the database. It is important that the grid is tied to the collection object rather then the dataset for business logic and other reasons.

The idea is to populate the worklist automatically when a new row is inserted into the database. We can definitely refresh the query and bring all data from the database, but that is something we are lookign to avoid.

Is there any way we can use new SQL 2005 tech like Service Broker or any other technology to push data out into the client from the database based on changes to data in the tables?

Value all your inputs.

 

[mrdenny]

You could probably use either Service Broker or Notification Services to send a message to the client. At the least the client could then rerun the query and get the updated data set. Depending on how the collection object works (and I'm not VB or C# programmer) would deturmin if you could go get that one record and add it to the object.

You'll probably want to check with the programming forums (VB, C#, etc) on how to do that (if it can be done).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[druer]

Denny has amptly described both of the new wonderful features available in 2005. If you need a solution prior to upgrading you might consider using a trigger on the table that sends an email or executes an Extended Stored Procedure (.DLL) to notify your Collection Object code.

 

[Antzz]

Thanks to both of u.. the client has a complexity in the sense that there are queries being executed. My original idea was not to re-execute the queries which would incur additional bandwith since it would be returning data that is already displayed in the client - but to only return data that has changed in the database and have a business logic in the client that would update the data.

I think a combination of a trigger and Service Broker may do the job. And yes, we are on SQL 2005.

If you think of anything new, feel free to make a post here

 

[druer]

Well now I'm jealous. I went to the launch event today, and discovered that 2005 will do many things that would make our production run much, much, much better, but we can't upgrade to mid 2006. The database was created by and is used for a third party app, and has several bottlenecks in it relating to audit trails. The service broker would immediately allow us to remove the bottlenecks by sending the audit records to a queue, and then processing them after hours. We also have an issue with the application in that it created about 12 tables that are more than 255 columns so we can't use replication, but 2005 will break that barrier for us. Such is life. Best of luck.

 

[Antzz]

Yeah I went to a launch event about a couple of weeks back and I think the SQL team is on the right track. I will keep you guys posted on the ultimate design choice we make.


Like I said, feel free to continue posting here as more ideas will only be helpful for me.