Last post Jul 28, 2011 02:36 PM by atconway
Jul 25, 2011 01:01 PM|jonattree|LINK
I have a Suppliers table in SQL 2008 with just ID and Name columns. Name must be unique in the table. When user enters a new Supplier I could use the Business Logic layer to determine whether the name already exists which would entail another database call
or I could use the Data Access layer to return some sort of error code.
What would be the recommended approach in this scenario?
Many thanks for any help.
Jul 25, 2011 01:21 PM|AceCorban|LINK
if you tag the table on the database to be unique, you will get a SQL error when trying to insert a row with a name that already exists. Trouble is that it if you are using a try/catch to handle errors, it is difficult to know what caused the exception
(whether it was a unique column violation or some other connection issue).
One thing I would consider is using a Repository Pattern for your datamodel. In a repository pattern, you implement functions such as InsertUser(UserAccount) or GetUser(userId) or whatever. Inside the InsertUser method, you could perform the validation
check first and return an error if it already exists. That way, your business logic doesn't care whether or not the column is unique, it just sends off the information and spits out any error messages it comes across.
Jul 25, 2011 03:17 PM|atconway|LINK
Another option would be to use the .Find method on a list of objects collection. You can pass a predicate into the .Find method to determine if the element already exists within the collection. In your case you would be matching on the same 'Supplier' description.
What do you search against? The Suppliers already brought back, or all
suppliers retrieved in cases where the operation does not cause too much overhead (meaning - don't pull back 100,000 records just to do some server side matching). In the case where you do not have all of the records, or need to match up real-time against the
database because multiple users are inserting, then there is really no other way then to call back to the database to insert and check for the duplicate. In SQL you can prevent duplicate entries in a stored procedure easily by not preforming the insert until
making sure then new value (Supplier) is unique.
For a nice example on using the .Find() method, read the sample in the link below:
List(Of T).Find Method:
Jul 26, 2011 04:34 AM|jonattree|LINK
Thanks for the replies.
I know I can check for duplicates in the Insert stored procedure but this would mean that my business logic is being executed in the DAL rather than BLL - more efficient but not consistent with n-Tier. So I guess the correct solution is to check for duplicates
first in BLL even though an extra db call is required.
Jul 28, 2011 02:36 PM|atconway|LINK
I know I can check for duplicates in the Insert stored procedure but this would mean that my business logic is being executed in the DAL rather than BLL
I don't know if I completly agree the notion that inserting duplicates is
pure BL and cannot be done in the database. If there were some rules around the 'types' or 'conditions' where duplicates can or cannot be entered then I understand, but just disallowing dups period I think can be done just fine in the SP. The result of
if the duplicate was dissalowed can still be handled in the BLL based on if a newly created ID was returned from SQL or not.
Did you consider my other suggestions? If you only need to check for duplicates against a resultset you have already pulled back into code, then you don't need to go back to the database regardless. LINQ to Objects, LINQ to DataSets, or the avalble extension
methods on a list of objects will do this easily.