Streaming data support

Ok, I'm trying this again, as previous requests were closed due to lack of examples.

I have a need to insert a large amount of data maybe an image, file contents, large xml document, etc into the database. The challenge to a non streaming approach is that the application needs to load the entire blob into memory, then pass it to sql which causes large consumption of memory on the application side and sql server side.

I tryed implementing this with a TypeHandler but it has limitations and doesn't meet the needs.
I've included ADO.Net samples below on how streaming to/from the db is implemented. As for the comment around use case, I would have thought it was obvious. If there is a lot of data it just doesn't make sense to load it into memory then push it to sql because that consumes resources on both the application side, and the sql server side which could be completely mitigated using a streaming approach.

public class StreamTypeHandler : SqlMapper.TypeHandler<System.IO.Stream>
{
    public override void SetValue(IDbDataParameter parameter, System.IO.Stream value)
    {
        parameter.Value = value;

        SqlParameter sqlParameter;
        if ((sqlParameter = (parameter as SqlParameter)) != null)
        {
            //Set up parameter to stream to database
            sqlParameter.SqlDbType = SqlDbType.Binary;
            sqlParameter.Size = -1;          
        }
    }
        
    //Parse is too limited as the entire value is already loaded into memory,  creating a memory stream to store the contents then causes twice the memory consumption and isn't really streaming the data from the database anyhow.
    public override Stream Parse(object value)
    {
        if (value is null)
        {
            return null;
        }

        byte[] binValue;
        if ((binValue = (value as byte[])) != null)
        {
            MemoryStream stream = new MemoryStream();
            stream.Write(binValue, 0, binValue.Length);

            return stream;
        }


        throw new NotSupportedException($"Can not convert {value.GetType()} to Stream.");
    }
}

The limitation of TypeHandler is on the parse side, because by the time dapper calls the parse method it has already loaded the entire value into memory.

Here are ADO.Net samples for streaming to the database:

public void InsertBinaryStream(System.IO.Stream strData)
{
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand("INSERT INTO [StreamingSample] (BinaryData) VALUES (@bindata)", conn))
        {
            cmd.CommandTimeout = int.MaxValue;
            // Add a parameter which uses our stream
            // Size is set to -1 to indicate "MAX"
            cmd.Parameters.Add("@bindata", SqlDbType.Binary, -1).Value = strData;

            cmd.ExecuteNonQuery();
        }
    }
}

public void InsertTextStream(System.IO.Stream strText)
{
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand("INSERT INTO [StreamingSample] (TextData) VALUES (@bindata)", conn))
        {
            cmd.CommandTimeout = int.MaxValue;
            // Add a parameter which uses our stream
            // Size is set to -1 to indicate "MAX"
            using (StreamReader rdr = new StreamReader(strText))
            {
                //May not actually be NVarchar,  SQL Server supports streaming Char, NChar, NVarChar, Xml
                cmd.Parameters.Add("@bindata", SqlDbType.NVarChar, -1).Value = rdr;
                cmd.ExecuteNonQuery();
            }
        }
    }
}

public void InsertXmlStream(System.IO.Stream strData)
{
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand("INSERT INTO [StreamingSample] (XmlData) VALUES (@bindata)", conn))
        {
            cmd.CommandTimeout = int.MaxValue;
            // Add a parameter which uses our stream
            // Size is set to -1 to indicate "MAX"
            using (XmlTextReader rdr = new XmlTextReader(strData))
            {
                cmd.Parameters.Add("@bindata", SqlDbType.Xml, -1).Value = rdr;
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Here is a sample for streaming from the database. This will give you a stream object back. From there you can parse the stream into whatever object you need (XDocument, XmlDocument, TextReader, etc)

public Stream ReadBinaryStream(int iID)
{
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand("SELECT [BinaryData] FROM [StreamingSample] WHERE [id] = @id", conn))
        {
            cmd.CommandTimeout = int.MaxValue;
            cmd.Parameters.AddWithValue("id", iID);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    //Get index of column, so we don't assume the column order of the resultset
                    int iColumnIndex = reader.GetOrdinal("MyData");
                    return reader.GetStream(iColumnIndex);
                }
            }
        }
    }

    return null;
}

Start with the TypeHandler SetValue/Parse limitation described in issue #1265 and compare it with the supplied ADO.NET ExecuteNonQuery/ExecuteReader streaming samples. Define the supported write and read behavior and tests before implementation; done means large binary, text, and XML values can stream without eagerly buffering.