Introduction
Prepare Database Table and store procedure:
First design the database table and stored procedure to store and retrieve image information from database table. Execute the below table and store procedure script into your test database before design your asp.net page.
CREATE TABLE [dbo].[tblUploadedImagedetails](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [varchar](100) NOT NULL,
[ImageContent] [image] NOT NULL,
[Createdby] [varchar](100) NOT NULL,
[CreatedDt] [datetime] NOT NULL,
[Updatedby] [varchar](100) NULL,
[UpdatedDt] [datetime] NULL,
[Active] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Below sql server stored procedure will be used to insert the images details, select all images from database and select specific image from table based on image id.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_ImageUpload]
(
@pvchImageId int =0,
@pvchImageName varchar(100)=null,
@pvchImage image=null,
@pvchCreatedBy varchar(100)=null,
@pvchAction varchar(50)=null,
@pIntErrDescOut int output
)
AS
BEGIN
if(@pvchAction='select' and @pvchImageId=0)
begin
select ROW_NUMBER()OVER (ORDER BY ImageID) as rowid ,ImageID, ImageName, ImageContent from tblUploadedImagedetails
;
end
if(@pvchAction='select' and @pvchImageId!=0)
begin
select ROW_NUMBER()OVER (ORDER BY ImageID) as rowid ,ImageID, ImageName, ImageContent from tblUploadedImagedetails
where ImageID = @pvchImageId;
end
else if(@pvchAction='insert')
begin
INSERT INTO tblUploadedImagedetails(ImageName,ImageContent,Createdby,CreatedDt,active)
VALUES(@pvchImageName,@pvchImage,@pvchCreatedBy,GETDATE(),1);
end
IF (@@ERROR <> 0)
BEGIN
SET @pIntErrDescOut = 1
END
ELSE
BEGIN
SET @pIntErrDescOut = 0
END
END
ASP.NET project
Create an asp.net project and drag the file upload to upload images and gridvew control to display the stored images into the aspx page. Take the below code and place into your aspx page.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="SaveImagesToDB._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2 style="color: #0066FF; font-weight: bold;">
<u>Save Image to SQL server</u></h2>
</div>
<div>
<asp:FileUpload ID="ImageUploadToDB" Width="300px" runat="server" />
<asp:Button ID="btnUploadImage" runat="server" Text="Save Image to DB" OnClick="btnUploadImage_Click"
ValidationGroup="vg" /><br />
<br />
<asp:Label ID="lblMsg" runat="server" ForeColor="Green" Text=""></asp:Label>
<h2 style="text-decoration: underline; font-weight: bold; color: #0066FF;">
Image list Details
</h2>
<asp:GridView ID="GridViewUploadedImageFile" runat="server" EmptyDataText="No files found!"
AutoGenerateColumns="False" Font-Names="Verdana" AllowPaging="true" PageSize="5"
Width="40%" OnPageIndexChanging="GridViewUploadedImageFile_PageIndexChanging"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" OnRowDataBound="GridViewUploadedImageFile_RowDataBound"
DataKeyNames="ImageID,ImageContent">
<AlternatingRowStyle BackColor="#FFD4BA" />
<HeaderStyle Height="30px" BackColor="#FF9E66" Font-Size="15px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
<RowStyle Height="20px" Font-Size="13px" HorizontalAlign="Center" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
<Columns>
<asp:BoundField DataField="rowid" HeaderText="#" HeaderStyle-Width="10%" />
<asp:BoundField DataField="ImageID" HeaderText="#" Visible="false" HeaderStyle-Width="10%" />
<asp:BoundField DataField="ImageName" HeaderText="Image Name" HeaderStyle-Width="25%" />
<asp:TemplateField HeaderText="List of Images" HeaderStyle-Width="40%">
<ItemTemplate>
<asp:Image ID="UsrImages" runat="server" Height="35px" Width="35px" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Namespace Used:
using System;
using System.Web;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Configuration;
Store image to SQL server table :
In code behind part, place the below code in upload button click event and this will store the uploaded image into database table. First we need to verify whether the image is selected or not when clicks on the btnUploadImage button and convert the image into byte array then store into database table. If image is selected then gets the image name using GetFileName method to store the image name into database table. Then get the size of an uploaded image file and store into byte array and using httppostedfile InputStream read method read the file into the byte array and store into database table. Below I’m using sql server stored procedure SP_ImageUpload to insert the images details into table. Once uploaded images saved into table then display the image into page using LoadImages()method.
protected void btnUploadImage_Click(object sender, EventArgs e)
{
string ImageName = string.Empty;
byte[] Image = null;
if (ImageUploadToDB.PostedFile != null && ImageUploadToDB.PostedFile.FileName != "")
{
ImageName = Path.GetFileName(ImageUploadToDB.FileName);
Image = new byte[ImageUploadToDB.PostedFile.ContentLength];
HttpPostedFile UploadedImage = ImageUploadToDB.PostedFile;
UploadedImage.InputStream.Read(Image, 0, (int)ImageUploadToDB.PostedFile.ContentLength);
}
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_ImageUpload";
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchImageName", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchImage", SqlDbType.Image));
cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@pvchAction"].Value = "insert";
cmd.Parameters["@pvchImageName"].Value = ImageName;
cmd.Parameters["@pvchImage"].Value = Image;
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";
cmd.ExecuteNonQuery();
int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
}
}
LoadImages();
}
Retreive image from SQL server table:
Once uploaded images inserted into table then need to display in the aspx page using gridview control. Below LoadImages() method will be used to display the images in gridview control. Here I’m using stored procedure to get all image record from database. To display this uploaded images we need to use http handler, so right click on project and click Add new item then select Generic handler and changed the name as DisplayImage.ashx then place the below code in httphandler class and using this handler we get the specific image from database and convert as memorystream object then create an image from memory stream and save this image in specific stream and specific format.
string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlDataAdapter SqlAda;
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadImages();
}
}
private void LoadImages()
{
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_ImageUpload";
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters["@pvchAction"].Value = "select";
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
SqlAda = new SqlDataAdapter(cmd);
ds = new DataSet();
SqlAda.Fill(ds);
GridViewUploadedImageFile.DataSource = ds;
GridViewUploadedImageFile.DataBind();
}
}
}
protected void GridViewUploadedImageFile_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string ImageID = GridViewUploadedImageFile.DataKeys[e.Row.RowIndex].Values[0].ToString();
System.Web.UI.WebControls.Image UsrImages = (System.Web.UI.WebControls.Image)e.Row.FindControl("UsrImages");
UsrImages.ImageUrl = "DisplayImage.ashx?ImgId=" + ImageID;
}
}
In the above gridview rowdatabound event, pass the image id to httphandler to display the image from database.
DisplayImage Handler:
public class DisplayImage : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.Clear();
context.Response.ContentType = "image/jpeg";
if (context.Request.QueryString["ImgId"] != null)
{
int imgId = 0;
imgId = Convert.ToInt16(context.Request.QueryString["imgId"]);
MemoryStream memoryStream = new MemoryStream(GetImageFromDB(imgId), false);
System.Drawing.Image imgFromDataBase = System.Drawing.Image.FromStream(memoryStream);
imgFromDataBase.Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg); }
}
private byte[] GetImageFromDB(int ImgId)
{
string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlDataAdapter SqlAda;
DataSet ds;
byte[] btImage = null;
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_ImageUpload";
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchImageId", SqlDbType.Int));
cmd.Parameters["@pvchAction"].Value = "select";
cmd.Parameters["@pvchImageId"].Value = ImgId;
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
SqlAda = new SqlDataAdapter(cmd);
ds = new DataSet();
SqlAda.Fill(ds);
btImage = (byte[])ds.Tables[0].Rows[0][3];
}
}
return btImage;
}
public bool IsReusable
{
get
{
return false;
}
}
}
See the below screen shot, after inserting the images into table, gridview displays the list of saved images in the page.
What do you think, is this Article is interesting, please share this Article to your friends.
Any quries? Please send a mail to dotnetcircle@gmail.com
No comments:
Post a Comment