Manage BLOB Data in SQL Server with UPDATETEXT during OnRowAdded event

27 Jun 2006 3:36 PM

Olivier Matrot

Hello,
I would like to use UPDATETEXT in SQL Server to manage BLOB data the
efficient way. This topic is discribed in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp
The problem with the sample is that, in my case, the image column accepts
NULL value. Also, should we really insert one byte of data in the column to
successfully retreive a valid pointer ?

Anyway, the idea is to manage blob data insertion in chunck during the
OnRowAdded event (BLOB data if available is ignored during the insertion of
the row). The problem is that a this level, I need to retreive the pointer
to blob the data and a connection to be able to do the work... Because this
event is implemented in the datatable class, it has no idea of such
informations...
Any help appreciated.

27 Jun 2006 5:44 PM

Nate

You could create a stored proc:

CREATE PROC UpdateBlobField(
    @BlobID int,
    @BlobData image)
AS
    UPDATE MyTableContainingBlobData
    SET MyBlobField = @BlobData
    WHERE MyBlobID = @BlobID
GO

Then in .NET:

// assume: using System.Data.SqlClient;
//               using System.Data;

SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand command = new SqlCommand("UpdateBlobField", myConnection);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@BlobID", SqlDbType.Int).Value = id);
command.Parameters.Add(new SqlParameter("@BlobData", SqlDbType.Image).Value
= data);

conn.Open();
command.ExecuteNonQuery();
conn.Close();

Show quote

"Olivier Matrot" wrote:

> Hello,
> I would like to use UPDATETEXT in SQL Server to manage BLOB data the
> efficient way. This topic is discribed in
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp
> The problem with the sample is that, in my case, the image column accepts
> NULL value. Also, should we really insert one byte of data in the column to
> successfully retreive a valid pointer ?
>
> Anyway, the idea is to manage blob data insertion in chunck during the
> OnRowAdded event (BLOB data if available is ignored during the insertion of
> the row). The problem is that a this level, I need to retreive the pointer
> to blob the data and a connection to be able to do the work... Because this
> event is implemented in the datatable class, it has no idea of such
> informations...
> Any help appreciated.
>
>
>

28 Jun 2006 7:40 AM

Olivier Matrot

Ok found DataAdapter RowUpdated Event. Works like a charm...

Show quote

"Olivier Matrot" <olivier.matrot.rte@online.nospam> wrote in message
news:uRcTy9fmGHA.4536@TK2MSFTNGP04.phx.gbl...
> Hello,
> I would like to use UPDATETEXT in SQL Server to manage BLOB data the
> efficient way. This topic is discribed in
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp
> The problem with the sample is that, in my case, the image column accepts
> NULL value. Also, should we really insert one byte of data in the column
> to successfully retreive a valid pointer ?
>
> Anyway, the idea is to manage blob data insertion in chunck during the
> OnRowAdded event (BLOB data if available is ignored during the insertion
> of the row). The problem is that a this level, I need to retreive the
> pointer to blob the data and a connection to be able to do the work...
> Because this event is implemented in the datatable class, it has no idea
> of such informations...
> Any help appreciated.
>