Как я сериализую большой граф объекта .NET в BLOB SQL Server без создания большого буфера?

Ответ 1

Нет встроенных функций ADO.Net, чтобы обрабатывать это очень изящно для больших данных. Проблема состоит в двух вариантах:

• API не может "писать" в SQL-команду или параметры как в поток. Типы параметров, которые принимают поток (например, FileStream), принимают поток READ, что не согласуется с семантикой сериализации записи в поток. Независимо от того, каким образом вы это сделаете, у вас получится с копией всего сериализованного объекта в памяти. Плохо.
• даже если вышеописанная точка будет решена (и этого не может быть), протокол TDS и способ приема параметров SQL Server не очень хорошо работают с большими параметрами, поскольку весь запрос должен быть сначала получен, прежде чем он будет запущен в исполнение и это создаст дополнительные копии объекта внутри SQL Server.

Поэтому вам действительно нужно подходить к этому под другим углом. К счастью, есть довольно простое решение. Хитрость заключается в использовании высокоэффективного синтаксиса UPDATE .WRITE и передачи в кусках данных один за другим в серии операторов T-SQL. Это рекомендуемый метод MSDN, см. Изменение значительных (максимальных) данных в ADO.NET. Это выглядит сложным, но на самом деле тривиально делать и подключаться к классу Stream.

Класс BlobStream

Это хлеб и масло. Класс Stream, который реализует метод Write как вызов синтаксиса BLOB WRITE T-SQL. Прямо вперед, единственное, что интересно, это то, что он должен отслеживать первое обновление, потому что синтаксис UPDATE ... SET blob.WRITE(...) завершился с ошибкой в ​​поле NULL:

class BlobStream: Stream
{
    private SqlCommand cmdAppendChunk;
    private SqlCommand cmdFirstChunk;
    private SqlConnection connection;
    private SqlTransaction transaction;

    private SqlParameter paramChunk;
    private SqlParameter paramLength;

    private long offset;

    public BlobStream(
        SqlConnection connection,
        SqlTransaction transaction,
        string schemaName,
        string tableName,
        string blobColumn,
        string keyColumn,
        object keyValue)
    {
        this.transaction = transaction;
        this.connection = connection;
        cmdFirstChunk = new SqlCommand(String.Format(@"
UPDATE [{0}].[{1}]
    SET [{2}] = @firstChunk
    WHERE [{3}] = @key"
            ,schemaName, tableName, blobColumn, keyColumn)
            , connection, transaction);
        cmdFirstChunk.Parameters.AddWithValue("@key", keyValue);
        cmdAppendChunk = new SqlCommand(String.Format(@"
UPDATE [{0}].[{1}]
    SET [{2}].WRITE(@chunk, NULL, NULL)
    WHERE [{3}] = @key"
            , schemaName, tableName, blobColumn, keyColumn)
            , connection, transaction);
        cmdAppendChunk.Parameters.AddWithValue("@key", keyValue);
        paramChunk = new SqlParameter("@chunk", SqlDbType.VarBinary, -1);
        cmdAppendChunk.Parameters.Add(paramChunk);
    }

    public override void Write(byte[] buffer, int index, int count)
    {
        byte[] bytesToWrite = buffer;
        if (index != 0 || count != buffer.Length)
        {
            bytesToWrite = new MemoryStream(buffer, index, count).ToArray();
        }
        if (offset == 0)
        {
            cmdFirstChunk.Parameters.AddWithValue("@firstChunk", bytesToWrite);
            cmdFirstChunk.ExecuteNonQuery();
            offset = count;
        }
        else
        {
            paramChunk.Value = bytesToWrite;
            cmdAppendChunk.ExecuteNonQuery();
            offset += count;
        }
    }

    // Rest of the abstract Stream implementation
 }

Использование BlobStream

Чтобы использовать этот недавно созданный класс потока blob, вы подключаетесь к BufferedStream. Класс имеет тривиальный дизайн, который обрабатывает только запись потока в столбец таблицы. Я буду использовать таблицу из другого примера:

CREATE TABLE [dbo].[Uploads](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](256) NULL,
    [ContentType] [varchar](256) NULL,
    [FileData] [varbinary](max) NULL)

Я добавлю фиктивный объект для сериализации:

[Serializable]
class HugeSerialized
{
    public byte[] theBigArray { get; set; }
}

Наконец, фактическая сериализация. Сначала мы вставим новую запись в таблицу Uploads, а затем создаем BlobStream на вновь вставленном идентификаторе и вызываем сериализацию прямо в этот поток:

