0

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;
        }
4
Contributors
6
Replies
74
Views
8 Years
Discussion Span
Last Post by Roberto_3
0

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

0

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.

0

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

0

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.

0

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

Edited by prathapsv3: n/a

0

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
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.