Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL
Article

The Lob Stream Framework, Part 2

Rate me:
Please Sign up or sign in to vote.
4.43/5 (5 votes)
9 Jul 2007CPOL17 min read 33.6K   331   23   4
In this article, I build further on the foundation of Part 1 as I will actually implement a subclass of LobStream to manage binary/text data to and from an SQL Server Express database

Introduction

In this article, I build further on the foundation of Part 1 as I will actually implement a subclass of LobStream to manage binary/text data to and from an SQL Server Express database. The techniques used here are the same as for SQL Server 2005 databases.

.NET Data Provider-specific classes of the framework

Overview

In the present version of the lob stream framework, only two provider-specific classes have been included: SqlLobStream and OleDbLobStream. These classes are abstract to prevent direct instantiation, since a few database-specific features have to be implemented by the actual lob stream classes. In this section, we'll have a look into which way the abstract provider-specific classes complement the base LobStream class to provide a robust framework for database-specific lob stream classes. The provider-specific classes expose four properties:

  • Connection: the Connection property of type IDbConnection, as implemented in LobStream, is overridden with a .NET Data Provider-specific connection, i.e. SqlConnection or OleDbConnection.
  • Length: the "dummy" Length property of LobStream is overridden with a database specific Length property. As the Length property needs to return the length of the lob, the DataLength function is used for SQL Server databases. For OleDb databases, there is no uniform method to get the length of a lob. At least for MS Access, no equivalent of DataLength is available, leaving us pretty much in the dark on how to fetch this metadata. On the other hand, if you should access an SQL Server database via the OleDb Data Provider, the DataLength function would again be at your service.
  • ReadData: an abstract ReadData property is provided, either of type SqlCommand or OleDbCommand. In the base class, the ReadData generates an error since the command to actually read data must be implemented in the database-specific class.
  • Validate: the IDbCommand of LobStream is overridden with a .NET Data Provider-specific command -- either SqlCommand or OleDbCommand -- containing the proper command text to return the number of records that match the user-supplied input.

There is also one method:

  • WriteTo: the orthodox implementation of WriteTo, depending on Read and Length, is overridden with a version that leans heavily on a data reader class that accesses the lob sequentially. While iterating to retrieve the content from the reader, it is immediately written to the destination stream.

Length

C#
private long _length = 0;

public override long Length
{
    get
    {
        if (this._length == 0)
        {
            string sqlGetDataLength = 
                string.Format("select @length = DATALENGTH({0}) 
                from {1} where cast({2} as 
                varchar(50)) = cast('{3}' as varchar(50))",
                this.LobColumn, this.TableName, this.KeyColumn, 
                this.LobID);
            SqlCommand getDataLength = 
                new SqlCommand(sqlGetDataLength, 
                (SqlConnection)this.Connection);
             // The SQL DataLength functions returns 
             // bigint if expression is 
             // of the varchar(max), nvarchar(max) or 
             // varbinary(max) data types
             SqlParameter dataLength = 
                 getDataLength.Parameters.Add("@length", 
                 SqlDbType.BigInt);
             dataLength.Direction = ParameterDirection.Output;
             getDataLength.ExecuteNonQuery();
             if (dataLength.Value == null || dataLength.Value == 
                 System.DBNull.Value) this._length = 0;
             else this._length = Convert.ToInt64(dataLength.Value);
         }
         return _length;
    }
}

Validation

The Validate command must return the number of lob records in the database that match the KeyColumn, LobColumn, LobID and TableName. The implementation for OleDb is as follows:

C#
private OleDbCommand _validate = null;

public override IDbCommand Validate
{
    get
    {
        if (this._validate == null)
        {
            string sqlIsValid = 
                string.Format("select count({1}) from {0} 
                where cstr({1}) = cstr('{2}')",
                this.TableName, this.KeyColumn, this.LobID);
            this._validate = 
                new OleDbCommand(sqlIsValid, 
                this._connection);
        }
        return this._validate;} 
        set
        {
            this._validate = (OleDbCommand)value;
    }
}

As we learn from the code, the Validate command is built around a very simple SQL statement that could be something like: select count(lobID) from LargeObjectTable where CStr(lobID) = CStr('15') or select count(lobID) from LargeObjectTable where CStr(lobID) = CStr('SmallCompanyLogo'). This command is then handed over to the IsValid property -- see implementation in LobStream base class -- where, in the get section, the actual count is compared to 1. If not equal, the lob stream is not valid.

