ADO.NET DataSet with Parent-Child tables foreign key error

Question

I have a parent table and child table and I would want to insert into them using a dataset/datatable/dataadapter combination. Inside the SQL Server database the parent table is declared with a primary key, and the child table with a foreign key -- the primary of the parent. One entry in the parent table could potentially have 0->n entries in the child table.

The code is below. When parameters are passed and need to be introduced in the child table, the code throws an exception on the last call to .Update() -- in other words on updating the child indicating a foreign key violation. I tried to run it without the foreign key relationship defined on the SQLServer tables and rows were inserted in both tables, however the foreign key entry of the child had no connection with the primary key entry in the parent. They were just increasingly consecutive numbers, different than those inserted in the parent table by the same call.

How do I correctly update the tables?

Here is the code:

public void LogEvent(string eventType, string source, string user, string description, string level, DateTime time, IDictionary eventInfo) {

            string EventsSelect = @"SELECT * FROM Events";
            SqlDataAdapter EventsDA = new SqlDataAdapter(EventsSelect, this.CONNECTION_STRING);
            SqlCommandBuilder EventsCB = new SqlCommandBuilder(EventsDA);

            string EventPropertiesSelect = @"SELECT * FROM EventProperties";
            SqlDataAdapter EventPropertiesDA = new SqlDataAdapter(EventPropertiesSelect, this.CONNECTION_STRING);
            SqlCommandBuilder EventPropertiesCB = new SqlCommandBuilder(EventPropertiesDA);


            DataSet  EventsDS = new DataSet();
            EventsDA.TableMappings.Add("Table", "dsEvents");
            EventPropertiesDA.TableMappings.Add("Table", "dsEventProperties");

            EventsDA.FillSchema( EventsDS, SchemaType.Mapped);
            EventPropertiesDA.FillSchema( EventsDS, SchemaType.Mapped);

            EventsDS.Tables["dsEvents"].Columns["Id"].AutoIncrementSeed = -1;
            EventsDS.Tables["dsEvents"].Columns["Id"].AutoIncrementStep = -1;
            EventsDS.Relations.Add("FK_Parent_Child",
                                      EventsDS.Tables["dsEvents"].Columns["Id"],
                                      EventsDS.Tables["dsEventProperties"].Columns["EventId"]);

            DataRow newEventRow = EventsDS.Tables["dsEvents"].NewRow();
            newEventRow["Type"] = eventType;
            newEventRow["Source"] = source;
            newEventRow["User"] = user;
            newEventRow["Description"] = description;
            newEventRow["Level"] = level;
            newEventRow["Time"] = time;
            EventsDS.Tables["dsEvents"].Rows.Add(newEventRow);

            if (eventInfo != null && eventInfo.Count > 0)
            {
                foreach (string property in eventInfo.Keys)
                {
                    DataRow newEventPropertyRow = EventsDS.Tables["dsEventProperties"].NewRow();
                    newEventPropertyRow["EventId"] = newEventRow["Id"];
                    newEventPropertyRow["Property"] = property;
                    newEventPropertyRow["Value"] = eventInfo[property];
                    EventsDS.Tables["dsEventProperties"].Rows.Add(newEventPropertyRow);
                }
            }

            EventsCB.GetInsertCommand();
            EventsDA.Update(EventsDS, "dsEvents");

            EventPropertiesCB.GetInsertCommand();
            EventPropertiesDA.Update(EventsDS, "dsEventProperties");
    }

Answer 1

Look at this