c# - OracleDataReader 出错。错误 : Invalid operation. 连接已关闭

Question

当我尝试分配读取器时，C# 抛出异常:

无效操作。连接关闭

我尝试从返回单个单元格的查询中获取结果，该单元格中包含平均值。cmd 是一个 oraclecomand，我用它来向表中插入一行，到目前为止一切顺利。接下来我看到消息框，然后出现异常。

          try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Recipe Rated");
                OracleCommand cm = new OracleCommand("select round(avg(rating),1) from rates where id_rec = "+id);
                OracleDataReader reader = cm.ExecuteReader();
                reader.Read();
                textBox5.Text =""+reader.GetInt16(0);
            }

Answer 1

你应该打开连接，你也应该使用sql-parameters。希望这是正确的 oracle 语法，因为我无法测试它:

using(var con = new OracleConnection("ConnectionString Here"))
using(var cmd = new OracleCommand("ADD YOUR INSERT/UPDATE/DELETE", con))
{
    con.Open();
    cmd.ExecuteNonQuery();
    using (var cm = new OracleCommand("select round(avg(rating),1)As AvgRating from rates where id_rec = @id", con))
    {
        cm.Parameters.AddWithValue("@id", id);
        using (var reader = cm.ExecuteReader())
        {
            if (reader.Read())
            {
                textBox5.Text = reader.GetInt16(0).ToString();
            }
        }
    }
}

请注意，我已使用 using 语句来确保尽快处理所有非托管资源。它还会关闭连接(即使出现错误)。

编辑:由于您只选择一个值，我建议使用ExecuteScalar:

using (var cm = new OracleCommand("select round(avg(rating),1)As AvgRating from rates where id_rec = @id", con))
{
    cm.Parameters.AddWithValue("@id", id);
    object avgRating = cm.ExecuteScalar();
    if (!(avgRating is DBNull))
    {
        textBox5.Text = avgRating.ToString();
    }
}