One lob stream for SQL Server Express databases?

The title of this section is a bit misleading, as if there were only one class that deals with lobs against an SQL Server Express database. In fact, there are two such classes: SqlLobStreamFromImage and SqlLobStreamFromVarBinary. It is not hard to guess the origin of this distinction. A lob in an SQL Server 2005 database -- of which SQL Server Express is one of the editions -- can be stored as either an image data type or a varbinary data type. The name of these classes recalls also the LobDataType enumeration, which contains members called SqlServer[…]FromVarBinary and SqlServer[…]FromImage. The image data type is deprecated in SQL Server 2005. The SQL Server Books Online warn that "ntext, text and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work and plan to modify applications that currently use them. Use nvarchar(max), varchar(max) and varbinary(max) instead." However, we need to provide a lob stream class for both alternatives, since both are still possible. In addition, while conforming to the new standards we don't want to forget about SQL Server 2000 databases where image was the lob data type of choice.

Being a LobStream child: SqlLobStreamFromVarBinary

There is great benefit in being a subclass of SqlLobStream when dealing with lobs in databases. As explained in Part 1, we get a lot of common functionality for free from SqlLobStream and LobStream. The only areas that need further coding are the ones that are database-specific:

  • Read a blob from the database, which depends on being able to retrieve the length of the blob. Remember that we only need the Read method when using a BinaryReader.
  • Write a blob to the database.

Reading the lob from the database

Code explanation

The orthodox approach

The Read method reads a sequence of bytes from the database and advances the position within the stream by the number of bytes read. The read bytes are returned in the byte[] buffer. The implementation of Read in the System.IO.Stream class is as follows:

C#
public abstract int Read ( [InAttribute] [OutAttribute] byte[] buffer, 
    int offset, int count )

Notice InAttribute and OutAttribute, indicating that the buffer array is received from the calling code and will be returned filled with read bytes. The buffer acts as a go-between. More information on the OutAttribute can be found on MSDN. Combining the InAttribute and OutAttribute is particularly useful when applied to arrays and formatted, non-blittable types. Callers see the changes a callee makes to these types only when you apply both attributes. Since these types require copying during marshaling, you can use InAttribute and OutAttribute to reduce unnecessary copies.

The CanRead property is used to determine whether the current instance of Stream supports reading. The LobStream has overridden the abstract Read method, but only to return an exception, thus forcing subclasses to override in turn the Read method. The SqlLobStreamFromVarBinary class has implemented the Read method as follows:

C#
public override int Read(byte[] buffer, int offset, int count)
{
    int actualRead = 0;
    long position = this.Position;
    long length = this.Length;

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

    actualRead = count;
    if (((position) + count) >= length) actualRead = 
        Convert.ToInt32(length - position);
    if (actualRead > 0)
    {
        this.ReadDataChunk.Parameters["@start"].Value = position + 1;
        this.ReadDataChunk.Parameters["@length"].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;
}

Read uses two important pieces of code: the Length property and the ReadDataChunk command. Length is already defined in the ascendant class SqlLobStream. As for ReadDataChunk, this is an SqlCommand that returns a sequence of bytes that is @length long -- usually the size of the buffer -- and starts at @start, which is the current position in the stream:

C#
public SqlCommand ReadDataChunk
{
    get
    {
        if (_readDataChunk == null)
        {
            string sqlReadDataChunk = 
                string.Format(
                "select substring({1}, @start, @length) 
                from {0} where cast({2} as 
                varchar(50)) = cast('{3}' as varchar(50))",
                this.TableName, this.LobColumn, 
                this.KeyColumn, this.LobID);
            this._readDataChunk = 
                new SqlCommand(sqlReadDataChunk, 
                (SqlConnection)this.Connection);
            this._readDataChunk.Parameters.Add(
                 "@start", SqlDbType.BigInt);
            this._readDataChunk.Parameters.Add(
                 "@length", SqlDbType.BigInt);
        }
        return _readDataChunk;
    }
    set
    {
        _readDataChunk = value;
    }
}

So, it is simple to see that if we call Read multiple times to get a buffer full of lob content -- which is a task for which Read uses ReadDataChunck -- and each time advance the position in the stream with the number of read bytes, we eventually retrieve the entire lob. The orthodox approach is needed for the BinaryReader and BinaryWriter classes in combination with the LobStream. In other cases, it is far more likely that alternative approaches will be used if the .NET Data Providers allow so. In the next section, I will look at the use of DataReader classes that access the lobs sequentially and thus take a lot of plumbing out of our hands.

The alternative approach with DataReaders and SequentialAccess

In the alternative approach, the Read method is simply not used. If we want to write a lob to another stream, the lob content must be read from the database. In the orthodox approach, we saw that the Read method is heavily used to this end. In this alternative approach, SqlDataReader is instantiated from the ReadData command, with SequentialAccess as the command behavior:

C#
public override void WriteTo(Stream destination, 
    bool closeWhenFinished, long bufferSize)
{
    try
    {
        long bytesRead;
        byte[] buffer = new byte[bufferSize];
        this.Position = 0; 
            //Reposition lob stream at the beginning...

        SqlDataReader sdr = 
            this.ReadData.ExecuteReader(
            CommandBehavior.SequentialAccess);
        sdr.Read();

        while (true)
        {
            bytesRead = 
                sdr.GetBytes(
                0, this.Position, buffer, 
                0, (int)bufferSize);
            if (bytesRead == 0) break;
            destination.Write(
                buffer, 0, (int)bytesRead);
            this.Position += bytesRead;
        }

        sdr.Close();
        destination.Flush();
    }
    catch (Exception ex)
    {
        throw new Exception(
            "Write to stream operation failed.", ex);
    }
    finally
    {
        if (closeWhenFinished) this.Close();
    }
}

The SqlDataReader allows us to read the lob on a chunk-per-chunk basis, much like the Read method does. The big benefit of the SqlDataReader scenario is that it is not necessary anymore to know the length of the lob. The SqlDataReader just keeps looping until the entire lob is processed. This benefit comes in very handy when dealing with database systems that provide a function to retrieve the length of a stored lob in SQL, like MS Access for example. The ReadData command, which will ultimately create the SqlDataReader, is implemented as follows:

C#
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;
    }
}

