Updating Data: The Beginning of the Data Access Layer
Concurrency conflicts that dealt with situations in which multiple people were editing the same data at the same time were discussed in the previous chapter. In addition to resolving concurrency errors, you need to understand how an update is accomplished.
When you set up constraints in the database, it takes a bit more work to send updates from many DataTable objects. The problem is that updates need to take place in a certain order. In this scenario, a customer must be added before an order can be added to that customer, and the order must be added before order items are added. When it's time to delete, the order items must be deleted before the orders, and the orders must be deleted before the customer. Do you see the problem? Normally, you perform an update on a DataTable-by-DataTable basis. When the Customer DataTable object is updated, all inserts, changes, and deletes are sent to the database, and then you update the next DataTable object, which might be the Order DataTable object. This order works for inserts, but it is backward for deletions because deletions must be done on the Order DataTable object first and then on the Customer DataTable object. (As long as changes are not modifying primary keys, the changes can be sent to the database in any order because the change does not affect data in any other DataTable object.)
You can solve the update problem by building an ordered table list based on the insert order, extracting the inserted and changed DataRow objects from each DataTable object, and submitting just the insert and changed DataRow objects to the database. Then you can reverse the ordered table list to get the delete order, extract the deleted DataRow objects from each DataTable object, and submit the deleted DataRow objects to the database. To build an ordered table list on a small database application, you can simply look at your database, and based on the relationships you can manually construct the list in your code. For large databases, it is much better to create a Data Access Layer to retrieve the list of relationships from Microsoft SQL Server and generate an ordered list of tables.
Retrieving the Relationships
The following code snippet creates a stored procedure called uspGetSqlRelationships that can be used on SQL Server 2000 or later to retrieve a list of relationships in a format that can be used to come up with an ordered table list.
SQL: uspGetSqlRelations
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id('dbo.uspGetSqlRelationships') AND OBJECTPROPERTY(id,'IsProcedure') = 1) DROP PROCEDURE dbo.uspGetSqlRelationships GO CREATE PROCEDURE dbo.uspGetSqlRelationships AS IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp SELECT r_obj.name AS RelationshipName, parent_obj.name AS Parent, child_obj.name AS Child, CONVERT(nvarchar(1000),'') as PK, CONVERT(nvarchar(1000),'') as FK, r.fkeyid, r.rkeyid, r.constid INTO #TEMP FROM dbo.sysobjects r_obj INNER JOIN dbo.sysreferences r ON r_obj.id = r.constid INNER JOIN dbo.sysobjects parent_obj ON r.rkeyid = parent_obj.id INNER JOIN dbo.sysobjects child_obj ON r_obj.parent_obj = child_obj.id INNER JOIN dbo.sysforeignkeys fkeys on fkeys.constid = r.constid INNER JOIN dbo.syscolumns cols on cols.id = fkeys.fkeyid and cols.colid = fkeys.fkey WHERE (r_obj.xtype = 'F') AND ((r_obj.status & 0x100)=0) -- OBTAIN TWO TABLE IDs DECLARE @PK VARCHAR(1000), @FK VARCHAR(1000) DECLARE @fkeyid INT, @rkeyid INT, @cnstid INT DECLARE @keys NVARCHAR(2126) DECLARE @cnstname SYSNAME DECLARE @cnstdes NVARCHAR(4000) DECLARE @cnsttype CHARACTER(2) DECLARE @relName VARCHAR(1000) DECLARE tempTable CURSOR local FOR SELECT RelationshipName, PK, FK, fkeyid, rkeyid, constid FROM #temp OPEN tempTable FETCH tempTable INTO @relName, @PK, @FK, @fkeyid, @rkeyid, @cnstid WHILE @@fetch_status >= 0 BEGIN -- USE CURSOR OVER FOREIGN KEY COLUMNS TO BUILD COLUMN LISTS -- (NOTE: @keys HAS THE FKEY AND @cnstdes HAS THE RKEY COLUMN LIST) DECLARE ms_crs_fkey CURSOR local FOR SELECT fkey, rkey FROM sysforeignkeys WHERE constid = @cnstid OPEN ms_crs_fkey DECLARE @fkeycol SMALLINT, @rkeycol SMALLINT FETCH ms_crs_fkey INTO @fkeycol, @rkeycol SELECT @keys = col_name(@fkeyid, @fkeycol), @cnstdes = col_name(@rkeyid, @rkeycol) FETCH ms_crs_fkey INTO @fkeycol, @rkeycol WHILE @@fetch_status >= 0 BEGIN SELECT @keys = @keys + ',' + col_name(@fkeyid, @fkeycol), @cnstdes = @cnstdes + ',' + col_name(@rkeyid, @rkeycol) FETCH ms_crs_fkey into @fkeycol, @rkeycol END DEALLOCATE ms_crs_fkey UPDATE #temp SET pk = @cnstdes, fk= @keys WHERE RelationshipName=@relName FETCH tempTable INTO @relName, @PK, @FK, @fkeyid, @rkeyid, @cnstid END DEALLOCATE tempTable SELECT DISTINCT RelationshipName, Parent, Child, PK, FK FROM #temp
This procedure queries the sysreferences table to get the complete list of references between objects. The sysreferences table contains id columns, which are converted to the object name by several joins back to the sysobjects table. The where clause filters the references to foreign keys (r_obj.xtype = 'F') and filters out unenforced relationships ((r_obj.status & 0x100)=0). The results are placed into a temporary table called #Temp. Notice that the PK and FK columns are blank; the latter part of the stored procedure fills them by opening a cursor to the temporary table, performing a lookup of the primary and foreign key columns, and placing this information into the PK and FK fields. If you have composite keys, this procedure populates the PK and FK fields with a comma-delimited list of fields. The last thing this procedure does is return the relationship information.
You can use this stored procedure to construct the relationships in your DataSet object if you choose not to use typed DataSet objects in your application. Also, this procedure is not very fast, primarily due to its use of cursors. You can execute the stored procedure at application startup to retrieve the relationship list and cache the results. Figure 6-2 shows the result of this stored procedure when it is run on the Customer database.
Figure 6-2: The output of the stored procedure uspGetSqlRelationships when run on the Northwind database
Retrieving the List of Tables
In addition to retrieving the list of relationships, you need to retrieve a complete list of tables in the database. There is a simple command that can accomplish this, but it should still be placed into a stored procedure. Here is the stored procedure:
SQL: uspGetSqlTables
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('dbo.uspGetSqlTables') AND OBJECTPROPERTY(id, 'IsProcedure') = 1) DROP PROCEDURE dbo.uspGetSqlTables GO CREATE PROCEDURE dbo.uspGetSqlTables AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties') AND (TABLE_NAME <> 'sysdiagrams')
Ordering the Table List
After creating these stored procedures, the next task is to write some code that builds the ordered table list. Create a new class called DataAccess in its own file; it can easily be copied to other projects as a more complete data access layer is created. The DataAccess class normally has a single instance that is referenced throughout the application, but you must create additional instances for each additional connection string in your application.
Add a property called Instance (the default instance) that is lazily initialized to an instance of the DataAccess class. Add a property called OrderedTableList that is lazily initialized when you request its value by calling a method called BuildOrderedTableList. Create a method called LoadTable that accepts the name of a stored procedure to execute and returns a DataTable object that contains the results. This method will be used in the steps that follow.
Load a DataTable object called sourceTableList with the list of database tables, and load a DataTable object called sourceRelationList with the list of relationships. Clone the sourceTableList schema to the output DataTable object called OrderedTableList.
Remove self-relationships (relationships between fields in the same table). For example, the Northwind database has an Employees table that has a self-relationship between the EmployeeId and the ReportsTo fields. Self-relationships have no value when you are trying to calculate the table order. After that, you can continuously loop through the sourceTableList, performing the following steps.
See if the current table is a child in any relationship. If it isn't, remove relations where the current table is the parent in the relationship table and move the current table to the OrderedTableList.
Restart the loop by resetting the position in the sourceTableList to 0. If the end of the list is reached, the current count of rows in the sourceTableList is recorded and the loop is restarted by resetting the position to 0. If the end of the list is reached again and the current count of rows in the sourceTableList is the same as the count that was recorded previously, there is a circular reference error. The circular references are located by calling the FindCircularErrors method, and an InvalidConstraintException is thrown with this circular reference information.
The FindCircularErrors method loops through the remaining relationships that exist in sourceRelationList and tests to see if each one is part of a circular reference by calling the IsCircular method. The IsCircular method recursively follows each relationship until it ends or goes to a relationship that it has already visited.
Here is the complete DataAccess class.
Visual Basic
Imports System.Data Imports System.Data.Common Imports System.Data.SqlClient Imports System.Configuration Imports System.Text Public Class DataAccess Public Shared ReadOnly Property Instance() As DataAccess Get If _instance Is Nothing Then _instance = New DataAccess() End If Return _instance End Get End Property Private Shared _instance As DataAccess Public Property OrderedTableList() As DataTable Get If _orderedTableList Is Nothing Then BuildOrderedTableList() End If Return _orderedTableList End Get Set(ByVal value As DataTable) _orderedTableList = value End Set End Property Private _orderedTableList As DataTable 'Builds an ordered list of tables and relationships. 'These lists will be used to determine the order of 'the updates that will be sent to the database. Private Sub BuildOrderedTableList() Dim sourceTableList As DataTable = GetTable("dbo.uspGetSqlTables") Dim sourceRelationList As DataTable = _ GetTable("dbo.uspGetSqlRelationships") OrderedTableList = sourceTableList.Clone() OrderedTableList.TableName = "OrderedTableList" 'first delete circular references to self Dim iCounter As Integer = 0 While (iCounter < sourceRelationList.Rows.Count) If sourceRelationList.Rows(iCounter)("Child").ToString() = _ sourceRelationList.Rows(iCounter)("Parent").ToString() Then sourceRelationList.Rows.Remove(sourceRelationList.Rows(iCounter)) Else iCounter += 1 End If End While 'continue looping through tables until there are none left. Dim position As Integer = 0 Dim lastCount As Integer = sourceTableList.Rows.Count While (sourceTableList.Rows.Count <> 0) 'for the current table, see if it is a child in a relationship 'if no child relationship exists '1. remove relations where it is the parent in the ' relationship table. '2. move the table from the sourceTableList to the OrderedTableList. Dim findChild As String = String.Format("[Child]='{0}'", _ sourceTableList.Rows(position)("TABLE_NAME")) If sourceRelationList.Select(findChild).Length = 0 Then Dim findParent As String = String.Format("[Parent]='{0}'", _ sourceTableList.Rows(position)("TABLE_NAME")) For Each parent As DataRow In _ sourceRelationList.Select(findParent) sourceRelationList.Rows.Remove(parent) Next 'move table OrderedTableList.Rows.Add( _ sourceTableList.Rows(position).ItemArray) 'delete table row sourceTableList.Rows.RemoveAt(position) position = 0 'restart loop Else position += 1 If (position = sourceTableList.Rows.Count) Then If (lastCount = sourceTableList.Rows.Count) Then FindCircularErrors(sourceRelationList) End If lastCount = sourceTableList.Rows.Count position = 0 End If End If End While End Sub Private Sub FindCircularErrors(ByVal sourceRelationList As DataTable) 'this means that all of the tables that are left are children 'so let's follow each relation to see if it is circular and 'throw an exception. For Each currentRelation As DataRow In sourceRelationList.Rows Dim beenThere As New ArrayList() If (IsCircular(currentRelation, beenThere, sourceRelationList)) Then Dim pathBuilder As New StringBuilder() For Each relation As DataRow In beenThere pathBuilder.AppendFormat(_ "Parent: {0,-35} Child: {1,-35} Relationship:{2}{3}", _ relation("Parent"), _ relation("Child"), _ relation("RelationshipName"), _ vbCrLf) Next Throw New InvalidConstraintException( _ "Circular relationships exist in the datbase." _ + vbCrLf + pathBuilder.ToString()) End If Next End Sub Private Function IsCircular(ByVal currentRelation As DataRow, _ ByRef beenThere As ArrayList, _ ByRef relations As DataTable) As Boolean beenThere.Add(currentRelation) 'follow relation until it ends or been there... For Each childRelation As DataRow In relations.Rows If (childRelation("Parent").ToString() = _ currentRelation("Child").ToString()) Then 'have we been there? or do recursive check If (beenThere.Contains(childRelation)) Then Return True End If Dim currentPath As New ArrayList(beenThere) If (IsCircular(childRelation, currentPath, relations)) Then beenThere = currentPath Return True End If End If Next Return False End Function Public Shared Function GetTable(ByVal storedProcedure As String) _ As DataTable Dim cnString As ConnectionStringSettings cnString = ConfigurationManager.ConnectionStrings("CustomerData") Dim dt As New DataTable() Using connection As DbConnection = New SqlConnection() connection.ConnectionString = cnString.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = storedProcedure connection.Open() dt.Load(cmd.ExecuteReader()) End Using Return dt End Function End Class
C#
using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Configuration; using System.Text; using System.Collections; public class DataAccess { public static DataAccess Instance { get { if (_instance == null) { _instance = new DataAccess(); } return _instance; } } private static DataAccess _instance; public DataTable OrderedTableList { get { if (_orderedTableList == null) { BuildOrderedTableList(); } return _orderedTableList; } set { _orderedTableList = value; } } private DataTable _orderedTableList; //Builds an ordered list of tables and relationships. //These lists will be used to determine the order of //the updates that will be sent to the database. private void BuildOrderedTableList() { DataTable sourceTableList = GetTable("dbo.uspGetSqlTables"); DataTable sourceRelationList = GetTable("dbo.uspGetSqlRelationships"); OrderedTableList = sourceTableList.Clone(); OrderedTableList.TableName = "OrderedTableList"; //first delete circular references to self int iCounter = 0; while (iCounter < sourceRelationList.Rows.Count) { if ( sourceRelationList.Rows[iCounter]["Child"].ToString() == sourceRelationList.Rows[iCounter]["Parent"].ToString()) { sourceRelationList.Rows.Remove(sourceRelationList.Rows[iCounter]); } else { iCounter += 1; } } //continue looping through tables until there are none left. int position = 0; int lastCount = sourceTableList.Rows.Count; while (sourceTableList.Rows.Count != 0) { //for the current table, see if it is a child in a relationship //if no child relationship exists //1. remove relations where it is the parent in the // relationship table. //2. move the table from the sourceTableList to the OrderedTableList. string findChild = string.Format("[Child]='{0}'", sourceTableList.Rows[position]["TABLE_NAME"]); if ( sourceRelationList.Select(findChild).Length == 0) { string findParent = string.Format("[Parent]='{0}'", sourceTableList.Rows[position]["TABLE_NAME"]); foreach ( DataRow parent in sourceRelationList.Select(findParent)) { sourceRelationList.Rows.Remove(parent); } //move table OrderedTableList.Rows.Add( sourceTableList.Rows[position].ItemArray); //delete table row sourceTableList.Rows.RemoveAt(position); position = 0; //restart loop } else { position += 1; if (position == sourceTableList.Rows.Count) { if (lastCount == sourceTableList.Rows.Count) { FindCircularErrors(sourceRelationList); } lastCount = sourceTableList.Rows.Count; position = 0; } } } } private void FindCircularErrors(DataTable sourceRelationList) { //this means that all of the tables that are left are children //so let's follow each relation to see if it is circular and //throw an exception. foreach (DataRow currentRelation in sourceRelationList.Rows) { ArrayList beenThere = new ArrayList(); if ( IsCircular(currentRelation,ref beenThere, ref sourceRelationList)) { StringBuilder pathBuilder = new StringBuilder(); foreach ( DataRow relation in beenThere) { pathBuilder.AppendFormat( "Parent: {0,-35} Child: {1,-35} Relationship:{2}{3}", relation["Parent"], relation["Child"], relation["RelationshipName"], "\r\n"); } throw new InvalidConstraintException( "Circular relationships exist in the datbase.\r\n" + pathBuilder.ToString()); } } } private bool IsCircular(DataRow currentRelation, ref ArrayList beenThere, ref DataTable relations) { beenThere.Add(currentRelation); //follow relation until it ends or been there... foreach (DataRow childRelation in relations.Rows) { if (childRelation["Parent"].ToString() == currentRelation["Child"].ToString()) { //have we been there? or do recursive check if ( beenThere.Contains(childRelation)) { return true; } ArrayList currentPath = new ArrayList(beenThere); if (IsCircular(childRelation, ref currentPath, ref relations)) { beenThere = currentPath; return true; } } } return false; } public static DataTable GetTable(string storedProcedure) { ConnectionStringSettings cnString; cnString = ConfigurationManager.ConnectionStrings["CustomerData"]; DataTable dt = new DataTable(); using(DbConnection connection = new SqlConnection()) { connection.ConnectionString = cnString.ConnectionString; DbCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = storedProcedure; connection.Open(); dt.Load(cmd.ExecuteReader()); } return dt; } }
You can test this code by simply adding a Button object to a form and assigning the OrderedTableList to the DataSource property of a DataGridView object, as shown in the following code snippet.
Visual Basic
Private Sub btnGetOrderedTables_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnGetOrderedTables.Click DataGridView1.DataSource = DataAccess.Instance.OrderedTableList End Sub
The DataGridView object displays the list of tables in the proper order for performing inserts. Modified DataRow objects can be sent to the database in any order provided they do not modify the primary key, so this is also the order that will be used for performing modifications, as shown in Figure 6-3.
Figure 6-3: The ordered table list, based on the order for performing insertions
Using the OrderedTableList to Perform Updates
Now that you have an ordered list of all of the tables in the database, you can use this list when you have a DataSet object that needs to be updated. Ideally, you want to be able to have an Update method in the DataAccess class that accepts a DataSet with any combination of DataTable objects that map to the database, and you want the DataSet object's changes to always be sent to the database in the proper order. To accomplish this, you can add a new method called BuildList that accepts your DataSet object and an indicator of the SQL operation that is to be performed (insert, modification, or delete). The operation will be based on members of the DataRowState enumeration (Added, Modified, and Deleted). The following code shows the BuildList method.
Visual Basic
Public Function BuildList(ByVal state As DataRowState, ByVal ds As DataSet) _ As ArrayList Dim list As New ArrayList() For Each drTable As DataRow In OrderedTableList.Rows Dim s As String = CType(drTable("TABLE_NAME"), String) Dim dt As DataTable = ds.Tables(s) If (Not dt Is Nothing) Then list.Add(dt) End If Next If (state = DataRowState.Deleted) Then list.Reverse() End If Return list End Function
C#
public ArrayList BuildList(DataRowState state, DataSet ds) { ArrayList list = new ArrayList(); foreach (DataRow drTable in OrderedTableList.Rows) { string s = (string)drTable["TABLE_NAME"]; DataTable dt = ds.Tables[s]; if (dt != null) list.Add(dt); } if (state == DataRowState.Deleted) { list.Reverse(); } return list; }
The next step is to create the Update method in the DataAccess class. You essentially need to execute the same code for the inserts, modifications, and deletes, so a method called UpdateOperation is added that accepts the operation to be performed. To operate with the TableAdapter objects that are created by the DataSet designers, the DataAccess class must also hold a Dictionary of TableAdapter objects in a property called TableAdapterMappings, which is based on the name of the table. Here is the code.
Visual Basic
Public ReadOnly Property TableAdapterMappings() _ As Dictionary(of string, TableAdapterBase) Get Return _tableAdapterMappings End Get End Property Private _tableAdapterMappings As _ New Dictionary(of String, TableAdapterBase) Public Sub Update(ds as DataSet) UpdateOperation(ds, DataRowState.Added) UpdateOperation(ds, DataRowState.Modified) UpdateOperation(ds, DataRowState.Deleted) End Sub Public Sub UpdateOperation(ds as DataSet, state as DataRowState) Dim _buildList as ArrayList = BuildList(state, ds) For Fach table as DataTable in _buildList Using tempTable as DataTable = table.GetChanges(state) If (not tempTable is Nothing) then Dim ta as TableAdapterBase = _ TableAdapterMappings(table.TableName) ta.UpdateTable(tempTable) End If End Using Next End Sub
C#
public Dictionary<string, TableAdapterBase> TableAdapterMappings { get { return _tableAdapterMappings; } } private Dictionary<string, TableAdapterBase> _tableAdapterMappings = new Dictionary<string, TableAdapterBase>(); public void Update(DataSet ds) { UpdateOperation(ds, DataRowState.Deleted); UpdateOperation(ds, DataRowState.Added); UpdateOperation(ds, DataRowState.Modified); } public void UpdateOperation(DataSet ds, DataRowState state) { ArrayList buildList = BuildList(state, ds); foreach (DataTable table in buildList) { using (DataTable tempTable = table.GetChanges(state)) { if (tempTable != null) { TableAdapterBase ta = TableAdapterMappings[table.TableName]; ta.UpdateTable(tempTable); } } } }
Why Is a Dictionary Object Required for the TableAdapter Objects?
The Dictionary object provides a list of key/value pairs. Since a TableAdapter object is used to fill and update a single table, creating a dictionary where the table name is the key and the TableAdapter object is the value lets you get to the TableAdapter object later by simply using the name of the table.
Wouldn't it be wonderful if a typed DataSet had a Dictionary of all of the TableAdapter objects that are required to fill and update the DataSet? The TableAdapter was designed to be used independently of the typed DataSet class that the Microsoft Visual Studio .NET DataSet Designer creates, so it's up to you to create this dictionary yourselves. You will use this Dictionary object in your data access layer, so that's where the Dictionary will be defined.
The last thing you need to make this code operate with TableAdapter objects is to create a base class from which all TableAdapter objects inherit and which contains code to perform the following operations.
Set the ContinueUpdateOnError property of the TableAdpater object's underlying Data-Adapter object to true.
Add the TableAdapter to the TableAdapterMappings Dictionary object.
Provide an UpdateTable method that uses the underlying DataAdapter to send inserts, modifications, and deletes to the database.
The following code block shows the new base class called TableAdapterBase. This code is placed in its own file.
Visual Basic
Imports System.Reflection Imports System.Data.Common Imports System.Data Partial Public MustInherit Class TableAdapterBase Inherits System.ComponentModel.Component Public Sub New() ContinueUpdateOnError = True For Each tableMapping As DataTableMapping In ChildAdapter.TableMappings DataAccess.Instance.TableAdapterMappings(tableMapping.DataSetTable) _ = Me Next End Sub Private ReadOnly Property ChildAdapter() As DbDataAdapter Get Dim myAdapter As PropertyInfo = Me.GetType().GetProperty("Adapter", _ BindingFlags.GetProperty Or BindingFlags.NonPublic Or _ BindingFlags.Instance) Return CType(myAdapter.GetValue(Me, Nothing), DbDataAdapter) End Get End Property Public Property ContinueUpdateOnError() As Boolean Get Return ChildAdapter.ContinueUpdateOnError End Get Set(ByVal value As Boolean) ChildAdapter.ContinueUpdateOnError = value End Set End Property Public Function UpdateTable(ByVal table As DataTable) As Integer Return ChildAdapter.Update(table) End Function End Class
C#
using System; using System.Collections.Generic; using System.Text; using System.Reflection; using System.Data.Common; using System.Data; public abstract class TableAdapterBase : System.ComponentModel.Component { public TableAdapterBase() { ContinueUpdateOnError = true; foreach (DataTableMapping tableMapping in ChildAdapter.TableMappings) { DataAccess.Instance.TableAdapterMappings[tableMapping.DataSetTable] = this; } } private DbDataAdapter ChildAdapter { get { PropertyInfo myAdapter = this.GetType().GetProperty("Adapter", BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance); return (DbDataAdapter)myAdapter.GetValue(this, null); } } public bool ContinueUpdateOnError { get { return ChildAdapter.ContinueUpdateOnError; } set { ChildAdapter.ContinueUpdateOnError = value; } } public int UpdateTable(DataTable table) { return ChildAdapter.Update(table); } }
All TableAdapter objects must be configured to inherit from this base class in order for this data access layer to function properly, as shown in Figure 6-4.
Figure 6-4: Every TableAdapter object must be configured to inherit from the TableAdapterBase class.
Testing the Relational Update
You can test the Update method of the data access layer by creating a new DataSet object and adding a button that has code to instantiate a new TableAdapter object for each DataTable. Then add more code in the button to add DataRow objects to each of the DataTable objects in the Customer DataSet object. Finally, call the Update method. The following code shows the Update test.
Visual Basic
Private custDataSet As New CustomerDataSet() Private Sub btnUpdate1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate1.Click 'initialize table adapters to get them into the 'TableAdapterMappings collection Dim Adapter as TableAdapterBase Adapter = New CustomerDataSetTableAdapters.TblProductTableAdapter() Adapter = New CustomerDataSetTableAdapters.TblSalesPersonTableAdapter() Adapter = New CustomerDataSetTableAdapters.TblCustomerTableAdapter() Adapter = New CustomerDataSetTableAdapters.TblLocationTableAdapter() Adapter = New CustomerDataSetTableAdapters.TblOrderTableAdapter() Adapter = New CustomerDataSetTableAdapters.TblOrderItemTableAdapter() 'Create the Guids for the objects Dim p1Id As Guid = Guid.NewGuid() Dim p2Id As Guid = Guid.NewGuid() Dim spId As Guid = Guid.NewGuid() Dim cId As Guid = Guid.NewGuid() Dim l1Id As Guid = Guid.NewGuid() Dim l2Id As Guid = Guid.NewGuid() Dim oId As Guid = Guid.NewGuid() Dim oi1Id As Guid = Guid.NewGuid() Dim oi2Id As Guid = Guid.NewGuid() 'Create the DataRow custDataSet.TblProduct.LoadDataRow( _ New Object() {p1Id, "Widget 1", 123.45}, False) custDataSet.TblProduct.LoadDataRow( _ New Object() {p2Id, "Widget 2", 234.56}, False) custDataSet.TblSalesPerson.LoadDataRow( _ New Object() {spId, "Joe"}, False) custDataSet.TblCustomer.LoadDataRow( _ New Object() {cId, spId, "Joe's Customer"}, False) custDataSet.TblLocation.LoadDataRow( _ New Object() {l1Id, cId, "HQ", "add1", "add2", "city", _ "state", "12345", "US"}, False) custDataSet.TblLocation.LoadDataRow( _ New Object() {l2Id, cId, "WAREHOUSE", "add1", "add2", _ "city", "state", "12345", "US"}, False) custDataSet.TblOrder.LoadDataRow( _ New Object() {oId, cId, "1", l2Id, l1Id}, False) custDataSet.TblOrderItem.LoadDataRow( _ New Object() {oi1Id, oId, p1Id, 1, "Widget1", 1, 123.45}, False) custDataSet.TblOrderItem.LoadDataRow( _ New Object() {oi2Id, oId, p2Id, 2, "Widget2", 2, 234.56}, False) 'Do the update of the DataSet DataAccess.Instance.Update(custDataSet) MessageBox.Show("Customer Database Updated") End Sub
C#
private CustomerDataSet custDataSet = new CustomerDataSet(); private void btnUpdate1_Click(object sender, EventArgs e) { //initialize table adapters to get them into the //TableAdapterMappings collection TableAdapterBase Adapter; Adapter = new CustomerDataSetTableAdapters.TblProductTableAdapter(); Adapter = new CustomerDataSetTableAdapters.TblSalesPersonTableAdapter(); Adapter = new CustomerDataSetTableAdapters.TblCustomerTableAdapter(); Adapter = new CustomerDataSetTableAdapters.TblLocationTableAdapter(); Adapter = new CustomerDataSetTableAdapters.TblOrderTableAdapter(); Adapter = new CustomerDataSetTableAdapters.TblOrderItemTableAdapter(); //Create the Guids for the objects Guid p1Id = Guid.NewGuid(); Guid p2Id = Guid.NewGuid(); Guid spId = Guid.NewGuid(); Guid cId = Guid.NewGuid(); Guid l1Id = Guid.NewGuid(); Guid l2Id = Guid.NewGuid(); Guid oId = Guid.NewGuid(); Guid oi1Id = Guid.NewGuid(); Guid oi2Id = Guid.NewGuid(); //Create the DataRow custDataSet.TblProduct.LoadDataRow( new Object[] { p1Id, "Widget 1", 123.45 }, false); custDataSet.TblProduct.LoadDataRow( new Object[] { p2Id, "Widget 2", 234.56 }, false); custDataSet.TblSalesPerson.LoadDataRow( new Object[] { spId, "Joe" }, false); custDataSet.TblCustomer.LoadDataRow( new Object[] { cId, spId, "Joe's Customer" }, false); custDataSet.TblLocation.LoadDataRow( new Object[] {l1Id, cId, "HQ", "add1", "add2", "city", "state", "12345", "US"}, false); custDataSet.TblLocation.LoadDataRow( new Object[] {l2Id, cId, "WAREHOUSE", "add1", "add2", "city", "state", "12345", "US"}, false); custDataSet.TblOrder.LoadDataRow( new Object[] { oId, cId, "1", l2Id, l1Id }, false); custDataSet.TblOrderItem.LoadDataRow( new Object[] { oi1Id, oId, p1Id, 1, "Widget1", 1, 123.45 }, false); custDataSet.TblOrderItem.LoadDataRow( new Object[] { oi2Id, oId, p2Id, 2, "Widget2", 2, 234.56 }, false); //Do the update of the DataSet DataAccess.Instance.Update(custDataSet); MessageBox.Show("Customer Database Updated"); }
DAL Update Caveats
The Update method works properly in most situations, but not all.
Self-references If a SQL Server table has an enforced self-reference, such as an Employee table that has a ReportsTo column that points to the ID of a different Employee, you must come up with a way to send the inserts and deletes for that table. This means ordering the updates on a row-by-row basis that is similar to the table-by-table basis that has been presented.
Circular References If a circular reference chain exists, the data access layer identifies this and throws an exception.
