Return last inserted record using ADO.NET

Question

1.00/5 (1 vote)

See more:

I have the below code

public void addSeesion(int userid)
{
string qry = "insert into Session_Info(Session_Id,User_Id)values(@SessionId,@UserId)";
MySqlCommand cmd = new MySqlCommand(qry, Connection.Get());
cmd.Parameters.AddWithValue("@SessionId", session);
cmd.Parameters.AddWithValue("@UserId", userid);
cmd.ExecuteNonQuery();
}

There are 3 columns in the table including primary key. Now I want to get the last inserted record after this without making a new query. cmd.ExecuteScalar and cmd.LastInsertedId gives the primary key.

But I need the entire row or the Session_Id value. Is it really possible or there have any other way???

What I have tried:

cmd.ExecuteScalar and cmd.LastInsertedId gives the primary key.
But i want the entire record.

Posted 19-Aug-16 1:48am

Member 12693361

Updated 23-Oct-19 2:23am

Add a Solution

Comments

ZurdoDev 19-Aug-16 7:58am

What are you missing? You just inserted the record so you have all the values, right?

Member 12693361 22-Aug-16 0:07am

Yes.but i have to make sure that the record is properly updated.

ZurdoDev 22-Aug-16 6:43am

As long as you do not get an error and your sql is correct, then it worked. I'm not sure what you're looking for.

F-ES Sitecore 19-Aug-16 9:46am

You'll need to do an additional query, there is no way around it. As Ryan said though, if none of the fields are computed in any way then you have all of the data anyway.

Member 12693361 22-Aug-16 0:06am

of course i have the values.But How can i strongly believe that the record is updated with the exact values??

F-ES Sitecore 22-Aug-16 4:03am

Because worldwide SQL probably updates a billion bits of information every second, and if there was a chance the data wasn't actually updated someone would have reported it as a bug? :)

Karthik_Mahalingam 20-Aug-16 1:40am

sql or mysql ?

Member 12693361 22-Aug-16 0:06am

mysql

Karthik_Mahalingam 22-Aug-16 0:40am

try this
SELECT LAST_INSERT_ID() FROM Session_Info

agolddog 28-Oct-19 10:36am

I think I have to go with ZurdoDev and the the others. Your interaction will only produce one of two things: success or an exception.

If there's no exception, you have all the values you came with. Use one of the things you've already discovered to retrieve the new id, and create some object to return (instead of void), building that object from the input + the new id.

If there's an exception, you want to handle that in whatever way is appropriate for this layer of your system (I'd guess just let it bubble up, unless you have logging at this level).

One minor thing is your method name is misspelled: addSession.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Knightmare RIP · Answer 1 · 2019-10-23T02:10:00

Solution 2

Try this...

Insert into [tableName]
    ([Col1], [Col2], [Coln])
  Output Inserted.*
  Values
    (...)

Posted 23-Oct-19 2:10am

Knightmare RIP

Comments

CHill60 23-Oct-19 10:56am

I've countered the downvote, but only with a 4 - the OP posted C# code and you have only included the SQL - for completeness I would have preferred to see an example of it being used in ADO.net/ASP.net/C#

Richard Deeming 23-Oct-19 11:11am

I've counter-countered the vote - the OP was using MySQL, and as far as I can see that doesn't support the OUTPUT clause.

CHill60 23-Oct-19 11:17am

Good one - I went on the tag and completely missed the object used in the code :-(

OriginalGriff · Answer 2 · 2016-08-19T02:11:00

Solution 1

The way I would do it is simple: add a DATETIME column called InsertAt and set it's default value to GETDATE()
Then, all you have to do is:

SQL

SELECT TOP 1 * FROM MyTable ORDER BY InsertAt DESC

Because you have given it a default, no other code should need changes.

Posted 19-Aug-16 2:11am

OriginalGriff

Comments

F-ES Sitecore 19-Aug-16 9:46am

If you are UserA and UserB causes another entry to be created between you inserting your record and doing the "select top" then you will get their data and not your own.

Member 12693361 22-Aug-16 0:09am

I do not prefer another query.I just want the record as a return of command object.Am i asking something cannot possible??Please share your thoughts.

Return last inserted record using ADO.NET

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Return last inserted record using ADO.NET

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us