Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > BOOK: Beginning ASP.NET 1.0
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 26th, 2004, 09:07 AM
Registered User
 
Join Date: Mar 2004
Location: montclair, nj, USA.
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)
        spCommand.Parameters.Add(paramClientNo)
        paramClientNo.Direction = ParameterDirection.Input
        paramClientNo.Value = iclientno

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

        Return dr
    End Function

Reply With Quote
  #2 (permalink)  
Old March 26th, 2004, 09:15 AM
Imar's Avatar
Wrox Author
Points: 67,271, Level: 100
Points: 67,271, Level: 100 Points: 67,271, Level: 100 Points: 67,271, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,227
Thanks: 66
Thanked 1,414 Times in 1,394 Posts
Default

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
MySQLDataReader.Close()

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.

Cheers,

Imar



---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old July 5th, 2007, 04:50 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 39
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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;
        GridView1.DataBind();
        dr.Close();

        // 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.

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        return dr;
    }

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 12:30 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.