Writing BLOBs (through C#)

[Marc Gravell]

Following up on a question somebody asked a day-or-so ago...

When /reading/ BLOBs in C# I can use sequential access and GetBytes(); fine,
sorted.

However, is there a similar equivalent for /writing/ BLOBs? *Without* having
to buffer the entire thing into a byte[] to set into param.Value? Or would
the stream-friendly way of doing this be to execute multiple commands with
the UPDATE column_Name .WRITE (@chunk, NULL, 0) syntax [or UPDATETEXT]?

Thoughts?

Marc

[Marc Gravell]

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(P roperties.Setti ngs.Default.Con Key))
using (SqlCommand cmd = conn.CreateComm and()) {
cmd.Parameters. Add("@id", System.Data.Sql DbType.Int).Val ue =
1; // just a row marker
SqlParameter paramData = cmd.Parameters. Add("@data",
System.Data.Sql DbType.Image); // the binary
SqlParameter paramAppend = cmd.Parameters. Add("@append",
System.Data.Sql DbType.Bit); // replace or append?
paramAppend.Val ue = false; // first pass should replace
existing
cmd.CommandText = "mgtsave";
cmd.CommandType = System.Data.Com mandType.Stored Procedure;
cmd.Prepare();
const int SQL_IMAGE_BUFFE R_SIZE = 8040; // optimal chunk
size
byte[] buffer = new byte[SQL_IMAGE_BUFFE R_SIZE];
int bytesRead;
conn.Open();
while((bytesRea d = input.Read(buff er, 0,
SQL_IMAGE_BUFFE R_SIZE)) > 0) {
if(bytesRead==S QL_IMAGE_BUFFER _SIZE) { // pass the
filled buffer
paramData.Value = buffer;
} else { // didn't fill an entire buffer
byte[] smallBuffer = new byte[bytesRead];
Buffer.BlockCop y(buffer, 0, smallBuffer, 0,
bytesRead);
paramData.Value = smallBuffer;
}
cmd.ExecuteNonQ uery();
paramAppend.Val ue = true; // subsequent calls should
append data
totalBytes += bytesRead;
}
conn.Close();
input.Close();
}
Console.WriteLi ne(totalBytes);
Console.ReadLin e();

}