Please disable your adblock and script blockers to view this page
 

How to upload,store image into SQL server database and retrieve using asp.net C#, VB

Save and retrieve image from sql server database in asp.net
Click on image to enlarge
Introduction: In this article i am going to share the code trick to insert/save/store the image in binary format into Sql server database and then retrieve/read the image and display in image control in asp.net using both C# and VB languages.
Noteuploaded image will be converted to binary format before storing to database.
Image stored in binary format in database. Click to enlarge
Description:  Basically you will learn the following through this article.
  • How to upload image through asp.net fileupload control and store that image in folder?
  • How to store uploaded image in Binary format in Sql server database?
  • How to read/retrieve binary formatted image from database and show in Image control?
In previous related article i explained Upload,save image in folder and path in SQL server database and retrieve using Asp.net and  Show image preview before uploading using jQuery and Bind,upload,download,delete image files from the GridView and Drag & drop to upload multiple files using AjaxFileUpload and Upload and create Zip file and Validate and upload image files and Create thumbnail, small and large version of the uploaded image

I have created a demo web page from where i will insert book record like Book name, its author, publisher, price and Book picture. Then i will retrieve that record from database and fill in the corresponding textbox and Image control.

Implementation: Let's create a demo website page to insert data including image in database and retrieve that data and fill in corresponding controls.
  • First of all create a table "BookDetails" in Sql server Database with the columns and data type as shown in below:

Column Name
Data Type
BookId
Int (Primary key. Set Is Identity=true)
BookName
varchar(100)
Author
varchar(100)
Publisher
varchar(100)
Price
decimal(18, 2)
BookPic
varbinary(MAX)

Then create the following stored procedure in Sql server database
  • Stored procedure to save Book record in Database

CREATE PROCEDURE [dbo].[InsertBookDetails_Sp]
                @BookName                    VARCHAR(100),
                @Author                             VARCHAR(100),
                @Publisher                        VARCHAR(100),
                @Price                                 DECIMAL(18,2),
                @BookPic                          VARBINARY(MAX)=NULL,
AS
BEGIN 
                SET NOCOUNT ON;
                INSERT INTO BookDetails(BookName,Author,Publisher,Price,BookPic)
    VALUES (@BookName,@Author,@Publisher,@Price,@BookPic)
END

  • Stored procedure to fetch the Book record from Database

CREATE PROCEDURE [dbo].[GetBookDetails_Sp]         
AS
BEGIN 
                SET NOCOUNT ON;
                SELECT * FROM BookDetails   
END

  • Then in the web.config file create the connection string to connect our asp.net website to Sql server database as:

<connectionStrings>
    <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=Books_DB;Integrated Security=True"/>
  </connectionStrings>

Note: Replace the Data Source and Initial catalog as per your application

Asp.Net C# Section

Create a folder in the root directory of the project and name it "BookPictures". Uploaded Book images will be stored in this folder and then from this folder it will be converted into the binary format and then saved into the database.

In the <Head> tag of the asp.net design page(.aspx) design the page as:

<fieldset style="width:490px;">
    <legend>Save and retrieve image from database</legend>
    <table>
    <tr><td>Book Name: </td><td><asp:TextBox ID="txtBookName" runat="server"></asp:TextBox></td>
        <td rowspan="8" valign="top"><asp:Image ID="Image1" runat="server" Width="150px" Height="150px" /></td></tr>
    <tr><td>Author: </td><td><asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td></tr>
    <tr><td>Publisher: </td><td><asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td></tr>
    <tr><td>Price: </td><td><asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td></tr>
    <tr><td>Book Picture: </td><td>
        <asp:FileUpload ID="flupBookPic" runat="server" /></td></tr>
        <tr><td></td><td>
            <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
            <asp:Button ID="btnCancel" runat="server" onclick="btnCancel_Click"
                Text="Cancel" />
            <asp:Button ID="btnRetrieve" runat="server" onclick="btnRetrieve_Click"
                Text="Retrieve Image" />           
            </td></tr>
        <tr><td>&nbsp;</td><td>
            <asp:Label ID="lblStatus" runat="server"></asp:Label>           
            </td></tr>
    </table>  
    </fieldset>

