How to execute a update statement using Oracle ODP.Net in C#

Question

I am using Oracle.DataAccess.Client to work with Oracle database in my ASP.Net application. There is no help documentation in MSDN for ODP.Net and Oracle's documentation is really really bad. I am not able find the answer to this simple question.

Is it not possible to execute a simple update statement without having to build a dataset object and updating the dataset?

How to execute an update statement using Oracle ODP.Net in C#?

ODP.Net implements IDbCommand, IDbConnection and IDbDataAdapter why you cannot use them to prepare and execute your Update statement. — adt, Apr 23 '11 at 18:11

Chris Taylor · Accepted Answer · 2011-04-23T18:23:33.600

19

I will need to check the exact syntax, but here is some quick code off the top of my head

using (OracleConnection con = new OracleConnection(...))
{
  con.Open();
  OracleCommand cmd = con.CreateCommand();
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = "update table set col1 = :param1, col2 = :param2 where key = :keyValue";
  cmd.Parameters.AddWithValue("param1", 1);
  cmd.Parameters.AddWithValue("param2", "Text data");
  cmd.Parameters.AddWithValue("keyValue", "1");
  cmd.ExecuteNonQuery();
}

The above creates a command object sets the command up to execute an SQL Update statement, in this example I show one way to setup a parameterized query, you should always go with a parameterized query. Once the command is setup you just call ExecuteNonQuery to actually execute the command.

edited Apr 23 '11 at 18:23

answered Apr 23 '11 at 18:17

Chris Taylor

52,623
10
78
89

2

Sweet! Thank you! Although I couldn't find "AddWithValue". ExecuteNonQuery was the key, i tried it with out a parameterized query and it worked. I think I can improvise it from here on. Really appreciate your time!! – Karthik Murugesan Apr 23 '11 at 20:26
2

Karthik, the advantage of using a parameterized query is that it does all the SQL injection protection for you. – Keith Davidson Jul 23 '15 at 23:40
1

Heya! I'd only change a few things to add the transaction control that allows Oracle to perform update commands: `code` OracleCommand cmd = con.CreateCommand(); OracleTransaction oratrans = con.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = oratrans; . . . cmd.ExecuteNonQuery(); oratrans.Commit(); Yours, – Alexey Honorio Mar 08 '17 at 18:10

nshouppuohsn · Answer 2 · 2017-01-27T04:02:25.367

So after a bit of sleuthing and working this one out for a while, I found that the method I used to add a new parameter to the connection command is as follows. I did not find the method as was stated in the previous post. Mind you I am using a query object that I am passing the values around with.

  public Boolean InsertMethod(Query _query)
    {
        var success = false;
        var queryString = string.Format(@"INSERT INTO TABLE(ID, OWNER, TEXT) VALUES (TABLE_SEQ.NEXTVAL,:OWNER, :TEXT)");
        try
        {
            using (OracleConnection con = new OracleConnection(ConString))
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = queryString;
                cmd.Parameters.Add("OWNER", _query.Owner);
                cmd.Parameters.Add("TEXT", _query.Text);          

                int rowsUpdated = cmd.ExecuteNonQuery();

                if (rowsUpdated > 0) success = true;
            }

            return success;
        }
        catch (Exception ex)
        {
            log.Error(ex);
            throw;
        }
    }

Looks like you're inserting twice. two `cmd.ExecuteNonQuery();` — Halter, Jan 23 '17 at 20:32

score 0 · Answer 3 · answered Apr 23 '11 at 18:26

0

Further to @Chris's answer, here is the documentation page of OracleParameter class which has sample code on using OracleCommand to execute Updates.

EDIT: Here is the entry point for ODP.net documentation.

answered Apr 23 '11 at 18:26

shahkalpesh

33,172
3
63
88

1

The link to the sample code you provided is talks about how to fetch data using OracleDataReader and not on how to update data (as per my initial question). I already went through the ODP.net documentation and I couldn't find a similar code sample for updating. I wish MSDN has documented this :(. – Karthik Murugesan Apr 23 '11 at 20:36

How to execute a update statement using Oracle ODP.Net in C#

3 Answers3

Linked