C# - Create XML From any SQL Query

Post date: Feb 14, 2010 11:58:52 AM

[System.Xml.Serialization.XmlRoot("Root")] public class SerializableDataReader : IXmlSerializable { private SqlDataReader sqlReader; // Names in the xml-file private string rowName; private string unnamedColumnName; public string UnnamedColumnName { get { return unnamedColumnName; } set { unnamedColumnName = value.Replace(" ", ""); ; } } public string RowName { get { return rowName; } set { rowName = value.Replace(" ", ""); } } public SqlDataReader SqlReader { get { return sqlReader; } set { sqlReader = value; } } System.Xml.Schema.XmlSchema IXmlSerializable.GetSchema() { throw new Exception("The method or operation is not implemented."); } void IXmlSerializable.ReadXml(System.Xml.XmlReader reader) { throw new Exception("The method or operation is not implemented."); } void IXmlSerializable.WriteXml(System.Xml.XmlWriter writer) { XmlSerializer ser = new XmlSerializer(typeof(string)); if (sqlReader != null) { if (sqlReader.HasRows) { while (sqlReader.Read()) { writer.WriteStartElement(RowName, ""); for (int i = 0; i < sqlReader.FieldCount; i++) { string elementName = sqlReader.GetName(i); if (elementName != "") writer.WriteStartElement(elementName); else writer.WriteStartElement(UnnamedColumnName); string value = sqlReader.GetValue(i).ToString(); if (value != null) writer.WriteString(value); else writer.WriteString(""); writer.WriteEndElement(); } writer.WriteEndElement(); } } } else { throw new Exception("SerializableDataReader: SqlDataReader not set"); } } public SerializableDataReader() { RowName = "OneRow"; UnnamedColumnName = "UnnamedColumn"; } } //.. //.. //.. //.. // Att anv?nda den... private void UseIt { SqlConnection conn = new SqlConnection("Data Source=10.185.20.39;Initial Catalog=home;Persist Security Info=True;User ID=;Password="); SqlCommand cmd = new SqlCommand("Select * from bookmark", conn); conn.Open(); SerializableDataReader r = new SerializableDataReader(); r.SqlReader = cmd.ExecuteReader(); FileStream fs = new FileStream("c:\\test.xml", FileMode.Truncate); XmlSerializer serializer = new XmlSerializer(typeof(SerializableDataReader)); serializer.Serialize(fs, r); fs.Close(); conn.Close(); } // Author // Peter Henell // 2006 12 07