none
Adding Child Rows using auto incrementing primary keys RRS feed

  • Question

  • I'm writing a single user vb.net utility that populates a dataset from a text file.  The data is then persisted as an XML file.  There is no SQL backend (at this time).  I have a single parent table with 2 child tables that are related with foreign key constraints.  During the parsing of the file, I add a new parent row when a line of text from the import file contains a unique value that signals a new record.  Additional import lines are read and parsed and belong to the previous parent as child rows.  Lines are read and parsed to be added to the child rows in child table 1 or child table 2 until a line signals a new parent row.

     

    Question: Because I'm reading a single line at a time and then updating either the parent table, child table 1, or child table 2, how do I retrieve the last value of the autoincrement column to use for the adding of the child rows?  Is this handled automagically?  I googled the question and found lots of examples were the backend server can provide the identy as part of a SP combinded with using the step = -1 to prevent collisions on the backend.  If a dataset alone is used, how do you handle the foreign key constraint or how do you get the current value of the last row added?

     

    DeBug

    Monday, August 27, 2007 6:47 PM

Answers

  • If you define an autoincrement column in a DataTable, it gets populated when you call the Add method, e.g.:

     

    Code Snippet

       DataRow parentRow = parentDataTable.Rows.Add(itemArray);

       int id = (int)parentRow["ID"];

     

       object[] childData = new object[childDataTable.Columns.Count];

       childData[childDataTable.Columns["ParentID"].Ordinal] = id;

       // populate the rest of the array here

       DataRow childRow = childTable.Rows.Add(childData);

     

     

    This works whether or not you ever send the data to a database.  (And if you do, it's possible to configure the TableAdapters so that their Update methods replace the client-side autoincremented key values with the real values from the server.)

    Sunday, September 2, 2007 8:45 AM

All replies

  • Ah, I have no idea why you're reinventing the wheel here. There are a dozen types of database engines that can deal with these issues for you--many are free. I suggest you take advantage of one of them like SQL Server Compact Edition which is a memory-resident DBMS that does not require a separate install. See my ebook on the subject.

     

    Monday, August 27, 2007 9:03 PM
    Moderator
  • William,

     

    Thanks for your reply.

     

    I'm OK with approaching the problem in a different way.  I have SQL Express installed as well as Access so no problem getting to a backend database.  However, I'm not sure what pieces of code are the reinventing the wheel parts.  I know I need to read in a text file, create parent child relationships, add additional information to the data, and then finally persist the data out to an XML file.  I'm not sure how to avoid this without the dataset as the container.  I'll look into the Compact Edition to see if that will substitute for the dataset.

     

    Regards,

     

    DeBug

    Monday, August 27, 2007 9:34 PM
  • I guess I misunderstood. The SQL Compact Edition is not meant to be a substitute for a Dataset unless you mean to use it as a way to persist and transport data stored on the client and passed as a database (.sdf) file to others. It appeared to me that you were writing your own DBMS routines to manage relationships, adding, changing and deleting rows. That is something the SQLCe (and many other DBMS) engines are intended to handle.

     

     

    Wednesday, August 29, 2007 2:37 AM
    Moderator
  • Anyone? 

     

    Friday, August 31, 2007 8:32 PM
  • If you define an autoincrement column in a DataTable, it gets populated when you call the Add method, e.g.:

     

    Code Snippet

       DataRow parentRow = parentDataTable.Rows.Add(itemArray);

       int id = (int)parentRow["ID"];

     

       object[] childData = new object[childDataTable.Columns.Count];

       childData[childDataTable.Columns["ParentID"].Ordinal] = id;

       // populate the rest of the array here

       DataRow childRow = childTable.Rows.Add(childData);

     

     

    This works whether or not you ever send the data to a database.  (And if you do, it's possible to configure the TableAdapters so that their Update methods replace the client-side autoincremented key values with the real values from the server.)

    Sunday, September 2, 2007 8:45 AM