Writing BLOBs (through C#)

  • Thread starter Thread starter Marc Gravell
  • Start date Start date
M

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
 
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();

}
 
Back
Top