| Groups | Blog | Home |
dotnet distributed apps : Business Rules & Referential Integrity
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.
I don't know what you have read in the MSDN Library entitled
"Recommendations for Data Access Strategies" but it looks like that you have missed the point of the whole discussion about datasets and datareaders. Datareader are for ASP.NET which, even if they can be seen as clients from the SQL-Server perspective, must be seen as servers from the user perspective and today, the recommandation is to have the thinest servers and the fatest clients as possible; because it's the server resources who are scarce when comes the time to answer many client requests at the same time. The web server can use the Datareader when building an ASP.NET page because it's itself a server and the HTML pages are stateless in the www world; however that's not the case when your clients are connecting directly to the SQL-Server: they are statefull and as such, should be fat clients. Same thing about your ideas that you give us in your "quick example": not only you will end up with a big white elephant moving at the speed of a turtle but it's not even fail safe: SQL-Server being multi-threaded, in insertion from another thread with one or more identical values could easily be happened beetween your CHECKS and the SELECT INTO statement. Sorry to be rude but you are thinking to much; which is probably one the main reasons for project failures. S. L. [quoted text, click to view] "Paul Johnson" <ian.dixon@virgin.net> wrote in message news:OElMf%23xzEHA.3336@TK2MSFTNGP11.phx.gbl... > 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. > >
Thank you kindly for your reply.
I guess you are right, I am thinking too much, trying too hard to find the best approach and having never designed and implemented a Web Application using a Distributed Architecture, ASP.NET, ADO.NET and SQL Server with Stored Procedures its seems hard to find the right answers. I have had experience of ASP and ADO and see that using the datareader will allow for quicker development due to restrictive project time constraints and also the item I read in the MSDN that includes the following:- Web Forms In general, use data commands; to fetch data, use a data reader. Because Web Forms pages and their controls and components are recreated each time the page makes a round trip, it often is not efficient to create and fill a dataset each time, unless you also intend to cache it between round trips. Windows Forms In general, in a Windows Form, use a dataset. Windows Forms are typically used on rich clients where the form is not created and discarded (along with its data) with each user operation, as with Web Forms Datasets and Data Commands in Web Forms Pages When you are working with Web Forms pages, additional factors come into play when deciding whether to use a dataset or a data reader. A significant factor is the page life cycle - Web Forms pages are initialized, processed, and then discarded with each round trip. If you simply want to display data on the page, then creating a dataset, filling it, and then binding controls to it can represent unnecessary overhead, since the dataset will be discarded immediately. In many cases, it is more efficient to use data reader to fetch the data and then to bind controls to it at run time. My Web Application will primarily be retrieving and listing data input for which datasets seem overkill, please correct me if you believe this to be wrong. With regards to my stored procedures and the inherent "White Elephant", I fully appreciate your comments and will use Declared Referential Integrity in SQL Server and capture any named constraint errors/exceptions this way. I also understand that the example stored procedure was open to compromise and was not fail safe. Please could you offer any suggestions so I could fully appreciate a better more robust design. Kind Regards Paul Johnson. "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> [quoted text, click to view] wrote in message news:%230JzXy%23zEHA.2040@tk2msftngp13.phx.gbl...
>I don't know what you have read in the MSDN Library entitled >"Recommendations for Data Access Strategies" but it looks like that you >have missed the point of the whole discussion about datasets and >datareaders. Datareader are for ASP.NET which, even if they can be seen as >clients from the SQL-Server perspective, must be seen as servers from the >user perspective and today, the recommandation is to have the thinest >servers and the fatest clients as possible; because it's the server >resources who are scarce when comes the time to answer many client requests >at the same time. > > The web server can use the Datareader when building an ASP.NET page > because it's itself a server and the HTML pages are stateless in the www > world; however that's not the case when your clients are connecting > directly to the SQL-Server: they are statefull and as such, should be fat > clients. > > Same thing about your ideas that you give us in your "quick example": not > only you will end up with a big white elephant moving at the speed of a > turtle but it's not even fail safe: SQL-Server being multi-threaded, in > insertion from another thread with one or more identical values could > easily be happened beetween your CHECKS and the SELECT INTO statement. > > Sorry to be rude but you are thinking to much; which is probably one the > main reasons for project failures. > > S. L. > > "Paul Johnson" <ian.dixon@virgin.net> wrote in message > news:OElMf%23xzEHA.3336@TK2MSFTNGP11.phx.gbl... >> 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)
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
| home · blog · groups | Questions? Comments? Contact the dn |