chunking in binary (BLOB) data for generic providers

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/defaul...b;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.

Michael,

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

http://www.vb-tips.com/dbpages.aspx...46-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" <mburstin@newsgroups.nospam> schreef in bericht
news:O2tI$QP2GHA.1040@TK2MSFTNGP06.phx.gbl...
>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/defaul...b;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.

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] wrote:
> Michael,
>
> You never visited probably our (free of advertising) website.
>
> http://www.vb-tips.com/dbpages.aspx...46-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" <mburstin@newsgroups.nospam> schreef in bericht
> news:O2tI$QP2GHA.1040@TK2MSFTNGP06.phx.gbl...
>> 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/defaul...b;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.
>
>

Michael,

Did I not write something about zipping it first.?

Cor

"Michael Burstin" <mburstin@newsgroups.nospam> schreef in bericht
news:unH%23EvP2GHA.1268@TK2MSFTNGP02.phx.gbl...
> 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] wrote:
>> Michael,
>>
>> You never visited probably our (free of advertising) website.
>>
>> http://www.vb-tips.com/dbpages.aspx...46-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" <mburstin@newsgroups.nospam> schreef in bericht
>> news:O2tI$QP2GHA.1040@TK2MSFTNGP06.phx.gbl...
>>> 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/defaul...b;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.
>>

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/subscript...ault.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/subscript...rt/default.aspx.
==================================================

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

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

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> schreef in bericht
news:5$o2EPt2GHA.4916@TK2MSFTNGXA01.phx.gbl...
> 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/subscript...ault.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/subscript...rt/default.aspx.
> ==================================================
>
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>

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] wrote:
> 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/subscript...ault.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/subscript...rt/default.aspx.
> ==================================================
>
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>

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.)

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.)

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] wrote:
> 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.)
>