Store and Retrieve Image in Crystal Reports From SQL Database and Folder Using ASP.Net

Introduction

Saving and displaying images in database tables is a common requirement in ASP.NET projects.

There are two ways to store images in a database:

Store image URLs in the database as a normal string.
Store image as binary data.

The purpose of this article is:

Show how you to upload and save images directly in a SQL Server database table as binary data and display in Crystal Reports.
Show how you to store images in a folder and display in Crystal Reports.

Use the following procedure to do this.

Let’s start with 1 point.

Start by creating an ASP.NET web application and name it "CrystalreportImages".

creating web application

Then add a web form and name it as "Myimagebox.aspx".

Add the following 2 controls to get images:

FileUpload
Button

<form id="form1" runat="server">
<div style="padding-left: 200px; padding-right: 300px;">
<table width="1024px">
<tr>
<td>
<div style="text-align: center; color: Black; font-size: 18px;">
Store and Retrieve Image in Crystal Reports from SQL Database using ASP.NET
</div>
</td>
</tr>
<tr>
<td>
<div style="line-height: 40px;">
</div>
</td>
</tr>
<tr>
<td align="center">
<asp:FileUpload ID="FileUpload1" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Upload Photo" />
</td>
</tr>
</table>
</div>
</form>

Create a table in the database for storing image details.

CREATE TABLE [dbo].[MyphotoStore]
(
[ImageID] [int] IDENTITY(1,1) primary key NOT NULL,
[ImageName] [nvarchar](50) NULL,
[ImageBytes] [image] NULL,
[ImageSize] [bigint] NULL,
)

The following SQL Server Stored Procedure will be used to insert the image details.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Usp_mypicsstore]
@ImageName nvarchar(50),
@ImageBytes image,
@ImageSize bigint
as
begin
INSERT INTO [dbo].[MyphotoStore] (
[ImageName]
, [ImageBytes]
, [ImageSize])
VALUES (
@ImageName,
@ImageBytes,
@ImageSize)
return @@IDENTITY
end
GO

Now on Myimagebox.aspx

Namespace Used

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

Store image to SQL Server table

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
byte[] imageBytes = FileUpload1.FileBytes; // for getting bytes of image in byte array
SqlCommand cmd = new SqlCommand("Usp_mypicsstore", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ImageName", FileUpload1.FileName); // storing name of image
cmd.Parameters.AddWithValue("@ImageBytes", imageBytes); // bytes
cmd.Parameters.AddWithValue("@ImageSize", FileUpload1.PostedFile.ContentLength); // getting file size
int returnvalue = cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
if (returnvalue > 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Image Saved Successfully');", true);
}
}
}

Now run your application and save the images in the database.

Crystal Reports Part

Let’s add Crystal Reports to the project.

add Crystal report to project

Create the following Stored Procedure for displaying an image and the image details in the report.

Retrieving record by ID:

create proc Usp_GetPicbyImageID
@ImageID int
as
begin
SELECT
ps.ImageID,
ps.ImageName,
ps.ImageSize,
ps.ImageBytes
FROM MyphotoStore ps
where ps.ImageID = @ImageID
end

From Field Explorer select "Database Expert".

From Database Expert select "(OLE DB) (ADO)".

From OLE DB select "Microsoft OLE DB Provider for SQL Server".

Then click on the "Next" button.

OLE DB

You will then see this screen:

screen

The last screen will be:

Click on the "Finish" button.

After clicking on the "Finish" button you will see this screen:

Click on "(dbo)".
Inside that you will see Tables and Stored Procedure.
Click Storeprocedure (because we will use Storeprocedure).
Then you will see all Storeprocedure Name here.
Select the Storeprocedure you have created.
Click "( > )".

Then this pop-up will appear to you; "click OK".

Also click "OK" to finish this process.

pop up

Drag your fields in Crystal Reports from the Stored Procedure.

Crystal Report from Stored procedure

To test your report use Main Report Preview.

1. You will see a pop-up asking for a selection; select option 2 from it.

Afterwards this screen will pop up asking for the ImageID; enter the ImageID then click "OK".

pop up asking ImageID

