I am using asp.net Core Web API with Npsql, I want to retrieve the id from the address table to insert it into the foreign key of the company table.
What I tried was to make a transaction obtaining the id with a SELECT MAX and pass the result to the company table
'''
[HttpPost]
public JsonResult Post(Empresa emp)
{
string query = @"
Begin;
SELECT MAX(idDireccion) AS @max_id FROM direccion;
insert into empresa (nombreComercial,logotipo,idDireccion,idClasificacion,estado)
values (@nombreComercial,@logotipo,@max_id,@idClasificacion,true);
commit;
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
NpgsqlDataReader myReader;
using (NpgsqlConnection myCon = new NpgsqlConnection(sqlDataSource))
{
myCon.Open();
using (NpgsqlCommand myCommand = new NpgsqlCommand(query, myCon))
{
myCommand.Parameters.AddWithValue("@nombreComercial", emp.nombreComercial);
myCommand.Parameters.AddWithValue("@logotipo", emp.logotipo);
myCommand.Parameters.AddWithValue("@max_id", emp.idDireccion);
myCommand.Parameters.AddWithValue("@idClasificacion", emp.idClasificacion);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
myCon.Close();
}
}
return new JsonResult("Agregado exitosamente");
}
'''
2 things first of all:
1- Never use Datatables instead use fast member : https://github.com/mgravell/fast-member
2-When one uses using(){} it is not necessary to call the close() method neither to close the database connection nor to close the reader, rather call the close() method when using using(){ } can cause errors.
You can do it with a function and instead of a transaction just lock the table like in the following example and if for some reason you need to do it with transaction without locking the table then just create a store procedure and call the function from the sp in a transaction :
function in postgres:
And the controller would look like this: