On any change or update

I am wiriting a C# application that stores availabilty information in a tabl
e
ie UserName, UserAvailable (just to keep it simple)
I am looking for a recommendation for the best way to have the SQL database
notify my c# client that any data in this table has been changed.
In my old non .net application I used a client/server model where the
server watched for changes and sent messages to each registered client but n
o
data was ever saved. Two of my goals with the new application are eliminatin
g
the server application and saving the data to a SQL db.
Currently I have my application re-examine the table every n-seconds but
this is far from optimal. Any suggestions would be highly appreciated.
Regards,
SeanHi
What is your SQL Server vesion?
If it is SQL Server 2000 , I think you may want to consider using triggers
to examine "deleted" and "inserted" virtual tables
Does your App make changes too?
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:8F93298C-EBD9-4CE4-A7C9-8DA9E9731908@.microsoft.com...
>I am wiriting a C# application that stores availabilty information in a
>table
> ie UserName, UserAvailable (just to keep it simple)
> I am looking for a recommendation for the best way to have the SQL
> database
> notify my c# client that any data in this table has been changed.
> In my old non .net application I used a client/server model where the
> server watched for changes and sent messages to each registered client but
> no
> data was ever saved. Two of my goals with the new application are
> eliminating
> the server application and saving the data to a SQL db.
> Currently I have my application re-examine the table every n-seconds but
> this is far from optimal. Any suggestions would be highly appreciated.
> Regards,
> Sean|||If you are on, or can upgrade to, SQL Serer 2005, read up on Query Notificat
ions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:8F93298C-EBD9-4CE4-A7C9-8DA9E9731908@.microsoft.com...
>I am wiriting a C# application that stores availabilty information in a tab
le
> ie UserName, UserAvailable (just to keep it simple)
> I am looking for a recommendation for the best way to have the SQL databas
e
> notify my c# client that any data in this table has been changed.
> In my old non .net application I used a client/server model where the
> server watched for changes and sent messages to each registered client but
no
> data was ever saved. Two of my goals with the new application are eliminat
ing
> the server application and saving the data to a SQL db.
> Currently I have my application re-examine the table every n-seconds but
> this is far from optimal. Any suggestions would be highly appreciated.
> Regards,
> Sean|||There are many approaches for implementing that, the best and sexiest
solution is to use Query Notification as Tibor said. There are other
mechanisms for doing this (including calling Message Queues...), but
not as easy to use as QN :-)
HTH, jens Suessmeyer.|||Tibor,
Thanks for your responce. After reviewing the documentaion on the MSDN web
page this sounds like just what I had in mind.
Do you know if the service broker feature is available in the 2005 Express
edition of SQL?
"Tibor Karaszi" wrote:

> If you are on, or can upgrade to, SQL Serer 2005, read up on Query Notific
ations.
>|||Hi,
http://www.microsoft.com/sql/prodin...e-features.mspx
Service Broker (Subscriber only) --means yes, you can.
HTH, Jens Suessmeyer.|||Hi Jens!
the way I read the part "subscriber only" is that you cannot insert into a q
ueue on Express. To me,
this sounds like Express does not support query notifications. But I happily
admit that I'm only
guessing here. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1136912680.595505.22710@.g43g2000cwa.googlegroups.com...
> Hi,
> http://www.microsoft.com/sql/prodin...e-features.mspx
> Service Broker (Subscriber only) --means yes, you can.
>
> HTH, Jens Suessmeyer.
>|||You are right I just read down the white paper, you can subscribe
within broker queues outside the SQL Server Express Editions, so you
just can be notified. So even default queues are not creaetable. Sorry
for the wrong information to the original poster, thanks to Tibor.
-Jens.|||Service Broker is available in SQL Express 2005.
The only restriction is that when exchanging messages between separate SQL
instances, the message has to pass trough a higher version. That is, you
cannot send a message from a SQL Express to another SQL Express directly.
Since you're interested in Query Notifications, you will probably keep the
messages local within the SQL Express instance, so this limitation won't
affect you whatsoever.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:7DF1F9B5-19F0-4E41-AA75-D2B19DA5EE7B@.microsoft.com...
> Tibor,
> Thanks for your responce. After reviewing the documentaion on the MSDN web
> page this sounds like just what I had in mind.
> Do you know if the service broker feature is available in the 2005 Express
> edition of SQL?
>
> "Tibor Karaszi" wrote:
>|||Sorry, not true Tibor. The short story is that as long as you are working
within the same instance (which is the only way query notifications work
with the default listener) SQL Express Service Broker is 100% equivalent to
all the other SKU's. If you are talking between sku's, there is a small
restriction as Remus pointed out.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiQOc2gFGHA.3280@.TK2MSFTNGP10.phx.gbl...
> Hi Jens!
> the way I read the part "subscriber only" is that you cannot insert into a
> queue on Express. To me, this sounds like Express does not support query
> notifications. But I happily admit that I'm only guessing here. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1136912680.595505.22710@.g43g2000cwa.googlegroups.com...
>

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point