Transactions in ADO.Net 2 strongly-typed datsets.

[Guest]

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]]

Ditto on whatever Papa said.

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

 

[gwolinsky]

Gentlemen,

I would first like to thank you for your time and effort in helping me
and my team. After reading your latest comments today, we met and
decided on making some of your recommended changes to our approach.

Here's what we're doing:

We will still be using the strongly-typed datasets as a way to access
the data collections easily and to easily make insertions, updates and
deletes to the collection. However, when it's time to write the
dataset data to SQL, we will actually iterate through the rows and
(depending on row state) instantiate an our custom object, populate it
from the row data and then let the object add/update/delete item. This
limits object instantiation to only the items that were added or
changed in the dataset. This way, we also have very granular control
over the connection and transaction.

Also, one of the other requirements that wasn't in my previous posts
was that we will be keeping rather detailed change logs on most of our
data. Using a data adapter of any type made that requirement a bit
sticky. This new scenario makes that much easier.

Any thoughts?

Thanks,
Glen

 

[Sahil Malik [MVP]]

This looks good. Just one comment - you don't quite need to iterate through
changed rows. ADO.NET can do that for you.

You can do datatable.select ("","",DataRowState.Added) <-- To give you only
added rows as a datarow array.

Then this datarow array can be directly passed into a
DataAdapter.Update(DataRow[]) <--- Which has an InsertCommand populated to
handle the added rows.

This approach will make your code a lot smaller and readable.

How are you doing the change logs? One approach is to use audit triggers at
the db level - as long as the triggers aren't more than one level deep, and
donot fire off select or print commands inside of them, that just might be
the simple and easy approach. Triggers can get funny at times - so I asked
how exactly are you doing the logging part

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

 

[Guest]

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.

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/

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

Thanks,
Glen

 

[Guest]

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

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.

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/

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

Thanks,
Glen

 

[Sahil Malik [MVP]]

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
----------------------------------------------------------------------------
---------------

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.

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/

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

Thanks,
Glen

 

[Sahil Malik [MVP]]

I answered ur Q a moment back, so hopefully that takes care of it. The code
below seems okay, but looks incomplete (what is cmd?) - you will have to
write a truckload of code for this to work .. and if u ask me to review all
of it, I'm takin' a sick leave from unpaid work

Anyway, I do hope I have been able to convey the concept to you properly -
as long as that got through, your solution will be kickass !!

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

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

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.

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/




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

Thanks,
Glen

 

[Guest]

Thanks Sahil for taking the time to answer the queries. The code in the
earlier emails was a cut and paste of your initial code sample in this post
above and no i do not expect to get teh code reviewed .... because i dun
want you to go for a sick leave..

For 2,
Also i do undertsand what you mean.. as per the class design I have
separate methods for the master and transaction tables, in a data access
class. The data access class is derived from a base class which has the
create connection method which inturn calls the Enterprise data application
block DatabaseFactory.CreateDatabase();
and this is done for each method separately for master and transaction.. in
this way i can totally separate the data access layer from the Business layer
with no rules in the data layer.

If i have to modify the design to save the 2 master and transaction tables
in the same function then i will have to do a lot of modifications.....
alternativbely if i can have a way to just pass a parameter like the db
transaction.. then it will be just good.... if not i will be screwed making
all teh modifications..

I hope i was clear... Do you think if there is a way to achieve what i want
now.. with minimum modificaitons?

and.. Yes i have spearated the updates and the inserts by means of
different methods....

Thanks..
Hitesh

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
----------------------------------------------------------------------------
---------------

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.

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/




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

Thanks,
Glen

 

[Sahil Malik [MVP]]

You can pass only the DbTransaction - as long as you ensure that the
underlying connection is the same, and remains open. The connection
information is embedded within the transaction.

Does that help?


--

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

Thanks Sahil for taking the time to answer the queries. The code in the
earlier emails was a cut and paste of your initial code sample in this post
above and no i do not expect to get teh code reviewed .... because i dun
want you to go for a sick leave..

