How to Generate Crystal Report Using Custom Object Collection with ASP.NET

We can use the custom collection to bind to the Gridview or any other databound control. In the same way we can use custom collections to create a crystal report. In this article we will demonstrate how to create report using custom collection.

Implementation:

We learned in the previous article, how to create report with dataset in ASP.NET. You can read the article using the link below: 

http://www.highoncoding.com/Articles/201_Creating_Crystal_Reports_Using_Typed_DataSet.aspx

Here first we need to create .NET Object to design report, therefore you need to design a database with a table.



The created database name is “HouseRental” with “HouseOwnerInfo” table. There are eight columns in the “HouseOwnerInfo.

Let us design a report to this table, to get owners summary report.
Note: The database design is only for demonstration purpose.


Next we need to create ASP.NET project with Visual Studio, let us open visual studio and click File ->  new -> Project, then select the ASP.NET web Application from the template and give name  as “CustomObjectReportDemo”.



Now we need to create a custom class called as ‘HouseOwnerInfo’. Now add following fields and properties to the class.

01	using System;

02	using System.Collections.Generic;

03	using System.Linq;

04	using System.Web;

05

06	namespace CustomObjectReportDemo

07

{

08	public class HouseOwnerInfo

09

{

10	#region Variables

11

int id;

12	string fName;

13	string lName;

14	DateTime dateofPurchase;

15	string hPhone;

16	string mPhone;

17	string residencyStatus;

18	#endregion

19

20	#region Cons

21	public HouseOwnerInfo()

22

{

23

24

}

25	#endregion

26

27	#region Properties

28	public int Id

29

{

30	get { return id; }

31	set { id = value; }

32

}

33	public string FirstName

34

{

35	get { return fName; }

36	set { fName = value; }

37

}

38	public string LastName

39

{

40	get { return lName; }

41	set { lName = value; }

42

}

43	public DateTime DateofPurchase

44

{

45	get { return dateofPurchase; }

46	set { dateofPurchase = value; }

47

}

48	public string HomePhone

49

{

50	get { return hPhone; }

51	set { hPhone = value; }

52

}

53	public string MobilePhone

54

{

55	get { return mPhone; }

56	set { mPhone = value; }

57

}

58	public string ResidencyStatus

59

{

60	get { return residencyStatus; }

61	set { residencyStatus = value; }

62

}

63	#endregion

64

}

65

}

We have designed object for HouseOwnerInfor. We made this object from the columns of HouseOwnerInfo table.

Now we have database and custom .NET object. Remember our scenario creates report using the custom .net object. To that we need to do one more thing, which is we need XML structure for HouseOnwerInfo table to design report with .NET object.
Using the XML structure, you need to design crystal report. Now go to the SQL server 2005 and open new Query window, type followings lines.

1	SELECT [OwnerID]

2

,[FName]

3

,[LName]

4	,[DateOfPurchase]

5	,[PlanType]

6

,[HPhone]

7

,[MPhone]

8	,[RStatus]

9	FROM [HouseRental].[dbo].[HouseOwnerInfo] for XML AUTO,Elements

And run the query , you get table structure as below XML format.

01	<HouseOwnerInfo>

02	<OwnerID>0001      </OwnerID>

03	<FName>John                          </FName>

04	<LName>Peter                         </LName>

05	<DateOfPurchase>2009-03-20T00:00:00</DateOfPurchase>

06	<PlanType>HDC       </PlanType>

07	<HPhone>678965443      </HPhone>

08	<MPhone>678965443      </MPhone>

09	<RStatus>PR        </RStatus>

10	</HouseOwnerInfo>

Next  we need to copy this, paste in xml file and save. Then add to the project.
Let us start designing the report.

Right click on the project and select add new item, then select Crystal report from the list of item templates and give name of the report as “CustomObjectReportDemo.rpt”. Click ok button.

  

This is standard window to create report using design wizard. Here you can leave default selection and click ok button to process next step.



Here when you click “>>” button you will see a new window as follow.

 
This is a new place, when we select the .NET Object reporting, as we need to assign XML file to design report which has the same structure as the .NET Object. To that click on the ‘…’ button and locate the path of the XML file. The class name automatically picks the tree view node in crystal report previous window.
Now make sure that you have selected all the fields from the XML file. Then continue the wizard and finish it off. When you are finished with all the steps correctly, you will get the report as bellow.



