CODEWITHSUNDEEP
c#sqloracle-database
Jitesh Dammani
Jitesh Dammani

Reputation: 81

How to run one update and commit in single command in ORACLE

I want to run a update query in ORACLE but to reflect the change of that query (means updating the value in database with receptive values) i have to run a commit; after that query (then only the value change will be updated in the database). So, how can i pass the update statement and the commit in same line through C#.

i am running the query like below,

UPDATE table_name 
   SET columnname1 = 'N', 
       columnname2 = 1
 WHERE columnname3 = '-2085371064';

COMMIT;

Upvotes: 1

Views: 20840

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186833

Usually, you don't want explicit commit since all the changes will be auto commited on the connection close. However, if you insist on COMMIT you can do it with a help of Oracle's anonymous block: 

 //TODO: put the right connection here
 using (OracleConnection con = new OracleConnection(ConnectionStringHere)) {
   con.Open();

   string sql = 
     @"BEGIN -- Anonymous block: run these queries (update, commit) together
         UPDATE table_name 
            SET columnname1 = 'N', 
                columnname2 = 1
          WHERE columnname3 = '-2085371064'; -- Is it really a string?

         COMMIT; -- Not necessary, but possible
       END;";

   using (var q = con.CreateCommand()) {
     q.CommandText = sql;

     q.ExecuteNonQuery();   
   } 
 }

Upvotes: 2

NicoRiff
NicoRiff

Reputation: 4883

Supposing you are using ADO.NET, you can make use of Commit() method:

using (OracleConnection connection = new OracleConnection(connectionString))
{
    connection.Open();

    OracleCommand command = connection.CreateCommand();
    OracleTransaction transaction;

    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    command.Transaction = transaction;

    try
    {
        command.CommandText = 
            "UPDATE table_name    SET columnname1 = 'N',        columnname2 = 1 WHERE columnname3 = '-2085371064';";
        command.ExecuteNonQuery();
        transaction.Commit();
    }
    catch (Exception e)
    {
        transaction.Rollback();
        Console.WriteLine(e.ToString());
    }
}

Upvotes: 2

Related Questions