Barguast Barguast - 1 year ago 158
SQL Question

Safely get next SQL Server sequence value from .NET

I'm experimenting with SEQUENCE objects in SQL Server, and getting the next value with C# by specifying the sequence name. Ranges are simple, because there is a stored procedure for them, and you can pass the sequence name;

public static T Reserve<T>(string name, int count, SqlConnection sqlConn)
using (var sqlCmd = new SqlCommand("sp_sequence_get_range", sqlConn))
sqlCmd.CommandType = CommandType.StoredProcedure;
var firstValueParam = new SqlParameter("@range_first_value", SqlDbType.Variant) { Direction = ParameterDirection.Output };

sqlCmd.Parameters.AddWithValue("@sequence_name", name);
sqlCmd.Parameters.AddWithValue("@range_size", count);


return (T)firstValueParam.Value;

But what about single values? It seems to me that I can either call the above with a count of '1', or I can construct the SQL dynamically. i.e.

var sqlCmdStr = string.Format("SELECT NEXT VALUE FOR {0}", name);

Which I know to generally be bad practice (i.e. SQL injection).

What would anyone suggest?

Answer Source

My suggestion is a combination of both @Gserg's answer and your current solution. Write a stored procedure that takes a VARCHAR parameter @Name. Build the sql string in the stored procedure, using QUOTENAME as suggested by @GSerg. Use EXEC or sp_executesql to run the script.

Something like this (freehand):

    @Name VARCHAR(50)
    DECLARE @sql VARCHAR(200);
    SET @Name = QUOTENAME(@Name, '[');
    SET @sql = 'SELECT NEXT VALUE FOR ' + @Name;

    EXEC @sql;