Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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#?

like image 510
Karthik Murugesan Avatar asked Apr 23 '11 18:04

Karthik Murugesan


People also ask

What is ODP Net Oracle?

Oracle Data Provider for . NET (ODP.NET) features optimized ADO.NET data access to the Oracle database. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, self-tuning statement cache, Application Continuity, and Fast Connection Failover.

Can we use with clause in update statement?

UPDATE is a DML statement that modifies rows in a table. An UPDATE statement can start with a WITH clause to define common table expressions accessible within the UPDATE .

Does ODP net require Oracle client?

Managed ODP.NET does not require any Oracle Client installation. The final step is to configure the database server connection, which is specific to your application environment.

What is OracleDataAdapter?

The OracleDataAdapter serves as a bridge between a DataSet and database for retrieving and saving data. The OracleDataAdapter provides this bridge by using Fill to load data from the database into the DataSet, and using Update to send changes made in the DataSet back to the data source.


1 Answers

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.

like image 56
Chris Taylor Avatar answered Oct 11 '22 07:10

Chris Taylor