How to use the SqlParameter.Offset property and ADO.NET to store a binary file in SQL Server 2012

Question

I am currently learning how to store files without using FILESTREAM attribute on varchar(max) in SQL Server, because I dont need to store very large binary files. Despite that I want to store the binary data by small chunks of it. What I found was the SqlParameter.Offset Property. Here is some info about it from MSDN:

"The Offset property is used for client-side chunking of binary and string data. For example, in order to insert 10MB of text into a column on a server, a user might execute 10 parameterized inserts of 1MB chunks, shifting the value of Offset on each iteration by 1MB."

It sounds like it is exactly what I need, but I am confused about how to use it. I created a very simple Table called BinaryFilesTable (Id, FileData).The Id is the primary key and FileData is varbinary(max). Here is my code so far.

public static void Main(string[] args)
    {
        var fileBytes = File.ReadAllBytes("../../image.jpg");

        const string connectionString = @"Server=.; Database=TestDb; Integrated Security=true";
        SqlConnection connection = new SqlConnection(connectionString);

        connection.Open();
        using (connection)
        {
            string commandText = "INSERT INTO BinaryFilesTable(FileData) VALUES (@binaryFileData)";

            SqlCommand command = new SqlCommand(commandText, connection);

            SqlParameter commandParameter = new SqlParameter();
            commandParameter.ParameterName = "@binaryFileData";
            commandParameter.Value = fileBytes;
            commandParameter.SqlDbType = SqlDbType.VarBinary;
            commandParameter.Size = -1; // VarBinary becomes VarBinary(max) if size is set to -1.
            //commandParameter.Offset = ??? How to use it ???

            command.Parameters.Add(commandParameter);

            command.ExecuteNonQuery();
        }
    }

With this code I managed to store the data in the table, but I am not exactly sure how it works. As I understand it, in the moment the binary data is stored all at once and that is the reason I want to figure out how to use this Offset property. I spent several hours searching for a tutorial but had no success.

EDIT : If I set some value to the offset I get the following exception:

System.ArgumentException: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.

Bellwether

Probably because it's just commandParameter.Offset = 0. Just to be clear you mention VarChar(max) but based on your code, I'd have expected VarBinary(max)? So given chunks 1M in size Offset would go up in intervals of 1M. Nevery tried this, and I thank you for asking the question, as I learnt something new. – Leandro

You are correct, I will edit it. It's VarBinary(max). The offset is 0 by default. Thank you for your comment. – Bellwether

So you could probably answer this yourself now. Worth doing in my opinion. Of course you don't want to ReadAllBytes, you want to read a 1M chunk of the file. – Leandro

I will have to edit my post again. I forgot to mention that if I try to set some value to the offset I get an exception. – Bellwether

Ah... First query is an insert subsequent queries will be updates.So you insert the first meg and update the record for the following 9. Though it should work in any order – Leandro

Based on your edit and intenbt commandParameter.size should be 1M and you should get 1M of your file for The binary data file param – Leandro

That's an educated guess though... – Leandro

Thank you very much. I will try it. I will play around with it and will report how it went. – Bellwether

What do you want to achieve? Why not insert in one go? – Wretched

Answer 1

The Offset property is used for client-side chunking of binary and string data.

For example, in order to insert 10MB of text into a column on a server, a user might execute 10 parameterized inserts of 1MB chunks, shifting the value of Offset on each iteration by 1MB.

Offset specifies the number of bytes for binary types, and the number of characters for strings. The count of strings does not include the terminating character.

//Simple Use offset
parameter.Offset = 3;

Brownnose

There is no concept of a terminating character in ADO.NET. You seem to come from a C background. – Wretched