How to insert\retrieve image in SQL Server database?

Ramy Mahrous 0 Tallied Votes 10K Views Share

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;
        }
rikiroxs -4 Newbie Poster

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

Ramy Mahrous 401 Postaholic Featured Poster

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.

rikiroxs -4 Newbie Poster

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

Ramy Mahrous 401 Postaholic Featured Poster

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.

prathapsv3 0 Newbie Poster

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

Roberto_3 0 Newbie Poster

perfect. this is my code:

Private Sub SaveImagen(ByVal FileName As String, ByVal NombreArticuloCB As String)

    Try
        'abro la conexión
        Dim c As New System.Data.SqlClient.SqlConnection
        c.ConnectionString = TextBox1.Text
        c.Open()
        'MsgBox("ok CONEXION jj")


        'cargo la imagen en un array de bits
        Dim Fs As System.IO.FileStream = New System.IO.FileStream(FileName, System.IO.FileMode.Open)
        Dim imageAsBytes(Fs.Length) As Byte
        Fs.Read(imageAsBytes, 0, imageAsBytes.Length)
        Fs.Close()


        'preparo la sql a ejecutar
        Dim cmd As New System.Data.SqlClient.SqlCommand("insert into ArticuloImagenBlob (Cod_ArticuloImg, Imagen)values('" & NombreArticuloCB & "',@picture)", c)
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Picture", System.Data.SqlDbType.Image)).Value = imageAsBytes
        cmd.ExecuteNonQuery()


        MsgBox("Imagen almacenada")



    Catch ex As Exception
        MsgBox("Error al guardar en db." & vbCrLf & ex.ToString)

    End Try




End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.