[RESOLVED] SQL Server - Notified when a new entry is added?

[knxrb]

Hi guys and gals, my question for you is:

I have a VB.Net application that connects to a SQL Server database on my web host at GoDaddy. I'm wanting to use it for a chat program so that users can add messages to the database and then everyone else gets the new messages when it re-queries the database.

The only problem is that the only way to get the new messages is to re-query the database every # seconds.

Is there any way to have my program not re-query the database until an entry is added by another user?
Would the SQL.SQLNotificationRequest be used for this?

knxrb

[GaryMazzone]

Did you look at Insert triggers on the tables?

[Dnereb]

You should look at WCF to fix that problem.
it's possible to push to clients with this technology.

[knxrb]

Insert triggers on the tables, hmm, I've seen them in the SQL Server admin area when I was adding the tables but I don't know what they are for as I've just started using SQL Server.

I'll take a look at those and also WCF if the triggers don't work, thanks guys.

[szlamany]

I don't think a DB is a proper place for what you are looking for - a DB is more of a repository for data. A place to store the chat log from a chat session. There are other "pc-to-pc" communication methods that more closely match what you are looking for here.

With that said - if you really want to do it in a table in SQL - it's all as simple as having your "chat" table have a simple integer type primary key that is an identity value.

And then having your client program call a query that asks for all rows with a PK value greater then the last "max" PK value that was SELECTED.

And you can also protect your "INSERT's" of new rows into this table so that they do not INSERT if a "newer" row is already in place - that way you would "know" to hold that INSERT until you did your SELECT (as noted above) and shown that "new post" to your client.

There is no need for triggers or notification events (I've read that notification is not the "great" feature that you would think it to be).

[knxrb]

Hmm, thanks szlamany. I think your idea is the simplest and probably the most effective for my problem

knxrb

[szlamany]

btw - to do an INSERT that will fail if someone else has "since added a new row" do the following.

Assume a table with two columns - ChatId as int and ChatText as varchar(100)

And assume that you "know" the last "max" ChatId is 15.

You are expecting to insert 16.

This query will NOT INSERT if the "current max" ChatId is greater then 15.

Note that with IDENTITY columns they auto-number - so you don't specify them in the INSERT statement - just the text.

Insert into ChatTable
   Select 'New Chat Text'
   Where (Select Max(ChatId) From ChatTable)=15

This INSERT-from-a-SELECT trick allows you to put a WHERE clause. The WHERE clause only passes a new row if the current max chatid is still 15.

You check the RowCount after the INSERT and now your client program knows if the ChatTable changed since you last "read" from it.

[knxrb]

Aah, thanks very much That will help an awful lot.

knxrb