CODEWITHSUNDEEP
.netsql-serversql-server-2008streamblob
LosManos
LosManos

Reputation: 7692

Store blob in SQL Server without reading the blob into memory

What I try to accomplish is to read from a stream directly into a field/record in SQL Server.

It isn't hard to save a file to SQL Server, there are plenty of examples on the interweb;
but I can't find any example that doesn't read the whole file into memory first.

e.g.: 

byte[] data = BinaryReader.ReadBytes((int)filestream.Length);
cmd.Parameters.Add("@Data", SqlDbType.Image, data.Length).Value = data;

or something similar.

I could use SQL Server's own commands but then I'd have to fiddle with rights for SQL Server to read from another machine/folder and I'd hope to keep it all in .NET business layer instead.

Upvotes: 4

Views: 608

Answers (3)

LosManos
LosManos

Reputation: 7692

I used these resources:

http://lennilobel.wordpress.com/2011/01/23/sql-server-2008-filestream-part-3-of-3-using-the-opensqlfilestream-api-2/ <- More or less exact dotnet code to copy.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/03/filestream-configuration-and-setup-changes-in-sql-server-2008-february-ctp.aspx <- setup of filestream in sqlserver, mentions a bug and references a solution

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/06/09/enabling-filestream-post-sql2008-setup-a-known-issue-in-sql-config-manager.aspx <- the solution to the bug, a script to run

http://sqlsrvengine.codeplex.com/wikipage?title=FileStreamEnable&referringTitle=Home&ProjectName=sqlsrvengine <- the script

( http://www.mssqltips.com/tip.asp?tip=1489 // A bit too old, has some wrong names )

to get me through.
I exchanged the functionality to push the data right to the web browser (or reading to RAM) for saving it to file with this code:

    private static void CopyFile(string sourcePath, byte[] transToken, string targetPath, Guid uid)
    {
        //  Should we use a buffer here?  I mean - does this firehose use memory according to the file size?  If so - use a buffer and copy piece by piece.
        SafeFileHandle handle = NativeSqlClient.GetSqlFilestreamHandle(sourcePath, NativeSqlClient.DesiredAccess.Read, transToken);
        using (var fileSource = new FileStream(handle, FileAccess.Read))
        {
            using (var fileDest = new FileStream(Path.Combine(targetPath, uid.ToString()), FileMode.CreateNew, FileAccess.Write))
            {
                fileSource.CopyTo(fileDest);
                fileDest.Flush();
                fileDest.Close();
            }
            fileSource.Close();
        }
    }

If you use the code from LLobel's site above you'll find out where to insert call this method.

Also note that one cannot reorder the fields through drag'n'drop in SQLServermanagementstudio; the filestream attribute on the field gets dropped.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175776

The only way your going to be able to do that is to loop reading chunks from your stream & issuing an update on the underlying table using UPDATE .WRITE or a FILESTREAM.

This should not require any more permissions than a regular update/insert.

Upvotes: 1

Oded
Oded

Reputation: 499002

The BLOB has to end up in the memory of the process that passes the data to SQL Server, as the whole of it needs to be passed in on the call.

You can't stream a query to SQL so it will read the parameter as a stream - hence, you need to whole file in memory.

No way around that.

Upvotes: 0

Related Questions