Accessing rows PK after Insert (SqlDataSource)

Question

I need to be able to get the primary key of an nserted row in C#, and so far I'm unsure how. I've put SELECT SCOPE_IDENTITY() into my SQL query, but how do I access this from the SqlDataSource_Inserting method, so I can store it in an auditing table? In my method I only now how to access the parameters (e.Command.Parameters)

EDIT:

My parameters are stored in my ASP.NET file like so (some extracts):

InsertCommand="INSERT INTO [NominalCode] ([VAXCode], [Reference], [CostCentre], [Department], [ReportingCategory]) VALUES (@VAXCode, @Reference, @CostCentre, @Department, @ReportingCategory)"

        <InsertParameters>
            <asp:ControlParameter ControlID="DetailsView1" Name="VAXCode" 
                PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="DetailsView1" Name="Reference" 
                PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="DetailsView1" Name="CostCentre" 
                PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="DetailsView1" Name="Department" 
                PropertyName="SelectedValue" />
            <asp:ControlParameter ControlID="DetailsView1" Name="ReportingCategory" 
                PropertyName="SelectedValue" />
        </InsertParameters>

And I fill the parameters in C# codebehind

                command.Parameters.AddWithValue("@source", "Nominal");
            command.Parameters.AddWithValue("@action", "Insert");
            command.Parameters.AddWithValue("@item", fields);
            command.Parameters.AddWithValue("@userid", name);
            command.Parameters.AddWithValue("@timestamp", DateTime.Now);

Thanks

Answer 1

You can access it via the Command Parameters by adding a new one with a Direction of Output, (or you can use ReturnValue if you're not returning another result, like rows affected).

ie. Add these three rows to the end of your parameters list

SqlParameters pKey = new SqlParameters("@pKey", System.Data.SqlDbType.Int);
pKey.Direction = Output;
command.Parameters.Add(pKey);

Then at the end of your sql, set that parameter to the Primary Key value like so:

set @pKey = scope_identity();

ie.

 InsertCommand="INSERT INTO [NominalCode] ([VAXCode], [Reference], [CostCentre], [Department], [ReportingCategory]) VALUES (@VAXCode, @Reference, @CostCentre, @Department, @ReportingCategory); set @pKey = scope_identity()"

Then just read it back after you've executed the command:

int primaryKey = (int)pKey.Value;

Simples!

Another way to do auditing is to have the stored proc perform the audit capture directly, to ensure consistency and to save doing it in all the apps but this is all down to preference and system design.