locked
Get last inserted value from oracle database from inline c# coding RRS feed

  • Question

  • User1624753842 posted

    Hi all ,

            How i get last generated id from  oracle database. I not using store procedure. I am using inline code. below i pasting my code

    conncection.Open();
    Object emptyObj;
    emptyObj = Convert.DBNull;
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conncection;
    cmd.CommandType = CommandType.Text;

    string sqlSelectQuery = @"insert into ROLE_MSTR(ROLE_NM, DSPLY_TXT, DESC_TXT, ROLE_TYPE, CRTD_BY, CRTD_DT, STATUS) Values(:roleName,:displayText,:roleDescription,:roleType,:createdBy,:createdDate,:status)";
    cmd.CommandText = sqlSelectQuery;
    cmd.Parameters.Add(":roleName", userRole.RoleName);
    cmd.Parameters.Add(":displayText", userRole.DisplayText);
    cmd.Parameters.Add(":roleDescription", userRole.RoleDescription);
    cmd.Parameters.Add(":roleType", "US");
    cmd.Parameters.Add(":createdBy", "Binu");
    cmd.Parameters.Add(":createdDate",DateTime.UtcNow);
    cmd.Parameters.Add(":status", "ST");
    var lastId= cmd.ExecuteNonQuery();

    conncection.Close();

    From this how i get last inserted value in lastId variable

    Wednesday, September 7, 2016 8:59 AM

All replies

  • User-183374066 posted

    I am not an oracle expert. I wrote a simple query. You can give it a try

    DECLARE var_id NUMBER;
    insert into ROLE_MSTR(ROLE_NM, DSPLY_TXT, DESC_TXT, ROLE_TYPE, CRTD_BY, CRTD_DT, STATUS) Values(:roleName,:displayText,:roleDescription,:roleType,:createdBy,:createdDate,:status) RETURNING ID INTO var_id;
    select var_id;

    and cmd.ExecuteScalar() instead of cmd.ExecuteNonQuery()

    Wednesday, September 7, 2016 9:47 AM
  • User1624753842 posted

    Hi Nasser,

                     I am writing this query from c#. So Declare and select will not work in c#

    Wednesday, September 7, 2016 10:25 AM
  • User-183374066 posted

    I am writing this query from c#. So Declare and select will not work in c#

    You can write the same query as you can write in store procedure

    Wednesday, September 7, 2016 12:46 PM
  • User1624753842 posted

    Hi Nasser,

                    I tried your code. But i getting compile error.

    Thursday, September 8, 2016 3:43 AM
  • User269602965 posted

    Since a COLON means something in Oracle

    I would remove COLON symbol from the parameter name.

    BUT

    keep the colon in the INSERT statement since it tells Oracle the parameter name is a BIND variable.

    And cross-check, parameters are in the exact same order as your bind variables.

    Thursday, September 8, 2016 9:48 PM
  • User269602965 posted

    Example

    using System;
    using System.Xml.Linq;
    
      public static void updateUnitsActiveFlag(decimal decQuantity, string strDescription, DateTime dateDateClosed)
      {
    	// Insert Quantity into new row Units table
    	string OraConnStr = ConfigurationManager.ConnectionStrings["OraConnStr"].ConnectionString;
    	try
    	{
    	  var SQL = System.Xml.Linq.XElement.Parse("<SQL> INSERT INTO {YOURSCHEMANAME}.UNITS(UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) VALUES(UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)</SQL>");
    	  using (OracleConnection conn = new OracleConnection(OraConnStr))
    	  {
    		using (OracleCommand cmd = new OracleCommand(SQL.Value, conn))
    		{
    		  cmd.Parameters.Clear();
    		  cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input);
    		  cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input);
    		  cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input);
    		  conn.Open();
    		  cmd.ExecuteNonQuery();
    		}
    	  }
    	}
    	catch (Exception ex)
    	{
    	  AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb");
    	}
    } 

    Also notice I have a SEQUENCE object to autogenerate sequence values.

    UNIT_SEQ.NEXTVAL gets the next value for the insert

    query UNIT_SEQ.CURRVAL to get the value of the SEQUENCE made in the last insert.

    Thursday, September 8, 2016 9:52 PM