After creating a new row in the database, should I return the primary key to the row or a data transfer object?

Question

I have a three tier system, SQL Server backend, hand written data access layer, and using stored procedures.

I have a table called EventTable. Each row is an 'Event'. An Event has a primary key, and a start date.

CREATE TABLE EventTable
(
  ID INT IDENTITY(100,1) PRIMARY KEY,
  StartTime DateTime NOT NULL
)

There is a stored procedure called EventTable_Create. Incidentally, is the create method okay as written?

CREATE PROCEDURE Event_Create
    @NewID INT OUT
AS
    DECLARE @START DATETIME
    SELECT @START = getdate() 
    INSERT INTO EventTable VALUES(@START, NULL)
    SELECT @NewID = MAX(ID) FROM EventTable
GO

The data access layer returns an int to the caller, but should it instead return an instance of a data transfer object called Event? If that is true, should I be returning both the newly created ID and start time as well, so that the data access layer can create the event transfer object?

Answer 1

It all depends on what you need in your domain layer via data access. Since you are creating this by passing the information from domain layer, I ma assuming you already have most of the event information in your domain logic. In that case, all you need is an ID to populate it as part of your event object if you have one.

So it all depends on what you need in your domain layer and for what reasons.

Answer 2

As far as what you need to return, either way would likely work. As CodeToGlory said, it depends. I would lean toward returning both values myself.

I would change how I get the identity column in the stored procedure. There are a few ways to get it.

Instead of: SELECT @NewID = MAX(ID) FROM EXPERIMENTTABLE

I would do: Set @NewID = @@Identity

or

Set @NewID = SCOPE_IDENTITY()

Here is an excerpt from books on line with an example. I can help you decide which to use:

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Answer 3

You shouldn't use MAX(ID) to retrieve the Primary key of the newly added record.

Use, @@Identity instead.

INSERT INTO tbl(...) VALUES(...);

SELECT @@Identity as NewID;

Answer 4

You should return the only primary key unless you need the entire data structure. Returning the whole data structure by default, without a specific reason, is wasteful.