T-SQL statement

The orthodox approach

The SQL statement shows a straightforward use of T-SQL's substring function: a sequence of [@length] bytes is read from the lob column starting at byte number [@start]. In the sample application, the T-SQL statement would be:

SQL
select substring([lobVarBinary], @start, @length) 
from dbo.[Lob] 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

The alternative approach

The lobVarBinary column value is retrieved in one piece. The sliced reading of the lob is taken care of by the SqlDataReader.

SQL
select [lobVarBinary] 
from dbo.[Lob] 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

Graphical overview

The orthodox approach

Screenshot - img2.jpg

The alternative approach

Screenshot - img3.jpg

Writing the lob to the database

Code explanation

The Write method is an abstract method in System.IO.Stream that must be overridden in derived classes to write to the stream and, ultimately, to the underlying resource. The Write method is the one that will allow us to write binary data to the database.

C#
public abstract void Write ( byte[] buffer, int offset, int count )

The CanWrite property is used to determine whether the current instance supports writing. CanWrite should always return true, which we asserted already in the LobStream base class.

C#
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 full buffers are written.");

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

The Write method merely configures the ["@bytes"] parameter of the WriteData SqlCommand. The Value is set to the buffer parameter that was passed in. The Size parameter is set to the count parameter that was provided by the calling code. At the end of the Write method, the WriteData command is executed and the Position is updated accordingly. The WriteData command is implemented as follows:

C#
private SqlCommand _insertData;
private SqlCommand _appendData;
/// <summary>
/// SqlCommand that is responsible to write the data to 
/// the underlying database...
/// </summary>
public SqlCommand WriteData
{
    get
    {
        string sqlWriteData = string.Empty;
        if (this.Position == 0)
        {
            if (this._insertData == null)
            {
                sqlWriteData = string.Format(
                    "update {0} set {1} = @bytes where cast({2} 
                    as varchar(50)) = cast('{3}' as varchar(50))",
                    this.TableName, this.LobColumn, 
                    this.KeyColumn, this.LobID);
                this._insertData = 
                    new SqlCommand(sqlWriteData, 
                    (SqlConnection)this.Connection);
                this._insertData.Parameters.Add(
                     "@bytes", SqlDbType.Image);
            }
            return this._insertData;
        }    
        else
        {
            if (this._appendData == null)
            {
                sqlWriteData = string.Format(
                    "update {0} set {1} .write(@bytes, null, null) 
                    where cast({2} as 
                    varchar(50)) = cast('{3}' as varchar(50))",
                    this.TableName, this.LobColumn, 
                    this.KeyColumn, this.LobID);
                this._appendData = new SqlCommand(sqlWriteData, 
                    (SqlConnection)this.Connection);
                this._appendData.Parameters.Add(
                     "@bytes", SqlDbType.Image);
            }
            return this._appendData;
        }
    }
}

