Retrieve last inserted id and selected course [duplicate]

Question

Retrieve last inserted id and selected course [duplicate]

Navigation

#1 by (0 votes)

2

I have the following situation:

In the same form, I enter 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 an enrollment 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 enrollment, I need to retrieve the last id of the student entered (from that same form) and the id of the course that I selected in the combobox. How do I do that?

sql-server c#

asked by anonymous 11.03.2018 / 16:16

1 answer

Problem with input type="email" in Chrome Scrolltop does not work when I use the Body element and yes HTML

score 0 · Accepted Answer

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

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 have modified your code and added soon after INSERT to the OUTPUT INSERTED.ID function that will inform SQL Server to return the inserted log ID.

In addition, at the end of your code I added int id = (int)inserirAluno.ExecuteScalar(); which in turn will be responsible for executing the SQL command and returning the ID entered previously reported.

That way, in the variable id you will have the ID of the student entered.