How to read/write chunked to varbinary(max) in C#?
rene.r...@googlemail.com
I have read lots of articles of how to write chunked to a TEXT field
in SQL server but I cannot find any useful info on how to do it with
SQL Server 2005 and the new varbinary(max).
Can someone help me? Maybe even with some sample code?
What I want to do is...
a) write a stream to a varbinary(max) column and
b) read (as a stream) from a varbinary(max) column.
I do NOT want to write my data there in one piece because I have to
handle big chunks of data and do not want to consume whatever memory
is available on the machine.
René
Stefan Delmarco
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
< 0))
throw new ArgumentException("Invalid seek origin.");
_position = _position + offset;
break;
}
default:
{
throw new ArgumentOutOfRangeException("origin", origin,
"Unknown SeekOrigin");
}
}
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,
"Production.ProductPhoto", "LargePhoto",
"ProductPhotoId",
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,
"Production.ProductPhoto", "LargePhoto",
"ProductPhotoId",
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/
rene.r...@googlemail.com
Only thing left ist that I do not use SqlCommand but DataSets. My
question is now how do I convert a byte[] to something I can pass in
my querystring? Do I have to convert it to Base64? What is
SqlParameter.Value doing if you feed it with a byte array??? The docu
does not answer this.
René
Stefan Delmarco
although I expect the base64 representation will require some UrlEncoding
due to the + / and = characters.
Setting SqlParameter.Value to a byte[] causes it to be interpreted as a
SqlDbType.VarBinary... unless I haven't understood your question
correctly...
rajesherasani
1) When you are doing File.Open or File.OpenRead you are still loading whole file into the application server memory
2) When you are doing Select "avalue" of type varbinary(Max) you are actually reading the data into the memory of SQL Server Machine
After the data is been read into the respective memory the program then takes only the chunk that we need, to transfer over the wire, hence decreasing the bandwidth consumption but not the memory consumption.
But this is a good program to use where we have limited bandwidth. Thank you.
Stefan Delmarco wrote:
Hi Ren?,The following class exposes a VARBINARY(MAX) column as a stream.
21-May-07
Hi Ren?,
using System;
using System.IO;
public override void Flush()
{}
Previous Posts In This Thread:
On Monday, May 21, 2007 11:12 AM
rene.rupper wrote:
How to read/write chunked to varbinary(max) in C#?
Hi,
Ren=E9
On Monday, May 21, 2007 9:38 PM
Stefan Delmarco wrote:
Hi Ren?,The following class exposes a VARBINARY(MAX) column as a stream.
Hi Ren?,
using System;
using System.IO;
public override void Flush()
{}
On Tuesday, May 22, 2007 10:46 AM
rene.rupper wrote:
Ren=E9
On Tuesday, May 22, 2007 4:58 PM
Stefan Delmarco wrote:
I guess you could convert the byte[] to its hex or base64 representation,
I guess you could convert the byte[] to its hex or base64 representation,
although I expect the base64 representation will require some UrlEncoding
due to the + / and = characters.
Setting SqlParameter.Value to a byte[] causes it to be interpreted as a
SqlDbType.VarBinary... unless I haven't understood your question
correctly...
--
Cheers,
Stefan Delmarco | SQL Server MVP | http://www.fotia.co.uk/
Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorials/aspnet/6592d2d4-bbf4-4ecd-93df-52898c6aa5d7/iis-70-extensionless-url.aspx