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.
I have tried to add the value of the maestroId field directly from the Data layer, but I don't think that's what should be done. I have also tried to divide the methods, but I still don't know how to get the MaestroId value to pass it to the detail.
command.Parameters.AddWithValue("@MaestroId", maestroId);
This code does work for me, but I think there may be something that is more maintainable. I would like to register the kardex after registering the sale in the same transaction. Could you please comment on how a sales transaction works in a supermarket so that I can change my approach?
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();
}
}
And in this code in the presentation layer I have this code that works
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`enter code here`
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
Please, based on what I have done, give me an example so I can continue with my work. Thank you.