Solomon Rutzky (1/8/2014)
SQLKnowItAll (11/6/2013)
That is the final product. This clr is the interim while we slowly move millions of images out of the database.Hey Jared. Did you ever get your answer to this? It has been 2 months so I would like to think you have, but figured I should ask just in case.
I have written a library of SQLCLR functions, SQL# (SQLsharp)[/url], some of which do what you are asking about (reading / writing binary data to / from disk). While a large number of functions are available for free, the FileSystem ones are only in the Full (i.e. paid-for) version. It sounds like your need is (or I guess, was) more temporary so maybe not worth buying a tool, but I thought I would mention just in case.
Take care,
Solomon...
Thanks for your response. I will check those out. Here is the code I finally used:
using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Server;
public partial class ClrGetImageFile
{
private const string ConnectionString = "context connection=true";
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetImageFile(string filePath)
{
SqlPipe pipe = SqlContext.Pipe;
byte[] imageFile;
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("col1", SqlDbType.VarBinary, SqlMetaData.Max));
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
imageFile = File.ReadAllBytes(filePath);
record.SetSqlBinary(0,imageFile);
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
pipe.SendResultsEnd();
}
}
Jared
CE - Microsoft