Distributed APP strategies-What to do where is killing me...

I just read this quote at DevX.Com
"It's generally better to use the stored procedure option to perform the
transaction against the database rather than initiate a transaction on a
Connection object. When possible, you should isolate transactions to the
data tier, because it's the DBMS that actually performs the transactions."
OK, that seems reasonbale to me. Also, it makes sense to me that Business
Rule edits should be performed in the Application tier.
Howver, what if the Business edit requires a DB lookup, should it be
performed in the database in a SP or in a Buiness Object on the app tier?
I expect the "correct" answer is in the App Tier? Hower, if the edit is
performed in the App tier and if it passes the edit, and a SP is called to
perform the update, it seems to me that unless we have placed the Business
Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction on
the Connection object, that we cannot guarantee that at the time of the db
update, that the Business Rule edit check would still pass. Some other user
may have snuck in a performed an update in between the Business Rule check
on the application Tier and the actual update to the database!
I guess I need to understand more about LOCKING. If the records queried by
the Business Edit were still locked at the time of the update, we could
assume that the state of the database is still such that an update is still
permitted. I assumed that enclosing the Business edit and the update in a
transaction would ensure that.
Now my head starts hurting when I throw into the mix the fact that we are
using .NET Remoting (Well Known Single Call) STATELESS objects.
I understand that STATELESS remote objects allow for increase scalability
and load balancing, and so that is why this approach was probably taken.
Hwoever, since the objects ae stateless, each time we instantiate an object
to perform a database call, we are establishing a new connection. And this
presents the problem of how do you tie the Business Edit lookup and the Data
Update into a single transaction by initiating a Transaction on the
Connection object is a new Connection is established with each call to the
database!
Furthermore, if you have two business abojects, Account and User, and you
want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser method
and have both operation performed in a transaction, how do you do this if a
each object is establishing its own connection!?
The answer, it seems to me, is that you can't, that you need to create a new
object to do both, but this throws away the ability to reuse existing
objects.
It seems that the more granular an Object is the more reusable it is, but by
using SingleCall Remoting we do not have the ability to tie multiple updates
into a transaction. Not to mention that the Account object need to perform
an BO edit before adding an Account (to make sure it doesnt already exist in
the db) and the User object needs to peform an edit before adding a user,
that this will require *TWO* round trips to the server, which hurts
performance. By writing a new object to do both at the same time, you
increase performance by making one trip by throw away reusability since most
of the time you will need to do one operation or the otehr, not both.
I could go on and on. I hope you were able to follw the jist it.
I wish you MVPs were sitting next to my cube.
Many thanks for all the help you've given me in just a short time..
Chad
"Chad" wrote:

> I just read this quote at DevX.Com
> "It's generally better to use the stored procedure option to perform the
> transaction against the database rather than initiate a transaction on a
> Connection object. When possible, you should isolate transactions to the
> data tier, because it's the DBMS that actually performs the transactions."
> OK, that seems reasonbale to me. Also, it makes sense to me that Business
> Rule edits should be performed in the Application tier.
> Howver, what if the Business edit requires a DB lookup, should it be
> performed in the database in a SP or in a Buiness Object on the app tier?
>
Thats where you need to the data to apply your rules, you're right..

> I expect the "correct" answer is in the App Tier? Hower, if the edit is
> performed in the App tier and if it passes the edit, and a SP is called to
> perform the update, it seems to me that unless we have placed the Business
> Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction
on
> the Connection object, that we cannot guarantee that at the time of the db
> update, that the Business Rule edit check would still pass. Some other use
r
> may have snuck in a performed an update in between the Business Rule check
> on the application Tier and the actual update to the database!
This is a concurrency issue, I create a BINARY_CHECKSUM containing the rows
that will be updated when the data is loaded into the business object, it's
a
private readonly property, this gets passed back to the procedure that
updates if the checksum is different at the time of update then you know tha
t
the data has changed since the read, do whatever to handle it then.

> I guess I need to understand more about LOCKING. If the records queried by
> the Business Edit were still locked at the time of the update, we could
> assume that the state of the database is still such that an update is stil
l
> permitted. I assumed that enclosing the Business edit and the update in a
> transaction would ensure that.
The locking will only occur when you do the update.

> Now my head starts hurting when I throw into the mix the fact that we are
> using .NET Remoting (Well Known Single Call) STATELESS objects.
>
Don't let this confuse you these are totaly seperate techonologies, have you
looked at CSLA...? It's a very well thought out object oriented framework of
which there are many users...
http://groups.msn.com/CSLANET
http://www.apress.com/book/bookDisplay.html?bID=198
Also take a look at Ingo Rammers remoting book, very good stuff must have if
your doing .Net remoting.
http://www.apress.com/book/bookDisplay.html?bID=108

> I understand that STATELESS remote objects allow for increase scalability
> and load balancing, and so that is why this approach was probably taken.
> Hwoever, since the objects ae stateless, each time we instantiate an objec
t
> to perform a database call, we are establishing a new connection. And this
> presents the problem of how do you tie the Business Edit lookup and the Da
ta
> Update into a single transaction by initiating a Transaction on the
> Connection object is a new Connection is established with each call to the
> database!
> Furthermore, if you have two business abojects, Account and User, and you
> want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser meth
od
> and have both operation performed in a transaction, how do you do this if
a
> each object is establishing its own connection!?
> The answer, it seems to me, is that you can't, that you need to create a n
ew
> object to do both, but this throws away the ability to reuse existing
> objects.
In these cases where you have more than one business object that you want to
enlist in the same transaction you might create a controller class that
orchestrates the transaction using the EnterpriseServices.ServicedComponent
functionality which is basically MTS for .Net. This is not an ADO.NET
transaction anymore, even though your still using an ADO transaction in your
business object and maybe even T-SQL transactions in your sp's. Nonetheless
they'll be controlled by the serviced component, keep in mind this creates a
LOT of overhead, don't do this unless you have to.

> It seems that the more granular an Object is the more reusable it is, but
by
> using SingleCall Remoting we do not have the ability to tie multiple updat
es
> into a transaction. Not to mention that the Account object need to perform
> an BO edit before adding an Account (to make sure it doesnt already exist
in
> the db) and the User object needs to peform an edit before adding a user,
> that this will require *TWO* round trips to the server, which hurts
> performance. By writing a new object to do both at the same time, you
> increase performance by making one trip by throw away reusability since mo
st
> of the time you will need to do one operation or the otehr, not both.
> I could go on and on. I hope you were able to follw the jist it.
> I wish you MVPs were sitting next to my cube.
> Many thanks for all the help you've given me in just a short time..
> Chad
>
>