Connectionstring question - exlusive check

[Windsailor]

How do I create a DAL layer connection string that coincides with the default dataset/adapter connection object so that I can check to see if a connection is open by other private sub calls?

I have parameter based inserts etc. on a few forms that create a new connection; but I would like to see if there is an open connection already through other private sub calls etc. and use that connection and I'm not quite sure how to do that. I'm learning how to use exclusive mode... to use and check for a single connection that is either open or closed.

 

[jmcilhinney]

What you're trying to do goes against the design of ADO.NET. You should be creating, open and closing connections as, where and when they are needed. You should never find an open connection because all your methods that create connections should be closing them anyway.

 

[Windsailor]

That depends. There are reasons when you need to use the exclusive read/write mode; and there are times when you want to use a single connection when in shared mode too. This happens to apply to a file based local database and not a server.

Currently I have my connection as exclusive read/write. I ran into this problem while updating the database which had user conditional options on the form that reached out to other conditional statements in their own private subs. So in a way it was I guess like threading, with exclusive read/write... err not really. Only they were private subs that used their own connection.

To by-pass this issue I had to take those separate conditional blocks and string them together in a single using statement so they would use the same connection. Which works fine, but I would like to learn how to perform checks on connections that require a exclusive read/write while they are in private subs. This would also help when using a single connection in shared mode.

 

[cjard]

What youre discussing is pessimistic concurrency, whereas .net by default (when using tableadapters at least) generates statements that are optimistically concurrent. Each has their advantages, but generally optimistic concurrency presents less of a barrier to users doing their work.

If you can elaborate more on why you are using exclusive access I'm sure we can make more suggestions

 

[Windsailor]

When using exclusive mode the database doesn’t have to be so paranoid about other connections; other people updating some of the data that you are updating at the same time. This speeds up the processes considerably.

 

[cjard]

Er.. Yeah; only one person can access a database at once? That sounds like a great strategy for allowing 500 users to work off the same database..

 

[Windsailor]

Er.. Yeah; only one person can access a database at once? That sounds like a great strategy for allowing 500 users to work off the same database..

Who said this was on a server? Read the above posts.

How can a local desktop (file based) database (using windows file systems for locks etc.) handle 500 concurrent users? Are you suggesting that everyone should use a desktop database and use it to handle 500 concurrent users? Come on now... get real. For this app, maybe 5 total concurrent users.

All I wanted was suggestions on how to handle a certain situation... and all you have to offer is sarcasm...

If you don't know the answer please state so... and I will thank you for your time...

 

[Windsailor]

No... I think I am totally wrong in my original quest here and my goal is skewed too.

I learned I should install a server and service on a laptop or individuals desktop computer to run a local application.

Sigh...

 

[Windsailor]

To all other readers... when someone states a similar statement like I have;

This happens to apply to a file based local database and not a server

It usually means a database that is controlled by the windows file system (something like SQLCE or equivalent) and not a local server (like SQL Server Express).

I in no way intentionally implied that a desktop database can handle large amount of users... if I did; I sincerely apologize for that mistake.

 

[cjard]

Because the default set of DAL classes generated by the IDE work in an optimistically concurrent fashion, they arent expeccting you to exclusively lock the db.

 

[Windsailor]

Because the default set of DAL classes generated by the IDE work in an optimistically concurrent fashion, they arent expeccting you to exclusively lock the db.

True. And I believe (I haven't checked) you can modify that within the existing datatable or data adapter either after creation or during creation, or both, to your preference.

Also on a windows forms apps they use the app config file for the conn string properties (exclusive or non-exclusive read/write), or if you use provider factories you can provide the connection string properties there too.

I have in the past (99.9% of the time) used optimistic locking... now I want to explore with a single user or a very small amount of concurrent users the exclusive or pessimistic route. There will be no large pulls or inserts into the database.

What about a non-default class that the default ones can point to -or route through- and due the check?

For that .1% case in the past I have made them all ~re-route~ or use a special connection for that specific purpose.

And now I would like to see someone else's idea of doing a public call to see if the connection is open.