chunking in binary (BLOB) data for generic providers

[Michael Burstin]

I think that the answer is no, but is there a generic way to insert BLOB
data into a table in a generic method via ADO.Net in blocks/chunks?
From reading all examples, questions, and other information I can find
on various web sites, newsgroup posts, etc, it appears to be no.

For example, if I have a large binary object that I want to store in a
database (ignoring the philosophical debate of whether this should be in
the database), I certainly don't want to load a 100MB of data and
transmit it across the wire in one piece.

All other database access methods (ODBC via SQLPutData, JDBC, etc)
support inserting data in chunks. Reading MSDN docs, it appears that
old ADO does.
(http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q308042)

ADO.Net doesn't appear to:
http://support.microsoft.com/kb/317016/

The only thing I see even close to it is using proprietary SQL2005
extensions for update.write.
http://msdn2.microsoft.com/en-us/library/a1904w6t.aspx
http://staceyw.spaces.live.com/Blog/cns!1pnsZpX0fPvDxLKC6rAAhLsQ!404.entry

This seems to be a step backwards from ADO and even ODBC.

 

[Cor Ligthert [MVP]]

Michael,

You never visited probably our (free of advertising) website.

http://www.vb-tips.com/dbpages.aspx?ID=0bf3f72d-b722-459d-8a46-38b5a2f7fdf0

that it is a image does not matter, although if it is a document I would
even first zip it.

(How to do that is AFAIK as well on our website)

I hope this helps,

Cor

 

[Michael Burstin]

This code, which is similar to what I see everywhere, is loading the
entire BLOB in to local memory. I am looking to not need to load the
entire object from disk into memory, and only transmitting it in blocks.

Button 1 loads abyt by:
abyt = br.ReadBytes(CInt(fs.Length))
meaning it has loaded the entire image (ie, fs.Length). Lets say that
the image is 100MB, lets say it is 1GB. I don't want to load a 1GB file
into local memory, then submit it to my database in 1 block.

What I am looking for is to not load the entire thing into memory.

For example, when selecting data, I can use something like:

int chunkSize = 500;
byte[] tempData = new byte[chunkSize];
long position = 0;
long bytesRead = 0;
while ((bytesRead = reader.GetBytes(1, position, tempData, 0,
chunkSize))>0) {
... //do something with the 500bytes I read
position += bytesRead;
}

I can use GetBytes or GetChars for selecting data passing in a starting
posistion and blocksize to the datareader, I don't have something like
this for inserting data.

I want to be able to do something like
http://msdn2.microsoft.com/en-us/library/a1904w6t.aspx "Updating Data
using UPDATE .WRITE" without the need for this "new" functionality that
exists only in MSSQL2005, yet was supported in all databases via ODBC
SQLPutData. As the example from the blog I posted below does, but
generically.

 

[Cor Ligthert [MVP]]

Michael,

Did I not write something about zipping it first.?

Cor

This code, which is similar to what I see everywhere, is loading the
entire BLOB in to local memory. I am looking to not need to load the
entire object from disk into memory, and only transmitting it in blocks.

Button 1 loads abyt by:
abyt = br.ReadBytes(CInt(fs.Length))
meaning it has loaded the entire image (ie, fs.Length). Lets say that the
image is 100MB, lets say it is 1GB. I don't want to load a 1GB file into
local memory, then submit it to my database in 1 block.

What I am looking for is to not load the entire thing into memory.

For example, when selecting data, I can use something like:

int chunkSize = 500;
byte[] tempData = new byte[chunkSize];
long position = 0;
long bytesRead = 0;
while ((bytesRead = reader.GetBytes(1, position, tempData, 0,
chunkSize))>0) {
... //do something with the 500bytes I read
position += bytesRead;
}

I can use GetBytes or GetChars for selecting data passing in a starting
posistion and blocksize to the datareader, I don't have something like
this for inserting data.

I want to be able to do something like
http://msdn2.microsoft.com/en-us/library/a1904w6t.aspx "Updating Data
using UPDATE .WRITE" without the need for this "new" functionality that
exists only in MSSQL2005, yet was supported in all databases via ODBC
SQLPutData. As the example from the blog I posted below does, but
generically.

Michael,

You never visited probably our (free of advertising) website.

http://www.vb-tips.com/dbpages.aspx?ID=0bf3f72d-b722-459d-8a46-38b5a2f7fdf0

that it is a image does not matter, although if it is a document I would
even first zip it.

(How to do that is AFAIK as well on our website)

I hope this helps,

Cor

 

[Kevin Yu [MSFT]]

Hi Cor,

I think Michael means that he need to generic way to save BLOB data into
database in pieces. This procedure includes in memory operation and
database update operations. If there is any misunderstanding, please feel
free to point me out.

Yes, as Michael knows, there isn't a generic way in ADO.NET like in ADO.
Because inserting into BLOB column is different for each DBMS. But for SQL
Server, you can use Update .Write statement to insert pieces of data. Use a
parameter in WRITE clause and you needn't load the whole file into memoery
if the source file is really big.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Cor Ligthert [MVP]]

Kevin,

I have the same idea, and you reminds me that this method should exist (in
only have read about it), thank you for interfering in this.

Cor

 

[Michael Burstin]

Yes, that is exactly what I mean. If I have a 500MB <whatever> binary
<something>, I don't want to load the entire 500MB <something> into my
client application.

Kevin, do you know of any plans in future ADO.Net revisions to support
this? It seems to be lacking. I can't imagine any databases not
supporting this on the server side, given that this existed back in the
ODBC 1.0 days!

 

[Kevin Yu [MSFT]]

You're welcome, Cor.

Hi Michael,

Currently, I don't know if there will be any change in the next version of
ADO.NET. In my opinion, I suggest you send your feedback and suggestion to
Microsoft Connect. You can find it from the following link:

http://connect.microsoft.com/

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Kevin Yu [MSFT]]

Hi Michael,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Michael Burstin]

I suppose its as resolved as possible unless the actual API changes...
I still need to post on the Connect site to suggest improvements on
the API.

 

[Kevin Yu [MSFT]]

Thank you very much for your feedback!

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)