pass tableadapter as parameter to module

[jedimaster100]

Hi guys! There's something that's been bugging me for a while. I have an application which contains many windows forms and each have one or more connections to the database.

All connections have been made using the Data Source Configuration Wizard which ends up adding three important data objects to a form namely Dataset1, CustomersBindingSource and CustomersTableAdapter (if the source table is Customers). Now in each form I have an update event which goes like this:
--------------------------------------------------------------------------------------
Private Sub updateChanges()
'Try to save changes
Dim changedRecordsTable As System.Data.DataTable = Me.DataSet1.Customers.GetChanges()
Dim changedRecords As Boolean = Not (changedRecordsTable Is Nothing) AndAlso (changedRecordsTable.Rows.Count > 0)
Dim message As String
'Change message if there are changes
If changedRecords = True Then
message = String.Format("You are about to update {0} record(s)." + vbCrLf + "Do you want to continue?", changedRecordsTable.Rows.Count)
Else
'No changes
message = String.Format("Do you want to continue?")
End If
Dim messageReply As System.Windows.Forms.DialogResult = MessageBox.Show(message, Me.Text & " Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
'If reply is yes save changes
If messageReply = Windows.Forms.DialogResult.Yes Then
Try Me.CustomersTableAdapter.Update(Me.DataSet1.Customers)
Catch exc As Exception
End Try
End If
End Sub
--------------------------------------------------------------------------------------

Now this works well however since all my forms have that update subroutine, I was thinking of passing all data objects to a module and all my forms would be calling the module with appropriate parameters only. But I cannot seem to pass CustomersTableAdapter as a parameter. Is there another way to do this?

 

[jmcilhinney]

You can't do what you're asking for because TableAdapters don't have a common base type other than Object. All the Fill and Update methods are generated for that specific TableAdapter class and are not inherited from anywhere.

You can still do all the rest of that in the one place though. You can do everything but the Update in a common function and then have that function return a Boolean that indicates whether the Update should be done. You then only need the final Try block in each individual form.

 

[jedimaster100]

Ah. Ok then I will have to do it the way you say.

 

[jmcilhinney]

By the way, why use String concatenation when you're already using String.Format?

message = String.Format("You are about to update {0} record(s).{1}Do you want to continue?", changedRecordsTable.Rows.Count, ControlChars.NewLine)

 

[jedimaster100]

huh???!?!? please clarify. What would you advise?

 

[jmcilhinney]

huh???!?!? please clarify. What would you advise?

Um, I just posted what I advise. Compare my code to yours. It's about as clear as it's going to get.

 

[jedimaster100]

Er, sorry. Did not read properly. Neat! Thanks again.

 

[cjard]

You can use reflection to call the Update method if you really want to, but there's not much point because the time and coding effort you'll go to to do it will be in excess of the code to just update on each form..

 

[jedimaster100]

Somebody passed me this:
You'll need to pass both the DataAdapter and the DataTable....

Private Sub updateChanges(ByVal da As System.Data.Common.DataAdapter, ByVal dt As DataTable)
'Try to save changes
Dim changedRecords As Boolean = Not (dt Is Nothing) AndAlso (dt.Rows.Count > 0)
Dim message As String
'Change message if there are changes
If changedRecords = True Then
message = String.Format("You are about to update {0} record(s)." + vbCrLf + "Do you want to continue?", dt.Rows.Count)
Else
'No changes
message = String.Format("Do you want to continue?")
End If
Dim messageReply As System.Windows.Forms.DialogResult = MessageBox.Show(message, Me.Text & " Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
'If reply is yes save changes
If messageReply = Windows.Forms.DialogResult.Yes Then
Try
da.Update(dt.DataSet)
Catch exc As Exception
End Try
End If
End Sub


However I can't seem to pass Me.CustomersTableAdapter as a parameter.

 

[jmcilhinney]

I can't seem to pass Me.CustomersTableAdapter as a parameter.

Of course you can't. As I said, TableAdapters do NOT inherit a common base class so you can only pass any TableAdapter to a parameter declared as it's specific type or as Object. TableAdapters certainly do NOT inherit the DataAdapter class. All TableAdapters are generated on demand and built from scratch. Internally they create a DataAdapter and the corresponding connection object.

 

[cjard]

Somebody passed me this:
You'll need to pass both the DataAdapter and the DataTable....

Unfortunately they don't seem to have read your question properly, or they are not aware of the difference between a tableadapter and a dataadapter.

As jmc points out, TableAdapters have NO USEFUL BASE CLASS. Actually, you can even give a tableadapter a base class by altering the BaseClass property (I do this in some situations), to a class you created with an generic Overridable Function Update(dt as DataTable) but it will not help, because even if you do this, you will hit another stumbling block in that the methods that the designer makes for you such as Update, are NOT MARKED Overrides, so you'll make your base class with your common generic Update function, slip it into the hierarchy, and use it like:

Dim x as GenericTABase = New SpecificTableAdapter
x.Update(specificDataTable)

And the only thing that will be called is your base Update, not the specific one the designer made. To get that to work you have to write:

DirectCast(x, SPecificTableAdapter).Update(specificDataTable)


i.e. you have to make a design-time coding edict as to the type which ultimately doesnt advance you any

I did also consider delegation but you cannot attach a generic delegate Update(DataTable) to a specific method Update(SpecificDataTable); there is no guarantee that you will Invoke your delegate with compatible datatable type and that breaks the notion of .NET being type safe

The amount of time you till pour into building this functionality into a tableadapter isn't worth it and I'm not even convinced it's the right place for it. I think you should be declaring a MustInherit form with a Sub ConfirmSave a MustOverride Sub Save, and MustOverride Sub SetFormDataSet or SetFormDataTable. SetFormDataSet[/Table] assigns a value to the parent form's field _thisFormDataset[/table], and then that dataset/table/tables array is what you fill and use on your form.
The parent form will then provide generic save functionality such as ConfirmSave which will check to see if _thisFormDataset[tables etc].HasChanges and if so how many changes, prompt you to save. If user says cancel, exit the sub. If user says proceed, then call the MustOverride Save()

Thus you have a generic reminder to save your changes/count the changes and confirm across all your forms, and each form will override/implement Save() so it can do its OWN saving logic which may include multiple tableadapter usages. The compiler will ensure you provide a Save for every form. This is far more adaptable than trying to handle many different tableadapters in one place, through one generic interface because you will very rapidly find that not every form you use has just one tableadapter, and the order of saving is important. Your limited single-table, single-adapter code you posted is not flexible enough to cover your future needs

 

[jedimaster100]

How-To pass tableadapter as parameter to module

Ah. Very clear indeed. I'll set around optimizing the code for up-scaling adaptability. Thanks a lot for your answer. It really helped me out.

 

[cjard]

If it's of any interest, I crafted this from some ideas I found lying around the web:

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Reflection

Public Enum RestingConnectionState
  LeaveOpen
  CloseIfOpenedImplicitly
  CloseAlways
End Enum

Public Class TableAdapterTransactionManager

  'Credit to Ryan Whitaker's Blog at weblogs.asp.net/.../441529.aspx

  'Converted to VB by Martin Worger 5th July 2007

  'Incorporates Roberto F's suggestion

  'cjard made this class a bit more sensible; 
  'mature variable names, better generic behaviour, more comprehensive transaction management

  Private _prevConnState As ConnectionState
  Private _restingConnState As RestingConnectionState

  Private _isoLevel As IsolationLevel = Data.IsolationLevel.ReadCommitted
  Public Property IsolationLevel() As IsolationLevel
    Get
      Return _isoLevel
    End Get
    Set(ByVal value As IsolationLevel)
      _isoLevel = value
    End Set
  End Property

  Private _conn As DbConnection
  Public Property Connection() As DbConnection
    Get
      Return _conn
    End Get
    Set(ByVal value As DbConnection)
      _conn = value
    End Set
  End Property
  Private _trans As DbTransaction
  Public Property Transaction() As DbTransaction
    Get
      Return _trans
    End Get
    Set(ByVal value As DbTransaction)
      _trans = value
    End Set
  End Property

  Public Sub New()
    Me.New(RestingConnectionState.CloseIfOpenedImplicitly, IsolationLevel.ReadCommitted, Nothing)
  End Sub
  Public Sub New(ByVal isoLevel As IsolationLevel)
    Me.New(RestingConnectionState.CloseIfOpenedImplicitly, isoLevel, Nothing)
  End Sub
  Public Sub New(ByVal restingConnState As RestingConnectionState)
    Me.New(restingConnState, IsolationLevel.ReadCommitted, Nothing)
  End Sub
  Public Sub New(ByVal ParamArray tableAdapters As Object())
    Me.New(RestingConnectionState.CloseIfOpenedImplicitly, IsolationLevel.ReadCommitted, tableAdapters)
  End Sub
  Public Sub New(ByVal isoLevel As IsolationLevel, ByVal restingConnState As RestingConnectionState)
    Me.New(restingConnState, isoLevel, Nothing)
  End Sub
  Public Sub New(ByVal restingConnState As RestingConnectionState, ByVal ParamArray tableAdapters As Object())
    Me.New(restingConnState, IsolationLevel.ReadCommitted, tableAdapters)
  End Sub
  Public Sub New(ByVal isoLevel As IsolationLevel, ByVal ParamArray tableAdapters As Object())
    Me.New(RestingConnectionState.CloseIfOpenedImplicitly, isoLevel, tableAdapters)
  End Sub
  Public Sub New(ByVal restingConnState As RestingConnectionState, ByVal isoLevel As IsolationLevel, ByVal ParamArray tableAdapters As Object())
    _isoLevel = isoLevel
    _restingConnState = restingConnState


    If tableAdapters IsNot Nothing Then
      For Each o As Object In tableAdapters
        EnrollTableAdapter(o)
      Next
    End If
  End Sub


  Public Sub EnrollTableAdapter(ByVal tableAdapter As Object)
    Dim taType As Type = tableAdapter.GetType()

    If _trans Is Nothing Then
      If _conn Is Nothing Then
        Dim conPropInf As PropertyInfo = taType.GetProperty("Connection", BindingFlags.NonPublic Or BindingFlags.Instance)
        _conn = DirectCast(conPropInf.GetValue(tableAdapter, Nothing), DbConnection)
      End If

      _prevConnState = _conn.State

      If _conn.State = ConnectionState.Closed Then
        _conn.Open()
      End If

      _trans = _conn.BeginTransaction(IsolationLevel)
    End If


    'Original Ryan method - this sets the transaction in all the commands
    Dim cmdColPropInf As PropertyInfo = taType.GetProperty("CommandCollection", BindingFlags.NonPublic Or BindingFlags.Instance)
    Dim cmds() As DbCommand = DirectCast(cmdColPropInf.GetValue(tableAdapter, Nothing), DbCommand())

    For Each cmd As DbCommand In cmds
      If cmd.CommandText IsNot Nothing AndAlso cmd.CommandText <> String.Empty Then
        cmd.Transaction = _trans
      End If
    Next cmd

    'Roberto F's alternative suggestion... also sets the transaction in all the cached adapter statements
    Dim adapterPropInf As PropertyInfo = taType.GetProperty("Adapter", BindingFlags.NonPublic Or BindingFlags.Instance)
    Dim adapter As DbDataAdapter = DirectCast(adapterPropInf.GetValue(tableAdapter, Nothing), DbDataAdapter)

    If adapter.DeleteCommand IsNot Nothing Then adapter.DeleteCommand.Transaction = _trans
    If adapter.InsertCommand IsNot Nothing Then adapter.InsertCommand.Transaction = _trans
    If adapter.UpdateCommand IsNot Nothing Then adapter.UpdateCommand.Transaction = _trans

    SetConnection(tableAdapter, _conn)
  End Sub

  Public Sub Rollback()
    _trans.Rollback()

    If _restingConnState = RestingConnectionState.CloseAlways OrElse _
    (_restingConnState = RestingConnectionState.CloseIfOpenedImplicitly AndAlso _prevConnState = ConnectionState.Closed) Then
      _conn.Close()
    End If
  End Sub

  Public Sub Commit()
    _trans.Commit()

    If _restingConnState = RestingConnectionState.CloseAlways OrElse _
    (_restingConnState = RestingConnectionState.CloseIfOpenedImplicitly AndAlso _prevConnState = ConnectionState.Closed) Then
      _conn.Close()
    End If
  End Sub

  Private Sub SetConnection(ByVal tableAdapter As Object, ByVal connection As DbConnection)
    Dim type As Type = tableAdapter.GetType()

    Dim connPropInf As PropertyInfo = type.GetProperty("Connection", BindingFlags.NonPublic Or BindingFlags.Instance)

    connPropInf.SetValue(tableAdapter, connection, Nothing)
  End Sub

End Class

The code those other guys had written was a bit scruffy, but there are still improvements to be made to mine too. This uses reflection to enroll adapters in transactions. AN improvement I'd like to make would be to perform updates on a row by row basis in N tier relationships.

You might be able to use a similar method or expand on this to have your varying update methods called.. ?

 

[jedimaster100]

How-To pass tableadapter as parameter to module

That looks like an interesting way to handle connections... Will look into it a bit more. Thanks.