DataReader not closed when Connection is closed, consequences?

Question

for example i have this code :

Sub Month()
    Dim Conn As New Data.OracleClient.OracleConnection
    Conn.Open()
    Try

        Dim Cmd As New Data.OracleClient.OracleCommand
        With Cmd
            .Connection = Conn
            .CommandType = Data.CommandType.Text
            .CommandText = "SELECT * FROM MONTH"
        End With
        Dim datareader As Data.OracleClient.OracleDataReader = Cmd.ExecuteReader
        While datareader.Read
            Response.Write(datareader(0))
        End While
    Catch ex As Exception
        Throw ex
    Finally
        Conn.Close()
    End Try
End Sub

What will happen to the datareader when the Connection is closed ( Conn.close)

Will the Cursor that is used by the datareader be freed ? or will it stay open ?

If the cursor that is used by the datareader is still open , when will it be automatically closed ? or should i just closed it manually ?

Will it cause the dreaded "ORA-01000: maximum open cursors exceeded" ?

thanks in advance

use "with" command for vb.net and using for "C#", this will automatically closes the connection of dr, not closing it involves a huge risk on performance — Deepesh, May 31 '11 at 05:18
@user751975 - "With" in VB.Net is not the same as "using" in C#. In VB.Net it is called "Using" — Chris Dunaway, May 31 '11 at 16:28
Year 2014 and I have the same question with Oracle. Did you find an answer? I am getting that dreaded message, but all my connections created with the using statement (which is the same as a try..finally) and I am unsure where to look for a answer. — Fernando, Jun 05 '14 at 14:29

score 2 · Answer 1 · answered May 31 '11 at 16:36

You should create the objects in a using block so they are properly disposed:

Using Conn As New Data.SqlClient.SqlConnection
    Conn.Open()

    Dim Cmd As New Data.SqlClient.SqlCommand
    With Cmd
        .Connection = Conn
        .CommandType = Data.CommandType.Text
        .CommandText = "SELECT * FROM MONTH"
    End With

    Using datareader As Data.SqlClient.SqlDataReader = Cmd.ExecuteReader()
        While datareader.Read()
            Response.Write(datareader(0))
        End While
    End Using
End Using

There is no need to call Close on either the connection or the datareader.

Awadallah · Answer 2 · 2012-10-23T10:03:41.270

Just make new object of data reader after it been closed

private void button2_Click(object sender, EventArgs e)
    {
        //SqlConnection cn1 = new SqlConnection();
        cn.ConnectionString = "server = .\\SQLEXPRESS ; database=store ; integrated security = true  ";
        SqlCommand cm = new SqlCommand("select * from emp", cn);
        cn.Open();
        SqlDataReader dr = cm.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(dr);
        dataGridView1.DataSource = dt.DefaultView ;
        //SqlCommand cm3 = new SqlCommand("select * from emp", cn1);
        SqlDataReader dr1 = cm.ExecuteReader();
        listBox1.Items.Clear();
        while (dr1.Read())
        {
            //listBox1.Items.Add(dr.GetString(2));
            listBox1.Items.Add(dr1["name"]);

        }
        cn.Close();
    }

score 1 · Answer 3 · answered May 31 '11 at 04:03

1

CommandBehavior.CloseConnection

When you pass above values as argument to ExecuteReader 1. there is no need to close connection explicitly connection get close when you close your reader

check full post : http://pranayamr.blogspot.com/2010/11/executereader-with-commanbehavior.html

answered May 31 '11 at 04:03

Pranay Rana

175,020
35
237
263

yes , that's true but what will happen if you only close the connection but not the reader itself. – Garry - May 31 '11 at 04:15
@Garry - reader always use live connection to read data from database ...if you close connection and than try to read it by reader it throws excetpion the connection is closed – Pranay Rana May 31 '11 at 04:24
1

yes, that is if you try to access it when the connection is closed, will the cursor that is used by the reader is closed as well ? or it will get stuck on the oracle server and causing the "max cursor exceeded" – Garry - May 31 '11 at 04:29
@Garry - check this answer : http://stackoverflow.com/questions/2519379/close-reader-before-closing-connection – Pranay Rana May 31 '11 at 04:59
already read that before, still it doesn't explain what will happen to the cursor or to my other questions – Garry - May 31 '11 at 05:08

score 0 · Answer 4 · answered May 31 '11 at 05:13

0

Why shouldn't you explicitly close the reader like this.

datareader.Close()

Dim Conn As New Data.SqlClient.SqlConnection
Conn.Open()
Try

    Dim Cmd As New Data.SqlClient.SqlCommand
    With Cmd
        .Connection = Conn
        .CommandType = Data.CommandType.Text
        .CommandText = "SELECT * FROM MONTH"
    End With
    Dim datareader As Data.SqlClient.SqlDataReader = Cmd.ExecuteReader
    While datareader.Read
        Response.Write(datareader(0))
    End While
    datareader.Close()
Catch ex As Exception
    Throw ex
Finally
    Conn.Close()
End Try

answered May 31 '11 at 05:13

Sreekumar P

5,900
11
57
82

that's what i'm trying to find out , why do we have to close the data reader when the connection itself will be closed in the finally , i'm trying to find out what could happen if the datareader is used like my code – Garry - May 31 '11 at 06:00
You should be creating the SqlConnection and the DataReader in a Using block since they both implement IDisposable. This way they will be properly disposed when you are finished with them. – Chris Dunaway May 31 '11 at 16:32

DataReader not closed when Connection is closed, consequences?

4 Answers4

Linked