CODEWITHSUNDEEP
sqlsqlparameter
will
will

Reputation: 1407

C# SqlParameter - provide SQL (Microsoft SQL)

I am currently tasked with a project on a database whose schema cannot be changed. I need to insert a new row into a table that requires an ID to be unique, but the original creators of the structure did not set this value to autoincrement. To go around this, I have been using code akin to:

(SELECT TOP 1 [ID] from [Table] ORDER BY [ID] DESC) + 1

when giving the value of the ID field, basically having an inner query of sorts. Problem is that a few lines down, I need that ID I just inputted. If I could set a SQLParameter to output for this column, I could get the value it was set to, problem is I'm using SQL, and not a hard value like I do with other SQLParameters. Can't I use SQL in place of just a value?

This is a potential high volume exchange, so I'd rather not do 2 different queries (one to get id, then one to insert).

Upvotes: 2

Views: 243

Answers (4)

Joe White
Joe White

Reputation: 97828

You can put more than one SQL statement in a single SqlCommand. So you could easily do something along the lines of what Abe suggested:

DECLARE @NewId int
SELECT TOP 1 @NewId =  [ID] + 1 from [Table] ORDER BY [ID] DESC
INSERT INTO [Table] (ID, ...) VALUES (@NewId, ...)
SELECT @NewId

Then you just call ExecuteScalar on your SqlCommand, and it will do the INSERT and then return the ID it used.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85126

I'm a bit confused about where you need to use this ID. If it inside of the same stored proc just use this method:

DECLARE @NewId int
SELECT TOP 1 @NewId =  [ID] + 1 from [Table] ORDER BY [ID] DESC
SELECT @NewId

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416121

You say you cannot change the schema, but can you add an additional table to the project that does an autoincrement column? Then you could use that table to (safely) create your new IDs and return them to your code.

This is similar to how Oracle does IDs, and sometimes vendor applications for sql server that also run on Oracle will use that approach just to help minimize the differences between the two databases.

Update:
Ah, I just spotted your comment to the other answer here. In that case, the only other thing I can think that might work is to put your two statements (insert a new ID, and then read back the new ID) inside a transaction with the SERIALIZABLE isolation level. And that just kinda sucks, because it leaves you open to performance and locking gotchas.

Upvotes: 3

Burb
Burb

Reputation: 61

Is it possible for you to create a stored procedure in the database to do this and the return value of the stored procedure will then return the ID that you need?

Upvotes: 1

Related Questions