Using TableAdapters to Insert Related Data into an MS Access Database

Using TableAdapters to Insert Related Data into an MS Access Database

Rate This
  • Comments 63

I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships against SQL server. It’s pretty straightforward and Visual Studio generates all the code for you to properly insert, update and delete your data. However if you’re using MS Access then there’s one thing that Visual Studio doesn’t do because it’s not supported when using Access.

How Parent-Child Inserts Work

When Visual Studio generates the insert commands on a SQL-Server TableAdapter it looks to see if the table’s primary keys are auto-generated (identity columns) and if so, Visual Studio will write an additional statement to retrieve the key using the SCOPE_IDENTITY functionality of SQL Server. When in the DataSet designer, if you look at the insert statement in the properties window for the SQLTableAdapter you will see two statements separated by a semi-colon:

image

INSERT INTO [dbo].[Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = SCOPE_IDENTITY())

SQL Server supports batch statements through ADO.NET commands so this will populate the primary key back in the DataRow inside the DataSet as each row is inserted into the database. If you are enforcing foreign key constraints with a parent-child relation set up on two DataTables and you set the Update Rule to Cascade then any foreign key references will also be updated in the children. Because the TableAdapterManager will save the children after their parent records, when the child saves to the database it will contain the correct parent key which must already exist in the database before a child can be inserted in order to maintain referential integrity in the database.

Unfortunately Access doesn’t support batch statements. If you look at what is generated for Access you will only see one statement (also the OLEDB provider does not support named parameters hence the question mark placeholders):

INSERT INTO `Products` (`ProductName`, `SupplierID`, `CategoryID`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `ReorderLevel`, `Discontinued`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

So if you are doing inserts, especially for related parent-child data, you need a way to intercept the DataRow and set the primary key right after the row is inserted into the database and before any children are inserted. There’s an excellent article by Bill Vaughn (VB MVP) that explains this issue as well as a KB Article that shows how to solve it using the DataAdapter. These were written before Visual Studio had the concept of TableAdapters (which were added in VS 2008) so let’s see how we could use this technique to enhance our TableAdapters via partial classes.

Setting up the Parent-Child DataSet

The first step is to make sure you set up the tables in your Access database to use the AutoNumber feature for the primary keys on the rows. Here I’m using Access 2007 against the Northwind Access database. AutoNumber is used for both the primary keys on the Products and Categories tables:

image

Next you need to make sure you set up the relationship on the DataSet properly so that the primary key on the parent will cascade to the foreign key on the child. Set the relation in the DataSet designer to "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade. Just right-click on the relation and select "Edit Relation" in the DataSet designer:

image image

Loading and Editing the Parent-Child DataSet

You now are telling the DataSet to enforce the foreign key relationship which means that you must have a parent for every child. This means you have to load the data in parent then child order. You also have to be careful with your queries. You have to make sure that every row in the child DataTable will have a corresponding parent row in the parent DataTable. This also means that you have to make sure to call EndEdit on any new parent BindingSource before any children can be added.

For example, from the Data Sources window drag the Categories parent table as details and the related child Products table as a DataGridView on the form and Visual Studio will generate the code to load and save our data.

image

Head over to the code behind and make sure that the parent is filled first before the child. Also make sure that EndEdit is called on the CategoriesBindingSource before a new product can be inserted into the DataGridView. EndEdit will flush the data row being edited by the controls into the DataTable. In this example I just am calling EndEdit on the CategoriesBindingSource when the user selects the grid.

Public Class Form1

    Private Sub CategoriesBindingNavigatorSaveItem_Click() _
            Handles CategoriesBindingNavigatorSaveItem.Click
        Me.Validate()
        'Call EndEdit on all BindingSources! 
        Me.CategoriesBindingSource.EndEdit()
        Me.ProductsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ProductsDataSet)
    End Sub

    Private Sub Form1_Load() Handles MyBase.Load
        'Load parent before child!
        Me.CategoriesTableAdapter.Fill(Me.ProductsDataSet.Categories)
        Me.ProductsTableAdapter.Fill(Me.ProductsDataSet.Products)
    End Sub

    Private Sub ProductsDataGridView_Enter() Handles ProductsDataGridView.Enter
        'You must commit the parent row to the DataTable before adding child rows 
        Me.CategoriesBindingSource.EndEdit()
    End Sub

