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

Here, we will see how to store and retrieve an 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:
  1. Store image URLs in the database as a normal string.
  2. Store image as binary data.
The purpose of this article is:
  1. Show how you to upload and save images directly in a SQL Server database table as binary data and display in Crystal Reports.
  2. 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".

Myimagebox

Add the following 2 controls to get images:
  1. FileUpload
  2. Button
controls 
  1. <form id="form1" runat="server">  
  2.     <div style="padding-left: 200px; padding-right: 300px;">  
  3.         <table width="1024px">  
  4.             <tr>  
  5.                 <td>  
  6.                     <div style="text-align: center; color: Black; font-size: 18px;">  
  7.           Store and Retrieve Image in Crystal Reports from SQL Database using ASP.NET  
  8.                     </div>  
  9.                 </td>  
  10.             </tr>  
  11.             <tr>  
  12.                 <td>  
  13.                     <div style="line-height: 40px;">  
  14.                            
  15.                     </div>  
  16.                 </td>  
  17.             </tr>  
  18.             <tr>  
  19.                 <td align="center">  
  20.                     <asp:FileUpload ID="FileUpload1" runat="server" /><br />  
  21.                     <asp:Button ID="Button1" runat="server" Text="Upload Photo" />  
  22.                 </td>  
  23.             </tr>  
  24.         </table>  
  25.     </div>  
  26. </form>  
