Nice tip

Here i'll give a tip on working with VS.NET database designer and getting MSSQL inserted row's primary key.

First, create Dataset and goto Database Designer and add table adapter using TableAdapter Configuration wizard. Before you click finish, make sure checked the checkbox "Create methods send updates directly to the database". So you'll get generated Select, Insert, Update, Delete methods automatically. I always delete all columns after WHERE clause in automatically generated "update" and "delete" queries except primary key column. It's practically good for me.

And then add new parameter the same name with primary key in InsertCommand of table adapter and set its Direction property to ReturnValue, SourceColumn property to primary key name.

Now, It's by default that generated (for example) tempTableAdapter1.Insert(val1, val2, val3); will return 1 if successful. Means affected row count.

After that, go to source code of Dataset (such as Dataset1.Designer.cs) and find
public virtual int Insert( ... ); function. At the end of the function body, you'll see the code like following
try {
int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
return returnValue;
}
finally {
if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
this.Adapter.InsertCommand.Connection.Close();
}
}

and then change InsertCommand.ExecuteNonQuery() to Convert.ToInt32(InsertCommand.ExecuteScalar()).

ExecuteScalar will return first column value (object type need to be converted mostly), ExecuteNonQuery will return affected rows count.

At last, try tempTableAdapter1.Insert(val1, val2, val3) and print the result.