I have the following stored procedure which I use to fill the Resources table. To load the Resources table, I get the IdEntity that I bring from a datagrid that contains a list of accounts. The problem is that when loading the data in the Resources table, if I select 350 accounts, I upload 350 records regardless of whether the record already exists or not.
I leave the tables and the stored procedure:
Resources
-----------------------------
ResourceId(int)
ResourceName(varchar(max))
Carteras
-----------------------------
IdCartera (int)
NombreCartera (varchar(max))
estado (varchar(max))
IdEntidad (varchar(max))
Entidad
-----------------------------
IdEntidad (int)
NombreEntidad (varchar(max))
estado (varchar(max))
Alias (varchar(max))
CREATE PROCEDURE [dbo].[sp_CargarTablaResources]
@pIdEntidad int
AS
BEGIN
SET NOCOUNT ON;
if exists(
select ResourceId, ResourceName from Resources
where ResourceId = @pIdEntidad)
Begin
raiserror('Err -998: El registro ya existe.', 1, 10)
return -998
end
Else
begin
insert into Resources (ResourceID, ResourceName)
select Carteras.cart_Id, Carteras.cart_Codigo from Carteras
where Carteras.enti_Id = @pIdEntidad
end
END
I have this method in the Data layer which is executed for each account that I have loaded in the datagrid to verify each IdEntity, then I am loading the data without evaluating if the record already exists or not:
public static void LlenarTablaResources(int IdEntidad)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=MiServidor;Initial Catalog=MiProyecto;Integrated Security=True";
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
cmd.CommandText = "sp_CargarTablaResources";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pIdEntidad", SqlDbType.Int).Value = IdEntidad; //("@IdEntidad", SqlDbType.Int).Value = IdEntidad;
try
{
cmd.Connection = con;
con.Open();
dr = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
}