Just Code‎ > ‎

C# - Create XML From any SQL Query

posted Feb 14, 2010, 3:58 AM by Peter Henell

[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
Comments