Invalidate disconected recodset

Hi,

We would like to use MS SQL Server Express database for out data. The data
can be updated by many clients.

There will be an application that will show these data. This application can
work with the disconnected recordest.

My question is, how to notify this application, about the data in recordset
are not valid anymore, because the table in the database had been changed by
a client?

As I understand, I can not use Notification Services with SQL Express.

1/ Is the trigger that would send message to the Service broker suitable for
this scenario?

2/ Can I use Event notification? I think they donâ??t work with DML

3/ What would be the best way to inform *.exe app that the disconnected
recodset is not valid?

Thanks for any ideas.

Lubomir

Remus
Fri Mar 16 00:08:55 CDT 2007

Query Notification can detect changes in the dataset a client is interested:
http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx
The clients can use the ADO.Net wrappers: SqlNotifications or SqlDependency.

How many clients are we talking about? SqlDependency keeps a connection open
blocked in a WAITFOR(RECEIVE), therefore cosnsumming a thread on the server,
for each appdomain that called SqlDendency.Start(), so this cannot scale
high (hundreds of clients), but since you say is SQL Express, probably
you're scale is small to start with.

HTH,
~ Remus


"Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
> Hi,
>
> We would like to use MS SQL Server Express database for out data. The data
> can be updated by many clients.
>
> There will be an application that will show these data. This application
> can
> work with the disconnected recordest.
>
> My question is, how to notify this application, about the data in
> recordset
> are not valid anymore, because the table in the database had been changed
> by
> a client?
>
> As I understand, I can not use Notification Services with SQL Express.
>
> 1/ Is the trigger that would send message to the Service broker suitable
> for
> this scenario?
>
> 2/ Can I use Event notification? I think they don't work with DML
>
> 3/ What would be the best way to inform *.exe app that the disconnected
> recodset is not valid?
>
> Thanks for any ideas.
>
> Lubomir
>
>

Lubomir
Fri Mar 16 12:23:12 CDT 2007

There will be much more clients probably. We can not leave connecitons open.
However, the there will not be many transmitted data.


"Remus Rusanu [MSFT]" wrote:

> Query Notification can detect changes in the dataset a client is interested:
> http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx
> The clients can use the ADO.Net wrappers: SqlNotifications or SqlDependency.
>
> How many clients are we talking about? SqlDependency keeps a connection open
> blocked in a WAITFOR(RECEIVE), therefore cosnsumming a thread on the server,
> for each appdomain that called SqlDendency.Start(), so this cannot scale
> high (hundreds of clients), but since you say is SQL Express, probably
> you're scale is small to start with.
>
> HTH,
> ~ Remus
>
>
> "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
> news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
> > Hi,
> >
> > We would like to use MS SQL Server Express database for out data. The data
> > can be updated by many clients.
> >
> > There will be an application that will show these data. This application
> > can
> > work with the disconnected recordest.
> >
> > My question is, how to notify this application, about the data in
> > recordset
> > are not valid anymore, because the table in the database had been changed
> > by
> > a client?
> >
> > As I understand, I can not use Notification Services with SQL Express.
> >
> > 1/ Is the trigger that would send message to the Service broker suitable
> > for
> > this scenario?
> >
> > 2/ Can I use Event notification? I think they don't work with DML
> >
> > 3/ What would be the best way to inform *.exe app that the disconnected
> > recodset is not valid?
> >
> > Thanks for any ideas.
> >
> > Lubomir
> >
> >
>
>
>

Remus
Fri Mar 16 20:45:38 CDT 2007

If the updates of the data are rare, you can subscribe to query notification
w/o using SQL Dependency. Clients can check each time they connect if a
notification was sent, and if so they need to update the dataset.
That being said, a SQL Express instance wiht 'much more' than hudrends of
clients is not gonna work for many other reasons (CPU scheduler limitations,
database sizes etc etc), as even if clients connect seldom, there will be
spikes when many clients want to connect and the SQL Express will not be
able to handle them.


"Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
news:40E32875-186E-46FE-9D23-9E0C969185FB@microsoft.com...
> There will be much more clients probably. We can not leave connecitons
> open.
> However, the there will not be many transmitted data.
>
>
> "Remus Rusanu [MSFT]" wrote:
>
>> Query Notification can detect changes in the dataset a client is
>> interested:
>> http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx
>> The clients can use the ADO.Net wrappers: SqlNotifications or
>> SqlDependency.
>>
>> How many clients are we talking about? SqlDependency keeps a connection
>> open
>> blocked in a WAITFOR(RECEIVE), therefore cosnsumming a thread on the
>> server,
>> for each appdomain that called SqlDendency.Start(), so this cannot scale
>> high (hundreds of clients), but since you say is SQL Express, probably
>> you're scale is small to start with.
>>
>> HTH,
>> ~ Remus
>>
>>
>> "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
>> news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
>> > Hi,
>> >
>> > We would like to use MS SQL Server Express database for out data. The
>> > data
>> > can be updated by many clients.
>> >
>> > There will be an application that will show these data. This
>> > application
>> > can
>> > work with the disconnected recordest.
>> >
>> > My question is, how to notify this application, about the data in
>> > recordset
>> > are not valid anymore, because the table in the database had been
>> > changed
>> > by
>> > a client?
>> >
>> > As I understand, I can not use Notification Services with SQL Express.
>> >
>> > 1/ Is the trigger that would send message to the Service broker
>> > suitable
>> > for
>> > this scenario?
>> >
>> > 2/ Can I use Event notification? I think they don't work with DML
>> >
>> > 3/ What would be the best way to inform *.exe app that the disconnected
>> > recodset is not valid?
>> >
>> > Thanks for any ideas.
>> >
>> > Lubomir
>> >
>> >
>>
>>
>>

Lubomir
Sat Mar 17 12:43:05 CDT 2007

Thanks for help. I think SQLDpendecy could be fine after all.

Lubomir


"Remus Rusanu [MSFT]" wrote:

> If the updates of the data are rare, you can subscribe to query notification
> w/o using SQL Dependency. Clients can check each time they connect if a
> notification was sent, and if so they need to update the dataset.
> That being said, a SQL Express instance wiht 'much more' than hudrends of
> clients is not gonna work for many other reasons (CPU scheduler limitations,
> database sizes etc etc), as even if clients connect seldom, there will be
> spikes when many clients want to connect and the SQL Express will not be
> able to handle them.
>
>
> "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
> news:40E32875-186E-46FE-9D23-9E0C969185FB@microsoft.com...
> > There will be much more clients probably. We can not leave connecitons
> > open.
> > However, the there will not be many transmitted data.
> >
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> >> Query Notification can detect changes in the dataset a client is
> >> interested:
> >> http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx
> >> The clients can use the ADO.Net wrappers: SqlNotifications or
> >> SqlDependency.
> >>
> >> How many clients are we talking about? SqlDependency keeps a connection
> >> open
> >> blocked in a WAITFOR(RECEIVE), therefore cosnsumming a thread on the
> >> server,
> >> for each appdomain that called SqlDendency.Start(), so this cannot scale
> >> high (hundreds of clients), but since you say is SQL Express, probably
> >> you're scale is small to start with.
> >>
> >> HTH,
> >> ~ Remus
> >>
> >>
> >> "Lubomir" <Lubomir@discussions.microsoft.com> wrote in message
> >> news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
> >> > Hi,
> >> >
> >> > We would like to use MS SQL Server Express database for out data. The
> >> > data
> >> > can be updated by many clients.
> >> >
> >> > There will be an application that will show these data. This
> >> > application
> >> > can
> >> > work with the disconnected recordest.
> >> >
> >> > My question is, how to notify this application, about the data in
> >> > recordset
> >> > are not valid anymore, because the table in the database had been
> >> > changed
> >> > by
> >> > a client?
> >> >
> >> > As I understand, I can not use Notification Services with SQL Express.
> >> >
> >> > 1/ Is the trigger that would send message to the Service broker
> >> > suitable
> >> > for
> >> > this scenario?
> >> >
> >> > 2/ Can I use Event notification? I think they don't work with DML
> >> >
> >> > 3/ What would be the best way to inform *.exe app that the disconnected
> >> > recodset is not valid?
> >> >
> >> > Thanks for any ideas.
> >> >
> >> > Lubomir
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Cor
Sun Mar 18 07:06:19 CDT 2007

Lubomir,

Are you sure that you are talking about a disconected recordset.

That exist but is not a datatable or a dataset.

I am in doubt if you mean that thing, because you can do nothing with it in
ADONET than convert it. You make it difficult to help you if you are using
the wrong names for subjects you know.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbdataadapterclassfilltopic1.asp

Cor

"Lubomir" <Lubomir@discussions.microsoft.com> schreef in bericht
news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
> Hi,
>
> We would like to use MS SQL Server Express database for out data. The data
> can be updated by many clients.
>
> There will be an application that will show these data. This application
> can
> work with the disconnected recordest.
>
> My question is, how to notify this application, about the data in
> recordset
> are not valid anymore, because the table in the database had been changed
> by
> a client?
>
> As I understand, I can not use Notification Services with SQL Express.
>
> 1/ Is the trigger that would send message to the Service broker suitable
> for
> this scenario?
>
> 2/ Can I use Event notification? I think they don't work with DML
>
> 3/ What would be the best way to inform *.exe app that the disconnected
> recodset is not valid?
>
> Thanks for any ideas.
>
> Lubomir
>
>

