The Lob Stream Framework, Part 2

#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
#endregion
/*
 * LOBSTREAM FRAMEWORK
 * (c) Peter De Jonghe, Incalibration 2007
 * Check out the website for the latest updates of the framework.
 * http://www.incalibration.com
 * 
 * The LobStream Framework code is free. 
 * You may use it as you wish, with the sole restriction that you maintain the copyright notice here included. 
 * 
 * Disclaimer:
 * The LobStream Framework is provided "as is" without warranty of any kind, including, but not limited to, 
 * the implied warranties of merchantability and fitness for a particular purpose. 
 * The user assumes all responsibility for the accuracy and suitability of this program for a specific application. 
 * In no event will Incalibration, nor any of the authors of this code, be liable for any damages, including lost profits, lost savings, 
 * or other incidental or consequential damages arising from the use of or the inability to use this program. 
 * 
 * */
namespace LobStream
{
    /// <summary>
    /// The SqlLobStreamFromImage is a class derived from LobStream, and manages the exchange of lobs
    /// with a SQL Server database that stores the lobs in an image data type column.
    /// </summary>
    public class SqlLobStreamFromImage : SqlLobStream
    {
        #region Member variables & Properties
        #region Read data command ...
        private SqlCommand _readData;
        /// <summary>
        /// 
        /// </summary>
        public override SqlCommand ReadData
        {
            get
            {
                if (_readData == null)
                {
                    string sqlReadData = string.Format("select {1} from {0} where cast({2} as varchar(50)) = cast('{3}' as varchar(50))",
                        this.TableName, this.LobColumn, this.KeyColumn, this.LobID);
                    this._readData = new SqlCommand(sqlReadData, (SqlConnection)this.Connection);
                }
                return _readData;
            }
            set
            {
                _readData = value;
            }
        }
        #endregion
        #region Read data chunk command ...
        private SqlCommand _readDataChunk;
        /// <summary>
        /// 
        /// </summary>
        public SqlCommand ReadDataChunk
        {
            get
            {
                if (_readDataChunk == null)
                {
                    string sqlReadDataChunk = string.Format("READTEXT [{0}].[{1}] @pointer @offset @size", this.TableName, this.LobColumn);
                    this._readDataChunk = new SqlCommand(sqlReadDataChunk, (SqlConnection)this.Connection);
                    this._readDataChunk.Parameters.Add("@pointer", SqlDbType.VarBinary, 16);
                    this._readDataChunk.Parameters.Add("@offset", SqlDbType.Int);
                    this._readDataChunk.Parameters.Add("@size", SqlDbType.Int);
                }
                return _readDataChunk;
            }
            set
            {
                _readDataChunk = value;
            }
        } 
        #endregion
        #region Write data command ...
        private SqlCommand _insertData;
        private SqlCommand _appendData;
        /// <summary>
        /// 
        /// </summary>
        public SqlCommand WriteData
        {
            get
            {
                string sqlWriteData = string.Empty;
                if (this.Position == 0)
                {
                    if (this._insertData == null)
                    {
                        //WRITETEXT overwrites any existing data in the column it affects.
                        sqlWriteData = string.Format("WRITETEXT [{0}].[{1}] @pointer WITH LOG @bytes", this.TableName, this.LobColumn);
                        // Replace possible double square brackets ...
                        sqlWriteData = sqlWriteData.Replace("[[", "[");
                        sqlWriteData = sqlWriteData.Replace("]]", "]");
                        this._insertData = new SqlCommand(sqlWriteData, (SqlConnection)this.Connection);
                        this._insertData.Parameters.Add("@pointer", SqlDbType.VarBinary, 16);
                        this._insertData.Parameters.Add("@bytes", SqlDbType.Image);
                    }
                    return this._insertData;
                }
                else
                {
                    if (this._appendData == null)
                    {
                        // An offset value of null and a delete|length value of null results in appending the new data
                        // to the existing data value.
                        sqlWriteData = string.Format("UPDATETEXT [{0}].[{1}] @pointer null null WITH LOG @bytes", this.TableName, this.LobColumn);
                        // Replace possible double square brackets ...
                        sqlWriteData = sqlWriteData.Replace("[[", "[");
                        sqlWriteData = sqlWriteData.Replace("]]", "]");
                        this._appendData = new SqlCommand(sqlWriteData, (SqlConnection)this.Connection);
                        this._appendData.Parameters.Add("@pointer", SqlDbType.VarBinary, 16);
                        this._appendData.Parameters.Add("@bytes", SqlDbType.Image);
                    }
                    return this._appendData;
                }
            }
        }

