Re: Transactions without setting OleDbCommand.Transaction property
From: Osvaldo Bisignano (a_at_a.com)
Date: 08/04/04
- Next message: William \(Bill\) Vaughn: "Re: DataAdapter.Update Command Failed To Update Database"
- Previous message: Angel Saenz-Badillos[MS]: "Re: Transactions without setting OleDbCommand.Transaction property"
- In reply to: Angel Saenz-Badillos[MS]: "Re: Transactions without setting OleDbCommand.Transaction property"
- Next in thread: Osvaldo Bisignano: "Re: Transactions without setting OleDbCommand.Transaction property"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 4 Aug 2004 14:32:15 -0300
Very simple and interesting. I'll try that.
Thanks
"Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message
news:%23Ho5NekeEHA.3684@TK2MSFTNGP09.phx.gbl...
> Osvaldo,
> There are very few differences between calling the SqlClient
> connection.BeginTransaction and executing a command with
commandText="Begin
> Transaction".
>
> Code like this is perfectly valid:
> sqlconnection1.Open();
> SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
> sqlcommand1.CommandText = "begin transaction";
> sqlcommand1.ExecuteNonQuery();
>
> //everything here will run under this transaction:
> sqlcommand1.CommandText = "create table foo123 (myid int)";
> sqlcommand1.ExecuteNonQuery(); // -1
> SqlCommand sqlcommand2 = sqlconnection1.CreateCommand();
> sqlcommand2.CommandText = "insert into foo123 values (1)";
> sqlcommand2.ExecuteNonQuery(); // 1
> SqlCommand sqlcommand3 = sqlconnection1.CreateCommand();
> sqlcommand3.CommandText = "insert into foo123 values (2)";
> sqlcommand3.ExecuteNonQuery(); // 1
>
> //It is up to you to Commit or Rollback all the changes that
> //your commands have done.
> sqlcommand3.CommandText = "rollback transaction";
> sqlcommand3.ExecuteNonQuery(); // -1
>
> So what does conn.BeginTransaction buy you? Well you save one database
> roundtrip since we will batch the first Begin Transaction command with the
> first execute, in this case it will be sent with "create table...". We
keep
> track of the transaction, so if you leak it we will explicitly roll it
back
> on connection close. If you leak the transaction in the model above on
> connection close we will return the connection to the pool with the
> transaction active and it can lock your database for up to 8 minutes under
> worst case scenario.
>
> One word of warning, I would HIGHLY recommend you do not mix both API and
> client side TSQL transactions. This can easily be a recipe for disaster.
If
> you stick to either model you will be much better off.
>
> Saludos!
>
> --
> Angel Saenz-Badillos [MS] Managed Providers
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Osvaldo Bisignano" <a@a.com> wrote in message
> news:ehVfR9ieEHA.3632@TK2MSFTNGP09.phx.gbl...
> > Perdón Angel, but I think you missed that I want to run all (or at least
> > more than one) commands in the scope of the same transaction, in such a
> way
> > that if one of them fails, all of them rollback.
> > On the other hand, I think that when you run an ExecuteNonQuery, an
> > automatic transaction is begun automatically, and it is commited when it
> > reaches the end of the command text.
> > Correct me if i'm wrong.
> >
> > Saludos,
> > Osvaldo
> >
> >
> >
> > "Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in
message
> > news:%230mi6zLeEHA.212@TK2MSFTNGP12.phx.gbl...
> > > Osvaldo,
> > > There is nothing stopping you from using transactions outside of the
> API.
> > > For example when connecting to Sql Server I can executenonquery a
> command
> > > with command text="Begin Transaction" and this will start a
transaction
> on
> > > the server outside of the API. All of your commands will automatically
> run
> > > under this transaction without needing to enlist a transaction object
to
> > > them. When you are done you can just executenonquery "commit
> transaction"
> > to
> > > commit or "rollback transaction" to roll back the changes.
> > >
> > > --
> > > Angel Saenz-Badillos [MS] Managed Providers
> > > This posting is provided "AS IS", with no warranties, and confers no
> > > rights.Please do not send email directly to this alias.
> > > This alias is for newsgroup purposes only.
> > > I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
> > >
> > >
> > >
> > >
> > > "Osvaldo Bisignano" <a@a.com> wrote in message
> > > news:OIZKEokdEHA.2504@TK2MSFTNGP12.phx.gbl...
> > > > I have to modify existing code which doesn't use transactions at
all.
> It
> > > > only uses OleDbCommands to execute SQL Statements against the DB.
> > > >
> > > > I know that each of the commands must have their Transaction
property
> > > > established, but i don't want to modify all of the commands, which
are
> > > many.
> > > >
> > > > Is there any way to make the Commands ASUME by default the first
> > > transaction
> > > > started in Connection.BeginTransaction? I mean, like we used to do
> with
> > > ADO
> > > > and recordsets?
> > > >
> > > > By the way, all of the sentences are ExecuteNonQuery, so I cannot
use
> > > > DataAdapter.Update (what would be a kind of transaction)
> > > >
> > > > Thanks in advance,
> > > > Osvaldo
> > > > Buenos Aires
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: William \(Bill\) Vaughn: "Re: DataAdapter.Update Command Failed To Update Database"
- Previous message: Angel Saenz-Badillos[MS]: "Re: Transactions without setting OleDbCommand.Transaction property"
- In reply to: Angel Saenz-Badillos[MS]: "Re: Transactions without setting OleDbCommand.Transaction property"
- Next in thread: Osvaldo Bisignano: "Re: Transactions without setting OleDbCommand.Transaction property"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
