Search code examples
c#sql-serverblobvarbinary

Push file to sql server stored proc to save in Blob, then retrieve said file

I have found a lot of references to how to save a file to a blob field (VARBINARY), but they all seem to require that the sql server have access to the file on the file system. Our web server resides on a different machine and unfortunately there's not a shared file location that I can use for my purposes.

I'd like to push the file up from my web app (C# code) as a parameter to a stored proc: @FileStream VARBINARY(MAX) and then save it that way. Is this possible to do?

likewise, I'd like to be able to fetch the file from the sql server and save it to the web server's file system later.

Any help (code samples) would be greatly appreciated.

TIA.

Solution

  • If the file contents are small enough to keep in memory, a simple parameter with a byte[] will work just fine:

    comm.Parameters.Add(new SqlParameter("@FileStream", 
    SqlDbType.VarBinary, -1) { Value = someBytes });

    When reading, again if it's small enough to not worry about streaming, you can do:

    byte[] contents = (byte[])rdr[rdr.GetOrdinal("FileStream")];