do anyone have codes for sql 2005 for saving picture?

[ronakT]

I wanna saving picture in sql2005 with varbinary data type and retrieve it in C# but every code I find is in sql 2000 with image data type. please help me. :sigh:

[Pete OHanlon]

You would save it with something like this:

private void SaveImageToDb(string fileName, SqlConnection sqlConnection)
{
// save image to a memory stream
using (MemoryStream ms = new MemoryStream())
{
Image.FromFile( fileName ).Save( ms, System.Drawing.Imaging.ImageFormat.Bmp );

using (SqlCommand sqlCmd = new SqlCommand( 
  "INSERT INTO MyTable(MyImage) VALUES (@Image)", sqlConnection ))
{
  sqlCmd.Parameters.Add( "@Image", SqlDbType.Image );

  // The image is set based on the byte array in the MemoryStream.
  sqlCmd.Parameters \[ "@Image" \].Value = ms.GetBuffer( );

  // execute command
  sqlCmd.ExecuteNonQuery( );
}

}
}

I've just knocked this up in the web editor, so I apologise if the syntax isn't 100% perfect.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

[Luc Pattyn]

Hi Pete, I think that would work just fine, however you got me puzzled here, for two reasons. 1. Not sure why, but the OP actually asked for a VarBinary field, not an Image field. 2. The way you suggest the data moves from file to Image to MemoryStream to byte array (BTW the Image isn't disposed of). IMO you could have used File.ReadAllBytes() avoiding all conversions and copy operations, the one drawback is it would not detect a bad image. I guess I'm missing something. TIA.

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use < PRE > tags for code snippets, it preserves indentation, and improves readability.

[Pete OHanlon]

Luc Pattyn wrote:

Not sure why, but the OP actually asked for a VarBinary field, not an Image field.

Oops. With all the use of Image in the OP, I got confused and used code for the Image type.

Luc Pattyn wrote:

The way you suggest the data moves from file to Image to MemoryStream to byte array (BTW the Image isn't disposed of).

Yup - but I was just coding this up quickly in the editor. In reality, I would have used a copy of our full image management suite, which I touched very briefly on here[^].

Luc Pattyn wrote:

I guess I'm missing something.

The only thing you are missing is that I hacked this together in the CP text editor.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

[Luc Pattyn]

Your padawan says thanks. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use < PRE > tags for code snippets, it preserves indentation, and improves readability.

[Luc Pattyn]

Based on what Pete already offered, I would try this:

private void SaveImageToDb(string fileName, SqlConnection sqlConnection)
{
using (SqlCommand sqlCmd = new SqlCommand(
"INSERT INTO MyTable(MyImage) VALUES (@Image)", sqlConnection ))
{
sqlCmd.Parameters.Add( "@Image", SqlDbType.VarBinary);
sqlCmd.Parameters [ "@Image" ].Value = File.ReadAllBytes(fileName);
sqlCmd.ExecuteNonQuery( );
}
}
}

Warning: MSDN on SqlDbType.VarBinary holds a comment on size; something happens around 8000 bytes! :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use < PRE > tags for code snippets, it preserves indentation, and improves readability.

modified on Thursday, June 24, 2010 10:58 AM

[Pete OHanlon]

And that would do it nicely.:thumbsup:

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

[yu jian]

               using (SqlConnection sqlConnection = new SqlConnection(strConn))
                {
                    sqlConnection.Open();
                    foreach (string file in fileList)
                    {
                        FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read);
                        byte[] byteImage = new byte[fileStream.Length];
                        fileStream.Read(byteImage, 0, (int)fileStream.Length);

                        string commandText = "Insert into ImageTable(ImagePath, Image)Values(@ImagePath, @Image)";
                        SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection);
                        sqlCommand.Parameters.Add("@ImagePath", SqlDbType.Text);
                        sqlCommand.Parameters.Add("@Image", SqlDbType.Binary);
                        sqlCommand.Parameters["@ImagePath"].Value = file;
                        sqlCommand.Parameters["@Image"].Value = byteImage;

                        sqlCommand.ExecuteNonQuery();

                        System.Windows.Forms.Application.DoEvents();
                    }
                }