reuse connection object with diff username password?

[jtbr]

I have an ado.net based application (using BDP borland data provider actually). Everything is working fine. Up till now the connection string for my single connection object has been hard coded. set in designer. Now its time to implement login functionality using different user names and passwords. For a 'login' I figured, ok i'll close the connection if its open, then just change the ConnectionString. I set the exact same connection string as designer, I just set different username & password values.

The second time I run thru this cycle however I get strange exceptions when trying to close the connection. "No pool found to release connection". Then when I go to use it I get "connection already open".

The big question is, are connection objects meant to be reused in this manner, or must I new a new connection instance if I want to have a different connection string?

thanks

 

[William \(Bill\) Vaughn]

If this is a Windows Forms application then it makes sense to disable the
pool (in your case). If it's an ASP application, then the pool is essential
for scalability and no, you can't change the connection string in any way.
If you do, you get a new pool.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

 

[jtbm]

Yes this is a 'thick' winforms client. Well I went ahead and restructured my app to create a new connection object instance everytime I need a username change, rather then trying to reuse an existing connection object by changing its connectionstring. Things are working good now.

But this is just one more case that makes ado.net designers/design time development useless. I have decided to remove my connection completely from designer and create it in code now. I already had a problem where once ConnectionString is set in InitializeComponent() any following code that assigns the connection to a command object initiates database communications... and you have no control over this because it depends on serialization order of designer generated code!! HELLO!@# dont talk to the database until i ask for it **** it!!#

....I have several other issues with ado.net designers too. I wish I had done everything in code right from the beginning. Seriously is anyone able to use these in a real project? I just use them in a separate dummy project to generate code, then I copy and paste the code. I can see being able to use them for simple asp.net stuff I guess.

 

[William \(Bill\) Vaughn]

See >>>

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

 

[Cor Ligthert [MVP]]

jbr,

You can alwasy close and open the connection as you wish (as I do and
probably most dotNetters do foever) to use the connectionstring, just use
it, by creating a new connection. It is not needed the connectionstring
comes from the config sys, you can use it from everywhere,

Cor

 

