 |
| Click on image to enlarge |
Note: uploaded image will be converted to binary format before storing to database.
 |
| Image stored in binary format in database. Click to enlarge |
- 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?
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> </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.
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> </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."
17 comments
Click here for commentsThanks. Its really nice. Please go ahead and keep posting more articles.
ReplyNice article but this will throw an exception if for example data is stored without image.....
Replythanks, good stuff.
ReplyThanks..i am glad you found this article useful..
ReplyHi, 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..
ReplyHello Sai..thanks for appreciating my work..I will post more articles related to this one..so stay connected and keep reading..:)
ReplyHello Lalit, thank you very much for this great article, Really, appreciate you effort....
ReplyLot of thanks ..i almost read all your article..all are nice..
ReplyHi vaaru..thanks for appreciating my work..stay connected and keep reading..:)
ReplyHello Abhishek..thanks for appreciating my work..it is always nice to hear that my article helped anyone..stay connected and keep reading..:)
ReplyPlease provide an article on same tutorial using MySQL server.
ReplyReally appreciating, helpful. Am a fans of yours article.
ReplyThanks 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..:)
Replynice
ReplyThanks jayshree for your valuable comments. Stay connected and keep reading for more useful updates..:)
Replyvery 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
ReplyThank U :-) It worked for me.
ReplyIf 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..