locked
why is the seed ignored when not using binary serialization (and vice-versa) ? RRS feed

  • Question

  • I had this great idea to reduce the our company's bandwidth as it regulary sends 10MB DataSets over the wire; I would use the binary serialization on the DataSet. Alas, that simple change broke our product across the board. It ends up that when you don't serialize in binary form, adding new rows seeds with MAX(Primary Key) + 1 regardless of AutoIncrementSeed. I'm a little confused on that behavior, but our product was relying on this feature. (I also think it's a bug that SqlAdapter.Fill doesn't set the seed correctly on DB views, but that's a separate issue.) Is this expected behavior? Is there someway to make the post-binary-serialization seed behave the same way? Here's my code to repro the problem. You can uncomment the RemotingFormat line to cause the crash. Look at ds2 in the debugger to see the different PK seed behaviors.

    using System;
    using System.Data;
    using System.Diagnostics;
    using System.IO;
    using System.Runtime.Serialization.Formatters.Binary;
    using System.Windows.Forms;
    
    namespace TestDbSerialize
    {
    	class Program
    	{
    		static void Main(string[] args)
    		{
    			var t1 = new DataTable("go1");
    			var pk = t1.Columns.Add("pk", typeof(int));
    			t1.Columns.Add("dat", typeof(string));
    			t1.Rows.Add(3, "howdy");
    			t1.Constraints.Add(new UniqueConstraint("pk_cons", pk, true));
    
    			pk.AutoIncrement = true;
    			pk.AutoIncrementSeed = 2;
    			pk.AutoIncrementStep = 1;
    
    			var ds = new DataSet();
    			// uncomment this to cause the problem:
    			//ds.RemotingFormat = SerializationFormat.Binary;
    			ds.Tables.Add(t1);
    
    			byte[] data;
    			var formatter = new BinaryFormatter();
    			using (var ms = new MemoryStream())
    			{
    				formatter.Serialize(ms, ds);
    				ms.Flush();
    				data = ms.ToArray();
    			}
    
    			DataSet ds2;
    			using (var ms = new MemoryStream(data, false))
    			{
    				ds2 = formatter.Deserialize(ms) as DataSet;
    			}
    
    			var bs = new BindingSource(ds2, "go1");
    			var n1 = bs.AddNew();
    			((DataRowView)n1).Row["dat"] = "d1";
    			((DataRowView)n1).Row.EndEdit();
    			((DataRowView)n1).Row.Table.Rows.Add(((DataRowView)n1).Row);
    
    			var n2 = bs.AddNew();
    			((DataRowView)n2).Row["dat"] = "d2";
    			((DataRowView)n2).Row.EndEdit();
    			((DataRowView)n2).Row.Table.Rows.Add(((DataRowView)n2).Row);
    
    			Console.WriteLine("Successfully added two rows");
    			if (Debugger.IsAttached) Console.ReadKey();
    		}
    	}
    }
    
    

    Tuesday, June 7, 2011 3:27 PM

Answers

  • I got to the bottom of this issue.

    First, if you add data to a DataTable before you set AutoIncrement to true on that table's PK column, AutoIncrement will never work correctly after deserializing from binary. That's Microsoft's bug that is supposedly in progress.

    Second, when using SqlDataAdapter on a view that contains an OUTER JOIN, the schema loader will fail to create the PK and associated IDENTITY constraints on the DataTable. See KB817183 and elsewhere on the web.

    I was relying on the AddWithKey feature of SqlDataAdapter. On my views with an OUTER JOIN it failed to create the PK, and I had some code to manually add the PK after the SqlDataAdapter.Fill call. That plan won't work. I will change it to call FillSchema first, then check for the PK and potentially add it manually before calling Fill.

    I also wrote me a little test app to tell me which of my views struggle with the PK detection. I've gone through and fixed them. It seems that you can usually get the PK auto-detectable by adding the correct PK column (if it's not there) and changing columns that are used in JOIN and WHERE clauses to bring back the data from the primary table rather than the child table.


    • Marked as answer by Brannon Tuesday, August 2, 2011 3:08 PM
    • Edited by Brannon Tuesday, January 24, 2012 4:48 PM
    Tuesday, August 2, 2011 3:07 PM