You will see an image in the report; that means it's working.

Image on Report

Add a new form to the project and name it "DisplayReport.aspx".

Add New Form

From the toolbox select "Reporting"
Inside that select "CrystalReportViewer"
Drag to inside the form tag

Also add a TextBox to enter the ImageID
Drag a button to display the report
See the following, like this screen will appear to you.

<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtImageID" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="View Report"
onclick="Button1_Click" />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</div>
</form>

On the click event of the Button use the following code to display the report.

Use Namespace

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
protected void GenerateReport()
{
if (txtImageID.Text == "")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Please Enter ImageID');", true);
}
else
{
SqlCommand cmd = new SqlCommand("Usp_GetPicbyImageID", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ImageID", txtImageID.Text); // passing textbox value to parameter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable datatable = new DataTable();
da.Fill(datatable); // getting value according to imageID and fill dataset
ReportDocument crystalReport = new ReportDocument(); // creating object of Crystal Reports
crystalReport.Load(Server.MapPath("~/DisplayMystore.rpt")); // path of report
crystalReport.SetDataSource(datatable); // binding datatable
CrystalReportViewer1.ReportSource = crystalReport;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
GenerateReport(); // calling function
}

>Run the application

Use the following procedure to run the application and set DisplayReport.aspx as the startup page.

1. Enter the ImageID then click "View report".

You have finally displayed the image in the report.

Point 2

Add a new Web Form; name it "MyImagesinfolder.aspx".

Add New Web Form

Add 3 controls to the page:

File upload
Image control
Button

Add a folder to the project and name it "Userpics".

Add folder to project

Here is the design of "MyImagesinfolder.aspx":

<form id="form1" runat="server">
<div style="padding-left: 200px; padding-right: 300px;">
<table width="1024px">
<tr>
<td>
<div style="text-align: center; color: Black; font-size: 18px;">
Store and Retrieve Image in Crystal Reports from Folder using ASP.NET
</div>
</td>
</tr>
<tr>
<td>
<div style="line-height: 40px; text-align: center;">
<asp:Image ID="imgpro" Height="100" ImageUrl="~/images/noImage.jpg" Width="100" runat="server" />
</div>
</td>
</tr>
<tr>
<td align="center">
<asp:FileUpload ID="FileUpload1" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Upload Photo" OnClick="Button1_Click" />
</td>
</tr>
</table>
</div>
</form>

And on the click event of the Button write the following code to save the image in the folder and path in the database.

Create a table in the database for storing the image details.

CREATE TABLE [dbo].[MyphotoStoreinfolder]
(
[ImageID] [int] IDENTITY(1,1) primary key NOT NULL,
[ImageName] [nvarchar](50) NULL,
[ImagePath] [nvarchar](200) NULL,
[ImageSize] [bigint] NULL,
)

The following SQL Server Stored Procedure will be used to insert the image details:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Usp_mypicsstoreinfolder]
@ImageName nvarchar(50),
@ImagePath nvarchar(200),
@ImageSize bigint
as
begin
INSERT INTO [dbo].[MyphotoStoreinfolder] (
[ImageName]
, [ImagePath]
, [ImageSize])
VALUES (
@ImageName,
@ImagePath,
@ImageSize)
return @@IDENTITY
end
GO

Now for the "MyImagesinfolder.aspx".

The following namespaces are used:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;

Use the following to store the image path and image details to the SQL Server table:

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
if (extension.ToLower() == ".jpg" || extension.ToLower() == ".gif" || extension.ToLower() == ".png" || extension.ToLower() == ".bmp" || extension.ToLower() == ".jpeg")
{
string dtx = getspan(DateTime.Now);
string filenm = dtx + FileUpload1.FileName;
string path = Server.MapPath("~/Userpics/" + filenm);
FileUpload1.SaveAs(path);
imgpro.ImageUrl = "~/Userpics/" + filenm;
SqlCommand cmd = new SqlCommand("Usp_mypicsstoreinfolder", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ImageName", FileUpload1.FileName); // storing name of image
cmd.Parameters.AddWithValue("@ImagePath", path); // bytes
cmd.Parameters.AddWithValue("@ImageSize", FileUpload1.PostedFile.ContentLength); // getting file size
int returnvalue = cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
if (returnvalue > 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Image Saved Successfully');", true);
}
}
}
}
public string getspan(DateTime value)
{
return value.ToString("yyyyMMddHHmmssffff"); //for making unique name of image
}

