CODEWITHSUNDEEP
c#sqllinq-to-sqlstored-proceduresidentity-column
ango
ango

Reputation: 859

Update a Record containing Identity Element using Stored Proc in LinqtoSQL

I have a table defined as: 

CREATE TABLE [dbo].[procInfo](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [startTime] [datetime] NOT NULL,
    [endTime] [datetime] NULL,
    [procName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_procInfo] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

When I start a Process, I create a record for it using a LinQtoSQL Stored Procedure from my c# code. Now when the process ends, I want to update the same record with the endtime. The id should remain same as it is referenced by other tables. I am aware of the SQL queries:

SET IDENTITY_INSERT [dbo].[procInfo] ON
SET IDENTITY_INSERT [dbo].[procInfo] OFF

I have a way to store the id if the process that has finished. Now, I want to write a stored Procedure to update this record with the endTime. I have been trying various things, but none of those are working.
Any suggestions on how to do this ?

Upvotes: 0

Views: 185

Answers (3)

HatSoft
HatSoft

Reputation: 11191

Once a record is inserted you can get IDENTITY using the query SELECT SCOPE_IDENTITY()

With the help of this IDENTITY then you can update the endtime

SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

Upvotes: 0

Diego
Diego

Reputation: 7562

Perhaps I'm missing something, but couldn't you simply use an UPDATE query?

UPDATE procInfo
SET endTime = <end time>
WHERE id = <process id>

It should be quite straightforward.

Upvotes: 1

ango
ango

Reputation: 859

ahh, I found an easier way to do this.
I could just write a stored proc as: 

CREATE PROCEDURE dbo.insertProcEndTime
(
@id int,
@endtime datetime
)
AS
/* SET NOCOUNT ON */
UPDATE procInfo
SET endTime = @endtime
WHERE id = @id

RETURN

...and it worked. No need to modify IDENTITY_INSERT flag.
Keep It Simple Stupid !!

Upvotes: 0

Related Questions