When you deserialize an ADO.NET DataSet from XML in the DiffGram format, null values are returned for fabricated DataColumns whose values are computed by using aggregate functions with expressions that reference columns in a child DataTable. The DiffGram XML format is the default format that is used to serialize ADO.NET DataSet objects across process boundaries. ASP.NET Web Service and Microsoft .NET Remoting clients might experience this behavior when they consume (that is, deserialize) and access the data in a DataSet that a server component serializes and returns as a DiffGram.

When you deserialize a DataSet from XML in the DiffGram format, fabricated column values in a DataTable that are computed by using columns in a child DataTable are not recalculated.

Two workarounds are available:

• After the DataSet is returned, call the BeginEdit and the EndEdit methods on the DataTable.
• Serialize or persist the DataSet as XML without using the DiffGram format.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in Microsoft ADO.NET (included with the .NET Framework 1.1).

To reproduce this problem, you must complete four steps:

1. Fill the DataSet with data from two tables that can be related by using a parent-child DataRelation.
2. Add a column to the parent DataTable whose value is computed by using an aggregate function that references a column in the child DataTable.
3. Serialize this DataSet object into an XML document in the DiffGram format.
4. Deserialize the information from this serialized XML document back into a DataSet.

Steps to Reproduce the Behavior

1. In Microsoft Visual Studio .NET, create a new Microsoft Visual Basic .NET Console Application project.
2. Delete the existing code in Module1.vb.
3. Paste the following code in Module.vb:
   Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   Imports System.Data
   Imports System.Data.SqlClient
   Imports System.Xml
   
   Module Module1
   
      Sub Main()
          Try
             Dim cn As New SqlConnection _
             ("Server=.;Database=northwind;User Id=<username>;Password=<strong password>;")
             Dim OrdersDa As New SqlDataAdapter _
             ("Select * from Orders where OrderId=10248", cn)
             Dim OrderDetailsDa As New SqlDataAdapter _
             ("Select * from [Order Details]where OrderId=10248", cn)
             Dim dsOrderData As New DataSet()
   
             dsOrderData.DataSetName = "OrderData"
             OrdersDa.Fill(dsOrderData, "Orders")
             OrderDetailsDa.Fill(dsOrderData, "OrderDetails")
             cn.Close()
   
             Dim OrdersRelation As New DataRelation("OrderOrderDetails", _
             dsOrderData.Tables("Orders").Columns("OrderID"), _
             dsOrderData.Tables("OrderDetails").Columns("OrderID"))
   
             dsOrderData.Relations.Add(OrdersRelation)
             dsOrderData.Tables("Orders").Columns.Add("NumberOfItems", _
             System.Type.GetType("System.Int32"), _
             "COUNT(Child.ProductId)")
   
             Dim ds As New DataSet()
             '**********************************************
             'Persist Dataset data in REGULAR XML format and 
             'read persisted data using new Dataset object.  
             'This method of persisting and reading correctly
             'reads data values for fabricated columns.
   
             'dsOrderData.WriteXml("OrderData.xml")
             'ds.ReadXml("OrderData.xml")
             '**********************************************
   
             '**********************************************
             'The following method of persisting data using 
             'a DiffGram format and reading persisted data using
             'using a new DataSet returns null values for
             'fabricated columns.
   
             dsOrderData.WriteXml _
             ("OrderData.xml", XmlWriteMode.DiffGram)
             dsOrderData.WriteXmlSchema("OrderSchema.xsd")
             ds.ReadXmlSchema("OrderSchema.xsd")
             ds.ReadXml("OrderData.xml", XmlReadMode.DiffGram)
             '**********************************************
   
             '**********************************************
             'If you want to use the DiffGram format,
             'you can begin an edit and
             'end the edit. This will properly populate
             'the DataSet.
   
             'ds.Tables("Orders").Rows(0).BeginEdit()
             'ds.Tables("Orders").Rows(0).EndEdit()
             '**********************************************
   
             If Microsoft.VisualBasic.IsDBNull _
             (ds.Tables("Orders").Rows(0).Item("NumberOfItems")) Then
                MsgBox("NULL - Number of Items in Order No. 10248 : " & _
                ds.Tables("Orders").Rows(0).Item("NumberOfItems"))
             Else
                MsgBox("CORRECT - Number of Items in Order No. 10248 : " & _
                ds.Tables("Orders").Rows(0).Item("NumberOfItems"))
             End If
   
         Catch exp As Exception
              MsgBox("Exception Occurred : " & vbCrLf & exp.ToString)
         End Try
   
      End Sub
   
   End Module
   					

4. Change the connection string to point to the computer that is running Microsoft SQL Server.
5. Run the code. You receive a message that the value that is returned is a null value.
6. Open the Bin folder in the sample application directory and find the newly created OrderData.xml file.
7. Open the OrderData.xml file and see if the manually fabricated column that shows the count of the orders is correct. The line looks similar to the following:

   <NumberOfItems>3</NumberOfItems>
   					

   This shows that the column was calculated correctly and created correctly in the .NET code, but the value is not returned correctly. Close this XML file when you are finished.

First Workaround

1. Uncomment the following lines of code:

   'ds.Tables("Orders").Rows(0).BeginEdit()
   'ds.Tables("Orders").Rows(0).EndEdit()
   					

2. Run the code and notice that a message box appears with the correct number of items (three). If you again open the OrderDetails.xml file in the Bin folder, you see that NumberOfItems contains 3, so this value is returned correctly.

Second Workaround

Create the XML file in a format other than the DiffGram format to return the correct value.

1. Comment the following blocks of code:

   ds.Tables("Orders").Rows(0).BeginEdit()
   ds.Tables("Orders").Rows(0).EndEdit()
   					

   dsOrderData.WriteXml _
   ("OrderData.xml", XmlWriteMode.DiffGram)
   dsOrderData.WriteXmlSchema("OrderSchema.xsd")
   ds.ReadXmlSchema("OrderSchema.xsd")
   ds.ReadXml("OrderData.xml", XmlReadMode.DiffGram)
   					

2. Uncomment the following lines of code:

   'dsOrderData.WriteXml("OrderData.xml")
   'ds.ReadXml("OrderData.xml")
   					

3. Run the code and notice that a message box appears with the correct number of items (three). If you again open the OrderDetails.xml file in the Bin folder, you see that the NumberOfItems column contains 3, so this value is returned correctly. Again, if you open the OrderData.xml file in the Bin folder of the application directory, the NumberOfItems column is added to the XML file and is correctly populated.