Introduction: In this article i have explained how to upload the image through file upload control and save in binary form in Sql server database and then read/retrieve the data along with image and show in GridView data control in asp.net using both C# and VB languages.
Description: Basically you will learn the following through this article.
Description: Basically you will learn the following through this article.
- How to upload image in website folder through file upload?
- How to save uploaded image in Sql server database in binary format ?
- How to read/retrieve the image stored in binary format from the sql server database and bind and display in GridView data control?
I have created a sample 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 GridView 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 Grid View 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> </td><td>
<asp:Label ID="lblStatus" runat="server"></asp:Label>
</td></tr>
<tr><td colspan="2">
<asp:GridView ID="grdBooks" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<center> <asp:Image ID="ImgBookPic" runat="server" Height="80px" Width="80px" /><br />
<asp:Label ID="lblBookPicName" runat="server" Text='<%#Eval("BookPicName") %>'></asp:Label>
</center>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="BookName" HeaderText="Book Name" ItemStyle-HorizontalAlign="Center"/>
<asp:BoundField DataField="Author" HeaderText="Author" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="Publisher" HeaderText="Publisher" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-HorizontalAlign="Center" />
</Columns>
</asp:GridView>
</td></tr>
</table>
</fieldset>
Asp.Net C# Code to store image in binary format in database, then retrieve and bind in GridView
- In the code behind file (default.aspx.cs) write the code as:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
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)
{
BindGridView();
}
}
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();
BindGridView();
}
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 BindGridView()
{
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)
{
grdBooks.DataSource = dt;
grdBooks.DataBind();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["BookPic"])))
{
bytes = (byte[])dt.Rows[i]["BookPic"];
base64String = Convert.ToBase64String(bytes, 0, bytes.Length);
Image img = (Image)grdBooks.Rows[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 described in C# section above.
Asp.Net VB Code to store image in binary format in database, then retrieve and show in Grid View
- 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
BindGridView()
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 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()
BindGridView()
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 BindGridView()
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
grdBooks.DataSource = dt
grdBooks.DataBind()
For i As Integer = 0 To dt.Rows.Count - 1
If Not String.IsNullOrEmpty(Convert.ToString(dt.Rows(i)("BookPic"))) Then
bytes = DirectCast(dt.Rows(i)("BookPic"), Byte())
base64String = Convert.ToBase64String(bytes, 0, bytes.Length)
Dim img As Image = DirectCast(grdBooks.Rows(i).FindControl("ImgBookPic"), Image)
img.ImageUrl = "data:image/png;base64," & base64String
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()
bytes = Nothing
base64String = Nothing
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
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.