Transactions with Strongly typed dataset

[Chuck Hartman]

I have used VS 2005 Beta2 to create a strongly typed DataSet. Using the
DataSet designer, I have created 3 table adapters to select and retrieve
rows from those tables, which works very well. Two of the tables have a one
to many relationship with the third table, and now I need to perform my
Insert, Update and Delete processing. I had used a transaction to
synchronize the updates with my hand coded ADO.NET 1.1 code, but can't
figure out how to do the equivalent with the 2.0 designer generated code. I
can't find any PUBLIC properties or methods that will let me use
transactions with the designer generated SqlCommand objects.

Is there a way to wrap a transaction around the IUD commands of two tables
in a strongly typed DataSet? If so, how do you do it? Thanks.

Chuck Hartman

 

[Kevin Yu [MSFT]]

Hi Chuck,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to put your table adapter
operations in a transaction. If there is any misunderstanding, please feel
free to let me know.

As far as I know, there is a public property named Transaction in
SqlCommand. Before execution, we can assign a reference to a SqlTransaction
object to it. In the designer, we don't have an option to assign that
value. So, if we need to set it to a designer generated SqlCommand objects,
we have to write code to achieve that.

SqlCommand1.Transaction = SqlTransaction1
//use table adapters to fill.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Chuck Hartman]

Kevin,

Yes, I would like to put the IUD operations of two different table adapters
(from a designer generated DataSet) in a transaction. My business logic is
usually working with one row in one table along with one to many rows in a
second table. I have done this successfully with ADO.NET 1.1 using a
manually coded DataSet, several SqlDataAdapters, several SqlCommand objects
(with corresponding stored procedures), and an explicit SqlTransaction using
(as you mentioned) the public Transaction property of the SqlCommand object.

Looking at the VS 2005 designer generated code for the TableAdapter's
Connection property as an example, on the surface it looks like the designer
could have also implemented a Transaction property using 90% similar code.
Alternatively, the user could do it manually in external code if the Adapter
property were made public (or even internal) instead of private.

If those changes are not possible, I'm looking for a relatively
straightforward way to accomplish this since I have to do this many times.
I was looking forward to cashing in on the developer productivity gains
using the strongly typed DataSet, but without being able to use
transactions, I will probably have to stick with the same approach that I
used with ADO.NET 1.1 and use non-designer based code.

You mentioned that we have to write code to achieve this result. I would be
interested to know the approach that you would suggest. Thanks.

Chuck Hartman

 

[Kevin Yu [MSFT]]

Hi Chuck,

What I meant in my last post, is to add the transaction to the SqlCommand
property in external code after the original code is generated by the
desinger. This seems to be the only to do that, since we can't set
transaction directly in the wizard.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Chuck Hartman]

Kevin,

For obvious reasons, I would prefer not to modify the designer generated
code itself, however, if that is the easiest approach, I would prefer to
keep the changes to an absolute bare minimum in case I need to redo the
changes after making subsequent changes via the designer (which I am sure I
will need to do over time). I have tried to change the designer generated
Adapter and the CommandCollection properties of the TableAdapter object to
public (and also internal) access. When the project is built, that property
is still not accessible to my external code.

Is it possible that I am not modifying the designer generated code properly
or the wrong copy of the generated source code? I am opening and modifying
the designer generated code by right clicking the TableAdapter class name in
my external code and selecting Go To Definition. After Saving, the changes
that I make do not seem to take effect as I get build errors. What is the
proper way to make changes to the designer generated code? Thanks.

Chuck Hartman

 

[Kevin Yu [MSFT]]

Hi Chuck,

My suggestion on making changes to designer generated code is to write
another method outside the generated code region to add additional setting
such as transactions to the generated TableAdapter objects. Within the
class, the object is visible to all methods. We can call our own method in
the Form.Load event handler. In this case, before the Fill method is
called, the transaction is added. Also, if you change the TableAdapter in
wizard, your own code will not be affected and it can be reused.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[zebadiah81]

Hi Chuck


I had a similar problem, and have found a solution.

I created a Dataset for my "Settings" table (using OleDB, but should work similar in SQL)

Then I create a new class called SettingsCRUD, which inherits from the SettingsDataSet, the code for which follows. Have a look at the SaveData method. (Sorry it's in VB, you'll have to play a bit....)

