Transactions in ADO.Net 2 strongly-typed datsets.

[gwolinsky]

Here's the scenario: I have a custom object that represents a group in
our law firm. For each group, there are many members of that group.

Now, the group object exposes a property that is a strongly-typed
datatable of the member records. The UI programmer will add/delete
records from this datatable as the user adjusts the members of the
group.

Then, the Update method of the group object will not only update it's
normal properties (name, description, etc.) but will call the update
method of the adapter for the datatable to write the changes to the
members.

My question as a ADO.Net 2 newbie is this: How to I wrap the group
object changes and the changes written by the update in the datatable
in the same transaction?

I knew how to do this in ADO.Net 1.x, but the properties exposing the
insert, update, delete command objects in the datatable are not there.

What am I missing?

Any help would be GREATLY appreciated.

Sincerely,
Glen Wolinksy

 

[Sahil Malik [MVP]]

Glenn,

I recently finished the chapters concerning this portion of my upcoming
ADO.NET 2.0 book, so let me try and answer your question.

ADO.NET 2.0 has a new thing called TableAdapters and that is fairly easy to
understand, just experiment/play with creating a strongly typed dataset -
add a query, table adapter - you will figure it out. I personally don't like
that for one reason - it does not offer me the level of concurrency control
I need. It is vastly improved than 1.1 though, but still in an enterprise
app - it does not allow me to fix and twist the design to my liking . It
still emphasizes on running it's own queries and completely ignoring my data
layer - so its not perfect - but it's better than what it used to be.

Let me address the more enterprise level - get your hands dirty - have full
control method though - which is what I'd prefer to use, simply because it
allows me to use design patterns and data layers etc.

You have more than one tables, with a relation between them right? You need
to filter out Updated rows, (Modified), Added and deleted rows one by one
and use a data adapter to modify ONLY those rows.

Now the order of execution is important, so if there is a one to many
relationship between Group and Groupmembers, you need to first execute
Inserts on Group, then Updates on Groups, followed by Inserts on
GroupMembers, Updates on Group members, and wrap these up by calling Deletes
on Groupmembers, and Delete on Group Members.

Thus you need to
a) Create dataadapters for particular tables, and specify the
updatecommand./deletecommand/insertcommand on them.
b) Execute the Datadapter.Update(DataRow[]) overload, in which you pass in
rows in a particular rowstate - which can be filtered using the
datatable.select method.

You can make the whole thing transactional by doing a

connection.open
connection.begintransaction
... specify the transaction property to all the IUD commands.
tran.commit
conn.close

Multiple table hierarchical "save" (insert/update/delete) is not trivial,
but not impossible either. Previous data access architectures were even
kludgier in comparison - atleast it works now

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

Sahil,

Thank you for your detailed reply. However, I think there may be a
slight misunderstanding as to what we've done so far. The Group object
is NOT in a dataset with a relation to the Group members. They ARE
related in SQL, but not in a dataset. The Group object loads its data
and sets its properties. One of the properties is a single,
strongly-typed datatable of the Group Member data.

When we do an add, the Group object will add the Group and then call
the update to the dataset of members. The same would be true of
update. For delete, our db cascading will take care of deleting all
members if a group is deleted.

During these procedures, I want to wrap the object's add/update/delete
steps in a single transaction. One of these steps is the .UPDATE call
to the dataset. Is this possible? Am I being clear or just confusing
the issue more? )

Thanks in advance,
Glen

 

[Sahil Malik [MVP]]

Okay so if I understood you right,

You have an object "Group". It has a property called Members - which is a
datatable.
In one transactional save process, you need to save any changes to the
datatable AND any updates made to Group (name, description etc.)?

Did I understand you right this time? .. if I did please lemme know and
I'll answer your question.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

That's it. I'm sorry if wasn't very clear the first time.

Thanks,
Glen

 

[Sahil Malik [MVP]]

Okay now that the problem is in my head.

The approach really doesn't change much even in this case, but it does
depend on your final database structure.

