Recover last inserted id and selected stroke

Question

I have the following situation:

On the same form, I insert a student:

        SqlCommand inserirAluno = new SqlCommand("INSERT INTO ALUNOS (Nome, DataNascimento, CPF, Endereco, Bairro, " +
             "CEP, Cidade, IdEstado, Sexo) " +
             "Values(@Nome, @DataNascimento, @CPF, @Endereco, @Bairro, @CEP, @Cidade, @IdEstado, @Sexo)", sqlConn);
        inserirAluno.Parameters.Add("@DataNascimento", SqlDbType.Date).Value = mtb_Nasc.Text;
        inserirAluno.Parameters.Add("@Nome", SqlDbType.NVarChar).Value = tb_Nome.Text;
        inserirAluno.Parameters.Add("@CPF", SqlDbType.NVarChar).Value = mtb_Cpf.Text;
        inserirAluno.Parameters.Add("@Endereco", SqlDbType.NVarChar).Value = tb_Endereco.Text;
        inserirAluno.Parameters.Add("@Bairro", SqlDbType.NVarChar).Value = tb_Bairro.Text;
        inserirAluno.Parameters.Add("@CEP", SqlDbType.NVarChar).Value = mtb_Cep.Text;
        inserirAluno.Parameters.Add("@Cidade", SqlDbType.NVarChar).Value = tb_Cidade.Text;
        inserirAluno.Parameters.Add("@IdEstado", SqlDbType.Int).Value = cb_Uf.SelectedIndex;
        if (rb_Masc.Checked)
        {
            inserirAluno.Parameters.Add("@Sexo", SqlDbType.Bit).Value = 0;
        }
        else
        {
            inserirAluno.Parameters.Add("@Sexo", SqlDbType.Bit).Value = 1;
        }

And I also enter a license plate for this student:

        SqlCommand inserirMatricula = new SqlCommand("INSERT INTO MATRICULAS(IdAluno, IdCurso, Periodo, Mensalidade, " +
            "FlagAtivo, DataMatricula)" +
            "Values(@IdAluno, @IdCurso, @Periodo, @Mensalidade, @FlagAtivo, @DataMatricula)");
        inserirMatricula.Parameters.Add("@IdAluno", SqlDbType.Int).Value = 
        inserirMatricula.Parameters.Add("@IdCurso", SqlDbType.Int).Value = 
        inserirMatricula.Parameters.Add("@Periodo", SqlDbType.Int).Value = tb_Semestre.Text;
        inserirMatricula.Parameters.Add("@Mensalidade", SqlDbType.Decimal).Value = tb_Mensalidade.Text;
        inserirMatricula.Parameters.Add("@FlagAtivo", SqlDbType.Bit).Value = 1;
        inserirMatricula.Parameters.Add("@DataMatricula", SqlDbType.Date).Value = DateTime.Now.ToShortTimeString();

However, when entering the enrollment, I need to recover the last id of the student entered (from this form) and the id of the course I selected in the combobox. How do I do that?

Answer 1

For this, you need to use the function OUTPUT INSERTED.ID and the method command.ExecuteScalar().

try
{
    SqlConnection sqlConn = new SqlConnection(_connectionString);

    SqlCommand inserirAluno = new SqlCommand("INSERT INTO ALUNOS (Nome, DataNascimento, CPF, Endereco, Bairro, " +
                 "CEP, Cidade, IdEstado, Sexo) OUTPUT INSERTED.ID " +
                 "Values(@Nome, @DataNascimento, @CPF, @Endereco, @Bairro, @CEP, @Cidade, @IdEstado, @Sexo)", sqlConn);

    inserirAluno.Parameters.Add("@DataNascimento", SqlDbType.Date).Value = mtb_Nasc.Text;
    inserirAluno.Parameters.Add("@Nome", SqlDbType.NVarChar).Value = tb_Nome.Text;
    inserirAluno.Parameters.Add("@CPF", SqlDbType.NVarChar).Value = mtb_Cpf.Text;
    inserirAluno.Parameters.Add("@Endereco", SqlDbType.NVarChar).Value = tb_Endereco.Text;
    inserirAluno.Parameters.Add("@Bairro", SqlDbType.NVarChar).Value = tb_Bairro.Text;
    inserirAluno.Parameters.Add("@CEP", SqlDbType.NVarChar).Value = mtb_Cep.Text;
    inserirAluno.Parameters.Add("@Cidade", SqlDbType.NVarChar).Value = tb_Cidade.Text;
    inserirAluno.Parameters.Add("@IdEstado", SqlDbType.Int).Value = cb_Uf.SelectedIndex;

    if (rb_Masc.Checked)
    {
        inserirAluno.Parameters.Add("@Sexo", SqlDbType.Bit).Value = 0;
    }
    else
    {
        inserirAluno.Parameters.Add("@Sexo", SqlDbType.Bit).Value = 1;
    }

    sqlConn.Open();
    int id = (int)inserirAluno.ExecuteScalar();
}
catch (Exception ex)
{
    // Tratar exceção
}
finally
{
    if (sqlConn.State != sqlConn.Closed)
    {
        sqlConn.Close();
    }
}

Notice that I modified your code and added right after the INSERT the function OUTPUT INSERTED.ID who will inform the SQL Server to return the inserted record ID.

Also, at the end of your code I added int id = (int)inserirAluno.ExecuteScalar(); who in turn will be responsible for executing the command SQL and return the ID inserted that we informed previously.

Thus, in the variable id you will have the ID of the inserted student.