Getting the primary key of an newly inserted row in SQL Server 2008

In C#, right after your SQL Statement write SELECT SCOPE_IDENTITY(); so your code would be:

insert into TABLE (...) values (...); SELECT SCOPE_IDENTITY();

then, instead of executeNonQuery use executeScalar.

That should do the trick!

Scope_Identity() is what you want, assuming that by "primary key" you mean "Identity"

declare @id int 
insert yourtable values (some, values)
select @id = Scope_Identity()

If you're inserting a whole set of rows, selecting the SCOPE_IDENTITY() won't do. And SCOPE_IDENTITY also only works for (numeric) identity columns - sometimes your PK is something else...

But SQL Server does have the OUTPUT clause - and it's very well documented on MSDN!

INSERT INTO dbo.Table(columns)
OUTPUT INSERTED.p_key, INSERTED.someothercolumnhere .......
VALUES(...) 

Those values will be "echoed" back to the calling app, e.g. you'll see them in a grid in SQL Server Management Studio, or you can read them as a result set from your C# or VB.NET calling this INSERT statement.