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

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.

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;
    }