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



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



Add the following 2 controls to get images:

1. FileUpload
2. 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.







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.



You will then see this screen:



The last screen will be:



Click on the "Finish" button.

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

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

 

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

Also click "OK" to finish this process.



Drag your fields in Crystal Reports from the 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".



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



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

1. From the toolbox select "Reporting"
2. Inside that select "CrystalReportViewer"
3. Drag to inside the form tag

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.

<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 3 controls to the page:

1. File upload
2. Image control
3. Button

 

 

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





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.


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



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.

 

 

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

Then right-click on the image you inserted.

 

 

1. Select the "Picture" tab from it
2. Select the Graphic location

 

 

Then you will see a pop up like this:

 

 

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

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

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.



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



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