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:

  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 
 
<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;">

                        &nbsp;

                    </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,

)

Table

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);

       }
   }
}

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:

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".

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

<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".

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":

<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,

)

 
Table in Database  

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.

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:
 

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.

 
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.

<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.

Similar Articles