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