Norman
Sun Jul 20 10:27:46 CDT 2008
You could either do the transaction at the SQL Server end, or do it in your
ADO.NET code with SqlTransaction object.
With SQL Server transaction, you could place all the updateing process in a
single stored procedure, which wraps up the processes with
BEGIN TRANS
...
multiple updates
...
COMMIT TRANS/ROLLBACK TRANS
You need to be pretty good at T-SQL, of course. With SQL Server2005's
TRY...CATCH..., you could hanlde COMMIT or ROLLBACK a bit easier than using
SQL Server2000.
As .NET coder, I found in many cases, ADO.NET transaction is a bit easier to
use/write. Here is pseudo code:
public void TransactionUpdate(....)
{
using (SqlConnection cn=new SqlConnection(..))
{
try
{
cn.Open();
}
catch{//open connection failed, return}
//Start Transaction
using (SqlTransaction tran=cn.BeginTransaction())
{
//Define you first SqlCommand and add it into the transaction
SqlCommand cmd1=cn.CreateCommand();
cmd1.CommandType=CommandType.StoredProcedure;
cmd1.CommandText="MySP1";
...set up command parameter
cmd1.Transaction=tran;
//Define you second SqlCommand and add it into the transaction
SqlCommand cmd2=cn.CreateCommand();
cmd2.CommandType=CommandType.StoredProcedure;
cmd2.CommandText="MySP2";
...set up command parameter
cmd2.Transaction=tran;
//Define more commands to execute different SPs in the same
transaction, as your business logic needs
...
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
...
//If all commads execution are OK, commit the transaction
tran.Commit();
}
catch(...)
{
//Roll back the transaction, which is multiple SP executions
tran.RollBack();
//Notify the transaction rollbacl
...
}
}
}
}
Of course, in the real world, you need to be very careful as to what is
placed in a single transaction and write your code robust.
HTH.
"Jonathan Wood" <jwood@softcircuits.com> wrote in message
news:uGgTBMn6IHA.5012@TK2MSFTNGP02.phx.gbl...
> Until now, I've been using stored procedures to update my databases
> because I've been taught stored procedures are faster (precompiled) and
> eliminate the possibility of some kinds of injection attacks.
>
> But now I need to create code to record a purchase transaction. Not only
> would this require around a dozen arguments, but these arguments could
> include any number of invoice items. Based only on trying to pass these
> arguments, it does not appear that a stored procedures is up to the job.
>
> I'm still pretty new to database development and would appreciate any
> comments on the best way to proceed with this.
>
> Thanks.
>
> --
> Jonathan Wood
> SoftCircuits Programming
>
http://www.softcircuits.com
>