Notice also, at the bottom, I have added a partial class that extends the auto-generated SettingsTableAdapter, exposing the private Adapter property with a GetAdapter method, and adding a Transaction property, which sets the Transaction on the adapter's commands.

You should be able to copy and paste the code block into a new class, then do a Find/Replace on "Settings" -> "YourTableName"

Hope this helps


Ben

Public Class SettingsCRUD
	Inherits SettingsDataSet
#Region "Table Adapter Stuff"
	Private TableAdapter As New SettingsDataSetTableAdapters.SettingsTableAdapter
	Public Function Adapter() As System.Data.OleDb.OleDbDataAdapter
		Return TableAdapter.GetAdapter
	End Function
	Public Function TableName() As String
		Return "Settings"
	End Function
	Public Sub FillData()
		TableAdapter.Fill(Me.Settings)
	End Sub
	Public Function GetTable() As System.Data.DataTable
		Return Me.Settings
	End Function
#End Region
#Region "Update Data Stuff"
	Public Function SaveData() As Boolean
		Dim lReturnValue As Boolean = False
		Try
			If BeginTransaction() Then
				DeleteRows()
				AddRows()
				UpdateRows()
				If CommitTransaction() Then
					lReturnValue = True
				End If
			End If
		Catch e As Exception
			Try
				RollBackTransaction()
			Catch ex As OleDb.OleDbException
				Console.WriteLine("Exception" + ex.GetType().ToString() + " encountered while rolling back transaction.")
			End Try
			Console.WriteLine("Exception " + e.GetType().ToString() + " encountered while updating data.")
		End Try
		Return lReturnValue
	End Function
	Private Sub AddRows()
		Dim tmpAdditionsDataSet As DataSet = Me.GetChanges(DataRowState.Added)
		If tmpAdditionsDataSet IsNot Nothing Then
			TableAdapter.Update(tmpAdditionsDataSet)
			Me.Merge(tmpAdditionsDataSet)
		End If
	End Sub
	Private Sub DeleteRows()
		Dim tmpDeleteDataSet As DataSet = Me.GetChanges(DataRowState.Deleted)
		If tmpDeleteDataSet IsNot Nothing Then
			TableAdapter.Update(tmpDeleteDataSet)
			Me.Merge(tmpDeleteDataSet)
		End If
	End Sub
	Private Sub UpdateRows()
		Dim tmpUpdatesDataSet As DataSet = Me.GetChanges(DataRowState.Modified)
		If tmpUpdatesDataSet IsNot Nothing Then
			TableAdapter.Update(tmpUpdatesDataSet)
			Me.Merge(tmpUpdatesDataSet)
		End If
	End Sub
#End Region
#Region "Transaction Stuff"
	Private lTransaction As OleDb.OleDbTransaction
	Private lTransConnection As OleDb.OleDbConnection
	Public Function BeginTransaction() As Boolean
		If lTransaction IsNot Nothing Then
			Return False
		End If
		lTransConnection = TableAdapter.Connection
		lTransConnection.Open()
		lTransaction = lTransConnection.BeginTransaction()
		TableAdapter.Transaction = lTransaction
		Return True
	End Function
	Public Function CommitTransaction() As Boolean
		If lTransaction IsNot Nothing Then
			lTransaction.Commit()
			EndTransaction()
			Return True
		Else
			EndTransaction()
			Return False
		End If
	End Function
	Public Function RollBackTransaction() As Boolean
		If lTransaction IsNot Nothing Then
			lTransaction.Rollback()
			EndTransaction()
			Return True
		Else
			EndTransaction()
			Return False
		End If
	End Function
	Private Sub EndTransaction()
		lTransConnection.Close()
		TableAdapter.Transaction = Nothing
		lTransaction.Dispose()
		lTransaction = Nothing
	End Sub
#End Region
End Class
 
Namespace SettingsDataSetTableAdapters
	Partial Class SettingsTableAdapter
		Public Function GetAdapter() As OleDb.OleDbDataAdapter
			Return Me.Adapter
		End Function
		Public Property Transaction() As OleDb.OleDbTransaction
			Get
				Return Me.Adapter.UpdateCommand.Transaction
			End Get
			Set(ByVal value As OleDb.OleDbTransaction)
				Me.Adapter.UpdateCommand.Transaction = value
				Me.Adapter.DeleteCommand.Transaction = value
				Me.Adapter.InsertCommand.Transaction = value
			End Set
		End Property
	End Class
End Namespace