Problem Reading Image Data from SQL Server using ADO.NET

Hi Community,

I think I can store Binary Data in SQL Server but when I try to retrieve it,
I always only get one byte.

I think I stored my Binary Data in SQL Server in a Colum of Type Image. At
least when I execute the following code, I get some significant network
traffic. When I check the database with query analyzer, I see 4 Hex Chars in
the image colum. Like 0xe0 etc.

This is my first Question, does this mean that only 4 Bytes ended up in the
Database and my problem starts here or is this the preview mode of the image
daty type in query analyzer like I suppose?

Store Image to SQL-Server:

float[] image = MyImageData in a One Dimensional Float Array;
int byte_size = image.length * 4;
byte[] image_buffer = new byte[byte_size];

Buffer.BlockCopy(image,0,image_buffer,0,byte_size) ;

cmd = new SqlCommand("AddImage",Conn);
cmd.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@blob", SqlDbType.VarBinary, image_buffer.Length,
ParameterDirection.Input, false, 0, 0, null,
DataRowVersion.Current,image_buffer);
cmd.Parameters.Add(param);

Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();

As I already said, regarding the network traffic and the amount of time it
takes to execute this code, I think my image data is in sql server now.

When I try to retrieve it, I always only get one byte per Image.

Retreive Image-Data:

Conn.Open();
int chunkSize = 255;
using(reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess ))
{
while (reader.Read())
{
long bytesize = reader.GetBytes(5, 0, null, 0, 0);
byte[] imageData = new byte[bytesize]; //This always returns 1

long bytesread = 0;
int curpos = 0;

while (bytesread < bytesize)
{
bytesread += reader.GetBytes(5, curpos, imageData, curpos, chunkSize);
curpos += chunkSize;
}
Buffer.BlockCopy(imageData,0,result.data,curpos*by teoffset,byteoffset);
}
}

The Code above is from ado documentation. It says that after this loop, the
bytes from the imagedata colum are in the imagedata array. In my case I
always only get one byte.

I don´t have significant network traffic reading from sql-server there is
realy only one byte transfered.

Can somebody please tell me, what I am doing wrong and how I can check if
the data i want to retreive is realy in the database.

Can you see the full content of a image field in query analyzer?

What happened to the rest of my data, I don´t get an index out of bound
exception when I fill in 65000 Bytes but there seems to be only one byte
there afterwards.

Thanks in advance for your efforts

Best Regards

Chucker

re: Problem Reading Image Data from SQL Server using ADO.NET

"Chucker" <Chucker@discussions.microsoft.com> wrote in message
news:CC4F035E-EF80-4775-94CE-8F45FDC2DF2F@microsoft.com...[color=blue]
> Hi Community,
>
> I think I can store Binary Data in SQL Server but when I try to retrieve
> it,
> I always only get one byte.
>
> I think I stored my Binary Data in SQL Server in a Colum of Type Image. At
> least when I execute the following code, I get some significant network
> traffic. When I check the database with query analyzer, I see 4 Hex Chars
> in
> the image colum. Like 0xe0 etc.
>
> This is my first Question, does this mean that only 4 Bytes ended up in
> the
> Database and my problem starts here or is this the preview mode of the
> image
> daty type in query analyzer like I suppose?
>[/color]

I can't see anything particularly wrong with the code you posted.

Here's a complete working example (.net 2.0);

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Diagnostics;

