Crystal Reports Display Images From Database Asp.Net

This example explains how to Add Show Display Logo Pictures Images In Crystal Reports Dynamically From Sql Server DataBase In Asp.Net 2.0,3.5,4.0. There are two scenarios to achieve this task.

1. When images are saved in sql server database.

2. When pictures are saved on server and name or path is saved in database.


I'll explain both cases one by one.
Display Images In Crystal Reports From Database



1. Displaying From Database
Step 1. Upload And Save Images In SqlServer DB Using FileUpload Control by following the link.

Step 2. Right click on solution explorer and select add new item >Add DataSet and name it DataSet1.


Step 3. Right click on dataset designer window and select Add DataTable and rename it as reportTable.


Step 4. Right click on DataTable and select Add > Column.
Add 3 columns ID,Name,Image and define datatype in properties windows as System.Decimal for ID ,System.String for Name, and System.Byte[] for binary column respectively and save it.



Step 5. Right click on solution explorer > Add new Item > Crystal Report



Select Project Data > ADO.NET DataSets > reportTable from available datasources and add it to right pane.


Add All the fields in right pane in next window and click on finish.


Now Drag the Image field from field explorer on crystal report.


Step 6. Add CrystalReportViewer from toolbox to Default.aspx page.

Choose CrystalReportSource1 and CrystalReport.rpt from next dialog boxes.



HTML SOURCE OF PAGE WILL LOOK LIKE
   1:  <CR:CrystalReportViewer ID="CrystalReportViewer1" 
   2:                          runat="server" 
   3:                          AutoDataBind="True" 
   4:                          ReportSourceID="CrystalReportSource1"/>
   5:  <CR:CrystalReportSource ID="CrystalReportSource1" 
   6:                          runat="server">
   7:               <Report FileName="CrystalReport.rpt"/>
   8:  </CR:CrystalReportSource>

Write below mentioned code in Page_Load Event of Default.aspx page in code behind to fetch the data from databse and fill the Dataset.

C# CODE
using System;
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;

protected void Page_Load(object sender, EventArgs e)
    {
        //Create object of dataset1.xsd we created earlier in design mode 
        DataSet1 dsReport = new DataSet1();
        
        //Create SQL Connection And Command And Fill DataSet1
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string strSelect = "SELECT ID,Name,Image FROM Img";
        SqlCommand cmd = new SqlCommand(strSelect, con);
        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
        con.Open();
        dAdapter.Fill(dsReport.Tables["reportTable"]);
        con.Close();

        //Set Report Source
        ReportDocument crystalReport = new ReportDocument();
        crystalReport.Load(Server.MapPath("CrystalReport.rpt"));
        crystalReport.SetDataSource(dsReport);
        CrystalReportViewer1.ReportSource = crystalReport;
    }

VB.NET CODE
Protected Sub Page_Load(sender As Object, e As EventArgs)
 Dim dsReport As New DataSet1()
 Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
 Dim strSelect As String = "SELECT ID,Name,Image FROM Img"
 Dim cmd As New SqlCommand(strSelect, con)
 Dim dAdapter As New SqlDataAdapter(cmd)
 con.Open()
 dAdapter.Fill(dsReport.Tables("reportTable"))
 con.Close()
 Dim crystalReport As New ReportDocument()
 crystalReport.Load(Server.MapPath("CrystalReport.rpt"))
 crystalReport.SetDataSource(dsReport)
 CrystalReportViewer1.ReportSource = crystalReport
End Sub

Build and run the application.

2. Add Pictures From server.
Follow Steps 2 to 6 as mentioned above and save image name or path in sql server as shown below.


Write below mentioned code in code behind.

C# CODE
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using System.IO;
protected void Page_Load(object sender, EventArgs e)
    {
        DataSet1 imageDataSet = new DataSet1();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string strSelect = "SELECT ID,Name FROM Img";
        SqlCommand cmd = new SqlCommand(strSelect, con);
        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
        con.Open();
        dAdapter.Fill(imageDataSet.Tables["reportTable"]);
        con.Close();

        for (int rowNumber = 0; rowNumber < imageDataSet.Tables["reportTable"].Rows.Count; rowNumber++)
        {
            string imgName = Server.MapPath(imageDataSet.Tables["reportTable"].Rows[rowNumber]["Name"].ToString());
            DisplayImages(imageDataSet.Tables["reportTable"].Rows[rowNumber], "Image", imgName);
        }

        ReportDocument crystalReport = new ReportDocument();
        crystalReport.Load(Server.MapPath("CrystalReport.rpt"));
        crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]);
        CrystalReportViewer1.ReportSource = crystalReport;
    }
    private void DisplayImages(DataRow row, string img, string ImagePath)
    {
        FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
        byte[] ImgData = new byte[stream.Length];
        stream.Read(ImgData, 0, Convert.ToInt32(stream.Length));
        stream.Close();
        row[img] = ImgData;
    }
VB.NET CODE
Protected Sub Page_Load(sender As Object, e As EventArgs)
 Dim imageDataSet As New DataSet1()
 Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
 Dim strSelect As String = "SELECT ID,Name FROM Img"
 Dim cmd As New SqlCommand(strSelect, con)
 Dim dAdapter As New SqlDataAdapter(cmd)
 con.Open()
 dAdapter.Fill(imageDataSet.Tables("reportTable"))
 con.Close()

 For rowNumber As Integer = 0 To imageDataSet.Tables("reportTable").Rows.Count - 1
  Dim imgName As String = Server.MapPath(imageDataSet.Tables("reportTable").Rows(rowNumber)("Name").ToString())
  DisplayImages(imageDataSet.Tables("reportTable").Rows(rowNumber), "Image", imgName)
 Next

 Dim crystalReport As New ReportDocument()
 crystalReport.Load(Server.MapPath("CrystalReport.rpt"))
 crystalReport.SetDataSource(imageDataSet.Tables("reportTable"))
 CrystalReportViewer1.ReportSource = crystalReport
End Sub
Private Sub DisplayImages(row As DataRow, Img As String, path As String)
 Dim stream As New FileStream(path, FileMode.Open, FileAccess.Read)
 Dim ImgData As Byte() = New Byte(stream.Length - 1) {}
 stream.Read(ImgData, 0, Convert.ToInt32(stream.Length))
 stream.Close()
 row(Img) = ImgData
End Sub
Build and run the application

Download Sample Code


If you like this post than join us or share

0 comments:

Find More Articles