Ryan,
This is a really bad design for your data layer for a number of reasons.
First, you are passing sql statements to your data layer. This is
unadvisable, as you should use stored procs instead. Also, if you have to
pass SQL statements, you should at least make sure that you parameterize
them so that you protect yourself against injection attacks.
But that's not the real problem here. Here, you are passing your
connection and transaction object to your data layer. The problem with this
is that those objects both derive from MarshalByRefObject. This means that
a proxy is passed to your data layer and any calls to methods/properties on
the connection/transaction are being made back to your machine.
What you really should be doing is handling this in process. You aren't
gaining anything by sending it to another machine, since you are opening the
connection on yours.
Either that, or have the data layer construct the connection.
However, that doesn't solve the issue you have with managing the
transaction. In reality, you need to make the operation that you are
performing transactional. There are a few ways of doing this.
The first would be to use .NET 2.0 and use the TransactionScope class to
encapsulate the transaction scope. The second would be to create a COM+
component through the classes in the System.EnterpriseServices namespace.
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
-
mv*@spam.guard.caspershouse.com
"Ryan H" <rh******@smci.comwrote in message
news:e%****************@TK2MSFTNGP03.phx.gbl...
Hi,
My data access layer for my VB.NET 1.1 application resides on a different
server and is accessed via Remoting.
Is there a way to configure my data access layer so that I can use
Transactions? For example, I would like to do the following from the
client:
Try
DataAccessLayerClass.RunSQL("UPDATE MyTable SET Column3 = "aa", ConnObj,
TransactionObj)
SendEmail()
TransactionObj.Commit
Catch
' Update or sending of email failed
TransactionObj.Rollback
End Try
Any help on how to use Transactions while the data access layer is
accessed via Remoting would be appreciated.
I am also open to rewriting my current code (i.e. change data access
location) if there is an alternative solution.
Thanks.