Re: Update checking

From: Tommy Malone (tommy_at_angula.net)
Date: 05/20/04 

Date: Wed, 19 May 2004 19:01:35 -0500

What is the newsgroup etiquette around asking so many follow-up questions?

I really appreciate your considerate and thorough answers. If I may press my
luck a bit further...

1. Since you brought up Outlook, it's that type of polling I would like to
accomplish. My thought was to use a simple query with a "username" param
against a "haschanges" bit field to signal more involved queries against the
db. The trouble, it seems, is the volume of calls against the db (wasting
resources) or the complexity of message queues. How is this accomplished
and/or mitigated by mail servers or applications with similar needs?

2. In asking about closing connections which are "in-use", I'm referring to
the web services scenario. Multiple users would call on a single web service
which accesses the db. If the end of the procedure is to close the
connection, what happens to another user still in the middle of the query?

Thank you again for your time.

"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:%23MYzqYfPEHA.540@TK2MSFTNGP11.phx.gbl...
> In ADO.NET, concurrency exceptions happen if the data you queried has
> changed in between the update and the original select. If you for
instance,
> walk through the datatable on your own and manually build a command one at
a
> time, calling executenonquery, you'll never have a concurrency exception.
>
> You cna technically lock a whole table just as you can a row, but under
the
> scenario you are using you're not locking either except for that split
> second it takes to fire the query.
>
> 1)The most efficient way to poll is going to probably employ messagequeus.
> You could write the changes to a queue and then you can poll the queue
which
> may not seem like it's more efficient than pollign the db but it is.
> However, this isn't a trivial task. To that end, polling in general,even
if
> you just fire a standard select statement isn't going to be problem free.
> You are going to invariably pull over more data than you need and consume
> more resources than you need. To that end, i'd say the short answer is
that
> polling of this sort isn't an efficient beast no matter how you implement
it
> 2) You'd try/catch seperate pieces of the proc. The first of whichis
open.
> Let's say the connetion's attempt to open blows up. You may have a standby
> server that you could try to connect against or you could send back a
> notification and ask the user if they want to work in an offline context
> etc.. The same with the query. If the query blows up you could see what
it
> was and basically let the user work in a similar offline mode. It's
pretty
> much the same way Outlook works in this regard...pretty much loses a lot
of
> functionalityg wihtout network connectivity but at least having the
offline
> option is a lot better
> 3) Yikes. I'm assuming you are talking about a multithreaded environment?
> Well, sharing an active between connections is bad news. You can trap the
> Statechange http://www.knowdotnet.com/articles/connections.html event to
> find out if this happens but generally, this couldn't really happen b/c
the
> while loop would block unless you did have a async scenario. in this
> instance if you wanted to allow the user to cancel you may want to have a
> while(rdr.Read() && ShouldContinue){
>
> }
>
> If you have it running async you could set the ShouldContinue to false
which
> would cause execution to stop. This could get a little complex but it's
> definitely doable.
>
> HTH,
>
> Bill
>
> www.devbuzz.com
> www.knowdotnet.com
> http://www.msmvps.com/williamryan/
> "Tommy Malone" <tommy@angula.net> wrote in message
> news:OoauGcePEHA.2580@TK2MSFTNGP09.phx.gbl...
> > In my case, it appears my terminology needs to be corrected. In short,
is
> it
> > correct to say that concurrency will not be an issue for me, so long as
my
> > users are not accessing/changing the same record? Does concurrency only
> > apply to records and not to tables?
> >
> > My real problem (beyond the dynamic SQL) is twofold.
> >
> > 1. How do I efficiently poll for changes? Keep in mind, my database is
> > "accessible outside my trusted domain" via web service.
> > 2. I need a better understanding of connection management (among many
> other
> > things, but that's another thread or 10). In your article you state "a
> > failure from the Database opening is much different from the proc
> failing".
> > How can I use a Try/Catch to minimize those failures without causing the
> > whole procedure to fail? In other words, what goes in the Catch block to
> > make the code try to open the connection again (and/or loop until it
does
> it
> > successfully or fail gracefully)? Where does connection pooling come
into
> > play?
> >
> > And, finally, is it possible to close a connection during a read or is
> that
> > "managed" by VS? For example, could one procedure "conn.close" while
> another
> > is in a "while datareader.read"? If so, how is this avoided?
> >
> > Thanks for your time.
> >
> >
> > "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> > news:OcVa8OePEHA.2740@TK2MSFTNGP11.phx.gbl...
> > > First, get rid of that dynamic sql and replace it with a parameterized
> > query
> > > or a stored procedure
> > http://www.knowdotnet.com/articles/storedprocsvb.html
> > >
> > > Next, you can trap a concurrency exception and polling like you are
> asking
> > > about is very inefficient. There are a few different ways to handle
> > > concurrency but basically you can either do a last in wins, first in
> wins
> > or
> > > prompt the user
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskPerformingOptimisticConcurrencyChecking.asp
> > >
> >
>
http://sqljunkies.com/Newsgroups/microsoft.public.dotnet.framework.adonet/2004/5/11/212008.aspx
> > >
> > > In ADO.NET 2.0 there's a notification service register that will be
able
> > to
> > > broadcast notifications that may help you but it's also not intended
for
> > > 100's of users and won't be out for a while.
> > >
> > > --
> > >
> > > W.G. Ryan, eMVP
> > >
> > > http://forums.devbuzz.com/
> > > http://www.knowdotnet.com/williamryan.html
> > > http://www.msmvps.com/WilliamRyan/
> > > "Tommy Malone" <tommy@angula.net> wrote in message
> > > news:edWBE6dPEHA.1048@tk2msftngp13.phx.gbl...
> > > > I have an application that uses a web service to check for user
> updates
> > > > before calling more extensive code to sync data. My question is, how
> is
> > > > concurrency handled, if concurrency should even be my concern, where
> > many
> > > > users call the same web service to check the same database (although
> > each
> > > > user will have user-specific data not shared by other users) for
> > changes?
> > > In
> > > > other words, am I going to run into trouble when a hundred users hit
> the
> > > > "User" table for a HasChanges value every minute? Is there another,
> > > > preferred strategy to communicate that there are db changes that
need
> to
> > > be
> > > > downloaded? What happens to this code when it gets called at the
same
> > time
> > > > with two different users?
> > > >
> > > > <WebMethod(CacheDuration:=3600)> Public Function Ping(ByVal UserName
> As
> > > > String) As Boolean
> > > >
> > > > Dim datareader As SqlClient.SqlDataReader
> > > >
> > > > Dim cmdUserChanges As New SqlClient.SqlCommand("SELECT HasChanges
FROM
> > > > MohawkUsers WHERE UserName = '" & UserName & "'", connMohawk)
> > > >
> > > > Try
> > > >
> > > > connMohawk.Open()
> > > >
> > > > datareader = cmdUserChanges.ExecuteReader
> > > >
> > > > While datareader.Read
> > > >
> > > > Ping = datareader.Item(0)
> > > >
> > > > End While
> > > >
> > > > connMohawk.Close()
> > > >
> > > > Catch ex As Exception
> > > >
> > > > Finally
> > > >
> > > > connMohawk.Close()
> > > >
> > > > End Try
> > > >
> > > >
> > > > End Function
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... # Same query previously worked in Progress 8.x using SQL89. ... This entry allows you to keep your existing code written with the ODBC ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenDataSource & stored procedure
    ... recognises the ODBC escape syntax. ... You shouldn't need the connection parameter since Word gets it from the ... return multiple result sets - enough to confuse software such as MS Query ... >>> The call works in MSQuery but won't return the data to Word. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)
  • Re: Update checking
    ... My thought was to use a simple query with a "username" param> against a "haschanges" bit field to signal more involved queries against the> db. ... I guess it depends on how the service is set up but I can't imagine a scenario where you'd want to use just one connection shared across all users. ... concurrency exceptions happen if the data you queried has>> changed in between the update and the original select. ...
    (microsoft.public.dotnet.framework.adonet)