AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=669&pId=-1
Sample Application: FFAssist using Crystal .NET for Visual Studio 2005 - Part 3
page
by Eric Landes
Feedback
Average Rating: 
Views (Total / Last 10 Days): 25048/ 108

Introduction

This is the third part of a three-part series examining the new features in Visual Studio 2005, focusing on Crystal Reports.  Part 1 of this series introduced you to the sample application design, new features in Crystal, and some sample code for the admin application. Part 2 goes over the mechanics of creating a report using stored procedures.

In this final part, I will go over binding an object to the report, rather than a query or a stored procedure.  As more developers create business objects for applications, this may be something that a report designer may want to take advantage of.  This part of the sample application will use an automatically created object (a DataSet).  I will show how I created the report, and then how I created the business object as the data source for the report.  Finally, I'll demonstrate populating the business object in a web page,  allowing users to view a report bound to a business object.

System Requirements

I developed this part of the sample application using Windows XP Service Pack 2, SQL Express 2005 (which is included with Visual Studio Team Suite) April CTP, and Visual Studio Team Suite Beta 2.  The exciting thing about the Beta 2 for Visual Studio is that it includes a go live license, so it can be used now for a live site. I am assuming that the reader is familiar with ASP.NET, Crystal Reports .NET, SQL Server, and ADO.NET.  

To set up the database used in the application, do the following.  In the download, a backup of the FFAsistant database is included.  To restore the backup to your version of SQL 2005 Express, execute this command at a command prompt.

Listing 1: SQL Command to install Database

osql -SSERVERNAME\SQLEXPRESS -dFFAssistant -E -q"RESTORE DATABASE
TestDB FROM DISK = 'c:\ffassistantbak.dat'

This should set up the sample database for you with some data in the tables, and the correct table structure.  All code assumes that you use integrated Windows security to access the data.

To set up the Project Files, follow the Readme file included with the download.

Report Setup

To create the report, I used the DataSet referenced later in this article (in the Creating the Data Object section) as the data source.  The report will render players' totals and averages per year.  Let's set up the Report!

In my Visual Studio Project, I add a new item (right-click on the Project) and select a Crystal Report, and name it PlayersWithYards.rpt. 

Figure 1:

 

Next, select the "Using the Report Wizard" option to begin the creation of the report.  I did some additional formatting of the report after using the wizard, so feel free to use the downloaded report if you do not wish to repeat this process.  We'll step through the initial creation of the report to get the feel for how similar this is to using a SQL statement or stored procedure. 

Once you accept the Standard report wizard, the Data tab is displayed.  Because the Business Object is in a different Project (named FFBusinessObjects), select ADO.NET from the "Create a New Connection" option.  I then browsed in the File Path box to the FFAssistantDataSet.xsd DataSet schema found in the FFBusinessObjects project (in my case C:\Dev\FFAssistant\FFBusObjects).  You then need to select all the available tables (Players, Positions, Stats, and Teams). 

In the next dialog box, you need to create the relations between Teams and Positions, to Players.  You're probably already familiar with this, but simply drag and drop the TeamID from Teams to the Players table.  Do the same thing with the PositionID from the Positions table to the Players table.

In the next dialog box (Fields to Display), select the Position field from the Position table, PlayerName from the Players table, all the Passing fields (PassingYds, PassingAttmpts, PassingCmplt, PassingTDs, Interceptions, and PassingLong), Year and Gamenumber from the Stats table, TeamCity, and TeamName from the Teams table. 

In the Grouping dialog box, select PlayerName then Year.  In the next dialog, you are asked what fields to sum.  Remove all of the sums at the PlayerName Grouping level.  Also remove the sum of Year and GameNumber at the Year level. 

In the next Group Sorting dialogue box, leave the ordering on none.  In the chart dialogue box, be sure to accept the default. Next, don’t add anything in the Record selection dialogue box.  Finally, select the Leading Break Style in the next dialogue box.  You've now created the Report. 

Creating the Data Object

I wanted to see if Visual Studio can create Business Objects quickly.  I did not look thoroughly through the Visual Team System features, because I suspect there are some good tools there for accomplishing this.  However, I did find a way to automatically generate an object based on a DataSet that could serve as my business object.

To do this, in my Visual Studio Solution, I added a Windows Project to my solution, by right-clicking on the solution, and selecting Add New.  I then selected the Windows Application project template. 

Figure 2:

 

First, open the form Form1 that was automatically created.  Then, from the toolbox, add a BindingSource.  In the properties of the BindingSource, under Application Settings, open the DataSource Dialogue box, and click on Add Project Data Source. 