public class Program
{

static void Main(string[] args)
{

System.Diagnostics.Debug.Listeners.Add(new
TextWriterTraceListener(Console.Out));
try
{

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
cb.IntegratedSecurity = true;
cb.DataSource = "(local)";
using (SqlConnection con = new SqlConnection(cb.ConnectionString))
{
con.Open();
new SqlCommand("create table #blobtest(id int identity primary key,
blob image)",con).ExecuteNonQuery();


float[] image = new float[5000];
image[image.Length -1] = 4f;
int byte_size = image.Length * sizeof(float);
byte[] image_buffer = new byte[byte_size];
Buffer.BlockCopy(image,0,image_buffer,0,byte_size) ;

SqlCommand cmdInsert = new SqlCommand("insert into #blobtest(blob)
values (@blob)", con);
SqlParameter param = cmdInsert.Parameters.Add(new
SqlParameter("@blob",
SqlDbType.Image,
image_buffer.Length));
param.Value = image_buffer;
cmdInsert.ExecuteNonQuery();


//now read
int chunkSize = 255;

SqlCommand cmdRead = new SqlCommand("select id, datalength(blob)
bytes, blob from #blobtest", con);
using (SqlDataReader reader =
cmdRead.ExecuteReader(CommandBehavior.SequentialAc cess))
{
while (reader.Read())
{
int actualBytes = reader.GetInt32(1);
long bytesize = reader.GetBytes(2, 0, null, 0, 0);
Console.WriteLine("Actual Bytes: {0}, GetBytes reported {1}",
actualBytes, bytesize);
byte[] buf = new byte[chunkSize * sizeof(float)];
float[] nums = new float[bytesize/sizeof(float)];

int bytesread = 0;
while (bytesread < bytesize)
{
int bytes = (int)reader.GetBytes(2, bytesread, buf, 0,
buf.Length);
Buffer.BlockCopy(buf, 0, nums, bytesread, bytes);
bytesread += bytes;
}
Console.WriteLine("nums length {0}, first {1}, last {2}",
nums.Length, nums[0], nums[nums.Length - 1]);

}
}

}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
Console.WriteLine("Hit any key to exit.");
Console.ReadKey();

}

}








David

re: Problem Reading Image Data from SQL Server using ADO.NET

Thanks David, you are right, i made a very stupid mistake, I wrote binary
instead of varbinary in one place thanks for your help

Chucker

"David Browne" wrote:
[color=blue]
>
> "Chucker" <Chucker@discussions.microsoft.com> wrote in message
> news:CC4F035E-EF80-4775-94CE-8F45FDC2DF2F@microsoft.com...[color=green]
> > Hi Community,
> >
> > I think I can store Binary Data in SQL Server but when I try to retrieve
> > it,
> > I always only get one byte.
> >
> > I think I stored my Binary Data in SQL Server in a Colum of Type Image. At
> > least when I execute the following code, I get some significant network
> > traffic. When I check the database with query analyzer, I see 4 Hex Chars
> > in
> > the image colum. Like 0xe0 etc.
> >
> > This is my first Question, does this mean that only 4 Bytes ended up in
> > the
> > Database and my problem starts here or is this the preview mode of the
> > image
> > daty type in query analyzer like I suppose?
> >[/color]
>
> I can't see anything particularly wrong with the code you posted.
>
> Here's a complete working example (.net 2.0);
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Collections.Generic;
> using System.Diagnostics;
>
> public class Program
> {
>
> static void Main(string[] args)
> {
>
> System.Diagnostics.Debug.Listeners.Add(new
> TextWriterTraceListener(Console.Out));
> try
> {
>
> SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
> cb.IntegratedSecurity = true;
> cb.DataSource = "(local)";
> using (SqlConnection con = new SqlConnection(cb.ConnectionString))
> {
> con.Open();
> new SqlCommand("create table #blobtest(id int identity primary key,
> blob image)",con).ExecuteNonQuery();
>
>
> float[] image = new float[5000];
> image[image.Length -1] = 4f;
> int byte_size = image.Length * sizeof(float);
> byte[] image_buffer = new byte[byte_size];
> Buffer.BlockCopy(image,0,image_buffer,0,byte_size) ;
>
> SqlCommand cmdInsert = new SqlCommand("insert into #blobtest(blob)
> values (@blob)", con);
> SqlParameter param = cmdInsert.Parameters.Add(new
> SqlParameter("@blob",
> SqlDbType.Image,
> image_buffer.Length));
> param.Value = image_buffer;
> cmdInsert.ExecuteNonQuery();
>
>
> //now read
> int chunkSize = 255;
>
> SqlCommand cmdRead = new SqlCommand("select id, datalength(blob)
> bytes, blob from #blobtest", con);
> using (SqlDataReader reader =
> cmdRead.ExecuteReader(CommandBehavior.SequentialAc cess))
> {
> while (reader.Read())
> {
> int actualBytes = reader.GetInt32(1);
> long bytesize = reader.GetBytes(2, 0, null, 0, 0);
> Console.WriteLine("Actual Bytes: {0}, GetBytes reported {1}",
> actualBytes, bytesize);
> byte[] buf = new byte[chunkSize * sizeof(float)];
> float[] nums = new float[bytesize/sizeof(float)];
>
> int bytesread = 0;
> while (bytesread < bytesize)
> {
> int bytes = (int)reader.GetBytes(2, bytesread, buf, 0,
> buf.Length);
> Buffer.BlockCopy(buf, 0, nums, bytesread, bytes);
> bytesread += bytes;
> }
> Console.WriteLine("nums length {0}, first {1}, last {2}",
> nums.Length, nums[0], nums[nums.Length - 1]);
>
> }
> }
>
> }
> }
> catch (Exception ex)
> {
> Console.WriteLine(ex);
> }
> Console.WriteLine("Hit any key to exit.");
> Console.ReadKey();
>
> }
>
> }
>
>
>
>
>
>
>
>
> David
>
>
>[/color]