Thank you for your support, but I couldn't obtain the value of maestroId in the presentation layer. I couldn't because I don't have much knowledge, but I did this, which works well and does what I need. However, I believe there might be another way to do it. Here is the code:
public void registerMasterAndDetail(string name, List<EN_MasterDetail> masterDetails) { BDConnection bdConnection = new BDConnection(); SqlTransaction transaction = null; try { using (SqlConnection connection = bdConnection.OpenConnection()) { MessageBox.Show(connection.State.ToString()); transaction = connection.BeginTransaction(); // Output parameter to obtain the MaestroId SqlParameter outputParameter = new SqlParameter("@IdMaster", SqlDbType.Int) { Direction = ParameterDirection.Output }; using (SqlCommand command = new SqlCommand("sp_InsertMaster", connection, transaction)) { command.CommandTimeout = 20; command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@Name", name); // Add output parameter command.Parameters.Add(outputParameter); // Execute the stored procedure command.ExecuteNonQuery(); // Obtain the MaestroId from the output parameter int maestroId = Convert.ToInt32(outputParameter.Value); // Assign the MaestroId to each detail foreach (var detail in masterDetails) { detail.MaestroId = maestroId; } // Now masterDetails list contains MaestroId for each detail // Insert details into the Detail table foreach (var detail in masterDetails) { using (SqlCommand detailCommand = new SqlCommand("sp_InsertDetail", connection, transaction)) { detailCommand.CommandTimeout = 20; detailCommand.CommandType = System.Data.CommandType.StoredProcedure; detailCommand.Parameters.AddWithValue("@MaestroId", detail.MaestroId); detailCommand.Parameters.AddWithValue("@LastName", detail.LastName); detailCommand.ExecuteNonQuery(); } } } transaction.Commit(); } } catch (Exception ex) { // Handle the exception transaction.Rollback(); } }
public void Method1() { // Create a list of EN_MasterDetail objects List<EN_MasterDetail> masterDetails = new List<EN_MasterDetail>(); // Fill the list with the last names you want // You can use the constructor of the EN_MasterDetail class or its properties masterDetails.Add(new EN_MasterDetail { LastName = "Pérez" }); masterDetails.Add(new EN_MasterDetail { LastName = "García" }); masterDetails.Add(new EN_MasterDetail { LastName = "López" }); // Invoke the registerMasterAndDetail method passing the master name and the list of details BD_MasterDetail bdMasterDetail = new BD_MasterDetail(); bdMasterDetail.registerMasterAndDetail("Master 1", masterDetails); // You don't need the MaestroId here; it is handled internally }
I am writing this code because I believe it is the best way to control inconsistencies. After saving the sale, I have to save the Kardex, and that's why I need to obtain the MaestroId field. Please, and if it doesn't violate the forum policies, provide the code you think I should have written to achieve what I needed. I'm also providing the table structure in case you want to create a small project and test it without taking too much time:
-- Create Maestro table CREATE TABLE Maestro ( Id INT IDENTITY(1,1) PRIMARY KEY, Nombre VARCHAR(50) ); -- Create Detail table CREATE TABLE Detail ( Id INT IDENTITY(1,1) PRIMARY KEY, MaestroId INT FOREIGN KEY REFERENCES Maestro(Id), LastName VARCHAR(50) ); GO -- Create stored procedures to insert data CREATE PROCEDURE sp_InsertMaster @Name VARCHAR(50), @IdMaster INT OUTPUT AS BEGIN INSERT INTO Maestro (Nombre) VALUES (@Name); SET @IdMaster = SCOPE_IDENTITY(); END; GO CREATE PROCEDURE sp_InsertDetail @MaestroId INT, @LastName VARCHAR(50) AS BEGIN INSERT INTO Detail (MaestroId, LastName) VALUES (@MaestroId, @LastName); END; SELECT * FROM Maestro; SELECT * FROM Detail; DELETE FROM Detail; DELETE FROM Maestro; DROP TABLE Detail -- To test if the rollback works