As we can see from the code, the WriteData command takes the form of either an insert command or an update command, depending on the Position. If the Position is 0, nothing has been written to the database yet. Thus, the insert command will be used. If the Position is greater than 0, the lob in the database needs to be updated, i.e. appended.

Notice that the _insertData and _appendData commands are created only once. Subsequent appends will use the same _appendData instance. This reuse is the main reason why the _insertData and _appendData commands are not created within the Write method. Although this would have led to easier-to-understand code, the_appendData command would have to be recreated with every call to Write.

Another observation is that the lob stream class assumes that the database record that will hold the lob already exists at the time of insertion. Remember that upon initialization of the lob stream class, we had to indicate the table name, the lob column, etc. that must actually point to an existing record. If not, the lob stream was considered invalid. Therefore the insert operation is in fact an update of the lob field in the existing database record, by which the previous stored lob is overwritten.

T-SQL statement

The SQL statement for the update/overwrite couldn't be simpler and is an example of a regular update statement by which the lob field is set to the passed in [@bytes] parameter. The SQL statement for the update/append makes use of the new .Write clause. This enables partial updates and high performance appends to varchar(max), nvarchar(max) and varbinary(max) data type columns as part of the UPDATE statement. For an overview of the new enhancements in SQL Server 2005, see MSDN. Alternatively, refer to the SQL Books Online or this for an in-depth discussion of the .Write clause. In the sample application provided with this article, the T-SQL statement for the update/overwrite comes down to:

SQL
update dbo.[Lob] 
set [lobVarBinary] = @bytes 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

The T-SQL statement for the update/append translates to:

SQL
update dbo.[Lob] 
set [lobVarBinary] .write(@bytes, null, null) 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

The syntax of the .Write clause is .WRITE ( expression, @Offset , @Length ). If @Offset is null, @Length is ignored and appends the expression simply at the end of the column value, which is exactly what we need.

Graphical overview

The write process can graphically be illustrated as follows:

Screenshot - img4.jpg

Being a LobStream child: SqlLobStreamFromImage

The same principles apply as for SqlLobStreamFromVarBinary and will therefore not be repeated here. Instead, I will focus on the differences that are of interest.

Reading the lob from the database

As for the SqlLobStreamFromVarBinary class, a read operation can be carried out against the SqlLobStreamFromImage class in the orthodox way, as well as in the alternative approach.

Code explanation

The orthodox approach

The Read method is as follows:

C#
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;
    }
}

Some interesting things are going on here. First of all, a TextPointer is checked. Secondly, the ReadDataChunk command has an additional parameter, @Pointer. This parameter is set to this.TextPointer.Value, where TextPointer is an SqlParameter. ReadDataChunk is implemented as a regular SqlCommand with three parameters:

C#
private SqlCommand _readDataChunk;
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;
    }
}

TextPointer is implemented as follows:

C#
private SqlCommand _getTextPointer = null;
private SqlParameter _textPointer = null;
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;
    }
}

The alternative approach

The alternative approach is identical to that of the SqlLobStreamFromVarBinary class and will not be repeated here.

T-SQL statement

The orthodox approach

For the sample application, the T-SQL statement reads:

SQL
READTEXT [Lob].[lobImage] @pointer @offset @size

ReadText reads text, ntext or image values from a text, ntext or image column, starting from a specified offset and reading the specified number of bytes. The ReadText operation needs an @pointer value as an input parameter, which must be a valid text pointer of datatype binary(16). The SQL Books Online further recommend to "use the TEXTPTR function to obtain a valid @pointer value. TEXTPTR returns a pointer to the text, ntext or image column in the specified row or to the text, ntext or image column in the last row returned by the query if more than one row is returned." See this for further information. To make sure that a valid text pointer is obtained, a TextPointer SqlParameter is created that gets its value set by the following T-SQL statement:

SQL
declare @valid int

select @valid = 
    textvalid('[Lob].[lobImage]', textptr([lobImage])) 
from [Lob] 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

if(@valid = 0)
update [Lob] 
set [lobImage] = null 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

select @pointer = textptr({0}) 
from [Lob] 
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))

