Store DataTable in a Varbinary column in SQL and retrieve Varbinary back into DataTable

Question

I have a DataTable containing all the information I need for processing. I plan to transform it into a byte[] array and store it in a Varbinary column in SQL.

Here is my code, but I don't know how to directly convert it into bytes since I use a DataSet for it:

DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("Name");
DataColumn dc2 = new DataColumn("Age");
DataColumn dc3 = new DataColumn("Gender");

dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);

//adding of inner datatable to outer datatable
dt.Rows.Add("John", "23", "Male");
dt.Rows.Add("Gretchen", "25", "Female");
dt.Rows.Add("Jordan", "28", "Male");

DataSet ds = new DataSet();
ds.Tables.Add(dt);
string xmlString = ds.GetXml();

MemoryStream ms = new MemoryStream();
XmlDocument xml = new XmlDocument();
xml.LoadXml(xmlString);
xml.Save(ms);
byte[] xmlBytes = ms.ToArray();

Here is the code I use to store the data. I'm using Dapper in calling the Stored Procedure:

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Configuration.GetConnectionString("TEST_DB")))
            {
                var parameters = new DynamicParameters();
                parameters.Add("@Input_ByteData", xmlBytes);

                connection.Execute("dbo.uspInsertDataIntoVarbinaryColumn", parameters, commandType: CommandType.StoredProcedure);
            }

How do I store a DataTable into a Varbinary column in SQL Server and retrieve it from SQL back into a DataTable?

Answer 1

There's two separate issues here:

1. how to get a DataTable to/from a BLOB
2. how to store/fetch a BLOB from SQL Server

IMO they should be kept separate.

The first is relatively simple - it can be done a little more efficiently than your code using SerializationFormat.Binary, which requires BinaryFormatter, but:

static DataTable FromBytes(byte[] arr)
{
    using (var ms = new MemoryStream(arr))
    {
        return (DataTable)new BinaryFormatter().Deserialize(ms);
    }
}
static byte[] ToBytes(DataTable table)
{
    using (var ms = new MemoryStream())
    {
        table.RemotingFormat = SerializationFormat.Binary;
        new BinaryFormatter().Serialize(ms, table);
        return ms.ToArray();
    }            
}

Then all you need to do is send a byte[] to/from SQL-Server, which works in Dapper just by... passing a byte[] as a parameter, or reading it out as a column.

Note, however, that storing a DataTable in a column in a SQL table sounds a bit ... "inner platform".