• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1275
  • Last Modified:

C# oracle query - Lock, then update, then select

I am trying to write a oracle query that will lock a table then update the value by one then select the value.

But how would i write this to activate?

Here is what i have so far:

StringBuilder ticketNumber = new StringBuilder();
                string oradb = "Data Source=whs01_d; User Id=emuuser; Password=";
                string sqlLock = "lock table helpq.qw_keys in exclusive mode ";
                string sqlUpdate = "update helpq.qw_keys set keyvalue=keyvalue+1 where tablename='calls' ";
                string sqlSelect = "select keyvalue "
                                    + "from helpq.qw_keys "
                                    + "where tablename='calls' ";

                OracleConnection conn = new OracleConnection();
                conn.ConnectionString = oradb;

                conn.Open();

                OracleCommand cmdLock = new OracleCommand(sqlLock, conn);
                OracleCommand cmdUpdate = new OracleCommand(sqlUpdate, conn);
                OracleCommand cmdSelect = new OracleCommand(sqlSelect, conn);

                OracleDataReader dr = cmdSelect.ExecuteReader();

                if (dr.HasRows == true)
                {

                    while (dr.Read())
                    {
                        ticketNumber.AppendFormat(dr["keyvalue"].ToString());
                    }

                    return (ticketNumber.ToString());
                }

Open in new window


How do i get the lock to happen then the update? they aren't running.

God Bless!

0
benandbecky
Asked:
benandbecky
  • 8
  • 8
1 Solution
 
slightwv (䄆 Netminder) Commented:
I don't see where you are executing the other commands.

something like: cmdLock.ExecuteNonQuery();

also, Why lock the entire table to update a few rows?  Typically in Oracle there's no need to do this but if you still insist, why not a select for update.
0
 
benandbeckyAuthor Commented:
Can you show me the code how to do this?

Thanks!
0
 
slightwv (䄆 Netminder) Commented:
code to do what?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
benandbeckyAuthor Commented:
select for an update
0
 
slightwv (䄆 Netminder) Commented:
Just add 'FOR UPDATE' to the end of the select:

http://psoug.org/reference/select.html
0
 
benandbeckyAuthor Commented:
hmm its still not working the execute no query didnt work either.

Any more ideas or examples?

Thanks!
0
 
slightwv (䄆 Netminder) Commented:
you added 'for update' to the end of sqlSelect.

Execute the reader to make sure it executes and locks the rows.

Then you did something like:

...
                if (dr.HasRows == true)
                {

                    while (dr.Read())
                    {
                        ticketNumber.AppendFormat(dr["keyvalue"].ToString());
                        cmdUpdate.ExecuteNonQuery();
                    }
...
0
 
benandbeckyAuthor Commented:
Its still not working, i don't know why not.
0
 
slightwv (䄆 Netminder) Commented:
Can you post the complete code as it looks now?
0
 
benandbeckyAuthor Commented:

StringBuilder ticketNumber = new StringBuilder();
                string oradb = "Data Source=whs01_d; User Id=emuuser; Password=emuuser1";
                string sqlLock = "lock table helpq.qw_keys in exclusive mode " ;
                string sqlUpdate = "update helpq.qw_keys "
                                        + "set keyvalue=keyvalue+1 "
                                        + "where tablename='calls' " ;
                string sqlSelect = "select keyvalue "
                                    + "from helpq.qw_keys "
                                    + "where tablename='calls' "
                                    + "for update " ;

                

                OracleConnection conn = new OracleConnection();
                conn.ConnectionString = oradb;

                conn.Open();

                OracleCommand cmdSelect = new OracleCommand(sqlSelect, conn);
                OracleCommand cmdUpdate = new OracleCommand(sqlUpdate, conn);


                OracleDataReader dr = cmdSelect.ExecuteReader();

                if (dr.HasRows == true)
                {

                    while (dr.Read())
                    {
                        //Class.Connection.CreateCommand(sqlLock, oradb);
                        ticketNumber.AppendFormat(dr["keyvalue"].ToString());
                        cmdUpdate.ExecuteNonQuery();
                    }

                    return (ticketNumber.ToString());
                }
                else
                {
                    ticketNumber.AppendFormat("<FONT COLOR='#FF0000'>Error Creating Ticket Number</FONT></b>");

                    return (ticketNumber.ToString());
                }
            }

Open in new window

0
 
benandbeckyAuthor Commented:
It just stays processing forever
0
 
slightwv (䄆 Netminder) Commented:
I created a small test case.  It uses Oracle's ODP.Net not the MSoft provider.  It was tested and works using Oracle 10.2.0.3.

Every execution increments the value by 1.

Given the following table:
--------------------------------
drop table tab1 purge;
create table tab1( tablename varchar2(10), keyvalue number );

insert into  tab1 values('calls',0);
commit;

using System;
using System.Data;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class Bob
{

	public static void Main(string[] args)
	{

			OracleConnection con = new OracleConnection();
			OracleDataReader dr;

			con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud");

			OracleCommand sqlSelect = new OracleCommand();
			OracleCommand sqlUpdate = new OracleCommand();

			sqlSelect.CommandText = " select keyvalue from tab1 where tablename='calls' for update ";
			sqlUpdate.CommandText = " update tab1 set keyvalue = keyvalue+1 where tablename='calls' ";


			sqlSelect.Connection = con;
			sqlUpdate.Connection = con;

			con.Open();

        	dr = sqlSelect.ExecuteReader();
        	if(dr.Read()) {
        		sqlUpdate.ExecuteNonQuery();
        	}

			con.Close();
			con.Dispose();

	}

}

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Are you just wanting to generate an incrementing number?  

If there can be gaps,  check out Oracle Sequences.  This is what they are for.

>>It just stays processing forever

I'm wondering if one of your previous attempts still has the row locked and the current run is waiting for it to be released...

I'm also not sure why you why you have the WHILE loop on the data reader.  Can the select statement ever return more than 1 row?
0
 
benandbeckyAuthor Commented:
How would i parse that value out?

i tried this code:

dr = sqlSelect.ExecuteReader();
                if (dr.Read())
                {
                    sqlUpdate.ExecuteNonQuery();
                }

                ticketNumber2 = int.Parse(dr["keyvalue"].ToString());
                
			    con.Close();
			    con.Dispose();                    
                return (ticketNumber2);
                
            }

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I'm not at work anymore so I can't provide working code but for a datareader is something like:

ticketNumber2 = dr.GetDecimal(0);
0
 
benandbeckyAuthor Commented:
Fast and responsive!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now