Hi all,
I wasn't sure if this was more of a .Net question or SQL, it seemed more .Net but apologies if this is the wrong forum.
So I have a new third party database which provides a web service which I'm bound by contract to use to update any of their data tables in this sql-2008 R2 db (we host the db in-house).
So we can write our own .Net code for this app, providing we use the webservices (we have no source code, just the .dll) to update a table, but we want auditing as well. They didn't have an audit table so we built our own audit table (which we can update as we wish), and want to insert to that when we update certain "core" data.
However, I want the audit insert and the webservice update all wrapped in a transaction. I've always felt a little queasy about opening a transaction from .Net code, so I've always done transaction processing within a stored proc but with this new system I don't have that option.
So is there a best practice for this type of thing? Is this "done", ie, is it common practice to open a transaction from .Net code, then call webservices, then do your own updates, whatever, then commit from the .Net code, even if the webservice may be doing it's own thing with a transaction?
Sorry for seeming dense about this, I have never really done any transaction processing via .net before.
Thanks,
--Jim
I wasn't sure if this was more of a .Net question or SQL, it seemed more .Net but apologies if this is the wrong forum.
So I have a new third party database which provides a web service which I'm bound by contract to use to update any of their data tables in this sql-2008 R2 db (we host the db in-house).
So we can write our own .Net code for this app, providing we use the webservices (we have no source code, just the .dll) to update a table, but we want auditing as well. They didn't have an audit table so we built our own audit table (which we can update as we wish), and want to insert to that when we update certain "core" data.
However, I want the audit insert and the webservice update all wrapped in a transaction. I've always felt a little queasy about opening a transaction from .Net code, so I've always done transaction processing within a stored proc but with this new system I don't have that option.
So is there a best practice for this type of thing? Is this "done", ie, is it common practice to open a transaction from .Net code, then call webservices, then do your own updates, whatever, then commit from the .Net code, even if the webservice may be doing it's own thing with a transaction?
Sorry for seeming dense about this, I have never really done any transaction processing via .net before.
Thanks,
--Jim