How to insert\retrieve image in SQL Server database?

By Ramy Mahrous on Jul 11th, 2009 4:49 am

Here's I wrote some code to insert\retrieve images from SQL Server database
1- Create table to hold Image ID, Name and itself

USE [ImagesDatabase]
GO
/****** Object:  Table [dbo].[Image]    Script Date: 07/10/2009 23:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Image](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Caption] [nvarchar](50) NOT NULL,
	[Content] [varbinary](max) NOT NULL,
CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

2- Create stored procedure to insert new image

CREATE PROC InsertNewImage
@Caption varchar(50),
@Content varbinary(max)
AS
INSERT INTO [DummyDatabase].[dbo].[Image] ([Caption], [Content])
VALUES (@Caption, @Content)

3- Create stored procedure to get image by ID

CREATE PROC GetImageByID
@ID int
AS
SELECT Caption, Content FROM Image WHERE ID = @ID

//This code developed by Ramy Mahrous 
//ramyamahrous@hotmail.com
//Its contents is provided "as is", without warranty.

/// <summary>
        /// Inserts new image to database
        /// </summary>
        /// <param name="imageName">Image name</param>
        /// <param name="imagePath">Image file path</param>
        public void InsertImage(string imageName, string imagePath)
        {
            System.IO.FileStream fs = new System.IO.FileStream(imagePath, System.IO.FileMode.Open);
            byte[] imageAsBytes = new byte[fs.Length];
            fs.Read(imageAsBytes, 0, imageAsBytes.Length);
            fs.Close();

            SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=DummyDatabase;Integrated Security=True");// your connection string I place mine for illustration.. DON'T HARDLY WRITE IT, pass it as argument or add it in application configuration 

            string insertString = "[dbo].[InsertNewImage]";
            
            SqlCommand insertCommand = new SqlCommand(insertString, conn);                     

            insertCommand.CommandType = CommandType.StoredProcedure;
            
            insertCommand.Parameters.AddWithValue("@Caption", imageName);
            insertCommand.Parameters.AddWithValue("@Content", imageAsBytes);

            conn.Open();

            insertCommand.ExecuteNonQuery();

            conn.Close();
        }

        /// <summary>
        /// Gets image by ID
        /// </summary>
        /// <param name="imageID">Image ID</param>
        /// <returns>Image null if not exists</returns>
        public Image GetImageById(int imageID)
        {
            Image result = null;
            SqlDataReader imageReader;
            
            SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=DummyDatabase;Integrated Security=True");// your connection string I place mine for illustration.. DON'T HARDLY WRITE IT, pass it as argument or add it in application configuration 

            string selectString = "[dbo].[GetImageByID]";
            SqlCommand selectCommand = new SqlCommand(selectString, conn);
            selectCommand.CommandType = CommandType.StoredProcedure;

            selectCommand.Parameters.AddWithValue("@ID", imageID.ToString());
            
            conn.Open();

            imageReader =  selectCommand.ExecuteReader(CommandBehavior.CloseConnection);

            while(imageReader.Read())
            {
                result = Image.FromStream(new MemoryStream((byte[])imageReader.GetValue(1)));
            }

            return result;
        }

1 Year Ago

Do Not use the Data Type image because the Image will be converted into binary and will be saved so better you save the path

private void SaveImage()
        {
            string path = Application.StartupPath+"\\ProfessorImage\\";
            string path2 = txtBrowse.Text;

            try
            {
                if (Directory.Exists(path))
                {
                    string filename = Path.GetFileName(path2);
                    path = path + filename;
                    File.Copy(path2, path, true);

                    toolStripStatusAll.Text = "Succesfully Added";
                }
                else
                {

                    Directory.CreateDirectory(path);
                    string fileName = Path.GetFileName(path2);
                    path = path + fileName;
                    File.Copy(path2, path);
                    toolStripStatusAll.Text = "Succesfully Added";
                }           
                }
            catch (Exception ex)
            {

                toolStripStatusAll.Text = "Failed To Copy"+ex.ToString();
            }
            txtBrowse.Text = "";

            
        }

Save the Image in a directory and Save the path in the DB

rikiroxs

Newbie Poster

17 posts since Nov 2010

Reputation Points: 6

Solved Threads: 1

1 Year Ago

In many many many cases you have to save it as image not only the path.. In case users may change\delete images and you depend on them.

Ramy Mahrous

Postaholic

2,196 posts since Aug 2006

Reputation Points: 480

Solved Threads: 276

1 Year Ago

Ya u can protect them using hiding techniques right,because converting to binary can loose or corrupt images na?

rikiroxs

Newbie Poster

17 posts since Nov 2010

Reputation Points: 6

Solved Threads: 1

1 Year Ago

1- The work and the privilage you need to operate on file system is much harder with lots of exceptions.
2- In many cases there no images on HDD, all manipulated from database directly.

Conclusion... both solutions right and correct.

Ramy Mahrous

Postaholic

2,196 posts since Aug 2006

Reputation Points: 480

Solved Threads: 276

4 Months Ago

Solved

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
 
 Public Class Form1
    Dim str As String = "Data Source=NET3\SQLEXPRESS;Initial Catalog=RestPos;Persist Security Info=True;User ID=sa;Password=password"
    Dim con As New SqlClient.SqlConnection

    'To open an image from computer
    '-------------------------------
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        OpenFileDialog1.Title = "Please select a file"
        OpenFileDialog1.InitialDirectory = "c:temp"
        OpenFileDialog1.ShowDialog()
        TextBox2.Text = OpenFileDialog1.FileName.ToString  '--->To Show the file path in textbox2
        PictureBox1.ImageLocation = TextBox2.Text  '--->To show selected image in picturebox
    End sub
   
   'To insert selected image into database
   'The datatype of the column in table to store image should be <image>  
   '--------------------------------------------------------------------
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        con.ConnectionString = str
        Dim ms As New IO.MemoryStream()
        PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
        Dim arrimage() As Byte = ms.GetBuffer
        Dim cmd As New SqlCommand("insert into image (Emp_Image)values(@picture)", con)
        cmd.Parameters.Add(New SqlParameter("@Picture", SqlDbType.Image)).Value = arrimage
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
    
    'We have successfully inserted the image into database.
    'Now we want to Retrieve the image from database.
    '-------------------------------------------------
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim stream As New IO.MemoryStream()
        con.Open()
        Dim command As New SqlCommand("select Emp_Image from Image where Emp_Id='" +  TextBox3. Text + "'", con)  '--->You can give Emp_id instead of Textbox value.
        Dim image As Byte() = DirectCast(command.ExecuteScalar(), Byte())
        stream.Write(image, 0, image.Length)
        con.Close()
        Dim bitmap As New Bitmap(stream)
        PictureBox2.Image = bitmap '--->I have used another picturebox to display image from database.
    End Sub
    'Thats all, You can place a linklabel below the picturebox if you to change photo and update it in database.

Regards,
Prathap

prathapsv3

Newbie Poster

24 posts since Jan 2012

Reputation Points: 10

Solved Threads: 1

This article has been dead for over three months

You