Lubomir
Mon Mar 19 15:39:05 CDT 2007

Yes, my mistake. I was speaking about DataSet.

I had a look at the SQLDependency, but I can't find any description about
how to use in Windows Application. In all articles I read this class were use
with ASP.NET

I found a note, that it can be use in 2 scenarios:
pushing (Service Broker is involved)
polling (triggers are involved)

Unfortunatelly, again, I didn't find any more details.

Where coudl I find more information about using these scenerios in Windows
(Non Internet) app?
Thanks,

Lubomir

"Cor Ligthert [MVP]" wrote:

> Lubomir,
>
> Are you sure that you are talking about a disconected recordset.
>
> That exist but is not a datatable or a dataset.
>
> I am in doubt if you mean that thing, because you can do nothing with it in
> ADONET than convert it. You make it difficult to help you if you are using
> the wrong names for subjects you know.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbdataadapterclassfilltopic1.asp
>
> Cor
>
> "Lubomir" <Lubomir@discussions.microsoft.com> schreef in bericht
> news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
> > Hi,
> >
> > We would like to use MS SQL Server Express database for out data. The data
> > can be updated by many clients.
> >
> > There will be an application that will show these data. This application
> > can
> > work with the disconnected recordest.
> >
> > My question is, how to notify this application, about the data in
> > recordset
> > are not valid anymore, because the table in the database had been changed
> > by
> > a client?
> >
> > As I understand, I can not use Notification Services with SQL Express.
> >
> > 1/ Is the trigger that would send message to the Service broker suitable
> > for
> > this scenario?
> >
> > 2/ Can I use Event notification? I think they don't work with DML
> >
> > 3/ What would be the best way to inform *.exe app that the disconnected
> > recodset is not valid?
> >
> > Thanks for any ideas.
> >
> > Lubomir
> >
> >
>
>
>

Cor
Mon Mar 19 20:28:00 CDT 2007

Lubomir,

http://www.vb-tips.com/default.aspx

50% of our Grid and ADONET samples are with database server and 50% are not.

Cor


"Lubomir" <Lubomir@discussions.microsoft.com> schreef in bericht
news:D18A3120-D65C-46A3-8EE4-DDA16B84E5F9@microsoft.com...
> Yes, my mistake. I was speaking about DataSet.
>
> I had a look at the SQLDependency, but I can't find any description about
> how to use in Windows Application. In all articles I read this class were
> use
> with ASP.NET
>
> I found a note, that it can be use in 2 scenarios:
> pushing (Service Broker is involved)
> polling (triggers are involved)
>
> Unfortunatelly, again, I didn't find any more details.
>
> Where coudl I find more information about using these scenerios in Windows
> (Non Internet) app?
> Thanks,
>
> Lubomir
>
> "Cor Ligthert [MVP]" wrote:
>
>> Lubomir,
>>
>> Are you sure that you are talking about a disconected recordset.
>>
>> That exist but is not a datatable or a dataset.
>>
>> I am in doubt if you mean that thing, because you can do nothing with it
>> in
>> ADONET than convert it. You make it difficult to help you if you are
>> using
>> the wrong names for subjects you know.
>>
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbdataadapterclassfilltopic1.asp
>>
>> Cor
>>
>> "Lubomir" <Lubomir@discussions.microsoft.com> schreef in bericht
>> news:4F0075C6-37CA-4F2C-B4B4-C1BF04732BDC@microsoft.com...
>> > Hi,
>> >
>> > We would like to use MS SQL Server Express database for out data. The
>> > data
>> > can be updated by many clients.
>> >
>> > There will be an application that will show these data. This
>> > application
>> > can
>> > work with the disconnected recordest.
>> >
>> > My question is, how to notify this application, about the data in
>> > recordset
>> > are not valid anymore, because the table in the database had been
>> > changed
>> > by
>> > a client?
>> >
>> > As I understand, I can not use Notification Services with SQL Express.
>> >
>> > 1/ Is the trigger that would send message to the Service broker
>> > suitable
>> > for
>> > this scenario?
>> >
>> > 2/ Can I use Event notification? I think they don't work with DML
>> >
>> > 3/ What would be the best way to inform *.exe app that the disconnected
>> > recodset is not valid?
>> >
>> > Thanks for any ideas.
>> >
>> > Lubomir
>> >
>> >
>>
>>
>>