Asp.Net C# code to save Image in Sql server database and retrieve from Database
  • In the code behind file (.aspx.cs) write the code as:

But first of all include the required following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Drawing;

Then write the code as:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

    protected void btnSave_Click(object sender, EventArgs e)
    {
        string fileName = string.Empty;
        string filePath = string.Empty;
        Byte[] bytes;
        FileStream fs;
        BinaryReader br;
       
        SqlCommand cmd = new SqlCommand("InsertBookDetails_Sp", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BookName", txtBookName.Text.Trim());
        cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim());
        cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim());
        cmd.Parameters.AddWithValue("@Price", Convert.ToDecimal(txtPrice.Text));

        try
        {
            if (flupBookPic.HasFile)
            {
                fileName = flupBookPic.FileName;
                filePath = Server.MapPath("BookPictures/" + System.Guid.NewGuid() + fileName);
                flupBookPic.SaveAs(filePath);

                fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                br = new BinaryReader(fs);
                bytes = br.ReadBytes(Convert.ToInt32(fs.Length));
                br.Close();
                fs.Close();

                cmd.Parameters.AddWithValue("@BookPic", bytes);
            }
            con.Open();
            cmd.ExecuteNonQuery();
            lblStatus.Text = "Book Record saved successfully";
            lblStatus.ForeColor = Color.Green;
            ClearControls();
        }
        catch (Exception)
        {
            lblStatus.Text = "Book Record could not be saved";
            lblStatus.ForeColor = Color.Red;
        }
        finally
        {
            con.Close();
            cmd.Dispose();
            fileName = null;
            filePath = null;           
            fs = null;
            br = null;
        }
    }

    protected void btnRetrieve_Click(object sender, EventArgs e)
    {
        SqlDataReader dr = null;
        byte[] bytes;
        string Base64String = string.Empty;
        SqlCommand cmd = new SqlCommand("GetBookDetails_Sp", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        try
        {
            dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                txtBookName.Text = Convert.ToString(dr["BookName"]);
                txtAuthor.Text = Convert.ToString(dr["Author"]);
                txtPublisher.Text = Convert.ToString(dr["Publisher"]);
                txtPrice.Text = Convert.ToString(dr["Price"]);
             
                if (!string.IsNullOrEmpty(Convert.ToString(dr["BookPic"])))
                {
                    bytes = (byte[])dr["BookPic"];
                    Base64String = Convert.ToBase64String(bytes, 0, bytes.Length);
                    Image1.ImageUrl = "data:image/png;base64," + Base64String;
                }
                lblStatus.Text = "Book record retrieved successfully";
                lblStatus.ForeColor = Color.Green;
            }
        }
        catch (Exception)
        {
            lblStatus.Text = "Book record could not be retrieved";
            lblStatus.ForeColor = Color.Red;
        }
        finally
        {
            dr.Dispose();
            con.Close();
            cmd.Dispose();
            bytes = null;
            Base64String = null;
        }
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        ClearControls();
        lblStatus.Text = string.Empty;
    }

    private void ClearControls()
    {
        txtAuthor.Text = string.Empty;
        txtBookName.Text = string.Empty;
        txtPrice.Text = string.Empty;
        txtPublisher.Text = string.Empty;
        Image1.ImageUrl = null;
        txtBookName.Focus();
    }

Note: This article is just an example of the concept and it will retrieve only first records from the database. Generally we need to fetch only single record based on id e.g. BookId. So when working on the live application you can fetch the book record based on BookId by passing the BookId of the book that you want to retrieve from the database to the stored procedure "GetBookDetails_Sp" So the stored procedure will be as:

