How to efficiently stream an object graph as xml to sql server

Question

I have an object graph that I need to serialize to xml and save to a Sql Server row. I have a table with an xml data type and I've been using the SqlXml datatype, providing it a memorystream.

This works well for the most part. However, if my object graph is especially big (~200 meg in size), I get an OutOfMemoryException. What is the most efficient way to serialize an object graph as xml and stream it to Sql Server? Here is my code right now:

using (MemoryStream ms = new MemoryStream())
{
    using (SqlConnection connection = new SqlConnection(this.Settings.GetConnectionString()))
    {
 connection.Open();

 SqlCommand command = connection.CreateCommand();

 command.CommandText = "INSERT_MESSAGE";
 command.CommandType = System.Data.CommandType.StoredProcedure;

 SqlParameter param = command.CreateParameter();
 param.ParameterName = "@PARTITION_ID";
 param.Direction = System.Data.ParameterDirection.Input;
 param.DbType = System.Data.DbType.Int32;
 param.Value = this.PartitionId;
 command.Parameters.Add(param);

 param = command.CreateParameter();
 param.ParameterName = "@MESSAGE";
 param.Direction = System.Data.ParameterDirection.Input;
 param.DbType = System.Data.DbType.Xml;

 XmlSerializer xs = new XmlSerializer(typeof(MessageContext));
 xs.Serialize(ms, message);
 param.Value = new System.Data.SqlTypes.SqlXml(ms);

 command.Parameters.Add(param);

 param = command.CreateParameter();
 param.ParameterName = "@RETURN_VALUE";
 param.Direction = System.Data.ParameterDirection.ReturnValue;
 param.DbType = System.Data.DbType.Int32;
 command.Parameters.Add(param);

 command.ExecuteNonQuery();

 if ((int)command.Parameters["@RETURN_VALUE"].Value == 1)
     throw new IntegrationSystemException("Unknown error encountered while selecting the record count");
 else
 {
     if (log.IsInfoEnabled)
  log.InfoFormat("Saved message [{0}]", message.MessageId);
 }
    }
}

I am considering using a "hybridstream" like found here: HybridStream. This basically allocates a MemoryStream to some arbitrary limit, when that limit is hit, it creates a FileStream for the surplus.

Optimization help would be appreciated.

Answer 1

For a large object graph, try first serializing to a file on disk, then passing the FileStream to SqlXmL.