Abstract Microsoft has released typed data set to work with .NET applications. This article will explore about creating report in ASP.NET with typed dataset. When we implement crystal report with typed dataset, it's easy to work data table. Introduction The purpose of this tutorial is to illustrate how to create report in ASP.NET Applications with type dataset. As I mentioned my earlier articles reporting is the most important feature in many Information systems. Therefore Crystal report has supported web Application reporting. ASP.NET has a feature to develop reporting with crystal reports too. Prerequisites: 1. SQL Server 2005 2. Visual studio 2005/2008 3. Crystal report Before start to design a report, you need a database which is going to use by report. Here I have designed a small database for this demonstration. 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. Let us start designing the report. For that, there are two standard ways. The first way is we can use the crystal report and second one is we can use the Visual studio. Here I have used Visual Studio since I prefer that. (I have used this same method in my earlier articles as well). First open Visual studio 2008 or 2005 and create new web project called "CrystalReportInPart02ASPNET". Then right click the project and select Add New Item from the bar. Select Crystal Report from the templates list window. When you select crystal report, give a meaningful name to the report. Here I have given "SamplewithDSRpt.rpt". Then click Add button. After you click the Add button, you will get a welcome window as shown by the following figure. 
Keep the standard report format and click OK button. Then you need to create the database connection to access tables within the database. Once you click OK button, system gives a window to select the connection type (database provider) as shown below 
This demonstration report is to work with SQL Server 2005 database. So you need to select SQL native client as provider (As above figure shows). Then click Next button, to continue report creation process. Then you need to assign details of SQL Server and user credential to access the database from the report. Then you need to expand current connection tag, select your database connection then select the relevant table as shown by the following figure. 
Then click Next button to continue. There you need to select fields from selected tables to design report as shown by the following figure. 
Then click Next button. Now you have done necessary effort and finished the final step. As a result, you will get report like this. 
Then we need to go for the next section, which are you need to create typed dataset from demonstration table. Because of this demonstration is to explore how we create report with typed dataset. Now right click on your project. Now select the new item menu, then select dataset in the item list like following, 
And don't forget to supply a name, here I have given name as "HouseRentalDS.xsd" and then click to add button to add to project. When Dataset added to the project, it will show just an empty space. Then we need to connect database through the Server Explore, then create a connection to your database. Now drag and drop the table on the Dataset surface as like follow, 
Figure: Visual Studio Server explore and typed Data Set Designed View Now we have created typed dataset and report design, now we need to write code to integrate both. Before that we need to add report viewer to the webpage. Let us see how we can add the report viewer in the page, look at these html tags. {codecitation class="brush: c#; gutter: true;" width="700px"} {/codecitation} Now create a table with form object and then add a html row and a column also. Then place the crystalReportViewer to the column as shown by above html tag. If we complie this application, it will give a complier error "CR doesn't exist". Therefore, you need to register crystal report namespace on the top of the page as shown below. {codecitation class="brush: c#; gutter: true;" width="700px"} <%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %> {/codecitation} Note: when you double click on toolbox in the crystalreportviewer Visual studio will do all registeration and etc Now you need to write few lines to integerate report and dataset.Here we need write codes for two portions. The first one is to access database and fill records to dataset and the second one is pass the dataset to the report and load the report within crystal reportviewer. Both sections are pretty simple. Fisrt we write code for access the database. For that I have created a class call "DataBaseAccess.cs" within that I have a method like follow, {codecitation class="brush: c#; gutter: true;" width="700px"} public HouseRentalDS GetHouseRentals() { try { HouseRentalDS houseRentalDS = null; using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["HouseRentalConnectionString"].ToString())) { using (SqlCommand command = connection.CreateCommand()) { houseRentalDS = new HouseRentalDS(); // best practies ingore inline query command.CommandText = "SELECT * FROM HouseOwnerInfo;"; command.CommandType = System.Data.CommandType.Text; using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(houseRentalDS.HouseOwnerInfo); } } } return houseRentalDS; } catch (Exception ex) { throw ex; } } {/codecitation} In above lines of code, initialise SqlConnection object and then assign connection string to that object. {codecitation class="brush: c#; gutter: true;" width="700px"} using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["HouseRentalConnectionString"].ToString())) { // Implementation } {/codecitation} then we need SqlCommand to acces theT-SQL script with database {codecitation class="brush: c#; gutter: true;" width="700px"} using (SqlCommand command = connection.CreateCommand()) { // Implementation } {/codecitation} Note: When we use the "using " scope, get instance from the IDispose implemented classese, it properlly dispose the all resource from the momery. For more information about using IDispose object. 1.http://msdn.microsoft.com/en-us/library/yh598w02(VS.80).aspx 2.http://www.codeproject.com/KB/cs/CSharp_using.aspx then fill records to dataset table and finally return dataset. Now we have records in the dataset , we need to set records to report using SetDataSource method. Look the below code for this operation, {codecitation class="brush: c#; gutter: true;" width="700px"} DataBaseAccess dataBaseAccess = new DataBaseAccess(); ReportDocument report = new ReportDocument(); report.Load(Server.MapPath("SamplewithDSRpt.rpt")); HouseRentalDS dataHouseRental = dataBaseAccess.GetHouseRentals(); if (dataHouseRental != null) { report.SetDataSource(dataHouseRental); cryrptViewer.ReportSource = report; } {/codecitation} DataBaseAccess is custom class to retrieve records from the database. First we need to get instance to dataaccess classs and also create new object ReportDocument too. {codecitation class="brush: c#; gutter: true;" width="700px"} DataBaseAccess dataBaseAccess = new DataBaseAccess(); ReportDocument report = new ReportDocument(); {/codecitation} Then load designed report file from the physical location using Load method and find the full path using Server.mapPath(filename"); {codecitation class="brush: c#; gutter: true;" width="700px"} report.Load(Server.MapPath("SamplewithDSRpt.rpt")); {/codecitation} Then call method to access the database and retrieve the records by {codecitation class="brush: c#; gutter: true;" width="700px"} HouseRentalDS dataHouseRental = dataBaseAccess.GetHouseRentals(); {/codecitation} Then we need to set Data source to the report and then finally set report object to the report Viewer. {codecitation class="brush: c#; gutter: true;" width="700px"} if (dataHouseRental != null) { report.SetDataSource(dataHouseRental); cryrptViewer.ReportSource = report; } {/codecitation} From the above approach we have an adavantage,what is that? In the previouse articles we set database logon information to report file, but here we don't need that. because when we try to retrieve records from the database with dataset, we have set the connection information. So now it is working like Disconnected mode ADO.NET Access. Since report using records which has within the dataset.so we do not need set connection information report. Now build the application and run, you would get resule like followings, 
That's all .I hope you can do it. Conculusion: In this article you learned about creating report in ASP.NET using Typed Dataset. And here you got a good idea and solution how to set database logon settings to report issues, when we learnt from previous articles. References: 1.http://www.c-sharpcorner.com/UploadFile/rupadhyaya/TypedDataSets12032005021013AM/TypedDataSets.aspx 2.http://msdn.microsoft.com/en-us/library/system.data.dataset(VS.71).aspx |