How to effectively sink object graph as xml on sql server

Question

I have an object graph that I need to serialize in xml and save in the Sql server line. I have a table with the xml data type, and I use the SqlXml data type, giving it a memystream.

This works for the most part. However, if my object graph is especially large (~ 200 megabytes), 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 hybrid stream, for example here: HybridStream . This basically allocates a MemoryStream for some arbitrary limit, when this limit is hit, it creates a FileStream for the excess.

.

Answer 1

, FileStream SqlXmL.