For 2,
Also i do undertsand what you mean.. as per the class design I have
separate methods for the master and transaction tables, in a data access
class. The data access class is derived from a base class which has the
create connection method which inturn calls the Enterprise data application
block DatabaseFactory.CreateDatabase();
and this is done for each method separately for master and transaction.. in
this way i can totally separate the data access layer from the Business layer
with no rules in the data layer.

If i have to modify the design to save the 2 master and transaction tables
in the same function then i will have to do a lot of modifications.....
alternativbely if i can have a way to just pass a parameter like the db
transaction.. then it will be just good.... if not i will be screwed making
all teh modifications..

I hope i was clear... Do you think if there is a way to achieve what i want
now.. with minimum modificaitons?

and.. Yes i have spearated the updates and the inserts by means of
different methods....

Thanks..
Hitesh

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
--------------------------------------------------------------------------

--

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

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.



:

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/




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

Thanks,
Glen

 

[Guest]

Thanks,
Yes helps.....

seems like unfortunately i have to transfer the business logic to the data
access layer to save the master and transaction tables in the same data
access layer function instead of the business layer function as i am
currently doing, unless i make sure that the connection pooling in enterprise
data app block gets the same connection for consecutive calls...

Thanks..

Regards,
Hitesh

You can pass only the DbTransaction - as long as you ensure that the
underlying connection is the same, and remains open. The connection
information is embedded within the transaction.

Does that help?


--

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

Thanks Sahil for taking the time to answer the queries. The code in the
earlier emails was a cut and paste of your initial code sample in this post
above and no i do not expect to get teh code reviewed .... because i dun
want you to go for a sick leave..

For 2,
Also i do undertsand what you mean.. as per the class design I have
separate methods for the master and transaction tables, in a data access
class. The data access class is derived from a base class which has the
create connection method which inturn calls the Enterprise data application
block DatabaseFactory.CreateDatabase();
and this is done for each method separately for master and transaction.. in
this way i can totally separate the data access layer from the Business layer
with no rules in the data layer.

If i have to modify the design to save the 2 master and transaction tables
in the same function then i will have to do a lot of modifications.....
alternativbely if i can have a way to just pass a parameter like the db
transaction.. then it will be just good.... if not i will be screwed making
all teh modifications..

I hope i was clear... Do you think if there is a way to achieve what i want
now.. with minimum modificaitons?

and.. Yes i have spearated the updates and the inserts by means of
different methods....

Thanks..
Hitesh

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
--------------------------------------------------------------------------

--

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


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.



:

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/




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

Thanks,
Glen

 

[Sahil Malik [MVP]]

unless i make sure that the connection pooling in enterprise

data app block gets the same connection for consecutive calls...

You cannot guarantee that.

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

Thanks,
Yes helps.....

seems like unfortunately i have to transfer the business logic to the data
access layer to save the master and transaction tables in the same data
access layer function instead of the business layer function as i am
currently doing, unless i make sure that the connection pooling in enterprise
data app block gets the same connection for consecutive calls...

Thanks..

Regards,
Hitesh

You can pass only the DbTransaction - as long as you ensure that the
underlying connection is the same, and remains open. The connection
information is embedded within the transaction.

Does that help?


--

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

--

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

Thanks Sahil for taking the time to answer the queries. The code in the
earlier emails was a cut and paste of your initial code sample in this post
above and no i do not expect to get teh code reviewed .... because

i
dun

want you to go for a sick leave..

For 2,
Also i do undertsand what you mean.. as per the class design I have
separate methods for the master and transaction tables, in a data access
class. The data access class is derived from a base class which has the
create connection method which inturn calls the Enterprise data application
block DatabaseFactory.CreateDatabase();
and this is done for each method separately for master and

transaction..
in

this way i can totally separate the data access layer from the

Business
layer

with no rules in the data layer.

If i have to modify the design to save the 2 master and transaction tables
in the same function then i will have to do a lot of modifications.....
alternativbely if i can have a way to just pass a parameter like the db
transaction.. then it will be just good.... if not i will be screwed making
all teh modifications..

I hope i was clear... Do you think if there is a way to achieve what i want
now.. with minimum modificaitons?

and.. Yes i have spearated the updates and the inserts by means of
different methods....

Thanks..
Hitesh


:

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

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

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


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.



:

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/




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

Thanks,
Glen