473,396 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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

Nov 19 '05 #1
2 3721

"Chucker" <Ch*****@discussions.microsoft.com> wrote in message
news:CC**********************************@microsof t.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.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
Nov 19 '05 #2
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:

"Chucker" <Ch*****@discussions.microsoft.com> wrote in message
news:CC**********************************@microsof t.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.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

Nov 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Mee Yamo | last post by:
Fellas!! This is a very complicated one and it took me a few days to figure out exactly what's going on, but here's the final story: I have a production environment running on .NET with a SQL...
4
by: Gareth Thackeray | last post by:
Hi, Tasks in a MS Project SQL Server database have an image field called TASK_RTF_NOTES. It contains text in RTF format. We would like to convert this within a standard SQL statement into...
11
by: Abhishek | last post by:
I have a problem transfering files using sockets from pocket pc(.net compact c#) to desktop(not using .net just mfc and sockets 2 API). The socket communication is not a issue and I am able to...
4
by: Andy | last post by:
Hello All: I have a field in the database that is an Image. I have no idea how the data is stored in here (Image, compressed, encrypted, plain text, etc). I am trying to write the contents to...
1
by: Stephen | last post by:
Hi, I am using an Access database (OLE Object) and storing Image as a bytes after streaming. I am able to read it back as a stream and display it on the browser using the following code:...
7
by: Nathan Sokalski | last post by:
I am having a problem saving an image with the same name it originally had. I have two similar versions of my code, one in which I close the FileStream used to open the original image before saving,...
23
by: Peter | last post by:
I have a problem with a page show_image.asp that returns a jpg image under Windows XP Pro SP2. The page sets content type as: Response.ContentType = "image/jpg" While this works perfectly fine...
9
by: tshad | last post by:
This was posted before but the message got messed up (all NLs were stripped out for some reason). I have 2 labels that hold the name of different images on my .aspx page. <asp:Label ID="Logo"...
3
by: =?Utf-8?B?dGtpZWhs?= | last post by:
I have large 1bpp tiff scans of arch. drawings that are typically 12032x16890 pixels (filesize is about a 1 meg +/-) While I can readily view smaller (dimension) files, when I try to do anything...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.