You should NOT pass the connection object among forms. Basically, the pattern when using a connection to SQL Server is to create the connection, open it, perform your operation, then close the connection.
To that end, you should have a public static method somewhere which will generate your SqlConnection which you would use in a Using statement, like so:
Using connection As SqlConnection = GetConnection
' Use connection here.
End Using
That should prevent the processes from stacking up on the server.
There are several factors included, that you need to test further.
*)Please make sure that your connection string is indeed correct. You can try to connect to the same server but different database. But please make sure that the connection string is correct, so that we can eliminate one possibility.
*)Is there any firewall between the DB Server and App Server ? Because the firewall might terminate the existing SQL Server connection if the connection is left open after some time. Some firewall hardware has some configuration to terminate the SQL connection automatically after X time
*)I think there is a way or some code snippet to test whether the SQL Connection has been established or not. or Alternatively please do this :
- Create a file i.e. connection.udl (Make sure that it's not connection.udl.txt , but should be connection.udl) . this file should be created at the APP server.
- Double click that file
- Configure the necessary connection string,
- and finally click Test Connection
- if the test connection works successfully, that means it can connect well to the DB server
EDIT :
This particular error line is quite interesting : System.Data.SqlClient.SqlConnection.PermissionDemand()
as can be seen from the error message stack trace, maybe you need to google around using the PermissionDemand keyword.
Another thing: have you tried to use Windows Integrated Authentication or SQL Authentication ? Try to use a Windows user OR SQL user and check the result. I see that in your connection string, in the web.config, you are using Integrated Security=true, that means it will take the authentication of the user account who run the application pool of the ASP.NET web application. I suspect this user account doesn't have sufficient permission to access SQL Server.
To localize the problem, try to authenticate using SQL Authentication, if it's works, that means this Integrated Security is the culprit
Can you check the user account who run the application pool of the current ASP.NET web application ?
Best Answer
You failed to mention a key piece of information: It succeeds the first time Load() is called, but then fails forever after.
When using Using, Dispose() is called on the used variable when the Using block exits. So in your scenario:
At this point, the SqlConnection object still exists, and is still pointed to by _Connection. It's no longer in a usable state though, since it's been Dispose()d. When the second call to Load() comes in:
You're mixing conflicting approaches to connection management. Keeping the connection object around as a member of the class implies that you want to keep the connection alive for the life of the SomeEntity object, but using Using implies that you want to create and destroy the connection on the fly with each usage.