Using TableAdapters to Insert Related Data into an MS Access Database
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:
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:
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:
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.
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!
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
