Problem Reading Image Data from SQL Server using ADO.NET

[Chucker]

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.BlockCop y(image,0,image _buffer,0,byte_ size);

cmd = new SqlCommand("Add Image",Conn);
cmd.CommandType = CommandType.Sto redProcedure;
param = new SqlParameter("@ blob", SqlDbType.VarBi nary, image_buffer.Le ngth,
ParameterDirect ion.Input, false, 0, 0, null,
DataRowVersion. Current,image_b uffer);
cmd.Parameters. Add(param);

Conn.Open();
cmd.ExecuteNonQ uery();
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.ExecuteRead er(CommandBehav ior.SequentialA ccess))
{
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.BlockCop y(imageData,0,r esult.data,curp os*byteoffset,b yteoffset);
}
}

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

[David Browne]

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


"Chucker" <Chucker@discus sions.microsoft .com> wrote in message
news:CC4F035E-EF80-4775-94CE-8F45FDC2DF2F@mi crosoft.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.Sql Client;
using System.Collecti ons.Generic;
using System.Diagnost ics;

public class Program
{

static void Main(string[] args)
{

System.Diagnost ics.Debug.Liste ners.Add(new
TextWriterTrace Listener(Consol e.Out));
try
{

SqlConnectionSt ringBuilder cb = new SqlConnectionSt ringBuilder();
cb.IntegratedSe curity = true;
cb.DataSource = "(local)";
using (SqlConnection con = new SqlConnection(c b.ConnectionStr ing))
{
con.Open();
new SqlCommand("cre ate table #blobtest(id int identity primary key,
blob image)",con).Ex ecuteNonQuery() ;


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.BlockCop y(image,0,image _buffer,0,byte_ size);

SqlCommand cmdInsert = new SqlCommand("ins ert into #blobtest(blob)
values (@blob)", con);
SqlParameter param = cmdInsert.Param eters.Add(new
SqlParameter("@ blob",
SqlDbType.Image ,
image_buffer.Le ngth));
param.Value = image_buffer;
cmdInsert.Execu teNonQuery();


//now read
int chunkSize = 255;

SqlCommand cmdRead = new SqlCommand("sel ect id, datalength(blob )
bytes, blob from #blobtest", con);
using (SqlDataReader reader =
cmdRead.Execute Reader(CommandB ehavior.Sequent ialAccess))
{
while (reader.Read())
{
int actualBytes = reader.GetInt32 (1);
long bytesize = reader.GetBytes (2, 0, null, 0, 0);
Console.WriteLi ne("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.Get Bytes(2, bytesread, buf, 0,
buf.Length);
Buffer.BlockCop y(buf, 0, nums, bytesread, bytes);
bytesread += bytes;
}
Console.WriteLi ne("nums length {0}, first {1}, last {2}",
nums.Length, nums[0], nums[nums.Length - 1]);

}
}

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

}

}








David

[Chucker]

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@discus sions.microsoft .com> wrote in message
> news:CC4F035E-EF80-4775-94CE-8F45FDC2DF2F@mi crosoft.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.Sql Client;
> using System.Collecti ons.Generic;
> using System.Diagnost ics;
>
> public class Program
> {
>
> static void Main(string[] args)
> {
>
> System.Diagnost ics.Debug.Liste ners.Add(new
> TextWriterTrace Listener(Consol e.Out));
> try
> {
>
> SqlConnectionSt ringBuilder cb = new SqlConnectionSt ringBuilder();
> cb.IntegratedSe curity = true;
> cb.DataSource = "(local)";
> using (SqlConnection con = new SqlConnection(c b.ConnectionStr ing))
> {
> con.Open();
> new SqlCommand("cre ate table #blobtest(id int identity primary key,
> blob image)",con).Ex ecuteNonQuery() ;
>
>
> 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.BlockCop y(image,0,image _buffer,0,byte_ size);
>
> SqlCommand cmdInsert = new SqlCommand("ins ert into #blobtest(blob)
> values (@blob)", con);
> SqlParameter param = cmdInsert.Param eters.Add(new
> SqlParameter("@ blob",
> SqlDbType.Image ,
> image_buffer.Le ngth));
> param.Value = image_buffer;
> cmdInsert.Execu teNonQuery();
>
>
> //now read
> int chunkSize = 255;
>
> SqlCommand cmdRead = new SqlCommand("sel ect id, datalength(blob )
> bytes, blob from #blobtest", con);
> using (SqlDataReader reader =
> cmdRead.Execute Reader(CommandB ehavior.Sequent ialAccess))
> {
> while (reader.Read())
> {
> int actualBytes = reader.GetInt32 (1);
> long bytesize = reader.GetBytes (2, 0, null, 0, 0);
> Console.WriteLi ne("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.Get Bytes(2, bytesread, buf, 0,
> buf.Length);
> Buffer.BlockCop y(buf, 0, nums, bytesread, bytes);
> bytesread += bytes;
> }
> Console.WriteLi ne("nums length {0}, first {1}, last {2}",
> nums.Length, nums[0], nums[nums.Length - 1]);
>
> }
> }
>
> }
> }
> catch (Exception ex)
> {
> Console.WriteLi ne(ex);
> }
> Console.WriteLi ne("Hit any key to exit.");
> Console.ReadKey ();
>
> }
>
> }
>
>
>
>
>
>
>
>
> David
>
>
>[/color]