none
Enterprise Library Database.UpdateDataSet() error with FOREIGN KEY SAME TABLE Constraint RRS feed

  • Question

  • Hi,

    I've been able to reproduce an error I encountered in a large application, and was able to reduce it to a very small sample which has the same behaviour.

    The exception I can't handle:
    The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Table_Table". The conflict occurred in database "IWT.Test", table "dbo.Table", column 'Id'.
    The statement has been terminated.

    I usually know why this exception occurs.  But in my particular case, it's not because some references don't exist, as you'll see later.

    I use the following table:
    Table
    ====
    Id: uniqueidentifier -> PK
    ParentId : uniqueidentifier -> FK same table -> Id
    (as I said, this is a drilled down example)

    My VS 2008 solution looks like this:
    Program.cs: contains the code below.
    Table.xsd: typed dataset generated directly from the dragging the database into the schema designer (but I delete the TableAdapters as I don't use them).

    The Program.cs code:

     
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System.Data.Common;
    using System.Threading;
    
    namespace DBTest {
        class Program {
            static Database DB;
            static DbConnection conn;
    
            static void Main(string[] args) {
                // Read the data
                DataSet set = readData();
                DataSet newTable = replicateData(set);
                setNewIds(newTable);
                updateData(newTable);
    
                Console.ReadLine();
            }
    
            private static DataSet readData() {
                DB = DatabaseFactory.CreateDatabase("IWT.Test") as Database;
                conn = DB.CreateConnection();
                Table t = new Table();
                DbCommand command = DB.GetSqlStringCommand("SELECT Id, ParentId FROM [Table]");
                DB.LoadDataSet(command, t, "Table");
                foreach (DataRow r in t._Table) {
                    Console.WriteLine("Record read: Id = " + r["Id"] + "\n\tParentId = " + r["ParentId"]);
                }
                return t;
            }
    
            private static DataSet replicateData(DataSet table) {
                Table t = new Table();
    
                foreach (DataRow row in table.Tables[0].Rows) {
                    DataRow newRow = t.Tables[0].NewRow();
                    foreach (DataColumn col in table.Tables[0].Columns) {
                        newRow[col.Caption] = row[col.Caption];
                    }
                    t.Tables[0].Rows.Add(newRow);
                }
    
                return t;
            }
    
            private static void setNewIds(DataSet newTable) {
                Dictionary<Guid, Guid> ptdOldToNew = new Dictionary<Guid, Guid>();
                List<DataRow> rows = new List<DataRow>();
                foreach (DataRow r in newTable.Tables[0].Rows)
                    rows.Add(r);
                while (rows.Count > 0) {
                    List<DataRow> rowsCopy = rows.ToList();
                    foreach (DataRow row in rowsCopy) {
                        // See if the ParentId already exists
                        if (row["ParentId"] == DBNull.Value || ptdOldToNew.ContainsKey((Guid)row["ParentId"])) {
                            // For each column "Id"
                            foreach (DataColumn col in newTable.Tables[0].Columns) {
                                // If it is the Id column, create new key!
                                if (col.Caption == "Id") {
                                    // New key should be created
                                    Guid oldId = (Guid)row[col];
                                    Guid newId = Guid.NewGuid();
                                    row[col] = newId;
                                    ptdOldToNew.Add(oldId, newId);
                                }
                                else {
                                    // In this case, there MUST be a mapping, if the tables were updated in the correct order
                                    // Don't update if it is NULL
                                    if (row[col] != DBNull.Value) {
                                        Guid oldId = (Guid)row[col];
                                        // The collection SHOULD contain it.
                                        // Maybe it was still an AsIs id?
                                        row[col] = ptdOldToNew[oldId];
                                    }
                                }
                            }
                            // Delete this one from the collection, we've handled this one
                            rows.Remove(row);
                        }
                        else {
                            // Else, continue and do this one later
                            continue;
                        }
                    }
                }
    
                foreach (DataRow row in newTable.Tables[0].Rows) {
                    Console.WriteLine("\nRecord changed: Id = " + row["Id"] + "\n\tParentId = " + row["ParentId"]);
                }
            }
    
            private static void updateData(DataSet newTable) {
                conn.Open();
                using (DbTransaction trans = conn.BeginTransaction()) {
                    DbCommand insertCmd = DB.GetSqlStringCommand("INSERT INTO [Table](Id, ParentId) VALUES(@Id, @ParentId)");
                    DB.AddInParameter(insertCmd, "@Id", DbType.Guid, "Id", DataRowVersion.Current);
                    DB.AddInParameter(insertCmd, "@ParentId", DbType.Guid, "ParentId", DataRowVersion.Current);
                    DB.UpdateDataSet(newTable, "Table", insertCmd, null, null, trans);
    
                    trans.Commit();
                    conn.Close();
                }
            }
        }
    }
    
     

    Ok, let's walk throught it.
    First, the data from the table is read.  I've put some dummy data into it, namely:
    Id            ParentId
    111-...      222-....
    222-....     NULL
    333-....     NULL
    (So that's Guids filled with 1's, 2's or 3's, jsut for easy debugging).

    Next, when reading the database (MSSQL 2k8), I use the LoadDataSet method of the Enterprise Library to get the data.
    The Console.WriteLines show that it actually worked.

    Then, I am going to replicate the data by creating a new dataset with the exact same data inside.

    Then, I assign new ids to the rows in the new dataset.  I keep the mappings from old to new id in ptdOldToNew.
    Note that I use a while loop around a foreach loop so that if a row's ParentId doesn't exist yet, i.e. hasn't been remapped yet, it is left for later.  There is no deadlock risk here, as they are all in the same hierarchy.
    Again some Console.WriteLines to see that the new data is actually safe to be imported.

    Next, the data is committed to the database.
    This is done through the Enterprise Library again, using the UpdateDataSet method.

    This is the statement causing the exception:
    DB.UpdateDataSet(newTable, "Table", insertCmd, null, null, trans);
    Can anyone please help me figure this one out?

    Thanks :-)

    SabbeRubbish
    Monday, December 7, 2009 2:47 PM

Answers

  • I figured out the problem.

    It has to do with the order of the rows in the DataTable.
    When updating the database, for example with new rows, there should not be references to any ParentId that doesn't exist yet.
    However, LoadDataSet does NOT care about the order and retrieves the data in an order that is not suitable for UpdateDataSet.

    Knowing this, I just added an extra method to sort the table rows so that the most depending rows are at the end of the table.

    Hope this helps anyone in the future.
    Tuesday, December 8, 2009 3:30 PM