DataReader is not closed when the 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 happens to the data controller when the connection is closed (Conn.close)

Will the cursor used by the datareader be freed? or will he stay open?

If the cursor that the datareader is using is still open, when will it be automatically closed? or should I just close it manually?

Will this be the reason for the terrible "ORA-01000: maximum open cursors exceeded"?

early

Answer 1

 CommandBehavior.CloseConnection 

When you pass the values ​​above as an argument to ExecuteReader 1. there is no need to close the connection, explicitly, the connection closes when you close your reader.

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

Answer 2

You must create objects in the use block so that they are correctly positioned:

 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 either a connection or a datareader.

Answer 3

Just create a new object to read data after closing it.

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

Answer 4

Why you should not explicitly close the reader as follows.

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