Upload and save image in binary format in Sql server database and retrieve, bind to Repeater in Asp.Net C#, VB

Click on image to enlarge

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 binary form in Sql server database and then read/retrieve the record along with image and display in Repeater 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 file upload control in folder?
• How to store uploaded image in binary format in Sql server database?
• How to read/retrieve the image stored in binary form from the sql server database and show in Repeater data control?

In previous related article i explained and Save image in folder and path in Sql and retrieve and bind to Repeater and Bind, edit, update and delete data in Repeater and  How to Upload image in folder and save path in Sql, then read, bind in Gridview and Store image in binary format in Sql and retrieve, bind to Gridview and Save image in binary format in Sql server and read , bind to DataList and Bind,Save and perform run time calculations in asp.net Repeater

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 will be inserted it will get binded to the Repeater control as show in the sample image shown above.

Implementation: Let's create a demo website page to insert data including image in database and retrieve that data and fill in Repeater 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)
BookPic	Varbinary(MAX)
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),             

                @BookPic                          VARBINARY(MAX)=NULL,

                @BookPicName              VARCHAR(100)=NULL,

                @BookPicPath                 VARCHAR(200)=NULL

AS

BEGIN 

                SET NOCOUNT ON;

                INSERT INTO BookDetails(BookName,Author,Publisher,Price,BookPic,BookPicName,BookPicPath)

    VALUES (@BookName,@Author,@Publisher,@Price,@BookPic,@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 so that we can track the image from the folder if required and this image will be converted to binary format and stored in database.

• In the <Form> tag of the Asp.Net design page(Default.aspx) create the page as:

<fieldset style="width:400px;">

    <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 colspan="2">

            <asp:Repeater ID="rptBooks" runat="server">

            <ItemTemplate>

            <table>

            <tr>

            <td align="center">

                <asp:Image ID="ImgBookPic" runat="server" Height="100px" Width="100px" /><br />              

                </td>

            <td>

           

            <b>BookName:</b> <%#Eval("BookName")%><br />

            <b>Author:</b> <%#Eval("Author")%><br />

            <b>Publisher:</b> <%#Eval("Publisher")%><br />

            <b>Price:</b> <%#Eval("Price")%><br />

            <b>Book Name:</b> <%#Eval("BookPicName") %>

            </td>

            </tr>

            </table>

            </ItemTemplate>

            </asp:Repeater>

            </td></tr>

    </table>  

    </fieldset>

Asp.Net C# Code to store image and data and show in Repeater

• 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)

        {

            BindRepeater();

        }

    }

    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;

        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);

                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();

            BindRepeater();

        }

        catch (Exception ex)

        {

            lblStatus.Text = "Book Record could not be saved";

            lblStatus.ForeColor = System.Drawing.Color.Red;

        }

        finally

        {

            con.Close();

            cmd.Dispose();

            fileName = null;

            filePath = null;

            fs = null;

            br = null;

            getPath = null;

            pathToStore = null;

            finalPathToStore = null;

        }

    }

   

    private void BindRepeater()

    {

        DataTable dt = new DataTable();

        byte[] bytes;

        string base64String = string.Empty;

        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)

            {

                rptBooks.DataSource = dt;

                rptBooks.DataBind();               

                for (int i = 0; i < dt.Rows.Count; i++)

                {

                    if (!string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["BookPicPath"])))

                    {

                        bytes = (byte[])dt.Rows[i]["BookPic"];

                        base64String = Convert.ToBase64String(bytes, 0, bytes.Length);

                        Image img = (Image)rptBooks.Controls[i].FindControl("ImgBookPic");

                        img.ImageUrl = "data:image/png;base64," + base64String;

                    }

                }              

            }

        }

        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();

            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;      

        txtBookName.Focus();

    }
}

Asp.Net VB Section:

• Design the page as :

 <fieldset style="width:400px;">

    <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" />

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

            </td></tr>

        <tr><td>&nbsp;</td><td>

            <asp:Label ID="lblStatus" runat="server"></asp:Label>           

            </td></tr>

       

        <tr><td colspan="2">

            <asp:Repeater ID="rptBooks" runat="server">

            <ItemTemplate>

            <table>

            <tr>

            <td align="center">

                <asp:Image ID="ImgBookPic" runat="server" Height="100px" Width="100px" /><br />              

                </td>

            <td>           

            <b>BookName:</b> <%#Eval("BookName")%><br />

            <b>Author:</b> <%#Eval("Author")%><br />

            <b>Publisher:</b> <%#Eval("Publisher")%><br />

            <b>Price:</b> <%#Eval("Price")%><br />

            <b>Book Name:</b> <%#Eval("BookPicName") %>

            </td>

            </tr>

            </table>

            </ItemTemplate>

            </asp:Repeater>          

            </td></tr>

    </table>  

    </fieldset>

  

Asp.Net VB Code to store image and data and show in Repeater

• 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

            BindRepeater()

        End If

    End Sub

    Protected Sub btnSave_Click(sender As Object, e As 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 finalPathToStore 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)

                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()

            BindRepeater()

        Catch ex 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

            fs = Nothing

            br = Nothing

            getPath = Nothing

            pathToStore = Nothing

            finalPathToStore = Nothing

        End Try

    End Sub

    Private Sub BindRepeater()

        Dim dt As New DataTable()

        Dim bytes As Byte()

        Dim base64String As String = String.Empty

        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

                rptBooks.DataSource = dt

                rptBooks.DataBind()

                For i As Integer = 0 To dt.Rows.Count - 1

                    If Not String.IsNullOrEmpty(Convert.ToString(dt.Rows(i)("BookPicPath"))) Then

                        bytes = DirectCast(dt.Rows(i)("BookPic"), Byte())

                        base64String = Convert.ToBase64String(bytes, 0, bytes.Length)

                        Dim img As Image = DirectCast(rptBooks.Controls(i).FindControl("ImgBookPic"), Image)

                        img.ImageUrl = "data:image/png;base64," + base64String

                    End If

                Next

            End If

        Catch ex As Exception

            lblStatus.Text = "Book record could not be retrieved"

            lblStatus.ForeColor = System.Drawing.Color.Red

        Finally

            con.Close()

            dt.Clear()

            dt.Dispose()

            bytes = Nothing

            base64String = Nothing

            cmd.Dispose()

        End Try

    End Sub

    Protected Sub btnCancel_Click(sender As Object, e As 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

        txtBookName.Focus()

    End Sub 

End Class

Now over to you:

" I hope you have got the way to upload image in folder , store image in binary format in database and retrieve the image path from sql server database and display in Repeater 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."

• Tweet
• Share
• Share
• Share

Related Post