C# equivalent to java 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:

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 am getting exeption: "Operation is not valid due to the current state of the object."

"an exeption" - what one(s)? Can you give the type and message of these exceptions? — Damien_The_Unbeliever, Jul 29 '13 at 10:22
What exception is shown? Also maybe stupid question, but did you open your connection (conn.Open()) before using it? — BudBrot, Jul 29 '13 at 10:23
Try to move the Prepare call before the Parameters.Add call and Open connection. — Viacheslav Smityukh, Jul 29 '13 at 10:24
You should indeed be using a colon (`:` rather than an `@` for Oracle) — Timothy Groote, Jul 29 '13 at 10:29
the exception is in Hebrew, but it says, unhandle exeption in the ExecuteNonQuery line. conn is open, moving prepare doest help — susparsy, Jul 29 '13 at 10:29
tryed cmd.Parameters.Add(":message", msg); doesnt work still — susparsy, Jul 29 '13 at 10:30
And would you mind adding that exception? even if it *is* in hebrew, google should help us get something out of it — Timothy Groote, Jul 29 '13 at 10:30

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

2

A typical Oracle prepared statement looks like this.

(notice that only the definition in the prepared statement has the : colon, and the one in the cmd.Parameters.AddWithValue call does not)

String msg = "something here";

using (OracleConnection con = new OracleConnection(...insert connection params here...))
{
  con.Open();
  OracleCommand cmd = con.CreateCommand();
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = @"
        insert into daily_cdr_logs
        (message) 
        values 
        (:message)";
  cmd.Parameters.AddWithValue("message", msg);
  cmd.ExecuteNonQuery();
}

answered Jul 29 '13 at 10:37

Timothy Groote

8,614
26
52

i already have an open connection and it cant find AddWithValue – susparsy Jul 29 '13 at 10:41
The parameter name supplied to `AddWithValue(string, object)` may have be prepended with a colon and may be not. It is optional. – Anton Shepelev Oct 23 '20 at 17:22

score 0 · Answer 2 · edited May 23 '17 at 11:44

I would suggest doing it like this:

//create a connection
string conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString;
OracleConnection con = new OracleConnection(conString);

//create SQL and insert parameters
OracleCommand cmd = new OracleCommand("insert into daily_cdr_logs (message) values (:_message)", con);
cmd.Parameters.Add(new OracleParameter("_message", msg));

    try
    {
        //if connection is closed, open it
        if (con.State == ConnectionState.Closed)
             con.Open();

             //execute query
             cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
         //do something with the error
    }
    finally
    {
         //if connection is open, close it
         if (con.State == ConnectionState.Open)
         con.Close();
    }

I have created my connection string in my web.config file, demonstrated here.

score 0 · Answer 3 · answered Oct 23 '20 at 17:15

First—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 parameters at the prepare stage:

par = cmd.CreateParameter();
par.ParameterName = ":message";
cmd.Parameters.Add( par );

and supply the value in each invocation of the prepared query:

cmd.Parameters[i].Value = message;

if you keep track of your parameters, and

cmd.Parameters[":message"].Value = message;

if you don't.

Third—the Prepare() method in ODP.NET does nothing. It is there only to satisfy the IDbCommand interface, that requres a Prepare() method. It is rather deplorable that although Oracle have implemented 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 repeating queries locally, which should help a bit.

As to the exception you are getting, I think it has nothing to do with .Prepare(). Try the same code without it and see if the exception remains. The most probable cause is that your connection is not open, so that it will not work regardless of .Prepare().

C# equivalent to java prepared statement

3 Answers3

Linked

Related