        #endregion
        #region Get text pointer ...
        private SqlCommand _getTextPointer = null;
        private SqlParameter _textPointer = null;
        /// <summary>
        /// 
        /// </summary>
        public SqlParameter TextPointer
        {
            get
            {
                if (this._getTextPointer == null)
                {
                    //You cannot obtain a text pointer when you insert NULL text. To obtain a text pointer, you must use the update statement
                    //to set the text to NULL. At that time, SQL Server allocates an 84-byte root structure for the NULL value.
                    //source: http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true
                    string sqlGetTextPointer = string.Empty;
                    sqlGetTextPointer += "declare @valid int;";
                    sqlGetTextPointer += "select @valid = textvalid('{1}.{0}', textptr({0})) from {1} where cast({2} as varchar(50)) = cast('{3}' as varchar(50))";
                    sqlGetTextPointer += "if(@valid = 0)update {1} set {0} = null where cast({2} as varchar(50)) = cast('{3}' as varchar(50))";
                    sqlGetTextPointer += "select @pointer = textptr({0}) from {1} where cast({2} as varchar(50)) = cast('{3}' as varchar(50))";

                    sqlGetTextPointer = string.Format(sqlGetTextPointer, this.LobColumn, this.TableName, this.KeyColumn, this.LobID);

                    this._getTextPointer = new SqlCommand(sqlGetTextPointer, (SqlConnection)this.Connection);
                    this._textPointer = this._getTextPointer.Parameters.Add("@pointer", SqlDbType.VarBinary, 16);
                    this._textPointer.Direction = ParameterDirection.Output;
                }
                this._getTextPointer.ExecuteNonQuery();
                return this._textPointer;
            }
            set
            {
                this._textPointer = value;
            }
        } 
        #endregion
        #endregion
        #region Read, Write
        /// <summary>
        /// 
        /// </summary>
        /// <param name="buffer"></param>
        /// <param name="offset"></param>
        /// <param name="count"></param>
        /// <returns>The number of bytes read</returns>
        public override int Read(byte[] buffer, int offset, int count)
        {
            int actualRead = 0;
            long position = this.Position;
            long length = this.Length;

            if (this.TextPointer.Value == null)
            {
                return 0;
            }
            else
            {
                actualRead = count;
                if ((position + count) >= length) actualRead = Convert.ToInt32(length - position);
                if (actualRead > 0)
                {
                    this.ReadDataChunk.Parameters["@pointer"].Value = this.TextPointer.Value;
                    this.ReadDataChunk.Parameters["@offset"].Value = position;
                    this.ReadDataChunk.Parameters["@size"].Value = actualRead;
                    SqlDataReader sdr = this.ReadDataChunk.ExecuteReader(CommandBehavior.SingleResult);
                    sdr.Read();
                    sdr.GetBytes(0, 0, buffer, offset, actualRead);
                    sdr.Close();
                }

                this.Position += actualRead;
                return actualRead;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="buffer"></param>
        /// <param name="offset"></param>
        /// <param name="count"></param>
        public override void Write(byte[] buffer, int offset, int count)
        {
            //long position = this.Position;

            if (offset != 0)
                throw new Exception("Offset parameter must be zero. Only complete buffers are written.");

            this.WriteData.Parameters["@pointer"].Value = this.TextPointer.Value;
            this.WriteData.Parameters["@bytes"].Size = count;
            this.WriteData.Parameters["@bytes"].Value = buffer;
            this.WriteData.ExecuteNonQuery();
            this.Position += count;
        }
        #endregion
    }
}