none
AutoIncrement fails when storing DataTable as XML RRS feed

  • Question

  • My typed DataTable has a column with AutoIncrement set to True, and the AutoIncrementStep is 1.  It works fine, with it incrementing by 1 for each successive row.  That is, until running WriteXml(), Clear(), and ReadXml() on the table.  Then the next new record has its column incremented not by 1, but by a seemingly random number usually somewhere between about 10 and 300.  This behavior is unaffected by whether the column is a Primary Key, and it's unaffected by whether XmlWriteMode.WriteSchema or XmlWriteMode.IgnoreSchema is used with the WriteXml().

    Is there a way to make it increment correctly when storing to and reading from XML?
    Friday, February 19, 2010 9:55 AM

Answers

  • Hello,

    When you WriteXml add XmlWriteMode.DiffGram parameter to serialize the Table as a DiffGram, when you read back make sure you have the Table with the correct schema setup.

    In your example you write the xml, then clear the rows and read the xml back, since you keep the schema for your table you could do.

     fItemsTable.WriteXml(string.Format(@"{0}\ItemsTable.dat", FOLDER_FOR_XML_FILE), XmlWriteMode.DiffGram);

     


    And that will keep the autoincrement number as you expect it.

    Please let me know if this helps

    Juan


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 26, 2010 2:03 AM
    Moderator

All replies

  • Where are you using the WriteXML from? Can you please post some code?
    Saturday, February 20, 2010 1:52 PM
  • In a new project, create a DataSet "DataSet1" with a DataTable "ItemsTable" with columns "UID" (int32, AutoIncrement=True), ItemName (string), and ItemDescription (string).  Then run the following code.  It creates the specified number of rows, stores to XML, reads from XML, and adds more rows.  When you change the constant that specifies the number of rows to create before storing to XML, and you run it again, you'll see that the number of auto-incremented numbers skipped in the "UID" column is always equal to the number of rows added before storing to XML.  There might be other factors affecting how many numbers are skipped.  In the project where I'm seeing this problem, the amount skipped varies between about 10 and 300, even though not that many new rows had been added.

    namespace GeneralTesting
    {

      class Demo
      {
        const string FOLDER_FOR_XML_FILE = @"d:\temp";
        const int INITIAL_ROWS_COUNT = 30;
        static Demo fDemo;
        DataSet1.ItemsTableDataTable fItemsTable = new DataSet1.ItemsTableDataTable();

        static void Main()
        {
          fDemo = new Demo();
          fDemo.Run();
        }

        internal void Run()
        {
          DataSet1.ItemsTableRow newRow;
          //
          // Add the initial rows
          fItemsTable.Clear();
          for (int xColumnNumber = 1; xColumnNumber <= INITIAL_ROWS_COUNT; xColumnNumber++)
          {
            newRow = (DataSet1.ItemsTableRow)fItemsTable.NewRow();
            newRow.ItemName = string.Format("Name {0}", xColumnNumber);
            newRow.ItemDescription = string.Format("Description {0}", xColumnNumber);
            fItemsTable.AddItemsTableRow(newRow);
          }
          fItemsTable.AcceptChanges();
          // Store to XML
          fItemsTable.WriteXml(string.Format(@"{0}\ItemsTable.dat", FOLDER_FOR_XML_FILE));
          // Read from XML
          fItemsTable.Clear();
          fItemsTable.ReadXml(string.Format(@"{0}\ItemsTable.dat", FOLDER_FOR_XML_FILE));
          // Add more rows
          for (int xColumnNumber = INITIAL_ROWS_COUNT + 1; xColumnNumber <= INITIAL_ROWS_COUNT + 10; xColumnNumber++)
          {
            newRow = (DataSet1.ItemsTableRow)fItemsTable.NewRow();
            newRow.ItemName = string.Format("Name {0}", xColumnNumber);
            newRow.ItemDescription = string.Format("Description {0}", xColumnNumber);
            fItemsTable.AddItemsTableRow(newRow);
          }
          fItemsTable.AcceptChanges();
          // Display the results
          foreach (DataSet1.ItemsTableRow xRow in fItemsTable)
          {
            Console.WriteLine(string.Format("Row 'Name' is {0}, 'UID' is {1}", xRow.ItemName, xRow.UID));
          }
          Console.WriteLine();
          Console.WriteLine("On the first row after reading from XML, several numbers were skipped in the auto-incremented 'UID' column.");
          Console.Read();
        }

      }
    }

    Wednesday, February 24, 2010 9:04 PM
  • Hello,

    When you WriteXml add XmlWriteMode.DiffGram parameter to serialize the Table as a DiffGram, when you read back make sure you have the Table with the correct schema setup.

    In your example you write the xml, then clear the rows and read the xml back, since you keep the schema for your table you could do.

     fItemsTable.WriteXml(string.Format(@"{0}\ItemsTable.dat", FOLDER_FOR_XML_FILE), XmlWriteMode.DiffGram);

     


    And that will keep the autoincrement number as you expect it.

    Please let me know if this helps

    Juan


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 26, 2010 2:03 AM
    Moderator
  • The problem is obviously caused by your autoincremented field being 30 when you write out to the XML file, then when you read it back in, you're reading in 30 more, so now it goes up to 60, and that's where you start when you add in new rows.

    Off the top of my head, you can avoid this by creating a new instance of your fItemsTable prior to reading the XML file instead of simply issuing fItemsTable.Clear().

    I was hoping to find a property somewhere that would let you set that auto-incremented field to whatever you wanted to, but curiously the AutoIncrementSeed property of the UID column is still set to 1 (and I would have thought it might have been incremented to 30). Somewhere, there's something that tells the DataTable that it needs to set that next UID column to 60 instead of 30, and if we could find that property, that would be the obvious solution to the problem.

    So far, I've not discovered it, so creating a new instance is the only workaround I've been able to come up with, but depending on what you're doing with your DataTable, that may not be an acceptable workaround.



    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 28, 2010 9:34 PM
  • Hello,

    Everytime a new Row is added to the table the autoincrement value is increased even if you set the value for the AutoIncremented Column, for example after adding the initial rows if you do something like:
    for (int xColumnNumber = 1; xColumnNumber <= INITIAL_ROWS_COUNT; xColumnNumber++)
    {
       newRow = fItemsTable.NewRow();
       newRow["ItemName"] = string.Format("Name {0}", xColumnNumber);
       fItemsTable.Rows.Add(newRow);
    }
    
    fItemsTable.Rows.Add(new object[] { 31, "MyRow" });
    fItemsTable.Rows.Add(new object[] { null, "MyRow2" });
    
    


    The first for loop adds 30 rows, the autoincrement value is 30. Then two rows are added and the first one is not using the AutoIncrement value to set the first column but the AutoIncrement value went up an AutoIncrementStep value(in this case 1) then the second row will set 32 to the autoincremented column.

    So when using ReadXml is called for a non-DiffGram XML a new set of 30 rows are added to the table and hence AuutoIncrement value goes up by 30.

    The solution for his problem is to use DiffGram when serializing writing into XML as pointed in my previous post.

    Let me know if this helps.
    Juan


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, March 1, 2010 6:36 PM
    Moderator
  • Thanks a lot, all of this information is helpful.  Re-instantiating the table each time might have been a good workaround, but I'm glad to learn about the DiffGram.  It works great, with the column always autoincrementing by 1.
    Wednesday, March 3, 2010 7:54 AM
  • Yes, it appears that this line in the diffgram XML is what clues it in: msdata:rowOrder="29". It's too bad that there's no way to set whatever property it is that controls this programmatically, at least no way that I've found. =0(
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, March 6, 2010 4:45 PM