CREATE PROCEDURE [dbo].[GetBookDetails_Sp]
                @BookId            INT       
AS
BEGIN 
                SET NOCOUNT ON;
                SELECT * FROM BookDetails WHERE BookId=@BookId
END

Asp.Net VB section

Create a folder in the root directory of the project and name it "BookPictures". Uploaded Book images will be stored in this folder and then from this folder it will be converted into the binary format and then saved into the database.
  • In the <Head> tag of the asp.net design page(.aspx) design the page as:
<fieldset style="width:490px;">
    <legend>Save and retrieve image from database</legend>
    <table>
    <tr><td>Book Name: </td><td><asp:TextBox ID="txtBookName" runat="server"></asp:TextBox></td>
        <td rowspan="8" valign="top"><asp:Image ID="Image1" runat="server" Width="150px" Height="150px" /></td></tr>
    <tr><td>Author: </td><td><asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td></tr>
    <tr><td>Publisher: </td><td><asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td></tr>
    <tr><td>Price: </td><td><asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td></tr>
    <tr><td>Book Picture: </td><td>
        <asp:FileUpload ID="flupBookPic" runat="server" /></td></tr>
        <tr><td></td><td>
            <asp:Button ID="btnSave" runat="server" Text="Save" />
            <asp:Button ID="btnCancel" runat="server" Text="Cancel" />
            <asp:Button ID="btnRetrieve" runat="server" Text="Retrieve Image" />           
            </td></tr>
        <tr><td>&nbsp;</td><td>
            <asp:Label ID="lblStatus" runat="server"></asp:Label>           
            </td></tr>
    </table>  
    </fieldset>

Asp.Net VB code to save Image in Sql server database and retrieve from Database
  • In the code behind file (.aspx.vb) write the code as:
But first of all import the required following namespaces:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Drawing

then write the code as: 

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

Protected Sub btnSave_Click(sender As Object, e As System.EventArgs) Handles btnSave.Click
        Dim fileName As String = String.Empty
        Dim filePath As String = String.Empty
        Dim bytes As [Byte]()
        Dim fs As FileStream
        Dim br As BinaryReader

        Dim cmd As New SqlCommand("InsertBookDetails_Sp", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@BookName", txtBookName.Text.Trim())
        cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim())
        cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim())
        cmd.Parameters.AddWithValue("@Price", Convert.ToDecimal(txtPrice.Text))

        Try
            If flupBookPic.HasFile Then
                fileName = flupBookPic.FileName
                filePath = Server.MapPath("BookPictures/" & Convert.ToString(System.Guid.NewGuid()) & fileName)
                flupBookPic.SaveAs(filePath)

                fs = New FileStream(filePath, FileMode.Open, FileAccess.Read)
                br = New BinaryReader(fs)
                bytes = br.ReadBytes(Convert.ToInt32(fs.Length))
                br.Close()
                fs.Close()
                cmd.Parameters.AddWithValue("@BookPic", bytes)
            End If
            con.Open()
            cmd.ExecuteNonQuery()
            lblStatus.Text = "Book Record saved successfully"
            lblStatus.ForeColor = Color.Green
            ClearControls()
        Catch generatedExceptionName As Exception
            lblStatus.Text = "Book Record could not be saved"
            lblStatus.ForeColor = Color.Red
        Finally
            con.Close()
            cmd.Dispose()
            fileName = Nothing
            filePath = Nothing
            fs = Nothing
            br = Nothing
        End Try
    End Sub

    Protected Sub btnRetrieve_Click(sender As Object, e As System.EventArgs) Handles btnRetrieve.Click
        Dim dr As SqlDataReader = Nothing
        Dim bytes As Byte()
        Dim Base64String As String = String.Empty
        Dim cmd As New SqlCommand("GetBookDetails_Sp", con)
        cmd.CommandType = CommandType.StoredProcedure
        con.Open()
        Try
            dr = cmd.ExecuteReader()
            If dr.HasRows Then
                dr.Read()
                txtBookName.Text = Convert.ToString(dr("BookName"))
                txtAuthor.Text = Convert.ToString(dr("Author"))
                txtPublisher.Text = Convert.ToString(dr("Publisher"))
                txtPrice.Text = Convert.ToString(dr("Price"))

                If Not String.IsNullOrEmpty(Convert.ToString(dr("BookPic"))) Then
                    bytes = DirectCast(dr("BookPic"), Byte())
                    Base64String = Convert.ToBase64String(bytes, 0, bytes.Length)
                    Image1.ImageUrl = "data:image/png;base64," & Base64String
                End If
                lblStatus.Text = "Book record retrieved successfully"
                lblStatus.ForeColor = Color.Green
            End If
        Catch generatedExceptionName As Exception
            lblStatus.Text = "Book record could not be retrieved"
            lblStatus.ForeColor = Color.Red
        Finally
            dr.Dispose()
            con.Close()
            cmd.Dispose()
            bytes = Nothing
            Base64String = Nothing
        End Try
    End Sub

    Protected Sub btnCancel_Click(sender As Object, e As System.EventArgs) Handles btnCancel.Click
        ClearControls()
        lblStatus.Text = String.Empty
    End Sub

    Private Sub ClearControls()
        txtAuthor.Text = String.Empty
        txtBookName.Text = String.Empty
        txtPrice.Text = String.Empty
        txtPublisher.Text = String.Empty
        Image1.ImageUrl = Nothing
        txtBookName.Focus()

    End Sub

