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)
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)
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>.
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