using (SqlConnection conn = new SqlConnection(Settings.Default.connString))
{
    conn.Open();
    using (SqlTransaction trn = conn.BeginTransaction())
    {
        SqlCommand cmdInsert = new SqlCommand(
@"INSERT INTO dbo.Uploads (FileName, ContentType)
VALUES (@fileName, @contentType);
SET @id = SCOPE_IDENTITY();", conn, trn);
        cmdInsert.Parameters.AddWithValue("@fileName", "Demo");
        cmdInsert.Parameters.AddWithValue("@contentType", "application/octet-stream");
        SqlParameter paramId = new SqlParameter("@id", SqlDbType.Int);
        paramId.Direction = ParameterDirection.Output;
        cmdInsert.Parameters.Add(paramId);
        cmdInsert.ExecuteNonQuery();

        BlobStream blob = new BlobStream(
            conn, trn, "dbo", "Uploads", "FileData", "Id", paramId.Value);
        BufferedStream bufferedBlob = new BufferedStream(blob, 8040);

        HugeSerialized big = new HugeSerialized { theBigArray = new byte[1024 * 1024] };
        BinaryFormatter bf = new BinaryFormatter();
        bf.Serialize(bufferedBlob, big);

        trn.Commit();
    }
}

Если вы отслеживаете выполнение этого простого примера, вы увидите, что нигде не создается большой поток сериализации. Образец выделит массив из [1024 * 1024], но для целей демо будет иметь что-то сериализованное. Этот код сериализуется буферизованным образом, кусок с помощью блока, с использованием рекомендуемого размера SQL Server BLOB размером 8040 байт за раз.

Ответ 2

Все, что вам нужно - это .NET Framework 4.5 и потоковая передача. Предположим, у нас есть большой файл на HDD, и мы хотим загрузить этот файл.

Код SQL:

CREATE TABLE BigFiles 
(
    [BigDataID] [int] IDENTITY(1,1) NOT NULL,
    [Data] VARBINARY(MAX) NULL
)

Код С#:

using (FileStream sourceStream = new FileStream(filePath, FileMode.Open))
{
    using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE BigFiles SET [email protected] WHERE BigDataID = @BigDataID"), _sqlConn))
    {
        cmd.Parameters.AddWithValue("@Data", sourceStream);
        cmd.Parameters.AddWithValue("@BigDataID", entryId);

        cmd.ExecuteNonQuery();
    }
}

Работает хорошо для меня. Я успешно загрузил файл 400 мб, а MemoryStream выбрал исключение, когда я попытался загрузить этот файл в память.

UPD: этот код работает в Windows 7, но не удался в Windows XP и 2003 Server.

Ответ 3

Вы всегда можете писать на SQL Server на более низком уровне, используя протокол TDS (табличный поток данных), который Microsoft использовал с самого первого дня. Они вряд ли изменят его в ближайшее время, даже если он использует SQLAzure!

Вы можете увидеть исходный код того, как это работает из проекта Mono и из проекта freetds

Проверьте tds_blob

• http://www.mono-project.com/TDS_Generic

• http://www.mono-project.com/SQLClient

• http://www.freetds.org/

Ответ 4

Как выглядит граф?

Одна проблема здесь - поток; Требование SQL 2005 - это боль, поскольку в противном случае вы могли бы напрямую написать SqlFileStream, однако я не думаю, что было бы слишком сложно написать собственную реализацию Stream, которая буферизирует байты 8040 (или несколько) и записывает это постепенно. Тем не менее, я не уверен, что это стоит этой дополнительной сложности - мне было бы очень сложно использовать файл в качестве буфера с нуля и , а затем (один раз сериализованный) цикл над файлами, вставляющими/добавляя фрагменты, Я не думаю, что файловая система повредит вашу общую производительность здесь, и это позволит вам начать писать обреченные данные - то есть вы не разговариваете с базой данных, пока не знаете, какие данные вы хотите написать. Это также поможет вам свести к минимуму время открытия соединения.

Следующая проблема - это сама сериализация. Лично я не рекомендую использовать BinaryFormatter для записи в постоянные хранилища (только для транспорта), поскольку он специфичен для реализации как в самом кодере, так и в ваших типах (то есть он хрупкий, если вы сделать невинные взгляды на ваши типы данных).

Если ваши данные могут быть представлены достаточно как дерево (а не полный график), у меня возникло бы желание попробовать буферы протокола /protobuf -net. Эта кодировка (разработанная Google) меньше, чем выход BinaryFormatter, более быстрый как для чтения, так и для записи, и основан на контрактах, а не на основе полей, поэтому вы можете надежно повторно укрепить его позже (даже если вы полностью переключите платформу).

Параметры по умолчанию означают, что он должен писать длину объекта перед каждым объектом (что может быть дорогостоящим в вашем случае), но если у вас есть вложенные списки больших (глубоких) объектов, вы можете использовать сгруппированную кодировку, чтобы избежать этой необходимости - позволяет записывать поток только в одностороннем режиме; здесь короткий простой пример с использованием сгруппированной кодировки, но если вы хотите бросить на меня более сложный сценарий, просто дайте мне знать...

using System;
using System.Collections.Generic;
using System.IO;
using ProtoBuf;
[ProtoContract]
public class Foo {
    private readonly List<Bar> bars = new List<Bar>();
    [ProtoMember(1, DataFormat = DataFormat.Group)]
    public List<Bar> Bars { get { return bars;}}
}
[ProtoContract]
public class Bar {
    [ProtoMember(1)]
    public int Id { get; set; }
    [ProtoMember(2)]
    public string Name { get; set; }
}
static class Program {
    static void Main() {
        var obj = new Foo { Bars = {
            new Bar { Id = 123, Name = "abc"},
            new Bar { Id = 456, Name = "def"},
        } };
        // write it and show it
        using (MemoryStream ms = new MemoryStream()) {
            Serializer.Serialize(ms, obj);
            Console.WriteLine(BitConverter.ToString(ms.ToArray()));
        }
    }
}

Примечание. У меня есть некоторые теории о том, как взломать формат проводки Google для поддержки полных графиков, но для этого потребуется некоторое время. О, "очень большие массивы" - для примитивных типов (а не объектов) yuo может использовать для этого "упакованную" кодировку; [DataMember(..., Options = MemberSerializationOptions.Packed)] - может быть полезно, но трудно сказать без видимости вашей модели.

Ответ 5

Почему бы не реализовать свою собственную систему:: io: stream производный класс? который позволит вам подключить его к столбцу SQL напрямую через UpdateText для записи.

например (псевдокод)

Вставить запись БД с помощью столбца blob 'initialized' (см. выше UpdateText статья)
Создайте свой тип потока/ Связывание DB с поток
Передайте поток в сериализовать вызов

Он мог бы объединиться (несколько из 8040 байт за раз, я полагаю), вызовы к нему и по каждому заполненному буферу передают это вызову DB UpdateText с соответствующим смещением.

При закрытии потока вы сбросите все оставшееся, которое не заполнило буфер полностью через UpdateText.

Аналогичным образом вы можете использовать один и тот же/подобный производный поток для чтения из столбца БД, передавая его десериализованному.

Создание производного потока не так уж много работает - я сделал это в С++/CLI для обеспечения взаимодействия с IStream - и если я могу это сделать:)... (я могу предоставить вам С++/CLI который я сделал как образец, если бы это было полезно)

