Last post Apr 17, 2019 02:31 AM by Ackerly Xu
Apr 16, 2019 06:17 PM|HelloWorld18|LINK
I have database class in a .Net 4.0 web application that is using TableAdapters to make calls to a SQL database. Every now and then, users receive the error message below stating that the TableAdapter connection is already open. I've added logic to try
to close the connection if a connection is already open but It was my understanding that TableAdapters close connections on their own. Can anyone explain why this issue is occurring or offer any advice on how to prevent the issue from occurring? I've included
a sample of one of the TableAdapters from the connection class below.
Message - "The following error occurred:ExecuteNonQuery requires an open and available Connection. The connection's state is open"
Private Shared _userAdapter As UserTableAdapter = Nothing
Public ReadOnly Property UserAdapter() As UserTableAdapter
If _userAdapter Is Nothing Then
_userAdapter = New UserTableAdapter()
_userAdapter.Connection.ConnectionString = ConfigurationManager.ConnectionStrings("UserConnString").ConnectionString _userAdapter.CommandTimeout = 0
If _userAdapter.Connection.State = ConnectionState.Open Then
Apr 16, 2019 10:48 PM|mbanavige|LINK
You've made _userAdapter Shared
Since it's Shared, all users of your site will share the same instance of the adapter and multiple users may be accessing it simultaneously.
So if user A is opening and closing the adapters connection at the same time that user B is opening and closing the exact same adapters connection, you would see problems like this.
Shared is much more useful in a single user app like a desktop app, but is something to generally avoid in a multi user web app.
Apr 16, 2019 11:38 PM|HelloWorld18|LINK
Thanks for the response and explanation. I will remove Shared from all of the TableAdapter connections.
Was there any performance benefit by using Shared? Will the users notice any difference in performance without it?
Apr 17, 2019 02:31 AM|Ackerly Xu|LINK
By default connection of TableAdapter uses connection pool, so it will manage connection by itself.
Every time your close and open the connection , it will be returned and got from the connection pool.
The performance is ensured by connection pool.
For more information about connection pool and how it maintains connection with sqlserver , you could refer to