VB.net and SQL Server

A[L]C

A[L]C

A[L]C

Soldato
Joined
18 Oct 2002
Posts
7,515
Location
Maidenhead
Hi all,

Ive got an application which I am creating in vb.net. It sits in the system tray and I need it to monitor a SQL 2008 database for new records in a table. When a new record is added, I need it to run a sub in my vb.net app.

To give you an idea, it is monitoring an ANPR system so when a new vehicle is detected I need to prompt the user.

So the question is how. The database is over the network and I need to be careful about performance, so I could do a select statement every x seconds and compare that to the previous result, but that seems inefficient.

Any ideas?
 
depending on the complexity of the statement and the speed of your hardware one query a second should not really be a problem just have a look at sql profiler to see what is currently happening , what you may find better however is to follow the directions in the Link and create a monitoring table that you can mark as notified or not and therefore reduce the frequency
 
Personaly I would create another table, we will call it x for now. Then on your table that you want to monitor create a trigger to insert a record in to x on insert, update or delete depending on which you need. Then have you application monitor x and delete the row once processed.

TrUz
 
Im only looking for the latest insert. ie

Select top 1 plate from dbo.reads_001 order by timestamp desc

I dont need to worry about any other changes.

Would the method suggested by Truz be any more efficient as i;ll still be querying a table.
 
If you prune the table so it contains only the latest x inserts, then the query should be lightning fast since the table will be very small.
 
Cool, will need to look in to it then. Dont have a clue where to start on triggers and pruning
 
why are you looking at only the top one could it not occur that two are put in in the time period between refreshes and if you say no now could it occur in the future
 
It is unlikely due to the position of the cameras ie the vehicle comes to the weighbridge, is picked up by ANPR at the weighbridge and they must weigh the vehicle in/out before it can proceed and the next vehicle is detected.

I suppose I may need to find all records since the last action and display them to the user though.
 
I assume the "user" is someone at a remote location from the ANPR vehicle/system?

The last ANPR system I dealt with had to have two operators in the vehicle monitoring the captured data.
 
No the user is in the weighbridge office next to the weighbridge which is where the vehicle is.

At most there will be two machines accessing the data at one time, although they will be accessing the records for different cameras (therefore different sql statements)
 
Oh well, if it is a local setup with just two machines multiple queries is not going to be a huge issue, especially if it is on a 'monitor' table with very few entries.
 
You must log in or register to reply here.
Back
Top Bottom