How to insert into tables with many-to-many relationship?

2

I have a question about how I'm going to do the insert with relationship n:n

My tables are:

Recibo
Analise
Analise_Recibo (recibo_id, analise_id)
    
asked by anonymous 20.12.2014 / 12:54

2 answers

2

To use this code you need to load the appropriate namespace :

using MySql.Data.MySqlClient; 

The secret is to get the last ID inserted after each INSERT with the LastInsertedId quipment property to use in the third table. General idea of code to illustrate (probably needs adaptation to go to production, especially exception handling):

try {
    var connection = new MySqlConnection("server=localhost;userid=user;password=1234;database=banco"); 
    connection.Open();
    var transaction = connection.BeginTransaction();

    var command = new MySqlCommand();
    command.Connection = connection;
    command.Transaction = transaction;

    command.CommandText = "INSERT INTO Recibo (ID, AlgumCampo) VALUES (DEFAULT, ?campo1)";
    command.Parameters.AddWithValue("?campo1", dadoDoRecibo);
    command.ExecuteNonQuery();
    var idRecibo = command.LastInsertedId;
    command.CommandText = "INSERT INTO Analise (ID, algumCampo) VALUES (DEFAULT, ?campo1)";
    command.Parameters.AddWithValue("?campo1", dadoDaAnalise);
    var idAnalise = command.LastInsertedId;
    command.ExecuteNonQuery();
    command.CommandText = "INSERT INTO Analise_Recibo (ID, recibo_id, analise_id) VALUES (DEFAULT, ?idRecibo, ?idAnalise)";
    command.Parameters.AddWithValue("?idRecibo", idRecibo);
    command.Parameters.AddWithValue("?idAnalise", idAnalise);
    command.ExecuteNonQuery();

    transaction.Commit();

} catch (MySqlException ex) {
    try { 
        transaction.Rollback();                
    } catch (MySqlException ex) {
        Console.WriteLine("Erro: {0}",  ex.ToString());                
    }
    Console.WriteLine("Erro: {0}",  ex.ToString());
} finally {
    if (connection != null) {
        connection.Close();
    }
}

I placed GitHub for future reference a>.

    
20.12.2014 / 14:50
0

To INSERT in Analyze_Recibo the ReciboId keys and an AnalyiseID are required, you need to reshape your Analyze_Recibo table to contain foreign keys for ReciboId and AnalyiseID:

Recibo       Analise_Recibo    Analise
==========   ============      ======
ReciboID     Recibo_ID         AnaliseID
             Analise_ID

For insert follows:

insert into dbo.Analise_Recibo (ReciboId, AnaliseId) 

select
  u.ReciboId,
  r.AnaliseId,

from dbo.Recibo u

inner join dbo.Analise r

on r.AnaliseId = u.ReciboId
    
03.01.2017 / 13:15