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
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);
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:
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:
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:
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:
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:
public override void WriteTo(Stream destination,
bool closeWhenFinished, long bufferSize)
{
try
{
long bytesRead;
byte[] buffer = new byte[bufferSize];
this.Position = 0;
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:
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:
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.
select [lobVarBinary]
from dbo.[Lob]
where cast([lobID] as varchar(50)) = cast('1' as varchar(50))
Graphical overview
The orthodox approach
The alternative approach
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.
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.
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:
private SqlCommand _insertData;
private SqlCommand _appendData;
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:
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:
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:
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:
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:
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:
private SqlCommand _getTextPointer = null;
private SqlParameter _textPointer = null;
public SqlParameter TextPointer
{
get
{
if (this._getTextPointer == null)
{
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:
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:
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:
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
.
private SqlCommand _insertData;
private SqlCommand _appendData;
public SqlCommand WriteData
{
get
{
string sqlWriteData = string.Empty;
if (this.Position == 0)
{
if (this._insertData == null)
{
sqlWriteData =
string.Format("WRITETEXT [{0}].[{1}]
@pointer WITH LOG @bytes",
this.TableName, this.LobColumn);
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)
{
sqlWriteData = string.Format(
"UPDATETEXT [{0}].[{1}] @pointer null
null WITH LOG @bytes",
this.TableName, this.LobColumn);
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:
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:
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.
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:
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:
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.
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);
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";
}
}
}
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.
protected void btnShowSqlExpressVarchar_Click(object sender, EventArgs e)
{
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";
}
}
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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.