SQL paramaterisation in C#, using SQL declared variables

I have the following SQL query which I am sending from a C# program:

DECLARE @id as int
SELECT @id = max(fault_catagory_ident) FROM fault_catagory_list
INSERT INTO fault_catagory_list (fault_catagory_ident, fault_catagory)
VALUES (@id + 1, 'TEST')
SELECT @id + 1

The ‘fault_catagory’ value is coming from my program, but the ident value needs to be the next number in line (primary key) from the existing table in the database. My C# code is parameterising values for security.

I have two problems:

1. How can I get the @id + 1 value returned to my program (executeNonQuery doesn’t return anything)?
2. How can I get @id as a parametarised value for the insert command?

I am wondering if my primary key could be automated in some way?

I want to carry all this out in one single query, as there will be a risk of multiple logins running this same query. If any happened to run simultainiously, the @id value may get duplicated and one would fail.

Apologies if there isn’t enough info here, I’m on a learning curve!

Any advice would be greatly appreciated. Thanks in advance.

Answer

I think that you will find everything you need in this example provided in MSDN: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=dotnet-plat-ext-5.0

in short:

1. to return a single parameter from a query ull use ExecuteScalar()
2. parameters are added to a query through the SqlCommand class provided in System.Data.SqlClient.

cheers!