Manage BLOB Data in SQL Server with UPDATETEXT during OnRowAdded event

  • Thread starter Thread starter Olivier Matrot
  • Start date Start date
O

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/d...resourceswhenwritingblobvaluestosqlserver.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.
 
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();
 
Back
Top