[Miha Markic [MVP C#]]

There is a better and more scalable approach: create and open a new
connection instance just before you need it and dispose it asap.
This way your app will happily run multithreaded or doing more database
operations at same time if needed.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

in message news:[email protected]...

 

[Miha Markic [MVP C#]]

Ah, and yes, I agree with both: designer is just for simple apps and demos.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

in message news:[email protected]...

 

[William \(Bill\) Vaughn]

I disagree. In a Windows Forms application the extra overhead of opening an
closing connections hurts performance more than it limits scalability.
Reopening a pooled connection is far from free. The vast majority (over 90%)
of these applications are designed to support 5-500 users. SQL Server can
easily handle that number of connections X2. Open the connection, leave it
open and serialize the operations. I've also seen successful apps where the
layered forms open their own connection.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

There is a better and more scalable approach: create and open a new
connection instance just before you need it and dispose it asap.
This way your app will happily run multithreaded or doing more database
operations at same time if needed.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Yes this is a 'thick' winforms client. Well I went ahead and restructured
my app to create a new connection object instance everytime I need a
username change, rather then trying to reuse an existing connection
object by changing its connectionstring. Things are working good now.

But this is just one more case that makes ado.net designers/design time
development useless. I have decided to remove my connection completely
from designer and create it in code now. I already had a problem where
once ConnectionString is set in InitializeComponent() any following code
that assigns the connection to a command object initiates database
communications... and you have no control over this because it depends on
serialization order of designer generated code!! HELLO!@# dont talk to
the database until i ask for it **** it!!#

..I have several other issues with ado.net designers too. I wish I had
done everything in code right from the beginning. Seriously is anyone
able to use these in a real project? I just use them in a separate dummy
project to generate code, then I copy and paste the code. I can see being
able to use them for simple asp.net stuff I guess.

 

[Miha Markic [MVP C#]]

Hi Bill,

I disagree. In a Windows Forms application the extra overhead of opening an
closing connections hurts performance more than it limits scalability.
Reopening a pooled connection is far from free.

It certainly isn't free but it is not much either. Do you have any numbers
handy?

The vast majority (over 90%)

of these applications are designed to support 5-500 users. SQL Server can
easily handle that number of connections X2.

And certainly only one database is active on sql server? IOW what if there
are three databases each with 500 users?
It doesn't matter if you are pooling or not in singlethreaded apps (actually
it might if you set min pool size = 0), but it makes all the difference is
multithreaded ones.

Open the connection, leave it

open and serialize the operations. I've also seen successful apps where
the layered forms open their own connection.

What about multithreading? Are you going to have a connection per thread?

 

[Cor Ligthert [MVP]]

Miha,

In my idea you must be a fool to use multithreading for one server.
Your performance will in my idea only be slower by the collesions you can
get more easy.

If it is about more servers than the fact you describes are not true, they
have all their own pool.

Just my thought, never tested.

Cor

 

[Miha Markic [MVP C#]]

Miha,

In my idea you must be a fool to use multithreading for one server.
Your performance will in my idea only be slower by the collesions you can
get more easy.

If it is about more servers than the fact you describes are not true, they
have all their own pool.

Just my thought, never tested.

Then first do the testing and after the tests start calling people a fool,
will ya?

 

[Cor Ligthert [MVP]]

I was and will be never able to do a test with 500 persons in multithreading
connection on a LAN to test if that affects the performance of the
connection pool.

But it seems you did that, for me it sound crazy and it still will be.
Knowing somethings from LAN's I will always avoid almost parallel access to
that.

Cor

 

[William \(Bill\) Vaughn]

Ah, remember English is Cor's second (or third) language. I hope that he
does not know that calling someone a "fool" is an insult. I think he
might have meant "... it would be foolhardy" or "...it would be foolish" to
do something.

In response to your other comment. I have done some test that show that
retrieving a connection from the pool can be expensive. Remember that you
pay a bigger price when a pooled connection is first created. If you're
building several connections, the very first time is even more expensive
than subsequent connections. However, when a connection is reopened (fetched
from the pool), the data access layers reauthenticate the SSPI credentials
(each and every time). Depending on the server load, this can also take more
time on some systems. All of this is unneeded overhead for a Windows Forms
(what we used to call a "connected") application.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

 

[Miha Markic [MVP C#]]

Ah, remember English is Cor's second (or third) language. I hope that he
does not know that calling someone a "fool" is an insult. I think he
might have meant "... it would be foolhardy" or "...it would be foolish"
to do something.

That's our Cor, yes

In response to your other comment. I have done some test that show that
retrieving a connection from the pool can be expensive. Remember that you
pay a bigger price when a pooled connection is first created. If you're
building several connections, the very first time is even more expensive
than subsequent connections. However, when a connection is reopened
(fetched from the pool), the data access layers reauthenticate the SSPI
credentials (each and every time). Depending on the server load, this can
also take more time on some systems. All of this is unneeded overhead for
a Windows Forms (what we used to call a "connected") application.

Unless you are using threads to transfer data around .
I normally use a worker thread to fetch data, this gives following
advantages:
- doesn't block UI thread and you can entertain user at the same time
- allows multiple operations at same time (perhaps an operation takes time,
why wait if you can do other tasks?)
- allows killing an operation
And I am sure there are other benefits.

Makes sense?

 

[William \(Bill\) Vaughn]

Makes sense to me if the developer's coding skills are up to the challenge.
It's not that hard now-a-days. BWTs make it pretty easy (at least easier).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

 

[Cor Ligthert [MVP]]

Bill,

In my first language, if you don't personate somebody special, does a
sentence as

"It is a fool who", means the same as "It would be foolish to"

I thought that it was in English the same, if I that is a misunderstanding
from me, than sorry.

It seems that Miha took it personal what was in no way meant.

Cor

Ah, remember English is Cor's second (or third) language. I hope that he
does not know that calling someone a "fool" is an insult. I think he
might have meant "... it would be foolhardy" or "...it would be foolish"
to do something.

In response to your other comment. I have done some test that show that
retrieving a connection from the pool can be expensive. Remember that you
pay a bigger price when a pooled connection is first created. If you're
building several connections, the very first time is even more expensive
than subsequent connections. However, when a connection is reopened
(fetched from the pool), the data access layers reauthenticate the SSPI
credentials (each and every time). Depending on the server load, this can
also take more time on some systems. All of this is unneeded overhead for
a Windows Forms (what we used to call a "connected") application.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)