So if Group were to be saved in a table, and groupdetails in another, you'd
have to call ExecuteNonQuery instead of DataAdapter.Update for Group, and
Datadapter.Update(DataRow[]) for the GroupDetails table.

The basic concept remains the same.
Group Insert
Group Update

GroupDetail Insert
GroupDetail Update

... Just remember, contrary to MSDN docs, DataAdapter.Update(dataSet) doesn't
really call the commands on EACH row in the dataset. Only in the first
datatable, or the datatable identified by the name "Table".

To wrap it all up in a transaction,

Do a

connection.open
tran = conn.BeginTransaction
cmd.transaction = tran
cmd.executenonquery
cmd2.transaction = tran
datadapter.insertcommand = cmd2
datarow[] insertedrows =
groupdetailsdatatable.select("","",DataRowState.Added)
dataadapter.Update(insertedrows);
/// other such commands
tran.commit
conn.close

I wish I could hand you a copy of my next book with a working example of
this in Chapter #10, but that doesn't come till September. I know it isn't
quite as straightforward as you'd like it to, but you do have to go through
all the hoops.

Did I mention one more detail - If a new Group is inserted, then you willl
have to retreive the right key values to put in GroupDetails. Now inserting
a Group can be into one table, or sixty tables - any SQL will do, but if
there is a foreign key association, you will definitely need to do that.

The EXACT answer depends on your DB structure. But the concepts are still
the same.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

We're getting closer. I have used transactions before and I understand
your last post. However, where I'm stuck is where you set the
dataadapter's insert command to a command object with a transaction
attached. I am using a strongly-typed dataset and therefore using the
associated table adapter (ADO.Net 2). That adapter has NO properties
(that I can find) that allow me access to the IUD commands or the
dataset connection to associate a transaction.

What about the new System.Transactions namespace? Does my answer lie
there? If so, how do I use it. I read an article about it but I'm not
quite clear on how to apply it. The article is here -->
http://msdn.microsoft.com/msdnmag/issues/05/02/datapoints/

The examples almost make it seem like you just instantiate your
transaction and any actions that happen on your connections is
contained in that transaction until you commit/reject your changes. Of
course that's WAY too easy to right! ;o)

Thanks,
Glen

 

[Sahil Malik [MVP]]

Glen,

System.Transactions really IS that easy . It is not easy - it is fudging
AWESOME. I mean the ADO.NET team has really scored a homerun there.

BUT

Distributed transactions are expensive. Let me reword - they are HELLA
expensive. And System.Transactions is better applied to that. If you wanna
learn more about System.Transactions versus IDBTransaction check this out -
http://www.codebetter.com/blogs/sahil.malik/archive/2004/12/09/35816.aspx
(worth bookmaring IMO, but I am not plugging my blog here .. hehe ).
Everything has a use, and in a transactional scenario like this (single
database) - you should For a single database transaction - stick with
regular IDBTransaction. If indeed you have distributed transaction (if I
mis-unders-stood) you again, then ping me back and I'll answer it from that
angle .

Now, DataAdapter does have 4 properties -
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand.



BTW, again don't view this as a plug, but you will LOVE Chapter 9,10,11 of
my upcoming book.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

Sahil,

I am looking forward to your book. Any way you can sneak me a chapter
before September? ;o)

I read through the blog items (and followed a few of the links to other
articles) that you provided. Good information although there's a bit
of it I don't really understand yet. While the DataAdapter does have
the SIUD commands as properties, the strongly-typed "myTableAdapter1"
object does not.

Can I email you a snippet of what we're doing so you can have a look?
Just email me your address if that's OK.

Thanks,
Glen

 

[Sahil Malik [MVP]]

Dude, I've been working harder than a rabbit overdozed on viagra, and been
busier than Michael Jackson at a daycare.

