Problem Reading Image Data from SQL Server using ADO.NET

[Guest]

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*byteoffset,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

 

[David Browne]

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?

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.SequentialAccess))
{
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

 

[Guest]

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

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?

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.SequentialAccess))
{
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