The next step is the most important, since you need to make the collection of your custom object, which builds using database data.
Therefore you need to write the data access code to retrieve data from the database and build collection of HouseOwnerInfo.
Code:

01	public List<HouseOwnerInfo> GetAllHouseOnwersInfor()

02

{

03

try

04

{

05

06	List<HouseOwnerInfo> collectionOfHouseOwners = new List<HouseOwnerInfo>();

07	using (SqlConnection connection = new SqlConnection(WebConfigurationManager.AppSettings["Connection"]))

08

{

09	HouseOwnerInfo houseOwnerInfo;

10	if (connection.State!=System.Data.ConnectionState.Open)

11

{

12	connection.Open();

13

}

14	using (SqlCommand cmd = connection.CreateCommand())

15

{

16	cmd.CommandText = "Select * from HouseOwnerInfo;";

17	cmd.CommandType = System.Data.CommandType.Text;

18	using (SqlDataReader reader = cmd.ExecuteReader())

19

{

20	while (reader.Read())

21

{

22	houseOwnerInfo = new HouseOwnerInfo();

23	if (reader["OwnerID"] != DBNull.Value)

24

{

25	houseOwnerInfo.OwnerID = int.Parse(reader["OwnerID"].ToString());

26

}

27	if (reader["FName"] != DBNull.Value)

28

{

29	houseOwnerInfo.FName = reader["FName"].ToString();

30

}

31	if (reader["LName"] != DBNull.Value)

32

{

33	houseOwnerInfo.LName = reader["LName"].ToString();

34

}

35	if (reader["DateOfPurchase"] != DBNull.Value)

36

{

37	houseOwnerInfo.DateofPurchase = DateTime.Parse(reader["DateOfPurchase"].ToString());

38

}

39	if (reader["HPhone"] != DBNull.Value)

40

{

41	houseOwnerInfo.HPhone = reader["HPhone"].ToString();

42

}

43	if (reader["MPhone"] != DBNull.Value)

44

{

45	houseOwnerInfo.MPhone = reader["MPhone"].ToString();

46

}

47	if (reader["RStatus"] != DBNull.Value)

48

{

49	houseOwnerInfo.MPhone = reader["RStatus"].ToString();

50

}

51	collectionOfHouseOwners.Add(houseOwnerInfo);

52

}

53

}

54

55

}

56

}

57

58	return collectionOfHouseOwners;

59

}

60	catch (Exception ex)

61

{

62

63

throw ex;

64

}

65

}

Here we have written a simple data access code which reads the data from the reader, assigns to our custom object(HouseOwnerInfo), then finally add to the list and return as a list.

Now we have the list of houseOwnerInfo, next step, you need to setup your report. Before that you need to setup the crystal report viewer in your page as below.

Html Code:

1

<u><b>

2	</b></u><form id="form1" runat="server">

3

<div>

4	<CR:CrystalReportViewer ID="cryreportViewer" runat="server" AutoDataBind="true" />

5

</div>

6

</form>

Now we have setup crystal report viewer in the web page. We need to load report to report document.

1	ReportDocument report = new ReportDocument();

2	report.Load(Server.MapPath("CustomObjectReportDemo.rpt"));

Note: Here we do not need to  set database logon Information to reportdocument.
Assign the data source to the report document
report.SetDataSource(new DataBaseManager().GetAllHouseOnwersInfor());
Finally you set report object to the report viewer to render the report on the page.
Complete code to load,set data source and render report on the page.

Code:

01	protected void Page_Load(object sender, EventArgs e)

02

{

03	if (!IsPostBack)

04

{

05	BiulReport();

06

}

07

}

08

09	private void BiulReport()

10

{

11	ReportDocument report = new ReportDocument();

12	report.Load(Server.MapPath("CustomObjectReportDemo.rpt"));

13	report.SetDataSource(new DataBaseManager().GetAllHouseOnwersInfor());

14	cryreportViewer.ReportSource = report;

15

}

Now build the solution and run. If you don’t have any exception you will get result as:



Key Points:

1. When you build report with custom object, fields and objects of XML file should be same.

2. When you design report, you need XML structure file which is going to be use in report data.

3. Here you do not need to set database logon information to report.

Conclusion:

In this article we learned, how to design report and build with custom .NET object.

[Download Sample]

source : http://highoncoding.com/Articles/558_How_to_Generate_Crystal_Report_Using_Custom_Object_Collection_with_ASP_NET.aspx