Updating Data The Beginning of the Data Access Layer

 

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.

image from book

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 
image from book

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.

image from book
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:

image from book

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') 
image from book

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.

  1. 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.

  2. 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.

image from book

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 
image from book

image from book

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;    } } 
image from book

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.

image from book

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 
image from book

image from book

C#

private void btnGetOrderedTables_Click(object sender, EventArgs e) {    dataGridView1.DataSource = DataAccess.Instance.OrderedTableList; } 
image from book

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.

image from book
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.

image from book

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 
image from book

image from book

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; } 
image from book

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.

image from book

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 
image from book

image from book

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);          }       }    } } 
image from book
image from book
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.

image from book

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.

The following code block shows the new base class called TableAdapterBase. This code is placed in its own file.

image from book

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 
image from book

image from book

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);    } } 
image from book

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.

image from book
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.

image from book

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 
image from book

image from book

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"); } 
image from book

DAL Update Caveats

The Update method works properly in most situations, but not all.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net