Newbie ADO.Net Question.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to do the following in ADO.net using C#.
I need to do an update, select, update statement as a single unit using the
same DB connection. Is that possible?
The database doesn’t support stored procedures/functions.
I also want to make sure that it is locked while doing all these operations
as a unit.
Any ideas/code examples would be great.
Thanks
 
Look into the DataAdapter. Used in combination with a DataSet object and one
or more tables contained within that DataSet, these classes are tailor-made
for exactly what you are talking about. What you will do is create a SELET,
INSERT, DELETE, and UPDATE command for each table and associate it with the
data adapter. Once all the data changes have been made to the DataSet (which
is an "in-memory" representaton of the data held within your clirent
application) you issue the DataAdapter's "update" method, which will issue
individual INSERT, DELETE and UPDATE statements, according to what has
changes within the DataSet.
There's even a "CommandBuilder" method that will Auto-generate the SQL for
you (might need some tweaking).

There's lots of sample code around - look at MSDN, for example. For
locking, you can effect pessimistic locking by associating the INSERT,
DELETE, AND UPDATE commands with a Transaction object, which has the added
benefit of rollback, in case of failure.

Good Luck
 
Thanks, Rich.
Can i use database transactions for this? Just my idea of transaction got
blurred. appreciate your comments.
 
I think it depends on whether the DBMS you are using supports transactions,
which most do.

In other words, commiting and rolling back transactions are not a
capabilities of ADO.NET itself. ADO.NET merely supports those capabilities
when they are provided by the DBMS it is interfacing with.

You instantiate a transaction with the BeginTransaction method of an open
connection, like this:

tran = connection.BeginTransaction()

Then, you include individual commands in the transaction by setting their
command property to the transaction object:

cmd.Transaction = tran

You then issue the commands normally. If you use an exception handler, you
can trap any exceptions and call the "rollback" method if any of them fail,
or call "commit" if everything succeeds.

When using the Update method of a DataAdapter, like I suggested before, you
can use an exception handler on the call to the Update method and commit or
rollback all the updates, inserts, and deletes that were done by the Update.
Just make sure that all their transaction properties are poeperly set.

HTH
 
Thanks again Rich.

One other question that poped in my mind:
Assuming the transactions are supported by DBMS, When you say
BeginTransaction() and execute couple of select,update,select statements. Is
the same connection used or we creating different connection to the DB?

The app i am developing needs to have a good performance and so needs to use
the resources efficiently.

Appreciate your time and knowledge for this discussion.
 
BeginTransaction is a method of the connection class. You have to
instantiate a connection, open it, and then execute BeginTransaction.

Just out of curiosity, why would you include SELECT statements in a
transaction?
 
Hi Rich,
Thanks for the reply. I am trying to execute the select, update, select as a
unit. Executing them as a transaction guarantees ACID property right? or is
the better way for my scenario other than locking in my C# code.
 
I think you need to look into "Isolation Levels" of transactions. That gets
into whether other users should be able to do dirty reads or whether they
should have to wait for access to the database (or at least certain rows),
while your process is making updates.

There's a lot of variables to consider and, to be honest, I have no
experience in that - at least with .NET. I've only used transactions to
either commit or rollback groups of commands - making sure things stay in
sync and backing everything out if necessary.

I do suspect that, like transactions themselves, the isolation levels you
can use probably depend on the DBMS. ADO will likely support only what the
DBMS does.

Having said that, I see what you're getting at. To get back to your
original queston: Yes, you can (in fact you have to) use the same connection
if you want a series of transactions to be treated as a logical unit of work.

Hope that helps, Nathan
 
Back
Top