End Class

Note that anytime you call EndEdit and flush the data to the DataTable, the row must not fail any constraints either (i.e. if NULLs aren’t being allowed then you have to set those values). One way to handle this is to add code to set default values in the TableNewRow handler on the DataTable.

Enhancing the TableAdapter Partial Classes

 

 

 

Now for the good stuff. Like I mentioned in the beginning, you need a way to set the primary key on the parent right after the row is inserted into the database and before any children are inserted. Now that we have keys cascading we just need to write code to handle the RowUpdated event on the DataAdapter inside the TableAdapter partial class. TableAdapters are generated classes that Visual Studio creates for us from the DataSet designer. These classes are declared as Partial Classes so that means we can add code to the same class even if it’s in a separate file. Right-click on the TableAdapter class in the DataSet Designer and select View Code and the partial class file that you can edit will be created for you.

Namespace ProductsDataSetTableAdapters

    Partial Public Class CategoriesTableAdapter

    End Class

    Partial Public Class ProductsTableAdapter

    End Class
End Namespace

In these classes we can handle the RowUpdated event on the private variable _adapter which gives us access to the ADO.NET DataAdapter that is executing the updates to our rows. The way we retrieve the primary key is by executing the statement  SELECT @@IDENTITY which tells Access to send back the last primary key it used on the connection. Because you have to add this handler to all your TableAdapters that are working against MS Access, to make things more manageable you can create a class with a Shared (static) method to handle setting the key and then call that from the handlers.

Imports System.Data.OleDb

Public Class AccessIDHelper
    ''' <summary>
    ''' Retrieves the primary key autonumber values from Access
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, _
                                    ByVal e As OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.Continue AndAlso _
           e.StatementType = StatementType.Insert Then
            ' If this is an INSERT operation...
            Dim pk = e.Row.Table.PrimaryKey
            ' and a primary key column exists...
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)
                ' Execute the post-update query to fetch new @@Identity
                e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                e.Row.AcceptChanges()
            End If
        End If
    End Sub
End Class

Namespace ProductsDataSetTableAdapters

    Partial Public Class CategoriesTableAdapter

        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class

    Partial Public Class ProductsTableAdapter

        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class
End Namespace

So that’s how you can get the primary keys into the data rows and have them properly cascaded to the child rows. So now when the children are updated they will have the correct foreign key and the parent will exist in the database. I hope this helps clear up how to work with Access and Visual Studio. 

I’ve posted this example on CodeGallery so have a look.