First, the existence of a valid text pointer is checked with the TEXTVALID function. Then, if no valid text pointer can be found, the lob value is initialized (updated) with null, which allows for a text pointer. The TextPointer's value needs to be set only once and is reused as long as needed by the Read method.

The alternative approach

This is the same as for SqlLobStreamFromVarBinary.

Writing the lob to the database

Code explanation

The Write method of the SqlLobStreamFromImage is coded as follows:

C#
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;
}

The WriteData SqlCommand is again a layer above two distinct SqlCommand classes: _insertData and _appendData.

C#
private SqlCommand _insertData;
private SqlCommand _appendData;
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;
        }
    }
}

T-SQL statement

The orthodox approach

For the sample application, the insert/overwrite operation is handled by this T-SQL statement:

SQL
WRITETEXT [Lob].[lobImage] @pointer WITH LOG @bytes

WRITETEXT overwrites any existing data in the column it affects. The @pointer parameter must be a valid text pointer. The @bytes parameter is the actual text, ntext or image data to store and can be a literal or a parameter. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 Kb for text, ntext and image data. See MSDN for further explanation. The update/append operation uses this T-SQL statement:

SQL
UPDATETEXT [Lob].[lobImage] @pointer null null WITH LOG @bytes

Notice that both the insert_offset and delete_length parameters are set to null. When insert_offset is set to null, @bytes is simply appended to the existing column value. Setting the delete_length parameter to null deletes all data from the insert_offset position to the end of the existing column value. See MSDN for more details.

The alternative approach

See the SqlLobStreamFromVarBinary class.

Sample application

Prerequisites

You need the .NET Framework 3.0 and SQL Server Express edition to run this sample ASP.NET application.

Description of the sample app

The sample application is built as a web application, although it is obvious that the LobStream framework can be used with other types of applications, e.g. Windows Forms applications, as well. In this web application, only the SqlLobStreamFromVarBinary class is used. However, it is almost a no-brainer to extend the sample application to SQL Server 2000 databases. The gray column headings show that two types of lobs can be stored in this sample application: texts and pictures. The lobs will be stored in an SQL Server Express database that is included in the download for this article. The SQL Server Express database resides in the App_Data folder of the web application.

Screenshot - img5.jpg

This eventually leads to 8 combinations. See the 8 buttons in the sample application for more details:

  • Storing text in an image and a varbinary column
  • Showing text from an image and a varbinary column
  • Storing a picture in an image and a varbinary column
  • Showing a picture from an image and a varbinary column

Database

The database is called LobStore and contains only one user table, dbo.Lob, that is designed in the following way:

Screenshot - img6.jpg

The columns in dbo.Lob are pretty much self-explanatory. The columns of interest for our sample application are:

  • lobID: the PK column.
  • lobImage: a column of the image datatype. The storage of lobs in this kind of column is the subject of the next installment of this article.
  • lobVarBinary: a column of the varbinary(max) datatype. The storage of lobs in this column is described in this article.

The dbo.Lob table contains only two records:

Screenshot - img7.jpg

The first record, lobID 1, is used to store pictures in the lobVarBinary column. The second record, lobID 2, is used to store text in the lobVarBinary column. Note that this sample application does not create any new records, nor does it contain functionality to delete records. In a real-world application, this should of course be covered as well.

The 10-second crash course

Just browse for a text file or a picture on your computer's hard drive and choose to save it to an image or a varbinary column. To display the text or picture again on the web page, click the appropriate Show buttons.

Exploring the code: how to use the LobStream framework

I invite you to examine the source code of the sample application in more detail. I will only scratch the surface here, but it should give you a fair idea of how to deal with the LobStream framework.

Store a picture in an image column

First, an SqlConnection is created and opened. Then the LobStreamFactory class is asked to return the appropriate LobStream class based upon the LobDataType. Note that the returned lob stream class is referenced by a variable of type LobStream -- although LobStream is an abstract class -- but is in itself either a SqlLobStreamFromImage or a SqlLobStreamFromVarBinary class. This should ring a bell regarding what we said about loose coupling and extensibility of the LobStream framework with new or updated lob stream classes.

