insert image in sql server 2005

Hi there,

             How would I insert an image in a sql server 2005 database? I am supposed to do two things-make a thumbnail of the image that the client uploads and save both the thumbnail and the image to a database. Is there any article that explains my needs?

Thanks in advance.
siddhuoopsAsked:
Who is Participating?
 
Alexandre SimõesConnect With a Mentor Manager / Technology SpecialistCommented:
I've created a new page named Test with a button in it.
Take a look at some comments I have, change it to suite your needs
The code behind compiling goes like this:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using System.IO;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

      public void UpdateUserImages(int hometour_id, byte[] image)
      {
            //FrmTaskDetailsDataSet ds =
            SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["Hometour"].ConnectionString);
            SqlCommand scmd = new SqlCommand("sp_SaveUserImages", scon);
            scmd.CommandType = CommandType.StoredProcedure;
            scmd.Parameters.AddWithValue("@hometour_id", 13);
            scmd.Parameters.AddWithValue("@thumbimg", image);
            scon.Open();
            scmd.ExecuteNonQuery();
            scon.Close();
      }



      protected void Button1_Click(object sender, EventArgs e)
      {
            int hometour_id = 0; // HOW DO YOU GET THIS VALUE?!?!!?

            string destpath = "extra_images/" + FileUpload1.FileName;

            FileStream fs = null;
            fs = new FileStream(destpath, FileMode.Open);
            FileInfo fi = new FileInfo(destpath);
            long temp = fi.Length;
            int lung = Convert.ToInt32(temp);
            byte[] picture = new byte[lung];
            fs.Read(picture, 0, lung);
            fs.Close();
            this.UpdateUserImages(hometour_id, picture);
      }
}
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi,

The column type must be VarBinary(MAX)

Bellow I'm giving you an example on how to update an Avatar of a user... so it updates the VarBinary(MAX) column on the Users table for a specified user:

            public override void UpdateUserAvatar(int idUser, byte[] image)
            {
                  FrmTaskDetailsDataSet ds = new FrmTaskDetailsDataSet();

                  SqlConnection connection = new SqlConnection(Global.ConnectionString());
                  SqlCommand command = connection.CreateCommand();

                  command.CommandType = System.Data.CommandType.StoredProcedure;
                  command.CommandText = "sp_SaveUserAvatar";

                  SqlParameter p1 = new SqlParameter("IdUser", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, true, 10, 0, "", System.Data.DataRowVersion.Current, Global.UserSession.IdUser);
                  SqlParameter p2 = new SqlParameter("avatar", System.Data.SqlDbType.VarBinary, -1, System.Data.ParameterDirection.Input, true, 0, 0, "", System.Data.DataRowVersion.Current, image);
                  SqlParameter p3 = new SqlParameter("errorCode", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, true, 10, 0, "", System.Data.DataRowVersion.Current, null);

                  command.Parameters.AddRange(new SqlParameter[] { p1, p2, p3 });

                  connection.Open();
                  command.ExecuteNonQuery();
                  connection.Close();
            }



THE SQL:

ALTER PROCEDURE [dbo].[sp_SaveUserAvatar]
(
      @IdUser INT,
      @avatar VARBINARY(MAX),
      @errorCode INT OUTPUT
)
AS
BEGIN
      SET NOCOUNT ON;

      UPDATE dbo.Users
      SET Avatar = @avatar
      WHERE IDUser = @IdUser;

      SET @errorCode = @@ERROR;
END



If you're wondering, to convert an image into a byte[] I use the following:

                  // Copy file to the configs folder
                  string destPath = "image file path";
                  File.Copy(_fileName, destPath, true);

                  FileStream fs = null;
                  fs = new FileStream(destPath, FileMode.Open);
                  FileInfo fi = new FileInfo(destPath);
                  long temp = fi.Length;
                  int lung = Convert.ToInt32(temp);
                  byte[] picture = new byte[lung];
                  fs.Read(picture, 0, lung);
                  fs.Close();



I hope it helps,
Alex
0
 
siddhuoopsAuthor Commented:
Do I need to define any namespace since I am not getting this:
FrmTaskDetailsDataSet

Also, where in under what section of webconfig should I copy the following code?
 string destPath = "image file path";
                  File.Copy(_fileName, destPath, true);

                  FileStream fs = null;
                  fs = new FileStream(destPath, FileMode.Open);
                  FileInfo fi = new FileInfo(destPath);
                  long temp = fi.Length;
                  int lung = Convert.ToInt32(temp);
                  byte[] picture = new byte[lung];
                  fs.Read(picture, 0, lung);
                  fs.Close();

Sorry I am new to this.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Alexandre SimõesManager / Technology SpecialistCommented:
Sorry... delete that line:
FrmTaskDetailsDataSet ds = new FrmTaskDetailsDataSet();
I don't use it anywhere...


The code...
I Have it on the Save button.
In my case I ask the user for the image path and when the user clicks the Save button I call that code and then the UpdateUserAvatar() method passing the user id and the image in byte[]
0
 
siddhuoopsAuthor Commented:
here is what I am doing..

public override void UpdateUserImages(int hometour_id, byte[] image)
    {
        //FrmTaskDetailsDataSet ds =
        SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["Hometour"].ConnectionString);
        SqlCommand scmd = new SqlCommand("sp_SaveUserImages", scon);
        scmd.CommandType = CommandType.StoredProcedure;
        scmd.Parameters.AddWithValue("@hometour_id", 13);
        scmd.Parameters.AddWithValue("@thumbimg", image);
        scon.Open();
        scmd.ExecuteNonQuery();
        scon.Close();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
       
        string destpath = "extra_images/" + FileUpload1.FileName;

        FileStream fs = null;
        fs = new FileStream(destpath, FileMode.Open);
        FileInfo fi = new FileInfo(destpath);
        long temp = fi.Length;
        int lung = Convert.ToInt32(temp);
        byte[] picture = new byte[lung];
        fs.Read(picture, 0, lung);
        fs.Close();
        this.UpdateUserImages();
    }
//Here is my stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_SaveUserImages]
@thumbimg varbinary(max),
@hometour_id nvarchar(20)

AS
BEGIN
      SET NOCOUNT ON;
      
      Insert Into Enhanced_Images (thumbimg, hometour_id) Values (@thumbimg, @hometour_id)

END

But I am not able to compile it. Could you please check what am I doing wrong.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Did you debug it?
Do you know where it throws the exception?
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Sorry... I misread it... it doesn't compile... where does it mark the errors?
0
 
siddhuoopsAuthor Commented:
I am sorry to respond late..i was out to lunch

whats this for? Is this code to store the image in the database?
FileStream fs = null;
            fs = new FileStream(destpath, FileMode.Open);
            FileInfo fi = new FileInfo(destpath);
            long temp = fi.Length;
            int lung = Convert.ToInt32(temp);
            byte[] picture = new byte[lung];
            fs.Read(picture, 0, lung);
            fs.Close();

the hometour_id is something I pass from the previous page when the user logs in.
0
 
siddhuoopsAuthor Commented:
Here is the problem I am having when I debug the code.

I upload a file from my desktop...when I submit it, it throws out error:
Could not find file 'C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\picture.jpg'

This error occurs at this line: fs = new FileStream(destpath, FileMode.Open);
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi,

please debug the code, put a breakpoint on that line and take a look at the path.

The problem is that the code isn't reaching the image, the image file path is wrong.
0
 
siddhuoopsAuthor Commented:
yeah i got it now...the image path was not set up correctly..Thank You so much for helping me out.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Glad to help,

Alex
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.