Transactions in ADO.Net 2 strongly-typed datsets.

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
Mon Apr 04 11:46:08 CDT 2005

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@millermartin.com> wrote in message
news:1112630101.283533.243380@l41g2000cwc.googlegroups.com...
> 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
>

gwolinsky
Mon Apr 04 12:52:53 CDT 2005

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? :o)

Thanks in advance,
Glen

Sahil
Mon Apr 04 13:31:10 CDT 2005

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@millermartin.com> wrote in message
news:1112637173.571963.16720@g14g2000cwa.googlegroups.com...
> 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? :o)
>
> Thanks in advance,
> Glen
>

gwolinsky
Mon Apr 04 15:46:55 CDT 2005

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

Thanks,
Glen

Sahil
Mon Apr 04 16:59:54 CDT 2005

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@millermartin.com> wrote in message
news:1112647615.725996.64160@o13g2000cwo.googlegroups.com...
> That's it. I'm sorry if wasn't very clear the first time. :-)
>
> Thanks,
> Glen
>

gwolinsky
Tue Apr 05 10:05:10 CDT 2005

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
Tue Apr 05 11:57:53 CDT 2005

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 :-P ).
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@millermartin.com> wrote in message
news:1112713510.694063.213870@o13g2000cwo.googlegroups.com...
> 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
>

gwolinsky
Tue Apr 05 13:03:20 CDT 2005

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
Tue Apr 05 13:45:32 CDT 2005 Deleted

gwolinsky
Tue Apr 05 14:28:35 CDT 2005

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
Tue Apr 05 15:23:48 CDT 2005

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@millermartin.com> wrote in message
news:1112729315.471639.27440@l41g2000cwc.googlegroups.com...
> 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
>

gwolinsky
Tue Apr 05 15:35:33 CDT 2005

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
Tue Apr 05 17:08:54 CDT 2005

> 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@millermartin.com> wrote in message
news:1112733333.789962.295250@z14g2000cwz.googlegroups.com...
> 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
>

gwolinsky
Wed Apr 06 07:47:41 CDT 2005

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
Wed Apr 06 08:10:53 CDT 2005

IUD methods of the DataAdapter. Not the DataSet/TableAdapter :)

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


<gwolinsky@millermartin.com> wrote in message
news:1112791661.415396.327180@g14g2000cwa.googlegroups.com...
> 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

gwolinsky
Wed Apr 06 08:55:18 CDT 2005

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
Wed Apr 06 11:56:57 CDT 2005

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@millermartin.com> wrote in message
news:1112795718.690607.50860@l41g2000cwc.googlegroups.com...
> 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
>

gwolinsky
Wed Apr 06 13:11:32 CDT 2005

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
Wed Apr 06 15:48:25 CDT 2005

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/




<gwolinsky@millermartin.com> wrote in message
news:1112811092.937152.170550@z14g2000cwz.googlegroups.com...
> 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
>

Papa
Thu Apr 07 14:09:02 CDT 2005

Sahil and I chatted about this a bit and we came up with a few options, most
he has discussed here with you already. My opinion is that you should either
use all business objects or all DataTable object to update your database. I
think Sahil agrees with me on this point as it makes it much cleaner. But if
you really want to use the TableAdapter, one option is to use a partial class
to create your own method to update the data and include it within a
transaction. By using a partial class in a separate file you can extend the
TableAdapter's generated code. <disclaimer>I am not a big fan of this method,
though. </disclaimer>

In the end I agree that you should just use a data adapter.

-- John Papa
http://codebetter.com/blogs/john.papa


"Sahil Malik [MVP]" wrote:

> 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/
>
>
>
>
> <gwolinsky@millermartin.com> wrote in message
> news:1112811092.937152.170550@z14g2000cwz.googlegroups.com...
> > 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
> >
>
>
>

NewBie
Wed Jun 08 09:37:09 CDT 2005

Hi Sahil,

your post is really good, however i have a few queries.....

1)In the below code block, does the ADO internals work in an inefficient
way.. i.e
first of all the inserted records will be inserted by calling the insert
stored procs(as per teh command object)......
and then for the same inserted rows which had a couple of fields updated an
update stored proc will be run? Is this not inefficient work by the ADO by
making an extra call to the db for the updates?