C#
protected void btnSaveSqlExpressImage_Click(object sender, EventArgs e)
{
    this.lblMessage.Text = string.Empty;
    string connectionString = 
        WebConfigurationManager.ConnectionStrings[
        "sqlexpress"].ConnectionString;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (LobStream.LobStream s = LobStreamFactory.CreateLobStream(
            LobDataType.SqlServerBinaryFromImage,
            connection,
            "Lob",
            "lobID",
            "lobImage",
            "1"))
        {
            if (s.IsValid)
            {
                int length = this.fuSqlExpress.PostedFile.ContentLength;
                s.ReadFrom(this.fuSqlExpress.PostedFile.InputStream); 
                    //InputStream is automatically 
                    //closed after Read operation...
                if (length > 0) this.lblMessage.Text = "Picture is saved";
                else this.lblMessage.Text = 
                    "No picture was selected. No change was made to 
                    the database.";
            }
            else
            this.lblMessage.Text = "Stream access not valid";
        }    
    } 
    // Dispose is called on the LobStream class, which 
    // closes the database connection...
}

The returned lob stream is configured with the table name, lob column name, etc. that points to the actual lob. Next, the lob stream reads the uploaded picture that is presented as a stream as well. Finally, a message is displayed on the web page with the result of the Save operation. Explore the sample application to verify that storing text or using another data type is very similar.

Showing text from a varbinary column

This example retrieves text from a varbinary column in the database and then uses a FileStream to persist the retrieved text as a physical file, i.e. c:\mytext.txt. The next step is to open this file with the collaboration of FileStream and BinaryReader. BinaryReader processes the FileStream and fills a byte array, called text. Finally, the BinaryWrite method of Response sends the text to the web page.

C#
protected void btnShowSqlExpressVarchar_Click(object sender, EventArgs e)
{
    //Retrieve lob from database and store in c:\mytext.txt...
    string connectionString = 
        WebConfigurationManager.ConnectionStrings[
        "sqlexpress"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (LobStream.LobStream s = 
            LobStreamFactory.CreateLobStream(
            LobDataType.SqlServerTextFromVarBinary))
        {
            s.Connection = connection;
            s.TableName = "Lob";
            s.KeyColumn = "lobID";
            s.LobColumn = "lobVarBinary";
            s.LobID = "2";

            if (s.IsValid)
            {
                using (FileStream fs = 
                    new FileStream(@"c:\mytext.txt", FileMode.OpenOrCreate))
                {
                    s.WriteTo(fs);
                }
            this.lblMessage.Text = 
                @"File c:\mytext.txt created successfully created.";
            }
            else
            this.lblMessage.Text = "Stream access not valid";
        } 
        //Dispose is called, which closes the database connection ...
    }

    //Display lob from c:\mytext.txt ...
    byte[] text = null;
    this.lblMessage.Text = string.Empty;

    using (FileStream fs = new FileStream(@"c:\mytext.txt", FileMode.Open))
    {
        BinaryReader br = new BinaryReader(fs);
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "text/plain";
        text = br.ReadBytes((int)fs.Length);
    }
    HttpContext.Current.Response.BinaryWrite(text);
    HttpContext.Current.Response.End();
}

Next steps

The LobStream framework contains a lob stream class to work with Access lobs. While the alternative approach works just fine, the lack of an SQL function to retrieve the length of a stored lob prevents the coding of a clean Read method. The current class gets the length in a rather absurd way, which must certainly be resolved in a future update of the LobStream framework.

In my opinion, the LobStream framework could be a solid foundation to build a database image control on. Given the table name, lob column, key column and lob ID, a lob picture in the database can be uniquely identified, retrieved and sent to the web page. I will work on this one.

From a more general perspective, the framework could handle any type of lob -- i.e. picture, text, Office-file, PDF -- in a more automatic way if the type or MIME-type could be made available. I experimented with this by including [lobType] and [lobMimeType] in the dbo.[Lob] table. I'm thinking about a light-weight online document management system now.

Anyway, your good ideas or remarks are more than welcome. Just drop me a line. I will make this LobStream framework available on the Incalibration website and post upcoming updates there also. So, make sure to check it from time to time.

History

  • 9 July, 2007 -- Original version posted

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralLob Stream Framework Pin
PurityMail11-Dec-09 14:49
PurityMail11-Dec-09 14:49 
GeneralLinks Pin
gezanal27-Nov-08 1:58
gezanal27-Nov-08 1:58 
GeneralHi. Pin
rajatpk2-Aug-07 8:27
rajatpk2-Aug-07 8:27 
GeneralRe: Hi. Pin
pdejonghe12-Aug-07 22:20
pdejonghe12-Aug-07 22:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.