How to insert data into an N:N Table

Asked

Viewed 172 times

0

I’m having trouble inserting data into an N:N table. The two tables in question are: Course and Subject. The relationship between them forms a table called tbMATERIA_CURSO which stores the primary keys of the two tables.

Code to insert in table tbCURSO:

Conexao conexao = new Conexao();
SqlCommand sql = new SqlCommand();

public void insereCurso(string nomeCurso, string periodo, string tipo)
{
    sql.CommandText = "insert into tbCURSO(NomeCurso, Periodo, TipoEnsino) VALUES (@nome_curso, @periodo, @tipoEnsino)";
    // parâmetros
    sql.Parameters.AddWithValue("@nome_curso", nomeCurso);
    sql.Parameters.AddWithValue("@periodo", periodo);
    sql.Parameters.AddWithValue("@tipoEnsino", tipo);

    try
    {
        sql.Connection = conexao.Conectar();
        sql.ExecuteNonQuery();
        conexao.Desconectar();
        MessageBox.Show("Curso cadastrado com sucesso", "Sucesso", MessageBoxButtons.OK , MessageBoxIcon.Information);
    } catch (SqlException e)
    {
        MessageBox.Show(e.Message, "Erro ao tentar se conectar com o Banco de Dados", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

}

Code to insert in table tbMATERIA:

Conexao conexao = new Conexao();
SqlCommand sql = new SqlCommand();

public void InsereMaterias(string nomeMateria, int cargaHoraria)
{
    sql.CommandText = "insert into tbMATERIA(NomeMateria, CargaHoraria) VALUES (@nome_materia, @carga_horaria)";
    // parâmetros
    sql.Parameters.AddWithValue("@nome_materia", nomeMateria);
    sql.Parameters.AddWithValue("@carga_horaria", cargaHoraria);

    try
    {
        sql.Connection = conexao.Conectar();
        sql.ExecuteNonQuery();
        conexao.Desconectar();
        MessageBox.Show("Curso cadastrado com sucesso", "Sucesso", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (SqlException e)
    {
        MessageBox.Show(e.Message, "Erro ao tentar se conectar com o Banco de Dados", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

}

Relacionamento entre três tabelas: Curso, Matéria e Materia_Curso

How can I enter the primary key created on tbCURSO and in the tbMATERIA as a foreign key in tbMATERIA_CURSO?

c# database

  • https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row

  • I don’t know what this helps me with.

  • As you are using artificial keys understand that your difficulty is to get the values of Idcurso and Idmateria that were assigned at the time of insertion of the records in the respective tables to be able to insert in tbMATERIA_CURSO.

  • Can each subject belong to one or more courses? Shouldn’t it belong to just one? There may exist several subjects with the same name (even so I find strange, but I believe there may be), but one belonging to two or more courses?

  • Yes, for example, the Portuguese language discipline can be contained in all registered courses, Computer Technician, Administration, Accounting and etc.

  • 1

    @Brunooliveira A tip: don’t use the tag visual-studio for questions that are not related to the tool itself (text editing, code analysis, plugins, among others). If you have any questions about this, it might be a good idea to read the post What is a programming language, IDE and compiler?

Show 1 more comment

1 answer

0

Your logic is not wrong seeing from the point that you can do the CRUD of each entity separately, to my see what you can do is create a screen where you can join these two entities and so do the Insert in the table tbMATERIA_CRUSO, informs your recovered keys in a query. I usually use a drop draw list to choose the entity item.

But there are other alternatives, such as creating a precedent to record the 3 entities in a single time recovering the saved ID of the main ones to use in intermediary.

Or you can use the way you are doing and recover the ID of each entity and create another method by receiving the ID of each entity to save in the table tbMATERIA_CRUSO.

Browser other questions tagged c# database

You are not signed in. Login or sign up in order to post.