Export SQL Server Varbinary Or Blob To Disk Using C#

Good evening Gents!

I was given a task to export 100+GB of files from an MSSQL DB of which these files are saved in a table using Varbinary/Blob column. I've made some spike applications using BCP and .NET CLR but to no avail all the spike applications don't work since I don't have full permission to the database. The only solution that work for me was using the OLE Automation Procedures. So in order to export files from Blob, I'll present the steps below using AdventureWorks2012 database.

1. Enable OLE Automation Procedures by running script below.

Use AdventureWorks2012
Go
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

2. Run the stored procedure script below. The script will return the LargePhoto of a specific product.

USE AdventureWorks2012
GO

IF OBJECT_ID('SP_AdventureWorks_Export_Blob', 'P') IS NOT NULL
DROP PROCEDURE SP_AdventureWorks_Export_Blob;
GO

CREATE PROCEDURE SP_AdventureWorks_Export_Blob 
@FILENAME nvarchar(2000), 
@ProductID int
AS
  DECLARE 
    @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

  DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
     Select LargePhoto 
            From Production.Product P
      Join Production.ProductProductPhoto PPP
        On P.ProductID = PPP.ProductID
      Join Production.ProductPhoto PP
        On PPP.ProductPhotoID = PP.ProductPhotoID
      Where P.ProductID = @ProductID

  OPEN IMGPATH 

  FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

  WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @TIMESTAMP = @FILENAME 

    PRINT @TIMESTAMP
    PRINT @SQLIMG

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
    END 

  CLOSE IMGPATH
  DEALLOCATE IMGPATH
GO

3. Make sure MSSQLServer has write permissions to the folder to where the file will be saved. Directory used is C:\Temp. Group or User Name: NT SERVICE\MSSQLSERVER. Reference: Configure File System Permissions For Database Engine.

4. Here's a simple console app program that will call the stored procedure passing in the filename w/ path and the product id.

class Program
{
  static void Main(string[] args)
  {
  ExportPhotoToDisk(@"C:\Temp\Men's Sports Shorts, XL.gif", 851);
  Console.WriteLine("Successfully exported the file to disk!!!");
  Console.ReadLine();
  }

  private static void ExportPhotoToDisk(string fileName, int productID)
  {
  string errMessage; 

  errMessage = string.Empty;

  try
  {
  using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["AdventureWorksConnectionString"]))
  {
     using (SqlCommand command = new SqlCommand("SP_AdventureWorks_Export_Blob", conn))
     {
     command.CommandTimeout = 600;
     command.CommandType = System.Data.CommandType.StoredProcedure;
     command.Parameters.Add(new SqlParameter("@FILENAME", fileName));
     command.Parameters.Add(new SqlParameter("@ProductID", productID));

     conn.Open();
     command.ExecuteNonQuery();
     }
  }
  }
  catch (Exception ex)
  {
  errMessage = ex.Message;
  }
  }
}

Reference: Fastest way to export blobs from table into individual files

Cheers! :)