Hello,
i have a doubt here, after a close the connection, i still can see the last stored procedure active in the LOCKS/PROCESSID in enterprise manager of SQL SERVER.
I think i'm closing the connection well
Try
IfMe.ID > 0Then
Dim strSqlAsString = "SP_CHANGECLASSCONTENTS"
Dim sqlcommandAsNew SqlCommand(strSql,New SqlConnection(ConnStr))
sqlcommand.CommandType = CommandType.StoredProcedure
sqlcommand.Parameters.Add("@.PerformActivation", SqlDbType.Bit).Value = 0
sqlcommand.Parameters.Add("@.PerformInactivation", SqlDbType.Bit).Value = 0
sqlcommand.Parameters.Add("@.PerformDelete", SqlDbType.Bit).Value = 1
sqlcommand.Parameters.Add("@.PerformUndelete", SqlDbType.Bit).Value = 0
sqlcommand.Parameters.Add("@.PermanentDelete", SqlDbType.Bit).Value = CBit(PermanentDelete)
sqlcommand.Parameters.Add("@.Class_ID", SqlDbType.Int).Value = _id
sqlcommand.Connection.Open()
sqlcommand.ExecuteNonQuery()
sqlcommand.Connection.Close()
Else
Err.Raise(10205, "CLASS", "CLASS ID is empty")
EndIf
Catch exAs Exception
_err = ex.Message
Return Err.Number
EndTry
Hi,
.NET Framework SQl data provider utilizes database connection pooling (there's one pool per unique connection string). When you close a SqlConnection, it is returned to this pool (there are min amount of connections in the pool ) and the hard connection to the db is not necessarily closed (connection ).
E.g this way usage of connections is kept efficient and opening & closing of "real connections" is kept minimum.
Here's more details
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp
|||ok tks,
but what if i use many procedures like this one several times, and each one creates it's own connection. Even if i close the connection well it still appears several processes concerning this procedures in SQLSERVER. does this degrades the performance ?
tks|||
Hi,
see the previous link for exact details.
Anyways, it happens within the limits of the connection pool. If there are free connections in the pool, they are utilized to fullfill those which you instantiate and run e.g even if you instantiate new connections in code, the pool might be able to deal with them without increasing its size (the actual pooled connections are of type SqlInternalConnection not those which SqlConnection objects which you instantiate).
If the pool cannot deal with new connection requests, it starts creating new ones (within the max limit of the pool). If max limit is reached then new connection requests are queued. Note also that after a burst usage of connections, they are also dropped from the pool to the mimum limit when lifetime has expired or connectiion is severed.
E.g the pools are there to keep connection count in control in situation like these.