2)In the below code example, does the connection has to be the same for both
the master and transaction tables?
Can just the idbtransaction be used to be passed around?
This is because the way my classes and methods are defined, the connection
woulld be pooled and the updates for the master and transaction tables from
the UI may be in a separate state and the code is modular so that the
connection object obtained may differ at each time and transaction based
details are kept transparent from the data layer (besides the information
about the transaction through IDBtransaction).

I hope i was clear to explain my queries..

Appreciate your response on the same.

Thanks.



"Sahil Malik [MVP]" wrote:

> 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@millermartin.com> wrote in message
> news:1112647615.725996.64160@o13g2000cwo.googlegroups.com...
> > That's it. I'm sorry if wasn't very clear the first time. :-)
> >
> > Thanks,
> > Glen
> >
>
>
>

NewBie
Wed Jun 08 09:52:08 CDT 2005

PS:- by below code I mean the code snippet earlier posted by you which is
below....

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

"NewBie" wrote:

> Hi Sahil,
>
> your post is really good, however i have a few queries.....
>
> 1)In the below code block, does the ADO internals work in an inefficient
> way.. i.e
> first of all the inserted records will be inserted by calling the insert
> stored procs(as per teh command object)......
> and then for the same inserted rows which had a couple of fields updated an
> update stored proc will be run? Is this not inefficient work by the ADO by
> making an extra call to the db for the updates?
>
>
> 2)In the below code example, does the connection has to be the same for both
> the master and transaction tables?
> Can just the idbtransaction be used to be passed around?
> This is because the way my classes and methods are defined, the connection
> woulld be pooled and the updates for the master and transaction tables from
> the UI may be in a separate state and the code is modular so that the
> connection object obtained may differ at each time and transaction based
> details are kept transparent from the data layer (besides the information
> about the transaction through IDBtransaction).
>
> I hope i was clear to explain my queries..
>
> Appreciate your response on the same.
>
> Thanks.
>
>
>
> "Sahil Malik [MVP]" wrote:
>
> > 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@millermartin.com> wrote in message
> > news:1112647615.725996.64160@o13g2000cwo.googlegroups.com...
> > > That's it. I'm sorry if wasn't very clear the first time. :-)
> > >
> > > Thanks,
> > > Glen
> > >
> >
> >
> >

Sahil
Wed Jun 08 11:42:50 CDT 2005

Answer to #1 ---

Slightly. Yes :-) (or should I put this smiley :-( ? ;) ).

Well, it will be slightly worse than a batched update in .NET 2.0. In .NET
1.1 it'll be just as good/bad.

Answer to #2 ---

Yes it has to be the same transaction AND connection object. What you could
do however is that you could segregate the connection and transaction object
to only a logical grouping of rows - rather than the whole hierarchical
table structure. If your architecture permits - that frankly would be a
kickass choice in comparison. This way you would lock the fewest rows. BTW -
I would strongly recommend that you go in row states - row groups in one
transaction rather than all the table, all rows, all row states in one
transaction.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------


"NewBie" <NewBie@discussions.microsoft.com> wrote in message
news:48CCD65C-C6B0-475B-811D-DAE39EA312A4@microsoft.com...
> Hi Sahil,
>
> your post is really good, however i have a few queries.....
>
> 1)In the below code block, does the ADO internals work in an inefficient
> way.. i.e
> first of all the inserted records will be inserted by calling the insert
> stored procs(as per teh command object)......
> and then for the same inserted rows which had a couple of fields updated
an
> update stored proc will be run? Is this not inefficient work by the ADO by
> making an extra call to the db for the updates?
>
>
> 2)In the below code example, does the connection has to be the same for
both
> the master and transaction tables?
> Can just the idbtransaction be used to be passed around?
> This is because the way my classes and methods are defined, the connection
> woulld be pooled and the updates for the master and transaction tables
from
> the UI may be in a separate state and the code is modular so that the
> connection object obtained may differ at each time and transaction based

> details are kept transparent from the data layer (besides the information
> about the transaction through IDBtransaction).
>
> I hope i was clear to explain my queries..
>
> Appreciate your response on the same.
>
> Thanks.
>
>
>
> "Sahil Malik [MVP]" wrote:
>
> > 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@millermartin.com> wrote in message
> > news:1112647615.725996.64160@o13g2000cwo.googlegroups.com...
> > > That's it. I'm sorry if wasn't very clear the first time. :-)
> > >
> > > Thanks,
> > > Glen
> > >
> >
> >