.....................................................aspx....................................................................
<div>
<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>
</div>
................................................................cs ................................................................................
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();
}
..............................................sql..........................................................................................
talble
CREATE TABLE [dbo].[BookDetails](
[BookID] [int] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](50) NULL,
[Author] [varchar](50) NULL,
[Publisher] [varchar](50) NULL,
[Price] [decimal](18, 2) NULL,
[BookPic] [varbinary](max) NULL,
[BookPicName] [varchar](100) NULL,
[BookPicPath] [varchar](200) NULL,
procedure
USE [Ekram]
GO
/****** Object: StoredProcedure [dbo].[InsertBookDetails_Sp] Script Date: 11/24/2016 22:30:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
