Getting primary key or any other attribute just af

Question

I have a table with a primary key, two foreign keys and other attributes. I want to insert a row in a way that just after insertion it should return the PRIMARY KEY, I am using following statement to execute query

int MyId = (int)insert.ExecuteScalar();  

but above code is returning one of foreign keys, which is part of insert query. How can I get primary key in return after insertion? Second, is there any way to get any specific attribute just after insertion.
I am using asp.net and Sql Server 2008

Answer 1

use output !!

create table mytab(
      pk int identity primary key,
      data nvarchar(20)
    );
    go

    insert into mytab
    output inserted.pk
    values ('new item my PK');
    go

Answer 2

With a table like this in SQL Server:

create table test
(
 id int identity primary key,
 data nvarchar(255)
)

You can use SCOPE_IDENTITY(): (error checking left out, etc)

using System;
using System.Data;
using System.Data.SqlClient;

namespace sqltest
{
  class Program
  {
      static void Main(string[] args)
      {
        SqlParameter id = new SqlParameter("id",0);
        //parameter value can be set server side and will be returned to client
        id.Direction=ParameterDirection.Output; 
        string query="Insert into test values ('hello world');select @id=SCOPE_IDENTITY()";
        int lastID=0;
        using (SqlConnection conn = new SqlConnection("put your connection string here"))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(query, conn))
            {
                comm.Parameters.Add(id);
                comm.ExecuteNonQuery();
                //id.Value now holds the last identity value
                lastID = Convert.ToInt32(id.Value);
            }//end using comm
        }//end using conn
       }//end Main
    }
}

But honestly I would recommend if at all possible you use an abstraction (Linq2SQL, Entity Framework, NHibernate, etc, etc) for this kind of thing because it frees you from having to deal with this kind of boilerplate.