Business Rules & Referential Integrity

Good Day All.
I have been trying to find general answers to a very specific question about
application design and implementation using DotNet.
My situation is that I would like to design and implement a Web Application.
I would like to avoid using datasets in favour of the datareader to
development time, increased application effeciency and a number of reasons
mentioned in the MSDN Library entitled "Recommendations for Data Access
Strategies".
Making this decision seems to pretty much end any ideas of creating a
distributed application.
I was hoping to implement the business logic and referential integrity in
stored procedures at the database. A quick example would be the following:-
CREATE PROCEDURE dbo.InsertItem
(
@.IID int = NULL OUTPUT,
@.Item_ITID int = NULL,
@.Item_IBID int = NULL,
@.ItemModel varchar(50) = NULL,
@.ItemColour varchar(50) = NULL,
@.ItemSerialNo varchar(50) = NULL,
@.ItemBarCode Image = NULL,
@.ItemDatePurchased datetime = NULL,
@.ItemPrice money = NULL,
@.ItemVAT money = NULL,
@.Item_IRID int = NULL,
@.ItemValue money = NULL,
@.ItemImg Image = NULL,
@.ItemImgPath varchar(255) = NULL,
@.Item_IGID int = NULL,
@.Item_IIID int = NULL,
@.ItemNotes varchar(255) = NULL,
@.Item_SPID int = NULL,
@.Item_SLID int = NULL,
@.Item_TXID int = NULL
)
AS
-- @.msgnum = (nn=DBID(10),nnn=TID(001),nn=OPID,nnn=ERRID)
SET NOCOUNT OFF;
DECLARE @.return_status int
-- CHECK Is Item Type Valid?
EXECUTE @.return_status = Exists_ItemType @.Item_ITID
IF @.return_status <> 1
raiserror(1001002001, 16, 1)
-- CHECK Is Item Brand Valid?
EXECUTE @.return_status = Exists_ItemBrand @.Item_IBID
IF @.return_status <> 1
raiserror(1001002002, 16, 1)
-- CHECK Is Item Receipt Valid - NOT MANDATORY?
IF ISNULL(@.Item_IRID, 0) <> 0 BEGIN
EXECUTE @.return_status = Exists_ItemReceipt @.Item_IRID
IF @.return_status <> 1
raiserror(1001002003, 16, 1)
END
-- CHECK Is Item Guarantee Valid - NOT MANDATORY?
IF ISNULL(@.Item_IGID, 0) <> 0 BEGIN
EXECUTE @.return_status = Exists_ItemGuarantee @.Item_IGID
IF @.return_status <> 1
raiserror(1001002004, 16, 1)
END
-- CHECK Is Item Insurance Valid - NOT MANDATORY?
IF ISNULL(@.Item_IIID, 0) <> 0 BEGIN
EXECUTE @.return_status = Exists_ItemInsurance @.Item_IIID
IF @.return_status <> 1
raiserror(1001002005, 16, 1)
END
INSERT INTO Item
(Item_ITID, Item_IBID, ItemModel, ItemColour,
ItemSerialNo, ItemDatePurchased, ItemPrice, ItemVAT, Item_IRID, ItemValue,
Item_IGID,
Item_IIID, ItemNotes, Item_SPID, Item_SLID,
Item_TXID, AO)
VALUES (@.Item_ITID, @.Item_IBID, @.ItemModel, @.ItemColour, @.ItemSerialNo,
@.ItemDatePurchased, @.ItemPrice, @.ItemVAT, @.Item_IRID, @.ItemValue,
@.Item_IGID, @.Item_IIID, @.ItemNotes, @.Item_SPID,
@.Item_SLID, @.Item_TXID, 2)
SET @.return_status = @.@.ERROR
IF @.return_status <> 0
raiserror(1001002006, 16, 1, @.return_status)
ELSE
SELECT @.IID = SCOPE_IDENTITY()
GO
The above procedure would attempt to ensure business logic and referetial
integrity by using other stored procedures.
Any integrity checking that fails would result in a user defined error
number and message being raised and the exception caught within the Web
Application.
1) Does this design/approach seem appropriate or is it a normalpractice?
2) I understand that it is possible for the integrity to still be
compromised. Should I also implement Declarative Referential Integrity by
using relationships and foreign key constraints. Is this normal practice?
3) Attempting to code referential integrity allows me to raise specific
errors that can easily be interpreted and handled by my application however
a foreign key constraint error seems impossible for me to interpret and give
any positive feedback to the user.
I hope I have mase myself clear and someone could shed some light on these
processes.
Kind Regards
Paul Johnson.
Paul,
Your questions are very clever.

> 1) Does this design/approach seem appropriate or is it a
normalpractice?
I've seen many different practices, so I wuld not like to answer to this.

> 2) I understand that it is possible for the integrity to still be
> compromised. Should I also implement Declarative Referential Integrity by
> using relationships and foreign key constraints. Is this normal practice?
I alway suggest to implement DRI as well. The more important the rule is,
the closer to the data it should be.

> 3) Attempting to code referential integrity allows me to raise specific
> errors that can easily be interpreted and handled by my application
however
> a foreign key constraint error seems impossible for me to interpret and
give
> any positive feedback to the user.
Yes, you are right - with DRI, you can't have your own error messages.
Still, you can catch them in your code. You can read a lot of good info on
error handling in two Erland's articles at http://www.sommarskog.se/.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thank you kindly for your reply.
It has now been made clear to me that if I use DRI I can capture there error
number which will reveal the type of constraint violated and then possibly
use any constraint name I have used to identify the constraint and provide
better feedback
Thank you also for the website link.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OgiwKUG0EHA.2600@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Paul,
> Your questions are very clever.
> normalpractice?
> I've seen many different practices, so I wuld not like to answer to this.
by[vbcol=seagreen]
practice?[vbcol=seagreen]
> I alway suggest to implement DRI as well. The more important the rule is,
> the closer to the data it should be.
specific
> however
> give
> Yes, you are right - with DRI, you can't have your own error messages.
> Still, you can catch them in your code. You can read a lot of good info on
> error handling in two Erland's articles at http://www.sommarskog.se/.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>