/// <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 }