Barguast Barguast - 4 months ago 52
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);
sqlCmd.Parameters.Add(firstValueParam);

sqlCmd.ExecuteNonQuery();

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

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):

CREATE PROCEDURE [GetNext]
    @Name VARCHAR(50)
AS
BEGIN
    DECLARE @sql VARCHAR(200);
    SET @Name = QUOTENAME(@Name, '[');
    SET @sql = 'SELECT NEXT VALUE FOR ' + @Name;

    EXEC @sql;
END