Create a table in the database for storing image details.
  1. CREATE TABLE [dbo].[MyphotoStore]    
  2. (    
  3.     [ImageID] [int] IDENTITY(1,1) primary key NOT NULL,    
  4.     [ImageName] [nvarchar](50) NULL,    
  5.     [ImageBytes] [image] NULL,    
  6.     [ImageSize] [bigintNULL,    
  7. )
Table
The following SQL Server Stored Procedure will be used to insert the image details.
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. CREATE PROC [dbo].[Usp_mypicsstore]  
  6. @ImageName   nvarchar(50),  
  7. @ImageBytes     image,  
  8. @ImageSize      bigint  
  9. as  
  10. begin  
  11. INSERT INTO [dbo].[MyphotoStore] (  
  12.   [ImageName]  
  13. , [ImageBytes]  
  14. , [ImageSize])  
  15.             VALUES (  
  16.   @ImageName,  
  17.   @ImageBytes,  
  18.   @ImageSize)  
  19. return @@IDENTITY  
  20. end  
  21. GO  
Now on Myimagebox.aspx
 
Namespace Used
  1. using System;  
  2. using System.Collections;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Web;  
  6. using System.Web.Security;  
  7. using System.Web.UI;  
  8. using System.Web.UI.HtmlControls;  
  9. using System.Web.UI.WebControls;  
  10. using System.Data.SqlClient;  

Store image to SQL Server table

  1. protected void Button1_Click(object sender, EventArgs e)  
  2. {  
  3.     if (FileUpload1.HasFile)  
  4.     {  
  5.         byte[] imageBytes = FileUpload1.FileBytes;  // for getting bytes of image in byte array  
  6.         SqlCommand cmd = new SqlCommand("Usp_mypicsstore", con);  
  7.         con.Open();  
  8.         cmd.CommandType = CommandType.StoredProcedure;  
  9.         cmd.Parameters.AddWithValue("@ImageName", FileUpload1.FileName); // storing name of image  
  10.         cmd.Parameters.AddWithValue("@ImageBytes", imageBytes); // bytes  
  11.         cmd.Parameters.AddWithValue("@ImageSize", FileUpload1.PostedFile.ContentLength); // getting file size  
  12.         int returnvalue = cmd.ExecuteNonQuery();  
  13.        con.Close();  
  14.        cmd.Dispose();  
  15.        if (returnvalue > 0)  
  16.       {  
  17.             ScriptManager.RegisterStartupScript(thisthis.GetType(), "alert""alert('Image Saved Successfully');"true);  
  18.        }  
  19.    }  
  20. }
Store image to SQL server table
 
Now run your application and save the images in the database.

Run your application

Image Saved
 
Image in Table

Crystal Reports Part

Let’s add Crystal Reports to the project.

add Crystal report to project

Crystal report to project

Crystal Report format

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

Retrieving record by ID:
  1. create proc Usp_GetPicbyImageID  
  2. @ImageID int  
  3. as   
  4. begin  
  5. SELECT  
  6.             ps.ImageID,  
  7.             ps.ImageName,  
  8.             ps.ImageSize,  
  9.             ps.ImageBytes  
  10. FROM MyphotoStore ps  
  11. where ps.ImageID = @ImageID  
  12. end  
From Field Explorer select "Database Expert".
Explorer Select Database Expert

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

Database Expert Select

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:

Last Screen

Click on the "Finish" button.

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

Finish Button
  1. Click on "(dbo)".
  2. Inside that you will see Tables and Stored Procedure.
  3. Click Storeprocedure (because we will use Storeprocedure).
  4. Then you will see all Storeprocedure Name here.
  5. Select the Storeprocedure you have created.
  6. Click "( > )".
Select the Storeprocedure  
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.

select option

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
  1. From the toolbox select "Reporting"
  2. Inside that select "CrystalReportViewer"
  3. Drag to inside the form tag
select CrystalReportViewer
  1. Also add a TextBox to enter the ImageID
  2. Drag a button to display the report
  3. See the following, like this screen will appear to you.
screen will appear
  1. <form id="form1" runat="server">  
  2.     <div>  
  3.         <asp:TextBox ID="txtImageID" runat="server"></asp:TextBox>  
  4.         <asp:Button ID="Button1" runat="server" Text="View Report"  
  5.             onclick="Button1_Click" />  
  6.         <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />  
  7.     </div>  
  8. </form>  
On the click event of the Button use the following code to display the report.
 
Use Namespace
  1. using System.Data;    
  2. using System.Configuration;    
  3. using System.Data.SqlClient;    
  4. using CrystalDecisions.CrystalReports.Engine;    
  5. protected void GenerateReport()    
  6. {    
  7.        if (txtImageID.Text == "")    
  8.        {    
  9.             ScriptManager.RegisterStartupScript(thisthis.GetType(), "alert""alert('Please Enter ImageID');"true);    
  10.       }    
  11.       else    
  12.       {    
  13.             SqlCommand cmd = new SqlCommand("Usp_GetPicbyImageID", con);    
  14.             cmd.CommandType = CommandType.StoredProcedure;    
  15.             cmd.Parameters.AddWithValue("@ImageID", txtImageID.Text); // passing textbox value to parameter    
  16.             SqlDataAdapter da = new SqlDataAdapter();    
  17.             da.SelectCommand = cmd;    
  18.             DataTable datatable = new DataTable();    
  19.             da.Fill(datatable); // getting value according to imageID and fill dataset    
  20.             ReportDocument crystalReport = new ReportDocument(); // creating object of Crystal Reports    
  21.             crystalReport.Load(Server.MapPath("~/DisplayMystore.rpt")); // path of report    
  22.             crystalReport.SetDataSource(datatable); // binding datatable    
  23.             CrystalReportViewer1.ReportSource = crystalReport;    
  24.      }    
  25. }    
  26. protected void Button1_Click(object sender, EventArgs e)    
  27. {    
  28.     GenerateReport(); // calling function    
  29. }  
>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".

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:
  1. File upload
  2. Image control
  3. Button
Add 3 controls  
 
Add a folder to the project and name it "Userpics".

Add folder to project

Solution Explorer

Here is the design of "MyImagesinfolder.aspx":
  1. <form id="form1" runat="server">  
  2.     <div style="padding-left: 200px; padding-right: 300px;">  
  3.         <table width="1024px">  
  4.             <tr>  
  5.                 <td>  
  6.                     <div style="text-align: center; color: Black; font-size: 18px;">  
  7.                         Store and Retrieve Image in Crystal Reports from Folder using ASP.NET  
  8.                     </div>  
  9.                 </td>  
  10.             </tr>  
  11.             <tr>  
  12.                 <td>  
  13.                     <div style="line-height: 40px; text-align: center;">  
  14.                         <asp:Image ID="imgpro" Height="100" ImageUrl="~/images/noImage.jpg" Width="100" runat="server" />  
  15.                     </div>  
  16.                 </td>  
  17.             </tr>  
  18.             <tr>  
  19.                 <td align="center">  
  20.                     <asp:FileUpload ID="FileUpload1" runat="server" /><br />  
  21.                     <asp:Button ID="Button1" runat="server" Text="Upload Photo" OnClick="Button1_Click" />  
  22.                 </td>  
  23.             </tr>  
  24.         </table>  
  25.     </div>  
  26. </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.
  1. CREATE TABLE [dbo].[MyphotoStoreinfolder]  
  2. (  
  3.     [ImageID] [int] IDENTITY(1,1) primary key NOT NULL,  
  4.     [ImageName] [nvarchar](50) NULL,  
  5.     [ImagePath] [nvarchar](200) NULL,  
  6.     [ImageSize] [bigintNULL,  
  7. ) 
Table in Database  
The following SQL Server Stored Procedure will be used to insert the image details:
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. CREATE PROC [dbo].[Usp_mypicsstoreinfolder]  
  6. @ImageName   nvarchar(50),  
  7. @ImagePath      nvarchar(200),  
  8. @ImageSize      bigint  
  9. as  
  10. begin  
  11. INSERT INTO [dbo].[MyphotoStoreinfolder] (  
  12. [ImageName]  
  13. , [ImagePath]  
  14. , [ImageSize])  
  15. VALUES (  
  16. @ImageName,  
  17. @ImagePath,  
  18. @ImageSize)  
  19. return @@IDENTITY  
  20. end  
  21. GO  
Now for the "MyImagesinfolder.aspx".

The following namespaces are used:
  1. using System;  
  2. using System.Collections;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.Security;  
  8. using System.Web.UI;  
  9. using System.Web.UI.HtmlControls;  
  10. using System.Web.UI.WebControls;  
  11. using System.Web.UI.WebControls.WebParts;  
  12. using System.Xml.Linq;  
  13. using System.Data.SqlClient;  
  14. using System.IO;  
Use the following to store the image path and image details to the SQL Server table:
  1. protected void Button1_Click(object sender, EventArgs e)  
  2. {  
  3.     if (FileUpload1.HasFile)  
  4.     {  
  5.         string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);  
  6.        if (extension.ToLower() == ".jpg" || extension.ToLower() == ".gif" || extension.ToLower() == ".png" || extension.ToLower() == ".bmp" || extension.ToLower() == ".jpeg")  
  7.       {  
  8.           string dtx = getspan(DateTime.Now);  
  9.           string filenm = dtx + FileUpload1.FileName;  
  10.           string path = Server.MapPath("~/Userpics/" + filenm);  
  11.           FileUpload1.SaveAs(path);                    
  12.           imgpro.ImageUrl = "~/Userpics/" + filenm;  
  13.           SqlCommand cmd = new SqlCommand("Usp_mypicsstoreinfolder", con);  
  14.           con.Open();  
  15.           cmd.CommandType = CommandType.StoredProcedure;  
  16.           cmd.Parameters.AddWithValue("@ImageName", FileUpload1.FileName); // storing name of image  
  17.           cmd.Parameters.AddWithValue("@ImagePath", path); // bytes  
  18.           cmd.Parameters.AddWithValue("@ImageSize", FileUpload1.PostedFile.ContentLength); // getting file size  
  19.           int returnvalue = cmd.ExecuteNonQuery();  
  20.           con.Close();  
  21.           cmd.Dispose();  
  22.           if (returnvalue > 0)  
  23.           {  
  24.               ScriptManager.RegisterStartupScript(thisthis.GetType(), "alert""alert('Image Saved Successfully');"true);
  25.           }  
  26.        }  
  27.    }  
  28. }    
  29. public string getspan(DateTime value)  
  30. {  
  31.     return value.ToString("yyyyMMddHHmmssffff"); //for making unique name of image  
  32. }  
Run the application and store some images in the folder.

store some images in folder

Table with image

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:
  1. create proc Usp_GetPicbyImageIDfromFolder  
  2. @ImageID int  
  3. as   
  4. begin  
  5. SELECT  
  6.             ps.ImageID,  
  7.             ps.ImageName,  
  8.             ps.ImageSize,  
  9.             ps.ImagePath  
  10. FROM MyphotoStoreinfolder ps  
  11. where ps.ImageID = @ImageID  
  12. end  
 
As in the preceding process, connect the Stored Procedure to Crystal Reports.

Drag an image field and details to Crystal Reports.
 
Drag image field to Crystal report 
  1. For the image (right-click on Crystal Reports Design)
  2. Select "Insert picture"
  3. Select any image you have
Select any image

image in Crystal Report

Then right-click on the image you inserted.
 
Right Click on image  
  1. Select the "Picture" tab from it
  2. Select the Graphic location
select Graphic location  
 
Then you will see a pop up like this:
 
see a pop up  
  1. Select "Stored Procedure" from the report field
  2. Select the path field from the Stored Procedure.
  3. Click the "Save" and "Close" buttons.
Save and close button
  1. Add a page to display the report
  2. Name the page "DisplayImagesfromFolder.aspx"
Add Page to display the report
  1. Also a TextBox to enter an ImageID
  2. Drag a button to display the report
  3. The following screen will appear to you.
  1. <form id="form1" runat="server">  
  2.     <div>  
  3.         <asp:TextBox ID="txtImageID" runat="server"></asp:TextBox>  
  4.         <asp:Button ID="Button1" runat="server" Text="View Report"  
  5.             onclick="Button1_Click" />  
  6.         <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />  
  7.     </div>  
  8. </form>  
On the click event of the Button write the following code to display the report.

display report

Use Namespace
 
  1. using System.Data;  
  2. using System.Configuration;  
  3. using System.Data.SqlClient;  
  4. using CrystalDecisions.CrystalReports.Engine;  
Just code the above; change the name of the report and Stored Procedure.
  1. protected void GenerateReport()    
  2. {    
  3.     if (txtImageID.Text == "")    
  4.     {    
  5.         ScriptManager.RegisterStartupScript(thisthis.GetType(), "alert""alert('Please Enter ImageID');"true);    
  6.     }    
  7.     else    
  8.     {    
  9.         SqlCommand cmd = new SqlCommand("Usp_GetPicbyImageIDfromFolder", con);    
  10.          cmd.CommandType = CommandType.StoredProcedure;    
  11.          cmd.Parameters.AddWithValue("@ImageID", txtImageID.Text); // passing textbox value to parameter    
  12.          SqlDataAdapter da = new SqlDataAdapter();    
  13.          da.SelectCommand = cmd;    
  14.          DataTable datatable = new DataTable();    
  15.          da.Fill(datatable); // getting value according to imageID and fill dataset    
  16.          ReportDocument crystalReport = new ReportDocument(); // creating object of Crystal Reports    
  17.          crystalReport.Load(Server.MapPath("~/DisplayImagefromFolder.rpt")); // path of report  
  18.          crystalReport.SetDataSource(datatable); // binding datatable    
  19.          CrystalReportViewer1.ReportSource = crystalReport;    
  20.     }    
  21. }    
  22. protected void Button1_Click(object sender, EventArgs e)    
  23. {    
  24.     GenerateReport(); // calling function    
  25. }  
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.