c# oracle sql prepared statement

Question

I am trying to create a prepared statement in c#.

For some reason everything I try ends up with an exeption.

*. This is my code for now:

the exception i am getting is: "Operation is not valid due to the current state of the object."

using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "insert into daily_cdr_logs " +
                "(message) " +
                "values " +
                "(:message)";

        cmd.Parameters.Add(:message, msg);
        //OracleDbType.Int32, postpaid_duration, ParameterDirection.Input);
        cmd.Prepare();
        cmd.ExecuteNonQuery();
    }

i have seen some people doing this with Parameters.AddWithValue. but for some reason i cant find the function AddWithValue in OracleCommand.Parameters, i have imported Oracle.DataAccess.Client and i have oracle DataAccess refferance, using visual studio 2010.

Don't you asked it before ? http://stackoverflow.com/questions/17921492/c-sharp-equivalent-to-java-prepared-statement — Soner Gönül, Jul 29 '13 at 13:24
The exception is raised by the Prepare line or by the ExecuteNonQuery line? — Steve, Jul 29 '13 at 13:28
try `cmd.Parameters.Add("message", DbType.Varchar).value = msg;` — Snow Blind, Jul 29 '13 at 13:53
Fist—your code will not compile, because `:message` is not a valid *C#* identifier. The line adding the parameter should be: `cmd.Parameters.Add(":message", msg);` Second—it is more efficient to *register* at prepare stage and supply the values in each invocation of the prepared query. — Anton Shepelev, Oct 23 '20 at 17:08
The `Prepare()` method in *ODP.NET* does [nothing](https://docs.oracle.com/database/121/ODPNT/OracleCommandClass.htm#ODPNT596). It is there only to satisfy the *IDbCommand* interface, that requres a [`Prepare()`](https://learn.microsoft.com/en-us/dotnet/api/system.data.idbcommand.prepare) method. — Anton Shepelev, Oct 23 '20 at 17:09
It is rather deplorable that although *Oracle* have [implemented](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) the prepare functionality for the native *JDBC* interface, they decided not against it for *ADO.NET*. On the other hand, *ODP.NET* is advertised to [cache](https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/2015/optimizing_data_access_performance_with_odpnet/optimizing%20data%20access%20performance%20with%20odpnet.html#section1) repeating queries locally, which should help. — Anton Shepelev, Oct 23 '20 at 17:09

score 2 · Answer 1 · answered Jul 29 '13 at 13:30

The general rule is open a connection only when necessary and close / dispose it as soon as you're done. The connection pool is managed automatically, so opening a new connection is not reall a heavy task. When you open a connection, close it and open the same connection again, a new native connection isn't really created. The previous one is fetched from the connection pool. The defaults are usually fine so I'd recommend not passing pooling stuff when creating the connection. If you're executing 10 commands in succession, open a connection, execute 10 commands and close it. Do that only if they are guranteed to be executed one after the other and you're not "holding on" to connections when doing other things. If you need to do something else, close the connection before doing so. This usually results in best performance.

Almost. Try:

cmd = new command(...);
cmd.parameters.Add(...)
cmd.parameters.Add(...)

then connection.open() //execute command and use results

score 0 · Answer 2 · answered Jul 29 '13 at 13:24

0

The connection needs to be open before you call cmd.ExecuteNonQuery().

Try this:

cmd.Connection.Open();
cmd.ExecuteNonQuery();

answered Jul 29 '13 at 13:24

Trevor Pilley

16,156
5
44
60

Robert · Answer 3 · 2013-07-29T14:07:56.367

0

Try this way:

using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "insert into daily_cdr_logs " +
                "(message) " +
                "values " +
                "(:message)";

         OracleParameter pMsg = new OracleParameter("message",OracleDbType.Varchar2);
            pMsg.Value = msg;

         cmd.Parameters.Add(pMsg);

        //OracleDbType.Int32, postpaid_duration, ParameterDirection.Input);
        cmd.Prepare();
        cmd.ExecuteNonQuery();
    }

edited Jul 29 '13 at 14:07

answered Jul 29 '13 at 13:29

Robert

25,425
8
67
81

DbType.Varchar; does not exists , maybe oracledbtypes? – susparsy Jul 29 '13 at 13:55
@susparsy Maybe `Varchar2` ? I edited the answer. – Robert Jul 29 '13 at 13:59

score 0 · Answer 4 · answered Jul 29 '13 at 13:31

The Sql Server version of the Prepare command requires

Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met

Because the Prepare command is defined in the IDbCommand class and inherited by other ADO.NET classes I suppose that this is true also for Oracle, thus add the following lines to add the parameter to your command (and be sure the connection is already open when you call Prepare)

cmd.CommandText = "insert into daily_cdr_logs (message) " +
                  "values (:message)";
OracleParameter p = new OracleParameter(":message", DbType.Varchar);
p.Size = 2000; // as a wild guess
p.Value = msg;
cmd.Parameters.Add(p);
cmd.Prepare();
cmd.ExecuteNonQuery();

c# oracle sql prepared statement

4 Answers4