c# - What can I do to set the value of a column in a new row in such a way that it have the same value of the auto increment primary key of the table?

Question

I am absolutly new in C# (I came from Java) and I have the following problem

I have a method that execute an INSERT query to insert a new record into the table that is univocallu identify by an Id (the primary key)

So my table have some fields including: Id and VulnerabilityAllertId.

These two fields must have the same value.

At this time my method that perform the INSERT query work in this way:

I pass a VulnsSmall model object to the method and it create a new record in the table with each fields having the value of the related field in the model object

    public void insert(DataModel.Vulnerability.VulnSmall v) {

        _strSQL = "";
        string strSQLParametri = "";
        string query = "";

        System.Data.Common.DbCommand command;
        command = _connection.CreateCommand();

        // [VulnerabilityAlertId] insertion on the DB:
        if (v.VulnerabilityAlertId != null) {
            _strSQL = "INSERT INTO VulnerabilityAlertDocument ( [VulnerabilityAlertId] ";
            strSQLParametri = " VALUES (@VULNERABILITYALERTID ";
            addParameter(command, "@VULNERABILITYALERTID ", v.Id);
        }

        // [SourceId] insertion on the DB:
        if (!String.IsNullOrEmpty(v.SourceId))
        {
            _strSQL += ",[SourceId] ";
            strSQLParametri += ", @SOURCEID ";
            addParameter(command, "@SOURCEID", v.SourceId);
        }

        .................................
        .................................
        .................................   

        // [Language] insertion on the DB:
        if (v.Language != null)
        {
            _strSQL += ",[Language] ";
            strSQLParametri += ", @LANGUAGE ";
            addParameter(command, "@LANGUAGE", v.Language);
        }


        query = _strSQL + " ) " + strSQLParametri + " );";
        command.CommandText = query;
        _executeNoQuery(command);

    } 

My problem is that this code section is wrong:

        // [VulnerabilityAlertId] insertion on the DB:
        if (v.VulnerabilityAlertId != null) {
            _strSQL = "INSERT INTO VulnerabilityAlertDocument ( [VulnerabilityAlertId] ";
            strSQLParametri = " VALUES (@VULNERABILITYALERTID ";
            addParameter(command, "@VULNERABILITYALERTID ", v.Id);
        }

It is wrong because, in the database VulnerabilityAlertDocument ** table the **[VulnerabilityAlertId] column must have the same value of the Id primary key that is auto increment

In my previous code it use the value into v.VulnerabilityAlertId model field to set the [VulnerabilityAlertId] column value for my new row, so it have not the same value of the Id primary key column

What can I do to set the same value of the [Id] autoincrement column for the [VulnerabilityAlertId] column in my new row?

Answer 1

If i get your query correctly, you can make changes at database level by adding a mirror column to the primary key.

ALTER TABLE VulnerabilityAlertDocument
ADD mirror AS [Id] UNIQUE

and then you have a column which always replicate the value of primary.