I need to execute queries against several tables from the Database, and convert the result into an XML String format (not XML document object), and maintain the relationship among the records.
For example, Customer and Customer Orders should look eventually like this in a String Variable:
<customer>
<name>Some Customer</name>
<customer order>
<order id>123</order id>
<amount>444.55</amount>
</customer order>
<customer order>
<order id>456</order id>
<amount>555.55</amount>
</customer order>
</customer>
Then eventually I will pass this string to Adobe LiveCycle PDF Form as XDP (different story).
I know that the first step is to using ADO.NET to open a connection, create an Adapter, and Fill the DataSet. I can use the DataSet method getXML(), but the last time I used it, and even though I created the Relationship objects in the DataSet, it did not
give me the format I was expecting like the one mentioned above.
I need to find a easy way to convert the DataSet to XML String and force to keep the relationship between the records from different tables.
public static DataSet ConvertXMLToDataSet(string xmlData)
{
StringReader stream = null;
XmlTextReader reader = null;
try
{
DataSet xmlDS = new DataSet() ;
stream = new StringReader(xmlData);
// Load the XmlTextReader from the stream
reader = new XmlTextReader(stream);
xmlDS.ReadXml(reader);
return xmlDS;
}
catch
{
return null;
}
finally
{
if(reader != null)
reader.Close();
}
}
// Use this function to get xml string from a dataset
public static string ConvertDataSetToXML(DataSet xmlDS)
{
MemoryStream stream = null;
XmlTextWriter writer = null;
try
{
stream = new MemoryStream();
// Load the XmlTextReader from the stream
writer = new XmlTextWriter(stream, Encoding.Unicode);
// Write to the file with the WriteXml method.
xmlDS.WriteXml(writer);
int count = (int)stream.Length;
byte[] arr = new byte[count];
stream.Seek(0, SeekOrigin.Begin);
stream.Read(arr, 0, count);
UnicodeEncoding utf = new UnicodeEncoding();
return utf.GetString(arr).Trim();
}
catch
{
return String.Empty;
}
finally
{
if (writer != null)
writer.Close();
}
}
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
// Use this function to get xml string from a dataset
public static string ConvertDataSetToXML(DataSet xmlDS)
{
MemoryStream stream = null;
XmlTextWriter writer = null;
try
{
stream = new MemoryStream();
// Load the XmlTextReader from the stream
writer = new XmlTextWriter(stream, Encoding.Unicode);
// Write to the file with the WriteXml method.
xmlDS.WriteXml(writer);
int count = (int)stream.Length;
byte[] arr = new byte[count];
stream.Seek(0, SeekOrigin.Begin);
stream.Read(arr, 0, count);
UnicodeEncoding utf = new UnicodeEncoding();
return utf.GetString(arr).Trim();
}
catch
{
return String.Empty;
}
finally
{
if (writer != null)
writer.Close();
}
}
What is the difference between the function your provided "ConvertDataSetToXML()" and the .NET DataSet Method "getXML()" ?
Will this function "ConvertDataSetToXML()" respect the relationship defined among the tables in the DataSet ? Will it give me the XML String in the structure expected as per my original post ?
I think you just need to set relation.Nested equals true and add this relation into relation collection of dataset.
see my sample,
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True");
SqlDataAdapter sda = new SqlDataAdapter("select menuid ,text,description,parentid from menu ", con);
DataSet ds = new DataSet();
sda.Fill(ds);
ds.Tables[0].TableName = "menu";
ds.DataSetName = "menus";
DataRelation relation = new DataRelation("parentchild", ds.Tables["menu"].Columns["menuid"], ds.Tables["menu"].Columns["parentid"], true);
relation.Nested = true;
ds.Relations.Add(relation);
this.xmlDataSource.Data = ds.GetXml();
Samu Zhang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.
Marked as answer by tarekahf on Jun 24, 2008 08:34 AM
I think you 100% correct. I will try it and confirm.
This will save a lot of coding effort for me especially when I am passing huge data to Adobe LiveCycle Form using XDP format. As of now, I am using For Loop to build the XML String, and it is really complex.
tarekahf
Member
143 Points
272 Posts
How to convert DataSet to XML and respect the Relationships.
Jun 20, 2008 10:20 PM|LINK
I need to execute queries against several tables from the Database, and convert the result into an XML String format (not XML document object), and maintain the relationship among the records.
For example, Customer and Customer Orders should look eventually like this in a String Variable:
<customer>
<name>Some Customer</name>
<customer order>
<order id>123</order id>
<amount>444.55</amount>
</customer order>
<customer order>
<order id>456</order id>
<amount>555.55</amount>
</customer order>
</customer>
Then eventually I will pass this string to Adobe LiveCycle PDF Form as XDP (different story).
I know that the first step is to using ADO.NET to open a connection, create an Adapter, and Fill the DataSet. I can use the DataSet method getXML(), but the last time I used it, and even though I created the Relationship objects in the DataSet, it did not give me the format I was expecting like the one mentioned above.
I need to find a easy way to convert the DataSet to XML String and force to keep the relationship between the records from different tables.
Any help will be greatly appreciated.
Tarek.
ramireddyind...
All-Star
31358 Points
4579 Posts
Re: How to convert DataSet to XML and respect the Relationships.
Jun 21, 2008 05:16 AM|LINK
Is it what you required?
public static DataSet ConvertXMLToDataSet(string xmlData)
{
StringReader stream = null;
XmlTextReader reader = null;
try
{
DataSet xmlDS = new DataSet() ;
stream = new StringReader(xmlData);
// Load the XmlTextReader from the stream
reader = new XmlTextReader(stream);
xmlDS.ReadXml(reader);
return xmlDS;
}
catch
{
return null;
}
finally
{
if(reader != null)
reader.Close();
}
}
// Use this function to get xml string from a dataset
public static string ConvertDataSetToXML(DataSet xmlDS)
{
MemoryStream stream = null;
XmlTextWriter writer = null;
try
{
stream = new MemoryStream();
// Load the XmlTextReader from the stream
writer = new XmlTextWriter(stream, Encoding.Unicode);
// Write to the file with the WriteXml method.
xmlDS.WriteXml(writer);
int count = (int)stream.Length;
byte[] arr = new byte[count];
stream.Seek(0, SeekOrigin.Begin);
stream.Read(arr, 0, count);
UnicodeEncoding utf = new UnicodeEncoding();
return utf.GetString(arr).Trim();
}
catch
{
return String.Empty;
}
finally
{
if (writer != null)
writer.Close();
}
}
tarekahf
Member
143 Points
272 Posts
Re: How to convert DataSet to XML and respect the Relationships.
Jun 21, 2008 09:26 AM|LINK
What is the difference between the function your provided "ConvertDataSetToXML()" and the .NET DataSet Method "getXML()" ?
Will this function "ConvertDataSetToXML()" respect the relationship defined among the tables in the DataSet ? Will it give me the XML String in the structure expected as per my original post ?
Regards ...
Tarek.
Samu Zhang -...
All-Star
62163 Points
6101 Posts
Re: How to convert DataSet to XML and respect the Relationships.
Jun 24, 2008 07:49 AM|LINK
Hi tarekahf ,
I think you just need to set relation.Nested equals true and add this relation into relation collection of dataset.
see my sample,
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True"); SqlDataAdapter sda = new SqlDataAdapter("select menuid ,text,description,parentid from menu ", con); DataSet ds = new DataSet(); sda.Fill(ds); ds.Tables[0].TableName = "menu"; ds.DataSetName = "menus"; DataRelation relation = new DataRelation("parentchild", ds.Tables["menu"].Columns["menuid"], ds.Tables["menu"].Columns["parentid"], true); relation.Nested = true; ds.Relations.Add(relation); this.xmlDataSource.Data = ds.GetXml();Samu Zhang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.
tarekahf
Member
143 Points
272 Posts
Re: How to convert DataSet to XML and respect the Relationships.
Jun 24, 2008 08:29 AM|LINK
Thanks a lot Samu Zhang,
I think you 100% correct. I will try it and confirm.
This will save a lot of coding effort for me especially when I am passing huge data to Adobe LiveCycle Form using XDP format. As of now, I am using For Loop to build the XML String, and it is really complex.
Tarek.