65.9K
CodeProject is changing. Read more.
Home

Probable leak while using SqlDataReader in ADO.NET

starIconstarIconstarIconstarIconstarIcon

5.00/5 (6 votes)

Aug 5, 2010

CPOL
viewsIcon

23242

Probable leak while using SqlDataReader in ADO.NET

I was doing a bit of exploration on Connections with ado.net.While doing this exercise i realized that when using CloseConnection along with the reader i was under the impression that it closes by itself but actually it wont close the connection. I had pasted the code sample below which i executed during the test.

            while (true)
            {
                Thread.Sleep(2000);
                SqlConnection objConnection = default(SqlConnection);
                objConnection = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=.;Max Pool Size=1");
                SqlCommand objCommand = new SqlCommand("Select * from customers", objConnection);
                objConnection.Open();
                SqlDataReader reader = objCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                }
                Console.WriteLine((i++).ToString());
            }
So the tip is either create the sqlconnection object by using - using syntax (which ensures that it Disposes connection Object) or after the execution before leaving make a call to explicitly close the connection. This ensures that connection will be closed for sure. Hence no leak I hope this helps!. Regards, -Vinayak