For ref, after some playing the best I can come up with is as follows; any
better offers?
/* Corresponding SP:
ALTER PROC mgtsave @id int, @data image, @append bit = 1
AS
DECLARE @ptr binary(16)
IF @append = 0 -- need to put in some empty data (not null) for
TEXTPTR to work
UPDATE MGT
SET data = ''
WHERE id = @id
SELECT @ptr = TEXTPTR(data)
FROM MGT
WHERE id = @id
IF @append = 1
UPDATETEXT MGT.data @ptr NULL 0 @data
ELSE
WRITETEXT MGT.data @ptr @data
*/
static void Main() {
long totalBytes = 0;
using (FileStream input = File.OpenRead(@"C:\Out.pdf")) //
random file
using (SqlConnection conn = new
SqlConnection(Properties.Settings.Default.ConKey))
using (SqlCommand cmd = conn.CreateCommand()) {
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value =
1; // just a row marker
SqlParameter paramData = cmd.Parameters.Add("@data",
System.Data.SqlDbType.Image); // the binary
SqlParameter paramAppend = cmd.Parameters.Add("@append",
System.Data.SqlDbType.Bit); // replace or append?
paramAppend.Value = false; // first pass should replace
existing
cmd.CommandText = "mgtsave";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Prepare();
const int SQL_IMAGE_BUFFER_SIZE = 8040; // optimal chunk
size
byte[] buffer = new byte[SQL_IMAGE_BUFFER_SIZE];
int bytesRead;
conn.Open();
while((bytesRead = input.Read(buffer, 0,
SQL_IMAGE_BUFFER_SIZE)) > 0) {
if(bytesRead==SQL_IMAGE_BUFFER_SIZE) { // pass the
filled buffer
paramData.Value = buffer;
} else { // didn't fill an entire buffer
byte[] smallBuffer = new byte[bytesRead];
Buffer.BlockCopy(buffer, 0, smallBuffer, 0,
bytesRead);
paramData.Value = smallBuffer;
}
cmd.ExecuteNonQuery();
paramAppend.Value = true; // subsequent calls should
append data
totalBytes += bytesRead;
}
conn.Close();
input.Close();
}
Console.WriteLine(totalBytes);
Console.ReadLine();
}
