[C#]OracleCommand.Executenonquery 抛出 '到当前页面

标签: sql Oracle C#
发布时间: 2017/2/24 3:13:53
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

以下函数抛出 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();
            }
        }
    }

额外的信息是︰

由于该对象的当前状态,操作无效。

我试着向表中插入一行。有另一种方式做到这一点,或若要修复此错误吗?

编辑︰ 我在中生成查询 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 (ExecuteReader):

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 块︰if (read[0] == System.DBNull.Value)

解决方法 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();
赞助商