Image upload in SQL binary format

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# 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?






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=REDDY-PC;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();


    }



}
 
********** The End *********** 
 

Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments. Stay tuned and stay connected for more technical updates."