how do I save images to SQL DB?

[Guest]

I'm having the wierdest problem. I'm trying to save images into a SQL 2005
database. the field is just called "IMAGES" and hte data type is "image".
here is my code to save

//following code saves an image as a byte array to a DB

string strFn = this.openFileDialog1.FileName;
this.pictureBox1.Image = Image.FromFile(strFn);
FileInfo fiImage = new FileInfo(strFn);
m_lImageFileLength = fiImage.Length;
FileStream fs = new FileStream(strFn, FileMode.Open, FileAccess.Read,
FileShare.Read);
fs.Seek(0, SeekOrigin.Begin);
m_barrImg = new byte[Convert.ToInt32(m_lImageFileLength)];
int iBytesRead = fs.Read(m_barrImg, 0, Convert.ToInt32(m_lImageFileLength));
fs.Close();

//method takes in customerID, and byte array of image data
savepicture(CustomerID, m_barrImg

//the following code pulls it back out
//first get the byte array back from SQL using this method.
Byte[] barrImg = DBAccess.getimage(CustomerID);

string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs1 = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);
fs1.Write(barrImg, 0, barrImg.Length);
fs1.Flush();
fs1.Close();

//try to display it
pictureBox1.Image = Image.FromFile(strfn);



my proble is that when I get it BACK, the byte array size is only 13! when I
put it in, it's much larger than that.

why is it always 13 ? what am I doing wrong? the methods for update and
select are basic UPDATE TABLE SET IMAGE = ' m_BarrImg ' WHERE CUSTOMERID =
(whatever) , and the SELECT is the same simplicity SELECT IMAGE WHERE
CUSTOMERID = (whatever).

why is this happening? I have tried to a couple hours now. thanks.

PS. NO stored procedures thanks.

 

[Guest]

I'm having the wierdest problem. I'm trying to save images into a SQL 2005
database. the field is just called "IMAGES" and hte data type is "image".
here is my code to save

//the following code pulls it back out
//first get the byte array back from SQL using this method.
Byte[] barrImg = DBAccess.getimage(CustomerID);

my proble is that when I get it BACK, the byte array size is only 13! when I
put it in, it's much larger than that.

why is it always 13 ? what am I doing wrong? the methods for update and
select are basic UPDATE TABLE SET IMAGE = ' m_BarrImg ' WHERE CUSTOMERID =
(whatever) , and the SELECT is the same simplicity SELECT IMAGE WHERE
CUSTOMERID = (whatever).

How does the code in DBAccess.getimage look like ?

Arne

 

[Morten Wennevik [C# MVP]]

I'm having the wierdest problem. I'm trying to save images into a SQL 2005
database. the field is just called "IMAGES" and hte data type is "image".
here is my code to save

//following code saves an image as a byte array to a DB

string strFn = this.openFileDialog1.FileName;
this.pictureBox1.Image = Image.FromFile(strFn);
FileInfo fiImage = new FileInfo(strFn);
m_lImageFileLength = fiImage.Length;
FileStream fs = new FileStream(strFn, FileMode.Open, FileAccess.Read,
FileShare.Read);
fs.Seek(0, SeekOrigin.Begin);
m_barrImg = new byte[Convert.ToInt32(m_lImageFileLength)];
int iBytesRead = fs.Read(m_barrImg, 0, Convert.ToInt32(m_lImageFileLength));
fs.Close();

//method takes in customerID, and byte array of image data
savepicture(CustomerID, m_barrImg

//the following code pulls it back out
//first get the byte array back from SQL using this method.
Byte[] barrImg = DBAccess.getimage(CustomerID);

string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs1 = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);
fs1.Write(barrImg, 0, barrImg.Length);
fs1.Flush();
fs1.Close();

//try to display it
pictureBox1.Image = Image.FromFile(strfn);



my proble is that when I get it BACK, the byte array size is only 13! when I
put it in, it's much larger than that.

why is it always 13 ? what am I doing wrong? the methods for update and
select are basic UPDATE TABLE SET IMAGE = ' m_BarrImg ' WHERE CUSTOMERID =
(whatever) , and the SELECT is the same simplicity SELECT IMAGE WHERE
CUSTOMERID = (whatever).

why is this happening? I have tried to a couple hours now. thanks.

PS. NO stored procedures thanks.

I don't see anything particularly wrong with your code (that my sleepy eyes registeres at 1:38), although you do seem overly fond of Convert, which isn't necessary. You should also enclose your streams in using statements, and use SqlParameters in your save/read database methods.

It might be worth rewriting it IMAGE to [IMAGE] (tells the SQL server that you are talking about the IMAGE field and not the IMAGE data type

string strFn = this.openFileDialog1.FileName;
FileInfo fiImage = new FileInfo(strFn);

using (FileStream fs = File.OpenRead(strFn))
{
byte[] data = new byte[fiImage.Length];
int iBytesRead = fs.Read(data, 0, fiImage.Length);

savepicture(CustomerID, data);
}

string strfn = DateTime.Now.ToFileTime().ToString());
using (FileStream fs1 = File.Create(strfn))
{
byte[] barrImg = DBAccess.getimage(CustomerID);
fs1.Write(barrImg, 0, barrImg.Length);
}

If you post your save and read methods I'll take a look at them tomorrow(that is, later today) unless you get your answers before then.

 

[Guest]

I figured it out. after about 5 hours of searching and searching. the problem
is my UPDATE statement. I cant use

UPDATE TABLE SET IMAGE = ' "+ m_BarrImg + " ' WHERE CUSTOMERID = (whatever)

I have to make a SQLDBType.Image object. like this...

SqlConnection SqlCon = new SqlConnection(ConnectionStr);

string sql = "UPDATE CUSTOMER SET IMAGE = (@ImagPar)";

SqlCommand cmd = new SqlCommand();
SqlParameter sp = new SqlParameter("@ImagPar",SqlDbType.Image);
sp.Value = m_barrImg;
SqlCmd.Parameters.Add(sp);
SqlCmd.CommandText = sql;
SqlCmd.CommandType = CommandType.Text;
SqlCmd.Connection = con;

//open connection
con.Open();

//execute the sql command
cmd.ExecuteNonQuery();

con.Close();

etc...

I cant believe I searched for so long only to find the answer minutes after
I finally decided to ask. O well. thanks.

I'm having the wierdest problem. I'm trying to save images into a SQL 2005
database. the field is just called "IMAGES" and hte data type is "image".
here is my code to save

//the following code pulls it back out
//first get the byte array back from SQL using this method.
Byte[] barrImg = DBAccess.getimage(CustomerID);

my proble is that when I get it BACK, the byte array size is only 13! when I
put it in, it's much larger than that.

why is it always 13 ? what am I doing wrong? the methods for update and
select are basic UPDATE TABLE SET IMAGE = ' m_BarrImg ' WHERE CUSTOMERID =
(whatever) , and the SELECT is the same simplicity SELECT IMAGE WHERE
CUSTOMERID = (whatever).

How does the code in DBAccess.getimage look like ?

Arne