Sunday, March 8, 2020

Execute Store Procedure by Entity Framework .NET core

Hello

For example, our Store Procedure (SP) called "DeletePartsById"

Now, from C# code we want call this SP on x Ids, so call SP x times
Function returns delete statuses of D (CRUD ) operation:

  1. public List<DeleteStatus> Delete(List<PartToDelete> partsToDelete)
  2. {
  3. //Order of usings etc.:
  4. //1. Create connection and make open
  5. //2. Create transaction from connection
  6. //3. Create command from connection and transaction
  7. //4. do all db CRUD operations
  8. //5. Commit transaction
  9. List<DeleteStatus> deleteStatuses = new List<DeleteStatus>();
  10. using (SqlConnection connection = new SqlConnection(_connectionString))
  11. {
  12. if (connection.State == System.Data.ConnectionState.Closed)
  13. {
  14. connection.Open();
  15. }
  16. using (var transaction = connection.BeginTransaction())
  17. {
  18. using (SqlCommand cmd = new SqlCommand("DeletePartsById", connection, transaction))
  19. {
  20. cmd.CommandType = System.Data.CommandType.StoredProcedure;
  21. cmd.Parameters.Add(SPGroupIdParameterName, System.Data.SqlDbType.VarChar);
  22. foreach (PartToDelete partToDelete in partsToDelete)
  23. { 
    //Execute Store Procedure
    cmd.Parameters[0].Value = partToDelete.Id; 
    int resultOfExecuteSP = cmd.ExecuteNonQuery();
  24. //delete from  table
  25. EntityEntry<Part> removedPart = _context.Remove
  26. (_context.Part.FirstOrDefault(part => part.Id == partToDelete.Id));
  27. _context.SaveChanges();
  28. //create result
  29. DeleteStatus deleteStatus = new DeleteStatus(partToDelete.Id, removedPart.State.ToString());
  30. deleteStatuses.Add(deleteStatus);
  31. }
  32. transaction.Commit();

    }
  33. }
  34. }
  35. return deleteStatuses;
  36. }


No comments:

Post a Comment