If you add a column to a DataTable with ColumnName set as TableName, and then save it as Xml using DataSet.WriteXml and then load it from Xml using DataSet.ReadXml, you’ll be surprised by that .Net automatically removed the column and added two more columns with this naming:
Assume that the TableName is “MyName” so the ColumnName is also “MyName”, after saving and loading you’ll lose “MyName” column and all the data in all rows, but you’ll have two new columns “MyName_Id” and “MyName_Id_0” both with data type set to Int32.
So the solution would be either to change the column name or pass a second parameter to WriteXml method as “XmlWriteMode.WriteSchema” so the schema would be written to the Xml and then in the load time .Net do not try to be the genius.
So here is the sample code:
DataSet ds = new DataSet(); DataTable dt = ds.Tables.Add("Table1"); dt.Columns.Add("Column1"); dt.Columns.Add("Column2"); dt.Columns.Add("Table1"); DataRow dr = dt.NewRow(); dt.Rows.Add(dr); dr["Column1"] = "d1"; dr["Column2"] = "d2"; dr["Table1"] = "d3"; ds.WriteXml("c:\\1.xml", XmlWriteMode.WriteSchema); ds = new DataSet(); ds.ReadXml("c:\\1.xml");