Using sqlTransaction with C#

Question

Hello, I'm trying to use SqlTransaction from C# to create a transaction that performs an insertion into a table called "maestro" and another table called "detalles". I have managed to insert without any issues, but I believe I am doing it wrong because I am passing the foreign key field to the details from the data layer, whereas I think I should pass the data from the presentation layer. However, I can't capture the auto-incremented value "MaestroId" at that moment to add it to my list. I have the following code: Data Layer:

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("@MaestroId", SqlDbType.Int)             {                 Direction = ParameterDirection.Output             };             int maestroId;              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                 maestroId = Convert.ToInt32(outputParameter.Value);             }              foreach (var detail in masterDetails)             {                 using (SqlCommand command = new SqlCommand("sp_InsertDetail", connection, transaction))                 {                     command.CommandTimeout = 20;                     command.CommandType = System.Data.CommandType.StoredProcedure;                     command.Parameters.AddWithValue("@MaestroId", maestroId);                     command.Parameters.AddWithValue("@LastName", detail.LastNames);                      command.ExecuteNonQuery();                 }             }              transaction.Commit();         }     }     catch (Exception ex)     {         // Handle the exception         transaction.Rollback();     } }

Logic Layer:

public void registerMasterAndDetail(string name, List<EN_MasterDetail> masterDetail) {     BD_MasterDetail bdMasterDetail = new BD_MasterDetail();     bdMasterDetail.registerMasterAndDetail(name, masterDetail); }

In the Presentation Layer, I'm struggling with how to obtain the value of the "MaestroId" field to send it to the detail. I have tried the following code, but I haven't been able to obtain that value:

public void Method1() {     RN_MasterDetail rnMasterDetail = new RN_MasterDetail();     EN_MasterDetail masterDetail = new EN_MasterDetail     {         MaestroId = 1, // Here I need to obtain the value from the master table,         LastNames = "Melgar",         Age = 30, // Assume a value for the example         Height = 1.75 // Assume a value for the example     };     rnMasterDetail.registerMasterAndDetail("Master 1", masterDetail); }

As you can see, in this line, I am hardcoding the value of the "MaestroId" field. But only one data is saved for each last name, and I want one "MaestroId" to have several "Details". This is an example because in the program I'm trying to make, it should iterate through a dataGridView. I have tried to change my methods to return a boolean, in the master so that if it is saved, the detail is saved only if the detail table exists. However, if the detail table does not exist, I would still have data inconsistency.

Answer 1

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