CODEWITHSUNDEEP
c#asp.net
PParmar
PParmar

Reputation: 105

XmlDocument.save() method is not store more then 100000 record in MemoryStream using c#

i try to demo more then 100000 record is not Xml Document object to save Memory Stream class inside.

this time error is raise out of memory exception.

i create demo in below.

explain code first i create xmldocument inside create vrtual xml file and i store this object in memory stream class inside

                        con.Open();
                        SqlCommand cmd = new SqlCommand("select top 1000000 * from 
                        temp ", con);

                        XmlDocument doc = new XmlDocument();
                        XmlElement root = doc.CreateElement("root");

                        using (sdr = cmd.ExecuteReader())
                        {
                            while (sdr.Read())
                            {
                                XmlElement audit = doc.CreateElement("AuditHistory");
                                XmlElement rowId = doc.CreateElement("rowid");
                                rowId.InnerText = Convert.ToString(sdr["rowid"]);
                                audit.AppendChild(rowId);
                                XmlElement rowName = doc.CreateElement("rowname");
                                rowName.InnerText = Convert.ToString(sdr["rowname"]);
                                audit.AppendChild(rowName);
                                XmlElement rowCount = doc.CreateElement("rcount");
                                rowCount.InnerText = Convert.ToString(sdr["rcount"]);
                                audit.AppendChild(rowCount);
                                root.AppendChild(audit);
                            }
                        }

                        doc.AppendChild(root);
                        con.Close();
                        MemoryStream str = new MemoryStream();
                        doc.Save(str);
            byte[] fileContent = str.ToArray();
                        str.Flush();
                        str.Close();


            doc.save(str) inside out of memory exception is generate.

            how can i resolve this issue.

Upvotes: 1

Views: 521

Answers (1)

Alexander Higgins
Alexander Higgins

Reputation: 6923

You could try directly doing the xml conversion in sql and selecting the byte array you want:

SqlCommand cmd = new SqlCommand("select cast((select top 1000000 rowid, rowname, rcount from temp AuditHistory for xml raw) as varchar(binary)", con);
byte[] data= cmd.ExecuteScalar() as byte[];

Or you can use an XmlWriter to write directly to the memory stream as creating an XmlDocument is going to take up a lot of memory and slow things down:

        XmlWriterSettings settings = new XmlWriterSettings();
        settings.Indent = true;
        MemoryStream ms = new MemoryStream();

        SqlCommand cmd = null;
        using (XmlWriter writer = XmlWriter.Create(ms, settings))
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("root");

            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    writer.WriteStartElement("AuditHistory");
                    writer.WriteStartElement("rowid");
                    writer.WriteString(Convert.ToString(sdr["rowid"]));
                    writer.WriteEndElement(); //end rowid
                    writer.WriteStartElement("rowname");
                    writer.WriteString(Convert.ToString(sdr["rowname"]));
                    writer.WriteEndElement();//end rowname
                    writer.WriteStartElement("rcount");
                    writer.WriteString(Convert.ToString(sdr["rcount"]));
                    writer.WriteEndElement();//end rcount
                    writer.WriteEndElement();//end AuditHistory
                }
            }
            writer.WriteEndElement();// end root
            writer.WriteEndDocument();
            writer.Flush();
        }

Update

Since you are trying to write the xml to a response, you can pass the Response.Output stream to the XmlWriter.Create method and it will eliminate any memory issues.

XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
using (XmlWriter writer = XmlWriter.Create(context.Response.OutputStream, settings))
{
    // write the results of the sql query to the writer...
}

Upvotes: 1

Related Questions