How do I get the PK when I saved a record into DB with ADO.NET

Question

0.00/5 (No votes)

See more:

Hi,everyone!
I want get the Primary Key when I saved a record into SQLServer with ADO.NET.
I can't find a FUNCTION in ADO.NET's objects to do this.
Do I query that record again?
Is there any good idea?

Posted 28-Oct-15 21:54pm

MyJoiT

Add a Solution

Comments

[no name] 29-Oct-15 4:02am

Can you explain what exactly you are doing in ADO.NET i mean your query.

MyJoiT 29-Oct-15 4:15am

oh sorry.
I have a Table in DB named "UserInfo".
When a people want to register.After he write down his all infomation.And click the "OK" button.
My program can get all the infomation about he.
Finally,Excute the SQL "insert into UserInfo(x,x,x,x) values(x,x,x,x)".
I used the FUNCTION "GetSqlCommand(connection, sql, parameters).ExecuteNonQuery();"
And the connectin is a SqlConnection object,it's no problem.
I can only write these.Can't get the PK.

[no name] 29-Oct-15 4:07am

Is your primary Key is Identity column ?

MyJoiT 29-Oct-15 4:16am

Yes ,it grow aotumatically.The step is 1.

2 solutions

Add a Solution

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

phil.o · Accepted Answer · 2015-10-28T22:10:00

Basically you have two choices:

- Using SCOPE_IDENTITY():

SQL

INSERT INTO [YourTable] ([AColumn]) VALUES (@aColumn);
SELECT SCOPE_IDENTITY();

- Or using OUTPUT INSERTED.ID:

SQL

INSERT INTO [YourTable] ([AColumn])
OUTPUT INSERTED.ID
VALUES (@aColumn);

Usage from c#:

C#

using (SqlConnection connection = /* connection initialization here */) {

   using (SqlCommand command = new SqlCommand("INSERT INTO [YourTable] ([AColumn]) VALUES (@aColumn); SELECT SCOPE_IDENTITY();", connection)) {
   // OR
   using (SqlCommand command = new SqlCommand("INSERT INTO [YourTable] ([AColumn]) OUTPUT INSERTED.ID VALUES (@aColumn);", connection)) {
      command.Parameters.AddWithValue("@aColumn", "dummy");
      int insertedId = command.ExecuteScalar();
   }
}

Hope this helps.

Grabbed from SO: How to get last inserted id?[^]

[Edit] Add query parameter qualification that I forgot [/Edit]

PANKAJMAURYA · Accepted Answer · 2015-10-28T22:23:00

Solution 2

Using Scope_IDENTITY() you can get that value.
For that you have to change your query to

SQL

"insert into UserInfo(x,x,x,x) values(x,x,x,x);SELECT SCOPE_IDENTITY();"

After that you have to change command execution from ExecuteNonQuery() to ExecuteScalar()

C#

Object id = GetSqlCommand(connection, sql, parameters).ExecuteScalar();

Now you got the last primary key inserted by above command.

Posted 28-Oct-15 22:23pm

PANKAJMAURYA

Comments

[no name] 29-Oct-15 4:32am

Is it helpful ?

MyJoiT 29-Oct-15 4:42am

Yes,Thank you so much!
I test it just now.So can't reply you immediately!
It worked successfully!
Thank you so much.

[no name] 29-Oct-15 5:07am

Please mark it as answer if it really helped you .

How do I get the PK when I saved a record into DB with ADO.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How do I get the PK when I saved a record into DB with ADO.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us