thavs

thavs

  • NA
  • 38
  • 0

How do I save and retreive xml files in a database?

Jan 15 2009 1:26 AM

Hi

I want to store and retrieve xml files into a database. This is what i'm currently doing but when I try to read it I get an error that the root element is missing.

The field in the database where the xml file is being stored is of type varbinary(max).

 

To write :

  FileInfo fileInfo = new FileInfo(strXmlFilePath);
                long fileLength = fileInfo.Length;
                FileStream fs = new FileStream(strXmlFilePath, FileMode.Open, FileAccess.Read, FileShare.Read);

                Byte[] array = new byte[Convert.ToInt32(fileLength)];
                int iBytesRead = fs.Read(array, 0, Convert.ToInt32(fileLength));
                fs.Close();

 

                UniConnection cn = new UniConnection(DataWorld.VA3.Runtime.References.ConnectionString);
                cn.Open();

                UniCommand cmd = new UniCommand("NewFile", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                UniCommandBuilder.DeriveParameters(cmd);               
                cmd.Parameters["MessageId"].Value = xVal;
                cmd.Parameters["Message"].Value = array;
                cmd.ExecuteNonQuery();
                cn.Close();

To read:


     UniConnection cn = new UniConnection(DataWorld.VA3.Runtime.References.ConnectionString);
            UniDataReader rd = null;
            cn.Open();

            UniCommand cmd = new UniCommand("GetMessages", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            UniCommandBuilder.DeriveParameters(cmd);
            cmd.Parameters["MessageID"].Value = ID;
            try
            {
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    id = 0;                   

                    if (!rd.IsDBNull(0)) id = rd.GetInt32(0);
                  
                    if (!rd.IsDBNull(8))
                    {
                        len = rd.GetBytes(8, 0, null, 0, 0);
                        xmlArray = new Byte[len];
                        rd.GetBytes(8, 0, xmlArray, 0, (int)len);
      }
  }
     }


 Byte[] xml = xmlArray;

        MemoryStream mStream = new MemoryStream();
        mStream.Write(xml, 0, xml.Length);

        XmlTextReader TR = new XmlTextReader(mStream);

        while (TR.Read())
        {
            Response.Write(TR.Name);
        }

 

 

Hope this is clear.

 

your assistance is appreciated.

Thanks and regards,

Thav


Answers (1)