c# - OracleCommand.Executenonquery 抛出 'System.InvalidOperationException'

Question

以下函数抛出 System.InvalidOperationException:

internal void executeNonQuery(string connectionString, OracleCommand cmd)
    {
        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            using (cmd)
            {
                conn.Open();
                cmd.ExecuteNonQuery(); //here is the error
                conn.Close();
            }
        }
    }

附加信息是:

Operation is not valid due to the current state of the object.

我尝试在表格中插入一行。是否有其他方法可以执行此操作或修复此错误？

编辑:我使用以下方法在 binaryManager 类中构建查询:

internal object[] binaryInsert(string tblName, string tblQuery, int conStrgID, int cq)
    {
        object[] retValues = new object[3];
        Stream myStream = null ;
        OracleConnection con = null;
        string conString = qm.getConnectionString("ConnectionStringToMyDB"); //is correct

        byte[] data = GetBytes(tblQuery);

        String sql = "INSERT INTO MYTABLES VALUES (NULL, '" + tblName + "', ':tblQueryBlob', " + conStrgID + ", " + cq + ")";


        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = sql;  // Set the sql-command
        cmd.Connection = con;   //con is an OracleConnection

        OracleParameter param = cmd.Parameters.Add("tblQueryBlob", OracleDbType.Blob); //Add the parameter for the blobcolumn
        param.Direction = ParameterDirection.Input;

        param.Value = data;     //Asign the Byte Array to the parameter

        //command containts the parameter :tblQueryBlob with its value
        retValues[0] = cmd;
        retValues[1] = conString;

        return retValues;
    }

private byte[] GetBytes(string str)
    {
        byte[] bytes = new byte[str.Length * sizeof(char)];
        System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
        return bytes;
    }

我使用以下代码从另一个类调用 binaryInsert 方法:

BinaryManager bm = new bBinaryManager();
string sqlQuery = "large string with 5100 characters";
object[] binaryValues = bm.binaryInsert("TextTbl", sqlQuery, 1, 1);
string conString = binaryValues[1].ToString();
OracleCommand cmd = (OracleCommand)binaryValues[0];

QueryManager qm = new QueryManager();
qm.executeNonQuery(conString, cmd);

EDIT2(执行阅读器):

int csID = 1;
using (OracleConnection conn = new OracleConnection(conString))
                {
                    using (OracleCommand myCommand = new OracleCommand(query, conn))
                    {
                        conn.Open();
                        myCommand.Connection = conn; // FIX!
                        using(OracleDataReader read = myCommand.ExecuteReader())
                        {
                            if (read.HasRows)
                            {
                                while (read.Read())
                                {
                                    if (read[0] == System.DBNull.Value)
                                    {
                                        TableObject tableDBNull = new TableObject(csID, Convert.ToString(read[1]), (byte[])read[2], Convert.ToInt32(read[3]), Convert.ToInt32(read[4]));
                                        allTables.Add(tableDBNull);
                                        csID++;
                                    }
                                    else
                                    {
                                        TableObject tableDBNNull = new TableObject(Convert.ToInt32(read[0]), Convert.ToString(read[1]), (byte[])read[2], Convert.ToInt32(read[3]), Convert.ToInt32(read[4]));
                                        allTables.Add(tableDBNNull);
                                    }
                                }
                            }
                        }
                        conn.Close();
                    }

错误发生在 if block 中:if (read[0] == System.DBNull.Value)

Answer 1

问题不在于查询(查询错误引发来自 Oracle 命名空间的异常)。

在您的情况下，该命令与任何连接无关。
您可以通过某些方式解决它，即

internal void executeNonQuery(string connectionString, OracleCommand cmd)
{
    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        using (cmd)
        {
            conn.Open();
            cmd.Connection = conn; // FIX!
            cmd.ExecuteNonQuery(); //here is the error
            conn.Close();
        }
    }
}

通常我更喜欢另一种方式来做到这一点

OracleConnection con = new OracleConnection(connectionString);
// Creating command from the connection
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = ...........
cmd.ExecuteNonQuery();