Hi René,
The following class exposes a VARBINARY(MAX) column as a stream. Is is
specialised for tables the have a single int column as the table's key:
using System;
using System.IO;
namespace ScratchTest
{
/// <summary>
/// The VarBinaryStream class inherits from Stream. It uses a
/// VarBinarySource class to execute the actual TSQL.
/// </summary>
public class VarBinaryStream : Stream, IDisposable
{
private long _position;
private readonly VarBinarySource _source;
public VarBinaryStream(VarBinarySource source)
{
_position = 0;
_source = source;
}
public override bool CanRead
{
get { return true; }
}
public override bool CanSeek
{
get { return true; }
}
public override bool CanWrite
{
get { return true; }
}
public override long Length
{
get { return _source.Length; }
}
public override long Position
{
get { return _position; }
set { this.Seek(value, SeekOrigin.Begin); }
}
public override void Flush()
{}
public override long Seek(long offset, SeekOrigin origin)
{
switch(origin)
{
case SeekOrigin.Begin:
{
if((offset < 0) && (offset > this.Length))
throw new ArgumentException("Invalid seek origin.");
_position = offset;
break;
}
case SeekOrigin.End:
{
if((offset > 0) && (offset < -this.Length))
throw new ArgumentException("Invalid seek origin.");
_position = this.Length - offset;
break;
}
case SeekOrigin.Current:
{
if((_position + offset > this.Length) || (_position + offset
throw new ArgumentException("Invalid seek origin.");
_position = _position + offset;
break;
}
default:
{
throw new ArgumentOutOfRangeException("origin", origin,
}
}
return _position;
}
public override void SetLength(long value)
{
throw new NotSupportedException();
}
public override int Read(byte[] buffer, int offset, int count)
{
if(buffer == null)
throw new ArgumentNullException("buffer");
if(offset < 0)
throw new ArgumentOutOfRangeException("offset");
if(count < 0)
throw new ArgumentOutOfRangeException("count");
if(buffer.Length - offset < count)
throw new ArgumentException("Offset and length were out of
bounds for the array");
byte[] data = _source.Read(Position, count);
if(data == null)
return 0;
Buffer.BlockCopy(data, 0, buffer, offset, data.Length);
_position += data.Length;
return data.Length;
}
public override void Write(byte[] buffer, int offset, int count)
{
if(buffer == null)
throw new ArgumentNullException("buffer");
if(offset < 0)
throw new ArgumentOutOfRangeException("offset");
if(count < 0)
throw new ArgumentOutOfRangeException("count");
if(buffer.Length - offset < count)
throw new ArgumentException("Offset and length were out of
bounds for the array");
byte[] data = GetWriteBuffer(buffer, count, offset);
_source.Write(data, _position, count);
_position += count;
}
private static byte[] GetWriteBuffer(byte[] buffer, int count, int
offset)
{
if(buffer.Length == count)
return buffer;
byte[] data = new byte[count];
Buffer.BlockCopy(buffer, offset, data, 0, count);
return data;
}
protected override void Dispose(bool disposing)
{
if(!disposing)
{
if(_source != null)
_source.Dispose();
}
base.Dispose(disposing);
}
}
/// <summary>
/// The VarBinarySource class constructs the TSQL used
/// to read to / write from the VARBINARY(MAX) column.
/// IT is currently specialised for a table with a single
/// int column for a PK, but this can be easily generalised
/// for compound keys.
/// </summary>
public class VarBinarySource : IDisposable
{
private SqlCommand _readCommand;
private SqlCommand _writeCommand;
private readonly long _length;
public VarBinarySource(SqlConnection connection, string table, string
dataColumn, string keyColumn, int key)
{
_length = GetLength(connection, table, dataColumn, keyColumn, key);
_readCommand = CreateReadCommand(connection, table, dataColumn,
keyColumn, key);
_writeCommand = CreateWriteCommand(connection, table, dataColumn,
keyColumn, key);
}
public long Length
{
get { return _length; }
}
private static SqlCommand CreateReadCommand(SqlConnection connection,
string table, string dataColumn, string keyColumn,
int key)
{
SqlCommand readCommand = connection.CreateCommand();
readCommand.CommandText = string.Format(@"
select substring({0}, @offset, @length)
from {1}
where {2} = @key", dataColumn, table, keyColumn);
readCommand.Parameters.Add("@key", SqlDbType.Int).Value = key;
readCommand.Parameters.Add("@offset", SqlDbType.BigInt);
readCommand.Parameters.Add("@length", SqlDbType.BigInt);
return readCommand;
}
private static SqlCommand CreateWriteCommand(SqlConnection connection,
string table, string dataColumn, string keyColumn,
int key)
{
SqlCommand writecommand = connection.CreateCommand();
writecommand.CommandText = string.Format(@"
update {0}
set {1}.write(@buffer, @offset, @length)
where {2} = @key", table, dataColumn, keyColumn);
writecommand.Parameters.Add("@key", SqlDbType.Int).Value = key;
writecommand.Parameters.Add("@offset", SqlDbType.BigInt);
writecommand.Parameters.Add("@length", SqlDbType.BigInt);
writecommand.Parameters.Add("@buffer", SqlDbType.VarBinary);
return writecommand;
}
private static long GetLength(SqlConnection connection, string table,
string dataColumn, string keyColumn,
int key)
{
using(SqlCommand command = connection.CreateCommand())
{
SqlParameter length = command.Parameters.Add("@length",
SqlDbType.BigInt);
length.Direction = ParameterDirection.Output;
command.CommandText = string.Format(@"
select @length = cast(datalength({0}) as bigint)
from {1}
where {2} = @key", dataColumn, table, keyColumn);
command.Parameters.Add("@key", SqlDbType.Int).Value = key;
command.ExecuteNonQuery();
return length.Value == DBNull.Value ? 0 : (long)length.Value;
}
}
public byte[] Read(long offset, long length)
{
// substring is 1-based.
_readCommand.Parameters["@offset"].Value = offset + 1;
_readCommand.Parameters["@length"].Value = length;
return (byte[])_readCommand.ExecuteScalar();
}
public void Write(byte[] buffer, long offset, long length)
{
_writeCommand.Parameters["@buffer"].Value = buffer;
_writeCommand.Parameters["@offset"].Value = offset;
_writeCommand.Parameters["@length"].Value = length;
_writeCommand.ExecuteNonQuery();
}
public void Dispose()
{
if(_readCommand != null)
_readCommand.Dispose();
if(_writeCommand != null)
_writeCommand.Dispose();
}
}
}
This is an example of how the class can be used (using AdventureWorks):
[Test]
public void CanReadVarBinaryStream()
{
using(SqlConnection connection = new
SqlConnection("Server=tcp:chenbro,18980;Database=AdventureWorks;Integrated
Security=SSPI"))
{
string filename =
Environment.ExpandEnvironmentVariables("%TEMP%\\LargePhoto.jpg");
connection.Open();
using(VarBinarySource productPhoto = new VarBinarySource(connection,
1))
using(VarBinaryStream reader = new VarBinaryStream(productPhoto))
using(FileStream fileStream = File.Open(filename, FileMode.Create,
FileAccess.Write))
{
byte[] buffer = new byte[1024];
int bytesRead = 0;
while((bytesRead = reader.Read(buffer, 0, buffer.Length)) != 0)
fileStream.Write(buffer, 0, bytesRead);
}
}
}
[Test]
public void CanWriteVarBinaryStream()
{
using(SqlConnection connection = new
SqlConnection("Server=tcp:chenbro,18980;Database=AdventureWorks;Integrated
Security=SSPI"))
{
connection.Open();
using(VarBinarySource productPhoto = new VarBinarySource(connection,
1))
using(VarBinaryStream writer = new VarBinaryStream(productPhoto))
using(FileStream fileStream = File.OpenRead("PinkLargePhoto.jpg"))
{
byte[] buffer = new byte[1024];
int bytesRead = 0;
while((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)
writer.Write(buffer, 0, bytesRead);
}
}
}
The writing performed by this class could be enhanced for situations where
you want to truncate existing data.
--
Cheers,
Stefan Delmarco | SQL Server MVP | http://www.fotia.co.uk/