How to make Insert in tables with many relationship for many?

Asked

Viewed 1,914 times

2

I’m having second thoughts about how I’m gonna do the Insert relationship n:n

my tables are:

Recibo
Analise
Analise_Recibo (recibo_id, analise_id)
c# mysql sql .net sql-insert

2 answers

2


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

using MySql.Data.MySqlClient;

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

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 put in the Github for future reference.

  • I got to almost that... I’ll put a FOR to insert the values in an array that stores the selected analyzes....

0

For INSERT in Parse_receipt you need the Receipt keys and an Parseid, you need to reshape your table Parse_receipt to contain Receipt and Parseid Foreign:

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

Browser other questions tagged c# mysql sql .net sql-insert

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