Crystal Reports Display Images From Database Asp.Net

Posted by Vikram Singh Rana

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.

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

---

01using System;

02using System.Data.SqlClient;

03using System.Configuration;

04using CrystalDecisions.CrystalReports.Engine;

05 

06protected void Page_Load(object sender, EventArgs e)

07    {

08        //Create object of dataset1.xsd we created earlier in design mode

09        DataSet1 dsReport = new DataSet1();

10 

11        //Create SQL Connection And Command And Fill DataSet1

12        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

13        string strSelect = "SELECT ID,Name,Image FROM Img";

14        SqlCommand cmd = new SqlCommand(strSelect, con);

15        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);

16        con.Open();

17        dAdapter.Fill(dsReport.Tables["reportTable"]);

18        con.Close();

19 

20        //Set Report Source

21        ReportDocument crystalReport = new ReportDocument();

22        crystalReport.Load(Server.MapPath("CrystalReport.rpt"));

23        crystalReport.SetDataSource(dsReport);

24        CrystalReportViewer1.ReportSource = crystalReport;

25    }

VB.NET CODE

---

01Protected Sub Page_Load(sender As Object, e As EventArgs)

02 Dim dsReport As New DataSet1()

03 Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

04 Dim strSelect As String = "SELECT ID,Name,Image FROM Img"

05 Dim cmd As New SqlCommand(strSelect, con)

06 Dim dAdapter As New SqlDataAdapter(cmd)

07 con.Open()

08 dAdapter.Fill(dsReport.Tables("reportTable"))

09 con.Close()

10 Dim crystalReport As New ReportDocument()

11 crystalReport.Load(Server.MapPath("CrystalReport.rpt"))

12 crystalReport.SetDataSource(dsReport)

13 CrystalReportViewer1.ReportSource = crystalReport

14End 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

---

01using System;

02using System.Data;

03using System.Data.SqlClient;

04using System.Configuration;

05using CrystalDecisions.CrystalReports.Engine;

06using System.IO;

07protected void Page_Load(object sender, EventArgs e)

08    {

09        DataSet1 imageDataSet = new DataSet1();

10        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

11        string strSelect = "SELECT ID,Name FROM Img";

12        SqlCommand cmd = new SqlCommand(strSelect, con);

13        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);

14        con.Open();

15        dAdapter.Fill(imageDataSet.Tables["reportTable"]);

16        con.Close();

17 

18        for (int rowNumber = 0; rowNumber < imageDataSet.Tables["reportTable"].Rows.Count; rowNumber++)

19        {

20            string imgName = Server.MapPath(imageDataSet.Tables["reportTable"].Rows[rowNumber]["Name"].ToString());

21            DisplayImages(imageDataSet.Tables["reportTable"].Rows[rowNumber], "Image", imgName);

22        }

23 

24        ReportDocument crystalReport = new ReportDocument();

25        crystalReport.Load(Server.MapPath("CrystalReport.rpt"));

26        crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]);

27        CrystalReportViewer1.ReportSource = crystalReport;

28    }

29    private void DisplayImages(DataRow row, string img, string ImagePath)

30    {

31        FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);

32        byte[] ImgData = new byte[stream.Length];

33        stream.Read(ImgData, 0, Convert.ToInt32(stream.Length));

34        stream.Close();

35        row[img] = ImgData;

36    }

VB.NET CODE

---

01Protected Sub Page_Load(sender As Object, e As EventArgs)

02 Dim imageDataSet As New DataSet1()

03 Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

04 Dim strSelect As String = "SELECT ID,Name FROM Img"

05 Dim cmd As New SqlCommand(strSelect, con)

06 Dim dAdapter As New SqlDataAdapter(cmd)

07 con.Open()

08 dAdapter.Fill(imageDataSet.Tables("reportTable"))

09 con.Close()

10 

11 For rowNumber As Integer = 0 To imageDataSet.Tables("reportTable").Rows.Count - 1

12  Dim imgName As String = Server.MapPath(imageDataSet.Tables("reportTable").Rows(rowNumber)("Name").ToString())

13  DisplayImages(imageDataSet.Tables("reportTable").Rows(rowNumber), "Image", imgName)

14 Next

15 

16 Dim crystalReport As New ReportDocument()

17 crystalReport.Load(Server.MapPath("CrystalReport.rpt"))

18 crystalReport.SetDataSource(imageDataSet.Tables("reportTable"))

19 CrystalReportViewer1.ReportSource = crystalReport

20End Sub

21Private Sub DisplayImages(row As DataRow, Img As String, path As String)

22 Dim stream As New FileStream(path, FileMode.Open, FileAccess.Read)

23 Dim ImgData As Byte() = New Byte(stream.Length - 1) {}

24 stream.Read(ImgData, 0, Convert.ToInt32(stream.Length))

25 stream.Close()

26 row(Img) = ImgData

27End Sub

Build and run the application

--

Regards :

Vikram Singh Rana

Computer Science Engineer

9983384690