Сохраните DataTable в столбце Varbinary в SQL и извлеките Varbinary обратно в DataTable

Question

У меня есть DataTable, содержащая всю информацию, необходимую для обработки. Я планирую преобразовать его в массив byte[] и сохранить его в столбце Varbinary в SQL.

Вот мой код, но я не знаю, как напрямую преобразовать его в байты, так как я использую для этого DataSet:

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();

Вот код, который я использую для хранения данных. Я использую Dapper при вызове хранимой процедуры:

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);
            }

Как сохранить DataTable в столбце Varbinary в SQL Server и извлечь его из SQL обратно в DataTable?

Answer 1

Здесь есть две отдельные проблемы:

1. как получить DataTable в / из BLOB
2. Как сохранить / получить большой двоичный объект из SQL Server

ИМО они должны храниться отдельно.

Первое относительно просто - это может быть сделано немного более эффективно, чем ваш код, используя SerializationFormat.Binary, что требует BinaryFormatter, но:

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();
    }            
}

Тогда все, что вам нужно сделать, это отправить byte[] в / из SQL-сервера, который работает в Dapper просто... передавая byte[] в качестве параметра, или чтение его в виде столбца.

Обратите внимание, однако, что хранение DataTable в столбце таблицы SQL звучит немного... "внутренняя платформа".