Wrox Programmer Forums
BOOK: Beginning ASP.NET 1.0
This is the forum to discuss the Wrox book Beginning ASP.NET 1.0 with C# by Chris Goode, John Kauffman, Christopher L. Miller, Neil Raybould, S. Srinivasa Sivakumar, Dave Sussman, Ollie Cornes, Rob Birdwell, Matt Butler, Gary Johnson, Ajoy Krishnamoorthy, Juan T. Llibre, Chris Ullman; ISBN: 9780764543708
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 1.0 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 26th, 2004, 10:07 AM
Registered User
Join Date: Mar 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mdonner
Default sqldatareader close connection

based on the the examples in the book, i wrote the following code.
This code return a sqldatareader to a record set. My question is
how does one close the connection? in order words, what does the
following statment actually do?

Dim dr As SqlDataReader =
  spCommand.ExecuteReader(CommandBehavior.CloseConne ction)

 Public Function UserList(ByVal iclientno As Integer) As SqlDataReader
 'Return the list of userid from the user table based on a client number.
        Dim dbConnection As New SqlConnection(ConfigurationSettings.AppSettings("C onnectionStringKamtechLogin"))
        Dim spCommand As New SqlCommand("spGetUsers", dbConnection)
        spCommand.CommandType = CommandType.StoredProcedure

        Dim paramClientNo As New SqlParameter("@ClientNo", SqlDbType.Int)
        paramClientNo.Direction = ParameterDirection.Input
        paramClientNo.Value = iclientno

        Dim dr As SqlDataReader = spCommand.ExecuteReader(CommandBehavior.CloseConne ction)

        Return dr
    End Function

Old March 26th, 2004, 10:15 AM
Imar's Avatar
Wrox Author
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts

The CommandBehavior.CloseConnection enum makes sure that the Connection is closed when the associated DataReader is closed.

Consider this:

MySQLDataReader = UserList(10)
' Do something useful with MySQLDataReader

As soon as you close the reader, the connection it was using is then closed automatically as well. Because Readers are connected objects (the need an open connection to function correctly), you can't close the Connection before you are done with the reader. By specifying the CommandBehavior.CloseConnection enum, the creator of the UserList method can make sure that the connection is closed as early as possible.
So, it's important to explicitly close your reader, or you'll end up with open connections.



Imar Spaanjaars
Everyone is unique, except for me.
Old July 5th, 2007, 04:50 AM
Authorized User
Join Date: Aug 2006
Posts: 39
Thanks: 1
Thanked 0 Times in 0 Posts

Hi, I have an issue with following code. When this code is executed it shows open connection in my database of Sql Server. I run the 'EXEC SP_WHO' in sql query analyzer. How to actually close reader and connection? Please help me.....

    protected void button1_Click(object sender, EventArgs e)
        SqlDataReader dr;
        dr = GetReader("2");
        GridView1.DataSource = dr;

        // same code continues for different GetReader many times


    private SqlDataReader GetReader(string strTran)
        SqlConnection con = new SqlConnection(MyConnectionString);

        SqlCommand cmd = new SqlCommand("Reports", con);
        cmd.CommandType = CommandType.StoredProcedure;

        //do something here.

        SqlDataReader dr = cmd.ExecuteReader();
        return dr;

Similar Threads
Thread Thread Starter Forum Replies Last Post
close sql connection MunishBhatia ASP.NET 2.0 Professional 3 September 27th, 2007 10:32 AM
When should you close the database connection? Stickannn Pro PHP 0 September 13th, 2007 04:25 AM
ASP / ADODB - How to close connection? hamproof Classic ASP Databases 4 August 24th, 2007 01:43 PM
Command Object -- How to close db connection? wolfpack391 Classic ASP Databases 4 July 31st, 2004 05:28 AM
Open/Close SQL Connection within every method? flyin ADO.NET 8 May 18th, 2004 07:19 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.