GetNextID() - method in C#

Question

How to execute this T-SQL command through a c# method so that I can get the next ID of a Table?

declare @IDColumnName as varchar(150)
declare @TableName as varchar(150)
declare @SQL as nvarchar(1000)
declare @returns as int

set @IDColumnName = 'ID'
set @TableName = 'User'
set @returns = -1

set @SQL = 'select MAX([' + @IDColumnName + ']) from [' + @TableName + ']'
--print @SQL

EXEC @returns = sp_executesql @SQL

--print @returns

Answer 1

Note that this is a blatant race condition unless you take a key-range lock. Why not use IDENTITY? But if you SELECT the value, you should be able use use ExecuteScalar.

Also; if you don't white-list the tables, that is a SQL injection risk.

Answer 2

Here is a similar question. AutoIncrement fields on databases without autoincrement field

Here is my answer with working solution.

Answer 3

better to use identity other create UDF and put the code in this UDF and call it by using Executescalar method.