Strongly typed dataset and transactions

[James Minns]

Hi all,
I am using VB.NET (2005) to write to an Access database, with oledb.
I have created strongly typed datasets in an xsd file to read from and
update several tables in the database.

I would like to use a transaction so that in case the update of one table
fails, I can roll back the updates made to another table, and here lies the
problem.

I can't see how to access the insert/update/delete command for the strongly
typed dataset so I can set the transaction property - The problem does not
arise when using a standard TableAdapter!

Any ideas?

Thanks, James

 

[Cowboy \(Gregory A. Beamer\)]

The DataSet, itself, whether typed or not, is a container. The Adapter
(DataAdapter, TableAdapter, etc.) is the bridge from the container to your
persistent source (it can actually get more complex than this, but that is a
good start).

The easiest system I have seen is to set up your DataSets with an ordering
attribute and then fire back so each sproc is fired in order to guarantee
parent records are inserted prior to child records. You can wrap all of the
adapters in a transaction and roll back.

Another option is XML input into a stored procedure, but I am not sure this
is the best option with Access, as I am not well versed enough to know what
XML it currently supports.

The third option is roll your own data layer. With SQL Server and other full
RDBMS products, this is easier than Access, as well.

Hope this gives you some useful hints.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Miha Markic [MVP C#]]

You should use TransactionScope thing, like:
using (scope As New TransactionScope())
' do the job here
scope.Complete();
end using

 

[James Minns]

Tried that, (vb, though, not c#), but it not allowed with Jet databases:

Exception generated:
The ITransactionLocal interface is not supported by the
'Microsoft.Jet.OLEDB.4.0' provider. Local transactions are unavailable with
the current provider.

James

You should use TransactionScope thing, like:
using (scope As New TransactionScope())
' do the job here
scope.Complete();
end using
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi all,
I am using VB.NET (2005) to write to an Access database, with oledb.
I have created strongly typed datasets in an xsd file to read from and
update several tables in the database.

I would like to use a transaction so that in case the update of one table
fails, I can roll back the updates made to another table, and here lies
the problem.

I can't see how to access the insert/update/delete command for the
strongly typed dataset so I can set the transaction property - The
problem does not arise when using a standard TableAdapter!

Any ideas?

Thanks, James

 

[James Minns]

The problem is that the automatically generated TableAdapter from the xsd
file, whilst derived from a standard TableAdapter, does not give you access
to the InsertCommand, so you cannot set its transaction!

I'm clear on parent/child records, and I know that SQL server has countless
advantages but I'm stuck with a Jet database for this project.
I can certainly use transactions with a normal TableAdapter, it just seems
stange that MS automatically generates these custom TableAdapters, then
makes is very difficult to use transactions, essential for all but the most
trivial databases.


James

 

[Miha Markic [MVP C#]]

Then you have to avoid tableadapters...

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Tried that, (vb, though, not c#), but it not allowed with Jet databases:

Exception generated:
The ITransactionLocal interface is not supported by the
'Microsoft.Jet.OLEDB.4.0' provider. Local transactions are unavailable
with the current provider.

James

You should use TransactionScope thing, like:
using (scope As New TransactionScope())
' do the job here
scope.Complete();
end using
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi all,
I am using VB.NET (2005) to write to an Access database, with oledb.
I have created strongly typed datasets in an xsd file to read from and
update several tables in the database.

I would like to use a transaction so that in case the update of one
table fails, I can roll back the updates made to another table, and here
lies the problem.

I can't see how to access the insert/update/delete command for the
strongly typed dataset so I can set the transaction property - The
problem does not arise when using a standard TableAdapter!

Any ideas?

Thanks, James

 

[James Minns]

Hi all,
I am using VB.NET (2005) to write to an Access database, with oledb.
I have created strongly typed datasets in an xsd file to read from and
update several tables in the database.

I would like to use a transaction so that in case the update of one table
fails, I can roll back the updates made to another table, and here lies
the problem.

I can't see how to access the insert/update/delete command for the
strongly typed dataset so I can set the transaction property - The problem
does not arise when using a standard TableAdapter!

The problem of accessing the Transaction member of the typed data sets was
addressed in this blog, and has solved the problem
http://www.madprops.org/cs/blogs/mabster/archive/2006/05/16/4884.aspx

Thanks to Cowboy (Gregory A. Beamer) and Miha Markic for your suggestions!

James

 

[Miha Markic [MVP C#]]

Nice solution, which requires certain amount of work unfortunatelly.
If only MS' code genertors would be flexible...

 

[Cowboy \(Gregory A. Beamer\)]

A way around the auto-gen "nightmare".

Thanks for responding with the info.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Cowboy \(Gregory A. Beamer\)]

MS normally uses the 80-20 rule or some variation. The auto-gen stuff fits a
good deal of scenarios, completely eliminating the need to code in many
instances.

The problem is the patterns used to auto-gen often lead to complex solutions
when one steps outside of the pattern. In most cases, I find that "extend
the class" does not offer much help in reducing this complexity. But, they
do help the masses quickly get an app up and running, so I can't be
completely down on them.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

Nice solution, which requires certain amount of work unfortunatelly.
If only MS' code genertors would be flexible...

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

The problem of accessing the Transaction member of the typed data sets
was addressed in this blog, and has solved the problem
http://www.madprops.org/cs/blogs/mabster/archive/2006/05/16/4884.aspx

Thanks to Cowboy (Gregory A. Beamer) and Miha Markic for your
suggestions!

James

 

[Cor Ligthert [MVP]]

Miha,

The strongly generated datasets are flexible in version 2.0 you can add
everything you want to a datasource (dataset) (as long as that is conform
the rules), than they will be new generated while this does not influence
your earlier code (assuming of course that there are no database changes in
the meanwhile by instance field names that change, but even than it is a
good tool).

Just my thought,

Cor

Nice solution, which requires certain amount of work unfortunatelly.
If only MS' code genertors would be flexible...

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

The problem of accessing the Transaction member of the typed data sets
was addressed in this blog, and has solved the problem
http://www.madprops.org/cs/blogs/mabster/archive/2006/05/16/4884.aspx

Thanks to Cowboy (Gregory A. Beamer) and Miha Markic for your
suggestions!

James

 

[Miha Markic [MVP C#]]

Strong typed dataset generator is rigid as titanium or even more.

 

[Miha Markic [MVP C#]]

MS normally uses the 80-20 rule or some variation. The auto-gen stuff fits
a good deal of scenarios, completely eliminating the need to code in many
instances.

The problem is the patterns used to auto-gen often lead to complex
solutions when one steps outside of the pattern. In most cases, I find
that "extend the class" does not offer much help in reducing this
complexity. But, they do help the masses quickly get an app up and
running, so I can't be completely down on them.

That's true, I agree. I would say that they are fine for smaller or pilot
projects. When your projects grow or you want some special feature you
quickly run into either maintenance problems or a titanium wall.
A (modifiable) template based approach would solve a great deal of code
generation...

 

[Sarit Kommineni]

Hi,

Here is a solution to your problem.
The dataset generator in VS 2005 generates all classes as partial
classes.

By default the TableAdapter does not expose the adapter object it uses
internally. However, you can get access to it by creating your own
partial class for the tableadapter and adding a public accesible Adapter
property which maps to TableAdapter.Adapter.

You can then set all properties of the adapter that supports your table
adapter.

I hope this helps.