Problem Reading Image Data from SQL Server using ADO.NET

19 Nov 2005 8:16 PM

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.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

19 Nov 2005 10:22 PM

David Browne

Show quote

"Chucker" <Chuc***@discussions.microsoft.com> wrote in message
news:CC4F035E-EF80-4775-94CE-8F45FDC2DF2F@microsoft.com...
> 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

19 Nov 2005 10:38 PM

Chucker

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

Show quote

"David Browne" wrote:

>
> "Chucker" <Chuc***@discussions.microsoft.com> wrote in message
> news:CC4F035E-EF80-4775-94CE-8F45FDC2DF2F@microsoft.com...
> > 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
>
>
>