Connection关闭时DataReader没有关闭，后果如何？

Question

例如我有这样的代码：

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

当连接关闭（Conn.close）时数据读取器会发生什么

数据读取器使用的游标会被释放吗？或者它会保持开放吗？

如果datareader使用的游标仍然打开，什么时候它会自动关闭？或者我应该手动关闭它？

它会导致可怕的“ORA-01000：超出最大打开游标数”吗？

提前致谢

Answer 1

您应该在 using 块中创建对象，以便正确处置它们：

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

无需在连接或数据读取器上调用 Close。

Answer 2

CommandBehavior.CloseConnection

当您将以上值作为参数传递给 ExecuteReader 时
1.不需要显式关闭连接，当您关闭阅读器时，连接会关闭，

检查全文：http://pranayamr.blogspot.com/2010/11/executereader-with-commanbehavior.html

Answer 3

只需在关闭后创建数据读取器的新对象

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

为什么你不应该像这样明确地关闭阅读器。

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