I've got a three tier system, SQL Server after sales, hands written data access layer, and taking advantage of saved methods.
I've got a table known as EventTable. Each row is definitely an 'Event'. A Celebration includes a primary key, along with a start date.
CREATE TABLE EventTable ( ID INT IDENTITY(100,1) PRIMARY KEY, StartTime DateTime NOT NULL )
There's a saved procedure known as EventTable_Create. Incidentally, may be 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 information access layer returns an int towards the caller, but should it rather return a clear case of an information transfer object known as Event? If that's true, must i be coming back both recently produced ID and begin time too, to ensure that the information access layer can make the big event transfer object?
Everything is dependent on the thing you need inside your domain layer via data access. As you are creating this by passing the data from domain layer, I ma presuming you have the majority of the event information inside your domain logic. For the reason that situation, you just need an ID to populate it in your event object for those who have one.
Therefore it all is dependent on the thing you need inside your domain layer as well as for what reasons.
So far as what you ought to return, in either case may likely work. As CodeToGlory stated, it is dependent. I'd lean toward coming back both values myself.
I'd change the way i obtain the identity column within the saved procedure. You will find a couple of methods for getting it.
Rather than: Choose @NewID = MAX(ID) FROM EXPERIMENTTABLE
I'd do: Set @NewID = @@Identity
Set @NewID = SCOPE_IDENTITY()
Here's an excerpt from books online by having an example. I will help you choose which to make use of:
For instance, you will find two tables, T1 and T2, as well as an Place trigger is determined on T1. Whenever a row is placed to T1, the trigger fires and card inserts a row in T2. This demonstrates two scopes: the place on T1, and also the place on T2 through the trigger.
Presuming that both T1 and T2 have identity posts, @@IDENTITY and SCOPE_IDENTITY will return different values in the finish of the Place statement on T1. @@IDENTITY will return the final identity column value placed across any scope in the present session. This is actually the value placed in T2. SCOPE_IDENTITY() will return the IDENTITY value placed in T1. It was the final place that happened within the same scope. The SCOPE_IDENTITY() function will return the null value when the function is invoked before any Place claims into a name column exist in the scope.
You need to return the only real primary key unless of course you'll need the whole data structure. Coming back the entire data structure automatically, with no specific reason, is inefficient.
You should not use MAX(ID) to retrieve the main key from the recently added record.
Use, @@Identity rather.
INSERT INTO tbl(...) VALUES(...); SELECT @@Identity as NewID;