Retrieving Inserted ID

Jun 19, 2012 at 5:20 PM
Edited Jun 19, 2012 at 5:51 PM

I am trying to retrieve the last inserted ID using DynaSQL

ie. Something like:

 

"INSERT INTO table VALUES (values); SELECT SCOPE_IDENTITY()";
To get the ID inserted with the record.
Is there a better way to do this with DynaSQL? Or should I execute another query to get the ID.
Thanks,
-Matt Hoffmann
Jun 19, 2012 at 6:01 PM

Unfortunately something like this:

 

var selectQuery = DBQuery.SelectFields("SCOPE_IDENTITY()")                 

  .As("Identifier");


   query = DBQuery.InsertInto(GetTableOwner(tableName), GetTableName(tableName))     

   .Fields(values.Keys.ToArray())         

   .Values(values.Values.Select(DBParam.ParamWithValue).Cast<DBClause>().ToArray())             

   .Select(selectQuery);


   _database.ExecuteRead(query, delegate(DbDataReader reader)

 

Give me this error:

"Cannot assign from values if the insert statement has an existing select statement"

Coordinator
Jun 19, 2012 at 9:03 PM

Hi Matt

There is a unit test similar to this requirement - 07
Start a script do the insert and select the last ID. 

DBScript script = DBQuery.Begin(
                DBQuery.InsertInto(Nw.Categories.Table)
                                  .Fields(Nw.Categories.CategoryName, Nw.Categories.Description)
                                  .Values(cname, cdesc),
                DBQuery.Select(DBFunction.LastID()) //the provider specific name of this function is catered for in each statement builder.
            );

Hope this helps.

Jun 19, 2012 at 10:02 PM

That is hugely helpful and makes this much easier.

 

The only follow up I have is: What is the proper handling for using DBScript object. I can use executenonquery but, how do I get the value for LastID out of it in code?Thanks for all the help, I really appreciate it.

 

Cheers,

-Matt

Coordinator
Jun 19, 2012 at 10:31 PM

I'd use the ExecuteScalar in this example - as it returns the first field of the first row and that is what the select does.

It is just part of a select statement so you can use it where ever you would use any other clause of field reference. e.g Select * FROM ... WHERE ID = SCOPE_IDENTITY()

The Script is just a grouping of multiple statements.

Jun 19, 2012 at 10:46 PM

Thanks for the information! I had actually figured that out shortly after my post. My head is just really slow today.

 

ExecuteScalar works perfectly and does what I need. 

 

Cheers,

 

-Matt

Mar 10, 2013 at 11:17 PM
How do I accomplish this when dynamically generating the insert query? Here's my code:
 _public int CreateRecord(string tableName, Dictionary<string, string> values)
    {
        int result = 0;

        try
        {
            DBDatabase db = OpenDynamicDB();
            DBInsertQuery qry = DBQuery.InsertInto(tableName);

            foreach (KeyValuePair<string, string> kvp in values)
            {
                DBParam paramV = DBParam.ParamWithValue(kvp.Key, kvp.Value);
                qry.Field(kvp.Key);
                qry.Value(paramV);
            }

            result = db.ExecuteNonQuery(qry);
        }
        catch (Exception ex)
        {
            ExceptionManager.Publish(ex);
        }

        return result;
    }_
Thanks,
Ronnie
Mar 10, 2013 at 11:17 PM
Edited Mar 10, 2013 at 11:18 PM
How do I accomplish this when dynamically generating the insert query? Here's my code:
 public int CreateRecord(string tableName, Dictionary<string, string> values)
    {
        int result = 0;

        try
        {
            DBDatabase db = OpenDynamicDB();
            DBInsertQuery qry = DBQuery.InsertInto(tableName);

            foreach (KeyValuePair<string, string> kvp in values)
            {
                DBParam paramV = DBParam.ParamWithValue(kvp.Key, kvp.Value);
                qry.Field(kvp.Key);
                qry.Value(paramV);
            }

            result = db.ExecuteNonQuery(qry);
        }
        catch (Exception ex)
        {
            ExceptionManager.Publish(ex);
        }

        return result;
    }
Thanks,
Ronnie