SQL Connection not released (VB.Net)
-
Hello friends, I am facing a strange problem here. I have a class with a private member of type string. This is actually a connection string. I've a public method getConnection that creates an instance of sqlConnection and returns it. I simply set reference to this returned object in my pages and after using this connection I close this connection using the reference. But when I go and check "connections using this database" through sql server enterprise manager it shows me 5 connections there even if I close the application. I am pasting the code snippets to make you understand the situation more clearly. ''''''''''''''''''''This is my vb class''''''''''''''''' Public Class DBConnection Private connString As String Public Sub New() connString = "Server=myPC; Database=testDB;UID=sa;Password=;" End Sub Public Function getConnection() As SqlClient.SqlConnection Dim connObj As New SqlConnection(connString) connObj.Open() Return connObj End Function End Class ''''''''''''''''''''This is code behind file''''''''''''''''' Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here populatePendingConsignmentsGrid() End Sub Private Sub populatePendingConsignmentsGrid() Dim objConn As SqlClient.SqlConnection Dim objDR As SqlClient.SqlDataReader Dim objConsignment As New Consignment Dim i As New Integer Dim consignmentObj As New Consignment Dim arrList As ArrayList Dim objDBConnection As New DBConnection objConn = objDBConnection.getConnection() objConsignment.getAllPendingConsignmentsDR(objConn, objDR) PendingConsignmentsDG.DataSource = objDR PendingConsignmentsDG.DataBind() objDR.Close() objConn.Close() objConn.Dispose() End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''' With best regards, Zee_Zee
-
Hello friends, I am facing a strange problem here. I have a class with a private member of type string. This is actually a connection string. I've a public method getConnection that creates an instance of sqlConnection and returns it. I simply set reference to this returned object in my pages and after using this connection I close this connection using the reference. But when I go and check "connections using this database" through sql server enterprise manager it shows me 5 connections there even if I close the application. I am pasting the code snippets to make you understand the situation more clearly. ''''''''''''''''''''This is my vb class''''''''''''''''' Public Class DBConnection Private connString As String Public Sub New() connString = "Server=myPC; Database=testDB;UID=sa;Password=;" End Sub Public Function getConnection() As SqlClient.SqlConnection Dim connObj As New SqlConnection(connString) connObj.Open() Return connObj End Function End Class ''''''''''''''''''''This is code behind file''''''''''''''''' Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here populatePendingConsignmentsGrid() End Sub Private Sub populatePendingConsignmentsGrid() Dim objConn As SqlClient.SqlConnection Dim objDR As SqlClient.SqlDataReader Dim objConsignment As New Consignment Dim i As New Integer Dim consignmentObj As New Consignment Dim arrList As ArrayList Dim objDBConnection As New DBConnection objConn = objDBConnection.getConnection() objConsignment.getAllPendingConsignmentsDR(objConn, objDR) PendingConsignmentsDG.DataSource = objDR PendingConsignmentsDG.DataBind() objDR.Close() objConn.Close() objConn.Dispose() End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''' With best regards, Zee_Zee
Hi, This is due to connection pooling option available by default in .Net. ADO.Net manages a pool of connection, and this pool is identified by the connection string you specify. If you create connections as you have specified then new connection's object would be added in the pool. These objects are released when anything connected through these connections is properly disposed and then these connections are also disposed propery and not used elsewhere. However ADO.Net takes its own decision when to finally release these connection. Try reading some article on connection pooling either from msdn or some are also available on this site.
-
Hi, This is due to connection pooling option available by default in .Net. ADO.Net manages a pool of connection, and this pool is identified by the connection string you specify. If you create connections as you have specified then new connection's object would be added in the pool. These objects are released when anything connected through these connections is properly disposed and then these connections are also disposed propery and not used elsewhere. However ADO.Net takes its own decision when to finally release these connection. Try reading some article on connection pooling either from msdn or some are also available on this site.