Upload image in folder and path in Sql server database and retrieve and bind to DataList control in Asp.Net C#,VB

Upload image in folder and path in Sql server database and retrieve and bind to DataList control in 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 root folder and store the image path in Sql server database and then read/retrieve the record along with image from image path and display in DataList data control in asp.net using both C# and VB languages.

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 DataList data control?

In previous articles i explained Save image in folder and path in Sql and retrieve and bind to Repeater and Upload image in folder and save path in Sql server database and read, bind in Gridview and  Upload,save image in folder and path in SQL server database and retrieve using Asp.net and  Drag & drop to upload multiple files using AjaxFileUpload like Facebook and Show image preview before uploading using jQuery and Bind,upload,download,delete image files from the GridView 

I have created a demo web page from where i will insert book record like Book name, its author, publisher, price and Book picture. As soon as a new record is inserted, it will get binded to the Datalist control as show in the sample image shown above.

Implementation: Let's create a demo website page to insert data including image path in database and retrieve that data and fill in datalist data 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)

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),             
                @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 the path will be saved into the database.
    • In the <Form> tag of the Asp.Net design page(Default.aspx) create 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>
            </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" />
                </td></tr>
            <tr><td>&nbsp;</td><td>
                <asp:Label ID="lblStatus" runat="server"></asp:Label>           
                </td></tr>
            <tr><td>&nbsp;</td><td>
                &nbsp;</td></tr>
            <tr><td colspan="2">
                <asp:DataList ID="dlBooks" runat="server" RepeatColumns="2" RepeatDirection="Horizontal" >
                <ItemTemplate>
                <table>
                <tr style="border:1 solid #888;">
                <td>
                <asp:Image ID="ImgBookPic" runat="server" Height="80px" Width="80px" /><br />
                <asp:Label ID="lblBookPicName" runat="server" Text='<%#Eval("BookPicName") %>'></asp:Label>
                </td>
                <td valign="top">
                <asp:Label ID="lblBookName" runat="server" Text='<%#Eval("BookName") %>'></asp:Label><br />
                <asp:Label ID="lblAuthor" runat="server" Text='<%#Eval("Author") %>'></asp:Label><br />
                <asp:Label ID="lblPublisher" runat="server" Text='<%#Eval("Publisher") %>'></asp:Label><br />
                <asp:Label ID="lblPrice" runat="server" Text='<%#Eval("Price") %>'></asp:Label>
                </td>
                </tr>
                </table>
                </ItemTemplate>
                </asp:DataList>
                </td></tr>
        </table>  
        </fieldset>

    Asp.Net C# Code to store image and data and show in DataList
    • In the code behind file(default.aspx.cs) write the code as:

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

    public partial class Default : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindDataList();
            }
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string fileName = string.Empty;
            string filePath = string.Empty;
            string getPath = string.Empty;
            string pathToStore = string.Empty;
            string finalPathToStore = 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);
                    finalPathToStore = "~/BookPictures/" + pathToStore;
                    cmd.Parameters.AddWithValue("@BookPicPath", finalPathToStore);
                }
                con.Open();
                cmd.ExecuteNonQuery();
                lblStatus.Text = "Book Record saved successfully";
                lblStatus.ForeColor = System.Drawing.Color.Green;
                ClearControls();
                BindDataList();
            }
            catch (Exception)
            {
                lblStatus.Text = "Book Record could not be saved";
                lblStatus.ForeColor = System.Drawing.Color.Red;
            }
            finally
            {
                con.Close();
                cmd.Dispose();
                fileName = null;
                filePath = null;
                getPath = null;
                pathToStore = null;
                finalPathToStore = null;
            }
        }

       
        private void BindDataList()
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("GetBookDetails_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            try
            {
                adp.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    dlBooks.DataSource = dt;
                    dlBooks.DataBind();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["BookPicPath"])))
                        {
                            Image img = (Image)dlBooks.Controls[i].FindControl("ImgBookPic");
                            img.ImageUrl=Convert.ToString(dt.Rows[i]["BookPicPath"]);      
                        }
                    }
                }
            }
            catch (Exception)
            {
                lblStatus.Text = "Book record could not be retrieved";
                lblStatus.ForeColor = System.Drawing.Color.Red;
            }
            finally
            {
                con.Close();
                dt.Clear();
                dt.Dispose();
                cmd.Dispose();        
            }
        }

        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;      
            txtBookName.Focus();
        }


    Asp.Net VB Section:
    • Design the page as described in the Asp.Net C# Section above but replace the lines           

    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
    <asp:Button ID="btnCancel" runat="server" onclick="btnCancel_Click" Text="Cancel" />

    With the following lines:

    <asp:Button ID="btnSave" runat="server" Text="Save" />
    <asp:Button ID="btnCancel" runat="server" Text="Cancel" />

    Asp.Net VB Code to store image and data and show in DataList
    • In the code behind file default.aspx.vb write the code as:

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

    Partial Class Default
        Inherits System.Web.UI.Page

        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not Page.IsPostBack Then
                BindDataList()
            End If
        End Sub

        Protected Sub btnSave_Click(sender As Object, e As EventArgs)
            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 finalPathToStore 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)
                    finalPathToStore = "~/BookPictures/" & pathToStore
                    cmd.Parameters.AddWithValue("@BookPicPath", finalPathToStore)
                End If
                con.Open()
                cmd.ExecuteNonQuery()
                lblStatus.Text = "Book Record saved successfully"
                lblStatus.ForeColor = System.Drawing.Color.Green
                ClearControls()
                BindDataList()
            Catch generatedExceptionName As Exception
                lblStatus.Text = "Book Record could not be saved"
                lblStatus.ForeColor = System.Drawing.Color.Red
            Finally
                con.Close()
                cmd.Dispose()
                fileName = Nothing
                filePath = Nothing
                getPath = Nothing
                pathToStore = Nothing
                finalPathToStore = Nothing
            End Try
        End Sub

        Private Sub BindDataList()
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand("GetBookDetails_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            Try
                adp.Fill(dt)
                If dt.Rows.Count > 0 Then
                    dlBooks.DataSource = dt
                    dlBooks.DataBind()

                    For i As Integer = 0 To dt.Rows.Count - 1
                        If Not String.IsNullOrEmpty(Convert.ToString(dt.Rows(i)("BookPicPath"))) Then
                            Dim img As Image = DirectCast(dlBooks.Controls(i).FindControl("ImgBookPic"), Image)
                            img.ImageUrl = Convert.ToString(dt.Rows(i)("BookPicPath"))
                        End If
                    Next
                End If
            Catch generatedExceptionName As Exception
                lblStatus.Text = "Book record could not be retrieved"
                lblStatus.ForeColor = System.Drawing.Color.Red
            Finally
                con.Close()
                dt.Clear()
                dt.Dispose()
                cmd.Dispose()
            End Try
        End Sub

        Protected Sub btnCancel_Click(sender As Object, e As EventArgs)
            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
            txtBookName.Focus()
        End Sub
    End Class

    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 and display in DataList 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."
    Read More
    Next
    « Prev Post
    Previous
    Next Post »

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

    Subscribe to: Post Comments (Atom)