How to upload,save image in folder and path in SQL server database and retrieve using Asp.net C#, VB?

Introduction: In this article i am going to share the code to upload the image through file upload control and save/store the image in website folder and store the image path in Sql server database and then read/retrieve the image  and display in image control and image name in label.
Upload image in  folder, Save image path in sql server database and retrieve and display using asp.net and
Click on image to enlarge
Saved image path in sql server database
Click to enlarge and view saved image path in sql server database
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 path in Sql server database?
  • How to read/retrieve the image from the image path stored in database and show in Image control?
In previous related  articles i explained Upload,store image in binary format into SQL server database and retrieve using asp.net  and jQuery to validate file extension and upload image file and Maintain image on PostBack in FileUpload control and Show image preview before uploading using jQuery and Restrict user to upload images of up to specified size 

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 path 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)
BookPicName
varchar(100)
BookPicPath
varchar(200)

Now create the following stored procedures 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),             
                @BookPicName              VARCHAR(100)=NULL,
                @BookPicPath                VARCHAR(200)=NULL
AS
BEGIN 
                SET NOCOUNT ON;      
                INSERT INTO BookDetails(BookName,Author,Publisher,Price,BookPicName,BookPicPath)
    VALUES (@BookName,@Author,@Publisher,@Price,@BookPicName,@BookPicPath)
END

  • Stored procedure to fetch the Book record from Database

CREATE PROCEDURE [dbo].[GetBookDetails_Sp]         
AS
BEGIN 
                SET NOCOUNT ON;
                SELECT * FROM BookDetails   
END
  • 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.
  • Now 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" />
           <center><asp:Label ID="lblBookPicName" runat="server" Text=""></asp:Label></center>
        </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 upload Image in folder and image path 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;
        string getPath = string.Empty;
        string pathToStore = string.Empty;
       
        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); 
                cmd.Parameters.AddWithValue("@BookPicName", fileName);
                int getPos = filePath.LastIndexOf("\\");
                int len = filePath.Length;
                getPath = filePath.Substring(getPos, len - getPos);
                pathToStore = getPath.Remove(0, 1);
                cmd.Parameters.AddWithValue("@BookPicPath", pathToStore);
            }
            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;           
            getPath = null;
            pathToStore = null;
        }
    }

    protected void btnRetrieve_Click(object sender, EventArgs e)
    {
        SqlDataReader dr = null;
        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["BookPicPath"])))
                {
                    Image1.ImageUrl = "~/BookPictures/" + Convert.ToString(dr["BookPicPath"]);                 
lblBookPicName.Text=Convert.ToString(dr["BookPicName"]);  
                }
                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();
        }
    }

    protected void btnCancel_Click(object sender, EventArgs e)
    {
        ClearControls();
        lblStatus.Text = string.Empty;
        lblBookPicName.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" />
        <center> <asp:Label ID="lblBookPicName" runat="server" Text=""></asp:Label></center>
        </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 upload Image in folder and image path 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 getPath As String = String.Empty
        Dim pathToStore As String = String.Empty
        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)

                cmd.Parameters.AddWithValue("@BookPicName", fileName)
                Dim getPos As Integer = filePath.LastIndexOf("\")
                Dim len As Integer = filePath.Length()
                getPath = filePath.Substring(getPos, len - getPos)
                pathToStore = getPath.Remove(0, 1)
                cmd.Parameters.AddWithValue("@BookPicPath", pathToStore)
            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
            getPath = Nothing
            pathToStore = 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 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("BookPicPath"))) Then
                    Image1.ImageUrl = "~/BookPictures/" + DirectCast(dr("BookPicPath"), String)
                    lblBookPicName.Text = DirectCast(dr("BookPicName"), String)
                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()
        End Try
    End Sub

    Protected Sub btnCancel_Click(sender As Object, e As System.EventArgs) Handles btnCancel.Click
        ClearControls()
        lblStatus.Text = String.Empty
        lblBookPicName.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

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

Now over to you:
" I hope you have got the way to upload image in folder , store image path in database and retrieve the image path 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 »

6 comments

Click here for comments
Unknown
admin
May 22, 2014 ×

awsm article, work with guid is very good. keep it up

Reply
avatar
May 22, 2014 ×

Thanks Raj Negi for your feedback..i am glad you found this article helpful..stay connected for more useful updates like this..:)

Reply
avatar
Anonymous
admin
April 09, 2015 ×

Very nice post
Thank you

Reply
avatar
April 13, 2015 ×

Your welcome.. and thanks for your valuable feedback....stay connected and keep reading for more useful updates..:)

Reply
avatar
August 10, 2015 ×

thanks, every time I try to retrieve the image back I fail, the path that I get is always in string format as you kindly provided in the image in the beginning of this article

Reply
avatar
August 11, 2015 ×

I suggest you to recheck the code..If still you face the same problem then let me know and i will help you in sorting out this problem.

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..