• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4946
  • Last Modified:

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.
0
siddhuoops
Asked:
siddhuoops
  • 7
  • 5
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Alexandre SimõesManager / 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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now