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.
Description: Basically you will learn the following
through this article.
 |
| Click on image to enlarge |
 |
| Click to enlarge and view saved image path in sql server database |
- 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?
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.
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> </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"]);
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
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.
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> </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
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."
6 comments
Click here for commentsawsm article, work with guid is very good. keep it up
ReplyThanks Raj Negi for your feedback..i am glad you found this article helpful..stay connected for more useful updates like this..:)
ReplyVery nice post
ReplyThank you
Your welcome.. and thanks for your valuable feedback....stay connected and keep reading for more useful updates..:)
Replythanks, 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
ReplyI 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.
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..