Should this logic be in the BO or the proc?

rhinoishere posted on Wednesday, October 11, 2006

Lets say I'm doing a delete on a "Users" table, but before I delete a user, I have to check if they have any associated rows in the "Assignments" table. If no rows are found in Assignments, the User is deleted. If rows are found in the Assignments table, nothing is done.

In the past, all this logic was handled in a proc. However... whether the user should be deleted or not is business logic, correct? So I'm assuming it should go in the BO instead of the proc.

This means I'll have to create a seperate proc to get the count on the Assignments table. The count would then be returned to the BO. If the count is greater than zero, no delete will be done.

If this sounds correct, then where do I put the method that gets the count from the Assignments table?

Should I just add a GetUserAssignmentCount(UserID) method inside the User business object?

Please tell me where I am going wrong.

thanks

(and, sorry, this question did end up being a bit similar to one I posted earlier)

William replied on Thursday, October 12, 2006

I will write this logic directly in the stored procedure or SQL statement because it is more efficient than checking the count in Assignments table (separate query) for each user. It might look like this (in non-optimized way):

DELETE [User]

WHERE UserID = @UserID

AND NOT EXIST ( SELECT UserID FROM Assignments WHERE UserID = @UserID )

Then, you check the affected row count from your code after ExecuteNonQuery().

Regards,

William

rhinoishere replied on Thursday, October 12, 2006

Ok, that's exactly how we have done it in the past.

That is essentially putting business logic into a proc though, correct? I thought that was a no no. I'm sure there is a line that you don't want to put business logic into the proc, but it seems like a blurry line to me at this point.

thanks for your help!

William replied on Thursday, October 12, 2006

Well, you can put all your logic in code, if this is your policy. In this case, you can create a private helper function in your BO that first executes a query to perform the count then the delete query. In a more critical case, you might encounter concurrency issue as you are executing two queries where the count might be invalid when your are about to execute your delete query.

I my opinion, it is always a good practise to centralize business logic at one location. However, what I have encountered in most real world projects, I don't think you can "practically" centralize ALL logic in one place; but I will try my best to achive centralization whenever possible. A good example would be on reporting and searching, which always involves a large data set in result. Thus, I would choose to write most of the query/logic in SQL/stored procedure for practical purposes.

Regards,

William

david.wendelken replied on Friday, October 13, 2006

rhinoishere:

Ok, that's exactly how we have done it in the past.

That is essentially putting business logic into a proc though, correct? I thought that was a no no. I'm sure there is a line that you don't want to put business logic into the proc, but it seems like a blurry line to me at this point.

thanks for your help!

This isn't a religion, it's just a job. That's a guideline, not a commandment.

JHurrell replied on Thursday, October 12, 2006

First off, I think you need to do a little work before you call the procedure that deletes a user and it should start in the UI.

You shouldn't even be able to delete a user unless that user is in a state that allows them to be deleted. You're probably already checking to see if the current user has the necessary authorization rules and roles required to allow them to delete users.

What you might want to consider is creating some object that retrieves information about the user and their relationship to other objects, in your case Assignments. If they have assignments and this precludes them from being deleted, don't even display the delete link or button.

For example, you might provide a grid that displays users. One of the columns could display a summary of the number of Assignments. If the number of Assignments is zero, display the delete link, if greater than zero, don't display the delete link.

If there were no Assignments associated with the user when you displayed the grid but are when you delete, the database can tell you that with an error and you can decide whether to update the grid, display an error, take the user to a page that alllows them to administer the relationships between users and Assignments or any other paths.

- John

jkellywilkerson replied on Thursday, October 12, 2006

You could also setup a CommandObject for the User BO similar to the "Exists" class that would return a bool, like "HasAssignments" or something. That way the delete button's enabled prop could be set to !(User.HasAssignments(USERID)). This works well and is somewhat close to your thought of adding a GetAssignmentCount.

Hope it helps.

Kelly.