Если вы поместили всю операцию (вставить начальную строку, вызовы для обновления blob через поток) в транзакцию, вы бы избегали любых потенциальных несоответствий db, если сработал шаг сериализации.

Ответ 6

Я бы пошел с файлами. В основном используйте файловую систему как промежуточное звено между SQL Server и вашим приложением.

• При сериализации большого объекта сериализуйте его в FileStream.

• Чтобы импортировать его в базу данных, инструктируйте базу данных использовать файл непосредственно при сохранении данных. Вероятно, выглядит примерно так:

  ВСТАВИТЬ В MyTable   ([MyColumn]) SELECT b.BulkColumn, FROM OPENROWSET (BULK N'C:\Path To My File\File.ext ', SINGLE_BLOB) как b

• При чтении данных попросите SQL сохранить большой столбец обратно в файловую систему в качестве временного файла, который вы удалите после десериализации его в память (нет необходимости немедленно его удалять, как можно кэшировать можно сделать здесь). Не совсем уверен, что для этой команды sql, так как я точно не знаю эксперта по БД, но я уверен, что он должен быть один.

• Повторное использование объекта FileStream для десериализации его обратно в память.

Эта процедура может быть обобщена на вспомогательный класс для ее выполнения, которая будет знать, когда удалять эти временные файлы, поскольку вы можете повторно использовать их, если вы точно знаете, что значение записи данных sql не изменилось.

Ответ 7

Обратите внимание, что с SQL Server 2012 есть также FileTable, что похоже на FILESTREAM, за исключением того, что позволяет также не транзакционный доступ.

https://msdn.microsoft.com/en-us/library/hh403405.aspx#CompareFileTable