All replies

  • Hi Brannon,

    Thank you for posting.

    I made a test for your code snippets. I changed SerializationFormat.Xml instead of Binary. It works well. It looks that your question is related with serialization format for a dataset. Please check here for more information.  http://msdn.microsoft.com/en-us/library/system.data.serializationformat.aspx

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 10, 2011 9:12 AM
  • Yes, I'm aware that XML serialization works fine in the above code -- at three times the output size! I don't have any questions about a format specifically. To restate the question, why do the two formats cause the DataSet to behave differently after it has been reinstantiated?

    Friday, June 10, 2011 7:55 PM
  • Hello again,

    I tested your original code snippets, the error information is “Column ‘pk’ is constrained to be unique. Value ‘3’ is already present. From this error information, you don’t allow to insert duplicated into t1 because you have set the unique constraint for this column. If you remove this constraint, everything looks OK. But XML format is case-sensitive and didn’t support overlapping tags in one XML file.

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 13, 2011 8:53 AM
  • Ya lost me there. You're saying this has something to do with the case-sensitive nature of XML? The constraint is there in both serialization methods and absolutely necessary. The question is why the seed behaves differently, not what underlying mechanism is triggering the exception.
    Monday, June 13, 2011 5:12 PM
  • Hello again Brannon,

    Sorry for my misunderstood. For your case, first of all you created a unique constraint for a column, so the value of this column cannot be duplicated. The AutoIncrementStep property worked well in your project, but you set AutoIncrementSeed was 2. It can inserted 2 to d1. When it inserted 3 into d2, it checked that this value was hosted in it. If you modify AutoIncrementSeed from 2 to 4 (any greater than 3 value), everthing looks good.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 15, 2011 10:12 AM
  • Yes, I recognize that the seed was poorly chosen. However, it works fine when using XML serialization. Why?
    Wednesday, June 15, 2011 2:32 PM
  • An additional question: what is the proper way to initialize the seed in a DataSet when filling it from a SQL Server view?
    Friday, July 8, 2011 5:20 PM
  • Hi Brannon,

     

    Can you try setting up the DataTable schema before adding data? In this case set the autoincrement properties before you add data to the table, I tried this workaround and I don't see the problem anymore and the end data result is the same for Xml and Binary.

    Please let me know if this helps. Regards

    Juan Vazquez

     

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 12, 2011 8:44 PM
  • That's fascinating. Moving the t1.Rows.Add down four lines and it works. Interpretation: setting up seed info has to be done before there is data in order for the binary serialization to exhibit the same bug (er um, behavior) as the XML serializer. Unfortunately, I'm still not sure how this applies to a real-world SqlDataAdapter example (where the adapter both fills the data and sets the seed).
    Tuesday, July 12, 2011 11:04 PM
  • SqlDataAdapter shouldn't have a problem as it should add first the tables schema and then will add data. If Fill is used to get the data and then set the seed it's very likely the same behavior described above will occur.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 14, 2011 4:08 AM
  • I got to the bottom of this issue.

    First, if you add data to a DataTable before you set AutoIncrement to true on that table's PK column, AutoIncrement will never work correctly after deserializing from binary. That's Microsoft's bug that is supposedly in progress.

    Second, when using SqlDataAdapter on a view that contains an OUTER JOIN, the schema loader will fail to create the PK and associated IDENTITY constraints on the DataTable. See KB817183 and elsewhere on the web.

    I was relying on the AddWithKey feature of SqlDataAdapter. On my views with an OUTER JOIN it failed to create the PK, and I had some code to manually add the PK after the SqlDataAdapter.Fill call. That plan won't work. I will change it to call FillSchema first, then check for the PK and potentially add it manually before calling Fill.

    I also wrote me a little test app to tell me which of my views struggle with the PK detection. I've gone through and fixed them. It seems that you can usually get the PK auto-detectable by adding the correct PK column (if it's not there) and changing columns that are used in JOIN and WHERE clauses to bring back the data from the primary table rather than the child table.


    • Marked as answer by Brannon Tuesday, August 2, 2011 3:08 PM
    • Edited by Brannon Tuesday, January 24, 2012 4:48 PM
    Tuesday, August 2, 2011 3:07 PM