Store and retrieve image from database MSSQL - asp.net

This is a example to upload image and store into database. The example also show how to retrieve image from database.

Note : Assume the web page have their own master page

SQL Create Table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[imageTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Image] [image] NULL,
 CONSTRAINT [PK_imageTest] 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] TEXTIMAGE_ON [PRIMARY]

GO

ASPX page Code

    <asp:Label ID="Label1" runat="server" Text="Image Upload :"></asp:Label>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />
    <br />
    <br />
    <asp:Panel ID="Panel1" runat="server">
       
    </asp:Panel>

Code Behind

protected void Page_Load(object sender, EventArgs e)
        {
            CheckIfHaveImage();
        }

        private void CheckIfHaveImage()
        {
            try
            {
                //Initialize SQL Server connection.
                SqlConnection dbConn = new SqlConnection("Connection string");

                //Initialize SQL adapter.
                SqlDataAdapter ADAP = new SqlDataAdapter("Select * from ImageTest", dbConn);

                //Initialize Dataset.
                DataSet DS = new DataSet();

                //Fill dataset with ImagesStore table.
                ADAP.Fill(DS, "ImagesStore");
                dbConn.Close();

                string path = Server.MapPath("~");
                foreach (DataRow row in DS.Tables[0].Rows)
                {
                    string image1 =  Convert.ToString(DateTime.Now.ToFileTime());
                    path += "/" + image1 + ".jpg";
                    FileStream fs = new FileStream(path, FileMode.CreateNew, FileAccess.Write);
                    byte[] bimage1 = (byte[])row["Image"];
                    fs.Write(bimage1, 0, bimage1.Length - 1);
                    fs.Flush();
                    Image I = new Image();
                    I.ImageUrl = "~/" + image1 + ".jpg";
                    Panel1.Controls.Add(I);
                }
            }
            catch (Exception ex)
            {                
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                //get root path
                string path = Server.MapPath("~");

                //save image upload
                path += "/" + FileUpload1.FileName;
                FileUpload1.SaveAs(path);

                //Initialize byte array with a null value initially.
                byte[] data = null;

                //Use FileInfo object to get file size.
                FileInfo fInfo = new FileInfo(path);
                long numBytes = fInfo.Length;

                //Open FileStream to read file
                FileStream fStream = new FileStream(path, FileMode.Open, FileAccess.Read);

                //Use BinaryReader to read file stream into byte array.
                BinaryReader br = new BinaryReader(fStream);

                //When you use BinaryReader, you need to supply number of bytes to read from file.
                //In this case we want to read entire file.
                //So supplying total number of bytes.

                data = br.ReadBytes((int)numBytes);

                //create db connection
                SqlConnection dbConn = new SqlConnection("Connection string");
                SqlCommand dbComm;

                string strsql = "insert into ImageTest (Image) values(@image)";
                dbComm = new SqlCommand(strsql, dbConn);
                SqlParameter param = new SqlParameter("@image", System.Data.SqlDbType.Image);
                param.Value = data;
                dbComm.Parameters.Add(param);

                dbConn.Open();
                dbComm.ExecuteNonQuery();
                dbConn.Close();
            }
        } 

Code Explanation

The example above have file upload controller.After user select on the image to upload and click save button, the code will save upload image into temporary file, and process it as a byre[]. After that store byte value into database. The retrieve process will load data from database and convert byte[] value from database into an image and then create Image controller dynamically and add the image controller into panel to show the image.


By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)