Now over to you:
" I hope you have got the way to store and retrieve the image from sql server database using asp.net and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

18 comments

Click here for comments
Sai
admin
March 05, 2014 ×

Thanks. Its really nice. Please go ahead and keep posting more articles.

Reply
avatar
Anonymous
admin
March 05, 2014 ×

Nice article but this will throw an exception if for example data is stored without image.....

Reply
avatar
burning.bits
admin
March 05, 2014 ×

thanks, good stuff.

Reply
avatar
March 05, 2014 ×

Thanks..i am glad you found this article useful..

Reply
avatar
March 05, 2014 ×

Hi, it will not throw exception since that case is handled in the code..you can try to insert data without selecting image..it will run smoothly..

Reply
avatar
March 05, 2014 ×

Hello Sai..thanks for appreciating my work..I will post more articles related to this one..so stay connected and keep reading..:)

Reply
avatar
March 05, 2014 ×

Hello Lalit, thank you very much for this great article, Really, appreciate you effort....

Reply
avatar
vaaru
admin
March 13, 2014 ×

Lot of thanks ..i almost read all your article..all are nice..

Reply
avatar
March 14, 2014 ×

Hi vaaru..thanks for appreciating my work..stay connected and keep reading..:)

Reply
avatar
March 14, 2014 ×

Hello Abhishek..thanks for appreciating my work..it is always nice to hear that my article helped anyone..stay connected and keep reading..:)

Reply
avatar
Imraan
admin
March 19, 2014 ×

Please provide an article on same tutorial using MySQL server.

Reply
avatar
April 10, 2014 ×

Really appreciating, helpful. Am a fans of yours article.

Reply
avatar
April 10, 2014 ×

Thanks Sungur sooraj for appreciating my articles..it is always nice to hear that my article helped anyone in learning asp.net..stay connected and keep reading..:)

Reply
avatar
December 12, 2015 ×

Thanks jayshree for your valuable comments. Stay connected and keep reading for more useful updates..:)

Reply
avatar
December 28, 2015 ×

very nice and very very helpful for me sir, plz help how to save the image in database which which shown in image control(image box) asp.net using vb.net . thank you

Reply
avatar
deepa madhan
admin
March 11, 2016 ×

Thank U :-) It worked for me.

Reply
avatar
March 26, 2016 ×

Thanks for your valuable feedback.Stay connected and keep reading for more updates.

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon