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:
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:
- public List<DeleteStatus> Delete(List<PartToDelete> partsToDelete)
- {
//Order of usings etc.:
//1. Create connection and make open
//2. Create transaction from connection
//3. Create command from connection and transaction
//4. do all db CRUD operations
//5. Commit transaction
List<DeleteStatus> deleteStatuses = new List<DeleteStatus>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
using (var transaction = connection.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand("DeletePartsById", connection, transaction))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(SPGroupIdParameterName, System.Data.SqlDbType.VarChar);
foreach (PartToDelete partToDelete in partsToDelete)
{
//Execute Store Procedure
cmd.Parameters[0].Value = partToDelete.Id;
int resultOfExecuteSP = cmd.ExecuteNonQuery();
//delete from table
EntityEntry<Part> removedPart = _context.Remove
(_context.Part.FirstOrDefault(part => part.Id == partToDelete.Id));
_context.SaveChanges();
//create result
DeleteStatus deleteStatus = new DeleteStatus(partToDelete.Id, removedPart.State.ToString());
deleteStatuses.Add(deleteStatus);
}
transaction.Commit();
}
}
}
return deleteStatuses;
- }