return id from DB in C#

i need to get generated id from a new row in OracleDB

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 21019 Views - Last Post: 25 July 2010 - 04:53 AM Rate Topic: -----

#1 Muqp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 15-June 10

return id from DB in C#

Posted 21 July 2010 - 06:00 AM

I need to get an id from a new just inserted row in Oracle DB, i use C# and created a method that executes sql commands, and return me bool have sql command executed or not, and now i can`t emagine how it is possible to get an id without using an sql comand like "select max id from bla bla bla". Have someone an idea ?
Is This A Good Question/Topic? 0
  • +

Replies To: return id from DB in C#

#2 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 21 July 2010 - 06:08 AM

Use .ExecuteScalar()

This method returns the value of the first row of the first column of your SQL query.
Was This Post Helpful? 0
  • +
  • -

#3 Muqp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 15-June 10

Re: return id from DB in C#

Posted 21 July 2010 - 06:10 AM

i have already tried, but it always is nullable...
Was This Post Helpful? 0
  • +
  • -

#4 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 21 July 2010 - 06:29 AM

Then you're doing something wrong... Post your code...
Was This Post Helpful? 0
  • +
  • -

#5 Muqp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 15-June 10

Re: return id from DB in C#

Posted 21 July 2010 - 06:42 AM

public bool ExecuteSQL(string sql, params object[] values)
        {
            if (values.Length > 0)
            {
            	sql = string.Format( sql, values );
            }
                
			
			var l_oracleCommand = new OracleCommand
                                {
                                    CommandText = sql,
                                    Connection = _connection,
                                    CommandType = CommandType.Text
                                };
            try
            {
                l_oracleCommand.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }
			finally
			{
				l_oracleCommand.Dispose();
			}

            
            
           
            return true;
        }

this is a method that executes all kind of sql commands but not select, and i need to add something in it to get a last inserted row id.
Was This Post Helpful? 0
  • +
  • -

#6 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 21 July 2010 - 07:18 AM

I've never worked with Oracle DB, but I suggest you look at this article:

http://www.digitalco...SQL-Server.html
Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: return id from DB in C#

Posted 21 July 2010 - 07:23 AM

Using ExecuteScalar for an insert query would return null, since the query isn't selecting any data at all. ExecuteNonQuery would return rows affected, but not the new autonumber.

I'm not too familiar with Oracle, but it looks like there is a way to do this using "CURRVAL". It's attached to something called a sequence, which I am unfamiliar with. So I would imagine if you created a stored procedure that took the parameters you wanted to insert, did the insert, and then returned the sequence_name.CURRVAL, it would do what you need.
Was This Post Helpful? 0
  • +
  • -

#8 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 21 July 2010 - 07:26 AM

Exactly... You need to get a current sequence value...
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: return id from DB in C#

Posted 21 July 2010 - 07:26 AM

By the way, Flash, that method they showed in that link is flawed. @@IDENTITY returns the last autonumber used in the DB. So if another write to a table were to take place between your insert and select, you would get the wrong ID back. SCOPE_IDENTITY() is the preferred way to do that in SQL Server.

Also, I believe this belongs in the help forum, not the programmers forum.

This post has been edited by insertAlias: 21 July 2010 - 07:27 AM

Was This Post Helpful? 0
  • +
  • -

#10 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 21 July 2010 - 07:30 AM

http://forums.oracle...threadID=354998
Was This Post Helpful? 0
  • +
  • -

#11 Muqp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 15-June 10

Re: return id from DB in C#

Posted 21 July 2010 - 11:05 PM

In Oracle there is no such thing as @@identity, i can get last created id with : "select maxid from table", but it is not what i am looking for... make a procedure is not an option to... i have a task to create a metod that will be possible to use it in all kind of c# clients made for Oracle databases... and i was told that there is some kind of thing that can get last created id from an Oracle table without doing such things like "select maxid from table"... i am searching in net, but still coudn`t find the answer....

This post has been edited by Muqp: 21 July 2010 - 11:09 PM

Was This Post Helpful? 0
  • +
  • -

#12 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 22 July 2010 - 01:00 AM

Could you be more specific about this method that all kinds of c# clients should be possible to use it for Oracle database? Is this supposed to be some kind of generic method? What should this method do? Why do you even need this kind of method?
Was This Post Helpful? 0
  • +
  • -

#13 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1536
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: return id from DB in C#

Posted 22 July 2010 - 04:30 AM

moved to the general C# forum.
Was This Post Helpful? 0
  • +
  • -

#14 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: return id from DB in C#

Posted 22 July 2010 - 07:04 AM

OK, so I downloaded Oracle Database 10g Express Edition, set up a sample database and did a few sample inserts and this is what I came up with:

static void Main(string[] args)
{
    OracleConnection conn = new OracleConnection("Data Source=localhost; User Id=test;Password=test; Integrated Security=no;");
    OracleCommand cmd = new OracleCommand("insert into orders (name) values(:nameToInsert) returning id into :outId", conn);

    OracleParameter p1 = new OracleParameter("nameToInsert", OracleType.VarChar);
    OracleParameter p2 = new OracleParameter("outId", OracleType.Int32);

    p1.Value = "John Doe";
    p2.Direction = ParameterDirection.Output;

    cmd.Parameters.Add(p1);
    cmd.Parameters.Add(p2);


    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

    int insertedId = (int)p2.Value;
}



The 'insertedId' variable holds the last row id that has been inserted after executing the insert statement in the example above.

This post has been edited by FlashM: 23 July 2010 - 12:53 AM

Was This Post Helpful? 1
  • +
  • -

#15 sithius92   User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 164
  • Joined: 01-August 08

Re: return id from DB in C#

Posted 22 July 2010 - 09:34 AM

View PostFlashM, on 21 July 2010 - 06:08 AM, said:

Use .ExecuteScalar()

This method returns the value of the first row of the first column of your SQL query.


FlashM has it correct. I've never worked with Oracle but in MSSQL if I want to return the id of something in one of my tables, I would have to do something like this:

SqlCommand sqlCmd = new SqlCommand("SELECT id FROM table1 WHERE fieldA = 'something'", SQLConnectionGoesHere);

int idFromSqlTable = Convert.ToInt32(sqlCmd.ExecuteScalar());


Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2