/// <summary>
/// Read BLOB field to stream property
/// </summary>
/// <param name="objID">Stream owner object ID</param>
/// <param name="propName">Name of stream property</param>
/// <returns>
/// Persistent stream that contains BLOB data.
/// </returns>
private PersistentStream read_blob( int objID, string propName )
{
#region debug info
#if (DEBUG)
Debug.Print( "<- ODB.imageRead( {0}, '{1}' )", objID, propName );
#endif
#endregion
// create stream to return as result
PersistentStream stream = new PersistentStream();
// get pointer to BLOB field using TEXTPTR.
DbCommand cmd = new SqlCommand( string.Format(
"SELECT @Pointer = TEXTPTR([Value]), @Length = DataLength([Value])\n" +
"FROM [dbo].[_images]\n" +
"WHERE [ObjectID] = {0} AND [Name] ='{1}'",
objID, propName) );
cmd.Connection = m_con;
cmd.Transaction = m_trans;
// setup parameters
DbParameter pointerParam = new SqlParameter("@Pointer", SqlDbType.VarBinary, 16);
pointerParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add( pointerParam );
DbParameter lengthParam = new SqlParameter("@Length", SqlDbType.Int);
lengthParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add( lengthParam );
// open connection and start new transaction if required
TransactionBegin();
try {
// get pointer and length of BLOB field
cmd.ExecuteNonQuery();
//check that BLOB field exists
if( pointerParam.Value == null ) {
throw new KeyNotFoundException( ERROR_IMAGE_IS_ABSENT );
}
// run the query.
// set up the READTEXT command to read the BLOB by passing the following
// parameters: @Pointer – pointer to blob, @Offset – number of bytes to
// skip before starting the read, @Size – number of bytes to read.
cmd = new SqlCommand(
"READTEXT [dbo].[_images].[Value] @Pointer @Offset @Size HOLDLOCK");
cmd.Connection = m_con;
cmd.Transaction = m_trans;
// temp buffer for read/write purposes
Byte[] buffer = new Byte[BUFFER_LENGTH];
// set up the parameters for the command.
cmd.Parameters.Add( new SqlParameter("@Pointer", pointerParam.Value) );
// current offset position
DbParameter offset = new SqlParameter("@Offset", SqlDbType.Int);
offset.Value = 0;
cmd.Parameters.Add( offset );
DbParameter size = new SqlParameter("@Size", SqlDbType.Int);
size.Value = 0;
cmd.Parameters.Add( size );
while( Convert.ToInt32(offset.Value) < Convert.ToInt32( lengthParam.Value ) ) {
// calculate buffer size - may be less than BUFFER_LENGTH for last block.
if( (Convert.ToInt32( offset.Value ) + buffer.GetUpperBound( 0 ))
>=
Convert.ToInt32( lengthParam.Value ) )
// setting size parameter
size.Value =
Convert.ToInt32( lengthParam.Value ) -
Convert.ToInt32( offset.Value );
else
size.Value = buffer.GetUpperBound( 0 );
// execute reader
DbDataReader dr =
cmd.ExecuteReader( CommandBehavior.SingleRow );
try {
// read data from SqlDataReader
dr.Read();
// put data to buffer
// and return size of read data
int count = Convert.ToInt32(
dr.GetBytes( 0, 0, buffer, 0, Convert.ToInt32( size.Value ) ) );
// append buffer data to stream
stream.Write( buffer, 0, count );
// increment offset
offset.Value = Convert.ToInt32( offset.Value ) + count;
} finally { dr.Dispose(); /*dispose DataReader*/}
}
// seek to begin of the stream after writing data
stream.Seek( 0, SeekOrigin.Begin );
} catch( Exception ex ) {
#region dubug info
#if (DEBUG)
Debug.Print( "[ERROR] @ ODB.imageRead: {0}", ex.Message );
#endif
#endregion
// rollback failed transaction
TransactionRollback();
throw;
}
// close connection and commit transaction if required
TransactionCommit();
#region debug info
#if (DEBUG)
Debug.Print( "<- ODB.imageRead( {0}, '{1}' )", objID, propName );
#endif
#endregion
// return filled with SQL BLOB stream
return stream;
}
///////////////////////////////////////////////////////////////////////
// SQL BLOB Section
///////////////////////////////////////////////////////////////////////
#region SQL BLOB interactions
/// <summary>
/// Saves stream property to SQL BLOB field
/// </summary>
/// <param name="objID">Stream owner object ID</param>
/// <param name="propName">Name of stream property</param>
/// <param name="stream">Stream property to save</param>
/// <param name="isnew">Flag to check property existance.</param>
private void save_blob( int objID, string propName, PersistentStream stream, bool isnew )
{
#region debug info
#if (DEBUG)
Debug.Print( "-> ODB.imageSave( {0}, '{1}' )", objID, propName );
#endif
#endregion
// open connection and start new transaction if required
TransactionBegin();
// create command text to create new or update existing record in th table
string sql = "DECLARE @_id as int; \n";
if( !isnew ) {
sql += "DELETE FROM [dbo].[_properties] WHERE [ObjectID]={0} AND [Name]='{1}'; \n" +
"UPDATE [dbo].[_images] SET @_id = [ID], [Value] = {2} \n" +
"WHERE @@ROWCOUNT = 0 AND [ObjectID] = {0} AND [Name] ='{1}'; \n";
}
sql += "IF @_id IS NULL BEGIN \n" +
" INSERT INTO [dbo].[_images] ([ObjectID], [Name], [Value]) VALUES ( {0}, '{1}', {2} ); \n" +
" SET @_id = SCOPE_IDENTITY(); \n" +
"END; \n" +
"SELECT @Pointer = TEXTPTR([Value]) FROM [dbo].[_images] WHERE [ID] = @_id; \n";
// command that executes previous sql statement
DbCommand cmd = new SqlCommand(string.Format( sql, objID, propName, (stream.Length > 0) ? "0x0" : "NULL"));
cmd.Connection = m_con;
cmd.Transaction = m_trans;
DbParameter pointerParam = new SqlParameter( "@Pointer", SqlDbType.Binary, 16 );
pointerParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add( pointerParam );
try {
// get pointer to image data
cmd.ExecuteNonQuery();
// set up UPDATETEXT command, parameters, and open BinaryReader.
cmd = new SqlCommand(
"UPDATETEXT [dbo].[_images].[Value] @Pointer @Offset @Delete WITH LOG @Bytes");
cmd.Connection = m_con;
cmd.Transaction = m_trans;
// assign value of pointer previously recieved
cmd.Parameters.Add( new SqlParameter("@Pointer", SqlDbType.Binary, 16) );
cmd.Parameters["@Pointer"].Value = pointerParam.Value;
// start insertion from begin
DbParameter offsetParam = new SqlParameter( "@Offset", SqlDbType.Int );
offsetParam.Value = 0;
cmd.Parameters.Add( offsetParam );
//delete 0x0 character
DbParameter deleteParam = new SqlParameter("@Delete", SqlDbType.Int);
deleteParam.Value = 1;
cmd.Parameters.Add( deleteParam );
DbParameter bytesParam = new SqlParameter( "@Bytes", SqlDbType.Binary );
cmd.Parameters.Add( bytesParam );
// save current stream position and seek to begin
long pos = stream.Position;
stream.Seek( 0, SeekOrigin.Begin );
// read buffer full of data and execute UPDATETEXT statement.
Byte[] buffer = new Byte[BUFFER_LENGTH];
// make first read from stream
int ret = stream.Read( buffer, 0, BUFFER_LENGTH );
// while something is read from stream, write to apend to BLOB field
while( ret > 0 ) {
// initing parameters for write
bytesParam.Value = buffer;
bytesParam.Size = ret;
// write to BLOB field
cmd.ExecuteNonQuery(); // execute iteration
deleteParam.Value = 0; // don't delete any other data
// prepare to next iteration
offsetParam.Value =
Convert.ToInt32( offsetParam.Value ) + ret;
// read from stream for next iteration
ret = stream.Read( buffer, 0, BUFFER_LENGTH );
}
// restore stream position after reading
stream.Position = pos;
} catch( Exception ex ) {
#region debug info
#if (DEBUG)
Debug.Print( "[ERROR] @ ODB.imageSave: {0}", ex.ToString() );
#endif
#endregion
// rollback failed transaction
TransactionRollback();
throw;
}
// close connection and commit transaction if required
TransactionCommit();
#region debug info
#if (DEBUG)
Debug.Print( "<- ODB.imageSave( {0}, '{1}' )", objID, propName );
#endif
#endregion
}