Enjoy!

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
  • It looks like the TableAdapter already has a parameterless constructor, and it isn't marked as virtual or override. Can I still make a constructor?  Or are you thinking that I could make a new constructor with a different definition, like: public TableAdapter(bool HookUpStuff) ?

    Thanks,

    Eric

  • Unfortunately, I'm still having some problems with the transaction being null.  From looking at the designer code, I can't see where the Transation/_transaction value would be set... any ideas?

    Is the syntax OK?  Is this.Transaction the C# equivalent of Me.Transaction in VB?  I seem to recall reading something about "Me" having a bit of extra functionality.

    Thanks,

    Eric

  • It would be great if some one get this working in C#....

  • Hi Eric,

    Remember that this is a partial class (I even forget sometimes) not a child class, there's no inheritance here. You just have two files for a single class definition in this case. So unfortunately a constructor approach won't work either. You need to have this in a init method or a method that runs first in order to hook up the events properly. That may be why your transaction is null, but that doesn't seem right.

    VB hooks up the handlers for you when the class is constructed with the Handles syntax so you need to find a way to do the same. Maybe the C# forums can help. I'll try to play with this and let you know if I can find a way to get it working in C#.

    Other option is to have a VB data access layer ;-)

    -B

  • I've used Oracle for years and am pretty good with SQL but I am new to Visual Studio 2008 and VB in general so I am experimenting.  I'm creating a contacts application with three tables, "Family", "Member", and "Communication".  I have one Master-Detail form with with the Family as the Parent and Member as child data.  I set it up as Family being basic information of the family and a DataGridView showing individual members of the family with other information like birthday, etc...

    I'm trying to conduct a Search routine for Family.  I've got most of the code from one of your videos but because I have foreign key constraints it's not working.  It gives me the following error:

    "Cannot clear table Family because ForeignKeyConstraint FK_Member_Family enforces constraints and there are child rows in Member."

    In another comment on this page you told someone to set EnforceConstraints to false, which I did and it works but when I set it back to True it gives me this error:

    "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

    I perform my search by putting code on the Double-Click to Enter Search Mode, and then perform the Search on the KeyDown (enter key). One problem I am seeing is I think I need to clear out the DataGridView when I enter search mode (double-click).  And then when I perform the search, have it automatically fill in the DataGridView.

    I hope I am clear on this.

    Thanks and your videos are great and very helpful, please add more.

  • Wow, this is frustrating...

    It looks like the code I posted above works using a Winforms app built using VS2008.  Unfortunately for me, I'm trying to play with VS2010 using WPF.

    I'm coming to the conclusion that data binding in WPF will be the bane of my existence.  There are so many levels of poorly-documented indirection that dozens of trips through the generated code (after disabling Just-My-Code, of course) still leave me no closer to figuring out what is going on here.

    Anyway, thanks for posting a great article on how to deal with Access.  Hopefully the C# version will be of use to someone.

  • Hi Eric,

    I don't see why this code wouldn't work with WPF data binding. The code above is more about the DAL, not the UI data binding. You may want to check out these videos to see if they help:

    http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx#wpfdata

    HTH,

    -B

  • I have been troubled by this problem(parent child table update) for a very long time.

    Although I don't use sql ce db,I find this article particularly useful.

    And the key point are:

    1. Set the relation in the DataSet designer to  "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade

    2.Make sure that EndEdit is called on the CategoriesBindingSource before a new product can be inserted into the DataGridView.

    Thanks a lot!

  • Thank you! I solved my ODBCDataSet's "identity crisis" with your IDHelper method... Just had to change the OleDb* objects to Odbc*.

  • Hi,

    am using VS 2005 and get this errors:

    Error 1 Handles clause requires a WithEvents variable defined in the containing type or one of its base types. D:\Visual Studio 2005\Projects\PeLogg\PeLogg\PelletsdatabasUtvecklingDataSet.vb 7 53 PeLogg

    Error 2 'Transaction' is not a member of 'PeLogg.peloggLeveranserRaderDataTable'. D:\Visual Studio 2005\Projects\PeLogg\PeLogg\PelletsdatabasUtvecklingDataSet.vb 9 38 PeLogg

    for each Partial Public Class

    Do I have to do something diffrent in VS2005?

  • Here some code to do the same thing for Microsoft SQL Server Compact Edition

    Partial Public Class JOBSTableAdapter

           Private Sub _adapter_RowUpdated(ByVal sender As Object, _

                                                   ByVal e As System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs) _

                                                   Handles _adapter.RowUpdated

               SQLCEIDHelper.SetPrimaryKey(Me.Connection, e)

           End Sub

       End Class

       Public Class SQLCEIDHelper

           ''' <summary>

           ''' Retrieves the primary key autonumber values from SQL CE

           ''' </summary>

           ''' <remarks></remarks>

           Public Shared Sub SetPrimaryKey(ByVal conn As SqlCeConnection, _

                                           ByVal e As SqlCeRowUpdatedEventArgs)

               If e.Status = UpdateStatus.Continue AndAlso _

                  e.StatementType = StatementType.Insert Then

                   ' If this is an INSERT operation...

                   Dim pk = e.Row.Table.PrimaryKey

                   ' and a primary key column exists...

                   If pk IsNot Nothing AndAlso pk.Count = 1 Then

                       Dim cmdGetIdentity As New SqlCeCommand("SELECT @@IDENTITY", conn)

                       ' Execute the post-update query to fetch new @@Identity

                       Dim id As Integer

                       id = CInt(cmdGetIdentity.ExecuteScalar)

                       e.Row(pk(0)) = id

                       e.Row.AcceptChanges()

                   End If

               End If

           End Sub

       End Class

  • Thanks Mikhail Mozolin,

    I also wrote an article on how to do this with SQL CE here:

    http://blogs.msdn.com/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx

    HTH,

    -B

  • I don't know if Eric is still around...

    This page has helped me tremendously (thanks Beth) & since I'm working with C# Eric's code was helpful.  I also ran into the null transaction problem he referred to until I realized I didn't need to use a transaction.  Simply pass in the adapter's connection (which is all you need to execute the @@Identity statement) to the AccessIDHelper method & forget the transaction.  It's the connection that manages the identity values I believe, which is what we're after. Thanks again!

    Merlin

  • Well

    I found this sight while looking for the solution for SqlCE as opposed to Access, and when I read in the post about the EndInit() I knew right away that is part of the ISupportInitialize interface.  Like Eric I was looking for the C# implementation that is functional.  Now currently, I do not use the MSDataSetGenerator that is build into .net because the .net default dataset generator does not handle Large tables very well.  (it takes some time to fill 250,000 rows into a datatable).  So I wrote my own AsyncDataTableBase, AsyncTableAdapterBase, and few other classes in a library that not only threads the communication with the database for filling purposes (not for updates) but also, in combination with a suite of stored procedures returns the ADODB.Recordsets "PageSize" cursor mentality.  (not perfectly, but well enough), allowing a broader range of dataset interaction.  from this i knew that by adding ISupportINitialize implementation to the table adapter I could achieve 99% of what I wanted directly out of the box.  The EndInit implemented method attaches the event, while the other above code handles the identity refresh after update.  the only added issue was the fact, that since I added the implementation to the partial extended side of the class, it does not auto run the EndInit like normal ISupportInitialize Components.  (Like my AsyncTableAdapterBase does when you drop it on the form).  So you have to add that line to the constructor of the form after the InitializeComponents() call.  Additionally, i found that SqlCE does not intrinsically create a transaction, as well I find the connection properties in the default MS generated dataset to be lacking.  I often want one connection for all table adapters, (unless otherwise necessary) so in addition to handling the Transaction for the update, it is wisest to make sure there is a valid OPEN connection for the adapter first.  

    below is my completely function C# code for the updating of identity columns for SqlCE (which should be easily transportable to access).

    namespace MyApp.MyDataSetTableAdapters {

      partial class TableAdapterManager {

         public void SetConnection(SqlCeConnection connection) {

            this.Connection = connection;

            if (this._TaMyTableTableAdapter != null) this._TaMyTableTableAdapter.Connection = connection;

            //add similar lines here for additional table adapters

         }

      }

       public partial class TaMyTableTableAdapter : ISupportInitialize {

          public static void SetPrimaryKey(SqlCeTransaction trans, SqlCeRowUpdatedEventArgs e) {

             if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert)) {

                DataColumn[] pk = e.Row.Table.PrimaryKey;

                if (pk.HasElems()) {

                   SqlCeCommand cmd = new SqlCeCommand("SELECT @@IDENTITY", (trans.Connection as SqlCeConnection), trans);

                   object identity = cmd.ExecuteScalar();

                   if (identity != null) {

                      e.Row[pk[0]] = (int)((decimal)identity);

                      e.Row.AcceptChanges();                

                   }

                }

             }

          }

          public virtual int Update(MyDataSet.MyTableDataTable dataTable, bool UseTrans) {

             if (UseTrans) {

                this.Transaction = this.Connection.BeginTransaction();

                try {

                   return this.Update(dataTable);

                } finally {

                   this.Transaction.Commit();

                }            

             }else {

                return this.Update(dataTable);

             }

          }

          void _adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {

             SetPrimaryKey(this.Transaction, e);

          }

          #region ISupportInitialize Members

          void ISupportInitialize.BeginInit() {

          }

          void ISupportInitialize.EndInit() {

             Adapter.RowUpdated += new System.Data.SqlServerCe.SqlCeRowUpdatedEventHandler(_adapter_RowUpdated);

          }

          #endregion

       }

    }

    Form Code:

    namespace MyApp {

       public partial class Form1 : Form {

           public Form1 {

               InitializeComponents();

               ((ISupportInitialize)TaMyTableTableAdapter).EndInit()

       }

    }

  • First of all thank you for this article Beth!

    Like Eric i tried to use your solution for Access in C#.

    But i also ran into the problem with transaction = null and even Merlin's way didn't work.

    Now, for all those with the same problem:

    Use the solution Eric provided but in SetPrimaryKey just the Connection and the Transaction from the OleDbRowUpdatedEventArgs

    Then you have to initialize the OleDbCommand like:

    OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT @@IDENTITY", e.Command.Connection, e.Command.Transaction);

    Regards,

    Bastian

Page 3 of 5 (63 items) 12345