Run the application and store some images in the folder.

In the following image you can see the image uploaded in the folder.

the image uploaded in folder

Add a new Crystal Reports as shown in the above example.

Add new crystal report

Name it "DisplayImagefromFolder.rpt".

Created a Stored Procedure for displaying the image and image details in the report.

Use the following to retrieve a record by ID:

create proc Usp_GetPicbyImageIDfromFolder
@ImageID int
as
begin
SELECT
ps.ImageID,
ps.ImageName,
ps.ImageSize,
ps.ImagePath
FROM MyphotoStoreinfolder ps
where ps.ImageID = @ImageID
end

As in the preceding process, connect the Stored Procedure to Crystal Reports.

Drag an image field and details to Crystal Reports.

For the image (right-click on Crystal Reports Design)
Select "Insert picture"
Select any image you have

Then right-click on the image you inserted.

Select the "Picture" tab from it
Select the Graphic location

Then you will see a pop up like this:

Select "Stored Procedure" from the report field
Select the path field from the Stored Procedure.
Click the "Save" and "Close" buttons.

Add a page to display the report
Name the page "DisplayImagesfromFolder.aspx"

Also a TextBox to enter an ImageID
Drag a button to display the report
The following screen will appear to you.

<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtImageID" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="View Report"
onclick="Button1_Click" />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</div>
</form>

On the click event of the Button write the following code to display the report.

display report

Use Namespace

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;

Just code the above; change the name of the report and Stored Procedure.

protected void GenerateReport()
{
if (txtImageID.Text == "")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Please Enter ImageID');", true);
}
else
{
SqlCommand cmd = new SqlCommand("Usp_GetPicbyImageIDfromFolder", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ImageID", txtImageID.Text); // passing textbox value to parameter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable datatable = new DataTable();
da.Fill(datatable); // getting value according to imageID and fill dataset
ReportDocument crystalReport = new ReportDocument(); // creating object of Crystal Reports
crystalReport.Load(Server.MapPath("~/DisplayImagefromFolder.rpt")); // path of report
crystalReport.SetDataSource(datatable); // binding datatable
CrystalReportViewer1.ReportSource = crystalReport;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
GenerateReport(); // calling function
}

Run your application set "DisplayReport.aspx" as the startup page.

Enter an ImageID and click "View report".

Enter ImageID click View report

You have finally displayed an image in a report from a folder.

12 Comments

Join the conversation! Your thoughts help the community grow.

sanjib manna8y ago
Want quick help..
sanjib manna8y ago
How to show all images in one crystal report side by side in C# windows application
Ch Hassan8y ago
Gr8 really helped me.
amol pathe10y ago
Ok
amol pathe10y ago
Hello Sir. I am student. i am trying the first method to show the img in crystal report. I am using Visual Studio 2013 and CR for VS_13 and SQL server 2016. I try the first method but i am unable to show img on report.
- Saineshwar Bageri10y ago
  What is issue you are facing can tell with snaphot
Jari Abbas10y ago
Hello Sir. I am student. i am trying the first method to show the img in crystal report. I am using Visual Studio 2013 and CRforVS_13 and SQL server 2012. I try the first method but i am unable to show img on report.
Ammar Shaukat10y ago
everything is working fine but image is not being fetched using entity framwork
Ammar Shaukat10y ago
Hi thanks for stuff. I'm using Visual Studio 15 and CRforVS_13 with entity framework.can you please do some help on that ?
- Saineshwar Bageri10y ago
  Requires Dataset to bind to crystal report and every thing remain same
Venkat Govind11y ago
Hi I am using Visual Studio 2010 crystal report CRforVS_13_0_12 Installed i cant display image binary format ,i cant get option Graphic location
Rowshan Ali12y ago
Nice.thanks. very helpfull..