Follow the Data Source Configuration Wizard by selecting Database as your Data Source type.  In the Data Connection dialogue, create a new connection to the FFAssistant database on your local server.  Next, save the connection string to the web.config file.  We will use this string later in the web application.

In the next dialogue box, select the four tables Players, Positions, Stats, and Teams.  Name the DataSet FFAssistantDataset.  Now the DataSet object has been created.

Unfortunately, this object has been created in the wrong project.  To overcome this hurdle, in my solution I added the files FFAssistantDataSet.xsd and FFAssistantDataSet.Designer.cs to the FFBusObjects project using "Add Existing Files".  To accomplish this in the FFBusObjects project, right-click on the project, and then select "Add Existing".

This method of creating the object also does not have a connection property, but has an automated method of retrieving the connection string.  So we need to add a public property to the class to be able to set the connection string in code.

To add that flexibility to this class, I added the following line to the top of the following classes: PlayersTableAdapter, PositionsTableAdapter, StatsTableAdapter, and TeamsTableAdapter:

Listing 2: Connection String Property

Public_String MainConnection = "";

After inserting the above string at the top of each TableaAapter class, we also need to change the connection initiation to utilize this property.  To do this, we need to change the InitConnection method of each of these classes.  Do this with the following code:

Listing 3: Code change in InitConnection

private void InitConnection()
{
    this.m_connection = new System.Data.SqlClient.SqlConnection();
    this.m_connection.ConnectionString = MainConnection;
}

Now, we can set the connection in code with this business object, and we created the object using a new method in Visual Studio 2005.  While we won't use the methods, Delete, Insert, and Update methods are automatically created using this method.  We'll use the Fill method of the adapter to put all the data in the report.  The next thing we're going to do is the data binding to the report.

Displaying the Report with the Data Object

In the downloadable project, the default.aspx file has a CrystalViewer object on it.  To accomplish this, I just dragged and dropped the Crystal Report Viewer onto the default.aspx page (in design mode). 

In code, binding the viewer to a report with a DataSet populated in the code-behind is similar to the methods used in VS 2003.  But, utilizing the auto-created object makes this a little easier.  First, create the object, and populate all the different tables.  Then, create a Report object and bind it to the Business Object filled with all the data. 

Listing 4: Bind the report to the viewer

CrystalDecisions.CrystalReports.Engine.ReportDocument oRpt = 
    new CrystalDecisions.CrystalReports.Engine.ReportDocument();
oRpt.Load(@"C:\Inetpub\wwwroot\FantasyReports\PlayerWithYards.rpt");
FFAssistantDataSet oDataSet = new FFAssistantDataSet();
PlayersTableAdapter oPlayers = new PlayersTableAdapter();
String strConn = 
    ConfigurationManager.ConnectionStrings["FFAssistantConnectionString"].ToString();
oPlayers.MainConnection = strConn;
oPlayers.Fill(oDataSet.Players);
TeamsTableAdapter oTeams = new TeamsTableAdapter();
oTeams.MainConnection = strConn;
oTeams.Fill(oDataSet.Teams);
StatsTableAdapter oStats = new StatsTableAdapter();
oStats.MainConnection = strConn;
oStats.Fill(oDataSet.Stats);
PositionsTableAdapter oPositions = new PositionsTableAdapter();
oPositions.MainConnection = strConn;
oPositions.Fill(oDataSet.Positions);
oRpt.SetDataSource(oDataSet);
CrystalReportViewer1.ReportSource = oRpt;

As you see in Listing 4, I set the connection string to the connection stored in the web.config file. This should look familiar if you are already binding data to your report using Visual Studio 2003.  Just create a new ReportDocument object, and then load the report into that object.  After filling the DataSet object with data, we bind it to the ReportDocument.  Finally, the ReportDocument object is set as the ReportSource for the viewer.  Build and run the web application and you'll see the report!

Conclusion

This sample application has gone from showing you the initial architecture of the application, to using a business object as the basis for the report.  The intent was to give you a flavor for the different tools available to you in Visual Studio 2005 for Crystal Reports. 

In my opinion, there's a lot to be excited about concerning the new viewer for Crystal, and the new options VS 2005 provides for accessing data.  Now that this is in Beta 2, feel free to try this and other new features!  Keep on coding.


Product Spotlight
Product Spotlight 

©Copyright 1998-2020 ASPAlliance.com  |  Page Processed at 2020-08-03 10:58:05 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search