Do send me the code, I love looking at code, but this newsgroup is the
bestest place for info (If I snooze, a lot of others here will pick the
baton) - I'll still try and look at it, but .. uhhhh .. I need to get some
sleep . See, it's not because I'm too busy to help - that'll never never
happen, but the collective brain power here exceeds mine (of course it
does). But if you want do send me the code.

I'd love to slip you a chapter if you promise to post a review somewhere
;-). I have a lot of pending requests for Beta readers, and plus it's not
only my decision (publishers own the content technically I think). But I'll
try my best.

So .. contact me thru my blog, I will do my best .. how 'bout dat !!??
(PS: Contact me thru my blog, and I'll reply with my email addy .. to avoid
spam .. how 'bout dat? )

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

Down here in the South, we say you're "workin' faster than a jack
rabbit on a date"!

I tried running the simple example but got an error when my dataset
tried to do its update. The error message was "Exception from HRESULT
0x8004D024 The transaction manager has disabled its support for
remote/network transactions." The code snippet is pasted below.

Thanks,
Glen

------------------------------------------------------

Here's some notes on the code:

1. This is part of a custom class that represents a Group in our law
firm.

2. You are looking at the Update method that
a. Updates the Group table
b. IUD's the members of the group in what we call our
"GroupAssociations" table.

3. The dataset is a strongly-typed ds based on the GroupAssoc table.
The select statements resolve some names, but the IUD methods just hit
the data fields in the table.

--------------------------------------------------------------
Code Below
--------------------------------------------------------------

#Region " Update "
''' <summary>
''' Update current Practice Group
''' </summary>
''' <remarks></remarks>
Public Overrides Sub Update()
'! Make sure that the appropriate values are available
If Me._groupID <= 0 Then Throw New
ApplicationException("Practice Group has not yet been added.")
If Me._deptID <= 0 Then Throw New
ApplicationException("Department Type ID is invalid or not specified.")
If Me._desc = String.Empty Then Throw New
ApplicationException("Practice Group Description not specified.")
If MyBase.EditedBy = Integer.MinValue Then Throw New
ApplicationException("Edited By value not specified.")

Dim tranScope As New Transactions.TransactionScope

Dim oConn As New
SqlClient.SqlConnection(GusGlobals.SqlConnectString)
Dim oCmd As New SqlClient.SqlCommand("spempGroupType_Update",
oConn)
oConn.Open()

Try
With oCmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@GroupTypeID", SqlDbType.Int).Value =
NullIfMinValue(Me._groupID)
.Parameters.Add("@DeptTypeID", SqlDbType.Int).Value =
NullIfMinValue(Me._deptID)
.Parameters.Add("@Description",
SqlDbType.VarChar).Value = Me._desc
.Parameters.Add("@CreatedBy", SqlDbType.Int).Value =
NullIfMinValue(MyBase.CreatedBy)
.Parameters.Add("@CreateDate",
SqlDbType.DateTime).Value = NullIfMinValue(MyBase.CreateDate)
.Parameters.Add("@EditedBy", SqlDbType.Int).Value =
NullIfMinValue(MyBase.EditedBy)
.Parameters.Add("@EditDate", SqlDbType.DateTime).Value
= NullIfMinValue(MyBase.EditDate)
.Parameters.Add("@ErrorCode", SqlDbType.Int).Direction
= ParameterDirection.Output
.ExecuteNonQuery()
End With

'Update members
'Throw New ApplicationException("Test error thrown")
CommitMembersChanges()

tranScope.Complete()

Catch ex As Exception
Throw ex
Finally
tranScope.Dispose()
End Try


End Sub
#End Region



#Region " Commit Members Changes "
Private Function CommitMembersChanges() As Boolean

Dim adGroup As New
GroupAssociationsDSTableAdapters.empGroupAssocTableAdapter

If _members IsNot Nothing Then
Try
adGroup.Update(_members)
Catch ex As Exception
Throw ex
End Try
End If


End Function
#End Region

 

[Sahil Malik [MVP]]

Don't do distributed transactions for one database man .. don't do it.

The below error can be remedied by the following steps -

Component Services -> Computers -> My Computer
Right-click properties
go to MSDTC tab
click on "Security Configuration"
enable checkbox for Network DTC Access

WOOHOO !!

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

Forgive me if I seem a bit dense about this, but I'm in high weeds here
trying find the ball. So, would the best way for me to do this is just
use the System.Transactions namespace to create an explicit transaction
and then pass it around?

I know you're busy, but would it be possible for you to give me an
example in the code I posted? I really appreciate your time and help.

Thanks,
Glen

 

[Sahil Malik [MVP]]

Forgive me if I seem a bit dense about this, but I'm in high weeds here

trying find the ball. So, would the best way for me to do this is just
use the System.Transactions namespace to create an explicit transaction
and then pass it around?

NO

What I meant was, use a SqlTransaction.

Here's a code sample.

SqlConnection conn = new SqlConnection(connString) ;
conn.Open ;
SqlTransaction myTran = conn.BeginTransaction() ;
SqlCommand cmd = conn.CreateCommand() ;
cmd.Transaction = myTran ;
// Now do whatever you want with the cmd.
myTran.Commit() ; // or rollback - use a try catch preferably
conn.Close();

Hope that helps

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

Now I think we've come full circle. I am very familiar with the
scenario you laid out. However, we come back to my problem of not
knowing how to assign the transaction object to the strongly-typed
dataset in my example. In one of your previous posts, you instructed
me to assign the transaction to the IUD methods of the dataset. I
can't figure out how to do this on this strongly-typed dataset.

I understand how to create a transaction, assign it to the first block
of code that updates the Group table. I can't figure out how to assign
the transaction to the IUD methods of the ADO.Net 2 dataTableAdapter or
the dataset. Intellisense doesn't reveal anything that I can discern
as the correct properties to set. This is where I need the help.

How do I assign the sqlTransaction to the IUD methods of this object?

Thanks for you patience,
Glen

 

[Sahil Malik [MVP]]

IUD methods of the DataAdapter. Not the DataSet/TableAdapter

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

Are you saying that there is no way to set the TABLEadapter to use the
transaction? There is no way to include a strongly-typed dataset in a
transaction?

I guess what's REALLY confusing me about your previous advice to "Don't
do distributed transactions for one database man .. don't do it." is
that John Papa's article on ADO.Net and System.Transactions gave a
similar example. While I know my example goes to the same database,
there are two differenct connections (aren't there?). One connection
is the one I explicitly define for updating the Group object and the
other is the one that the st-dataset uses in it's definition. John
just created a transaction scope and placed both his db calls inside of
it.

Why is doing the similar thing on one database such a bad thing?

(I told you I was a beginner at this stuff. I'm really NOT trying to
be difficult.)

Thanks,
Glen

 

[Sahil Malik [MVP]]

You don't need two connections. You can update two tables using two seperate
command objects on the same connection - so you don't and shouldn't use
distributed transactions for this scenario. You just can't do it
concurrently (ok that's a whole another discussion and lets not muddy the
water with that) - but you can execute two commands - one after another - in
the same transaction on the same connection.

John Papa is a good friend of mine, I can flag this post for him if you want
me to, he may come and answer it personally if you want. Should I?

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[gwolinsky]

What I don't know is how to wire the first connection the
strongly-typed dataset so that the dataset uses it instead of what is
defined inside of it. I follow all of your suggestions and understand
them perfectly as this is what we've done in the past. I just don't
know how to tell the dataset to use MY connection instead of its own.
If you have an example, I'd love to see it.

Go ahead and flag this for John Papa, that's fine.

Thank you,
Glen

 

[Sahil Malik [MVP]]

I think you are gonna have to get over using the TableAdapter for this.
Write your own command(s) with a DataAdapter. You could edit the generated
code - but that makes me nervous - it never quite worked damn well for 1.1,
and tableadapter auto generated code is too new to base an architecture on.

Anyway, long story short - use a data adapter.

I've flagged this for JP, but he's a busy guy.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/