Movatterモバイル変換

To display a report we first populate a data set with the data needed for the report, then load the report and bind it to the data set. Finally we pass the report to the crViewer control for display to the user.

The following references are needed in a project that displays a report:

The following code assumes that you created your report using a data set saved using the code shown inSection 5.13.1, “Creating a Data Source”, and have a crViewer control on your form namedmyViewer.

C# Code Example

using CrystalDecisions.CrystalReports.Engine;using System.Data;using MySql.Data.MySqlClient;ReportDocument myReport = new ReportDocument();DataSet myData = new DataSet();MySql.Data.MySqlClient.MySqlConnection conn;MySql.Data.MySqlClient.MySqlCommand cmd;MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;conn = new MySql.Data.MySqlClient.MySqlConnection();cmd = new MySql.Data.MySqlClient.MySqlCommand();myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();conn.ConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test";try{    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +        "country.name, country.population, country.continent " +        "FROM country, city ORDER BY country.continent, country.name";    cmd.Connection = conn;    myAdapter.SelectCommand = cmd;    myAdapter.Fill(myData);    myReport.Load(@".\world_report.rpt");    myReport.SetDataSource(myData);    myViewer.ReportSource = myReport;}catch (MySql.Data.MySqlClient.MySqlException ex){    MessageBox.Show(ex.Message, "Report could not be created",        MessageBoxButtons.OK, MessageBoxIcon.Error);}

Visual Basic Code Example

Imports CrystalDecisions.CrystalReports.EngineImports System.DataImports MySql.Data.MySqlClientDim myReport As New ReportDocumentDim myData As New DataSetDim conn As New MySqlConnectionDim cmd As New MySqlCommandDim myAdapter As New MySqlDataAdapterconn.ConnectionString = _    "server=127.0.0.1;" _    & "uid=root;" _    & "pwd=12345;" _    & "database=test"Try    conn.Open()    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " _        & "country.name, country.population, country.continent " _        & "FROM country, city ORDER BY country.continent, country.name"    cmd.Connection = conn    myAdapter.SelectCommand = cmd    myAdapter.Fill(myData)    myReport.Load(".\world_report.rpt")    myReport.SetDataSource(myData)    myViewer.ReportSource = myReportCatch ex As Exception    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)End Try

A new data set it generated using the same query used to generate the previously saved data set. Once the data set is filled, a ReportDocument is used to load the report file and bind it to the data set. The ReportDocument is the passed as the ReportSource of the crViewer.

This same approach is taken when a report is created from a single table using Connector/ODBC. The data set replaces the table used in the report and the report is displayed properly.

When a report is created from multiple tables using Connector/ODBC, a data set with multiple tables must be created in our application. This enables each table in the report data source to be replaced with a report in the data set.

We populate a data set with multiple tables by providing multipleSELECT statements in our MySqlCommand object. TheseSELECT statements are based on the SQL query shown in Crystal Reports in the Database menu's Show SQL Query option. Assume the following query:

SELECT `country`.`Name`, `country`.`Continent`, `country`.`Population`, `city`.`Name`, `city`.`Population`FROM `world`.`country` `country` LEFT OUTER JOIN `world`.`city` `city` ON `country`.`Code`=`city`.`CountryCode`ORDER BY `country`.`Continent`, `country`.`Name`, `city`.`Name`

This query is converted to twoSELECT queries and displayed with the following code:

C# Code Example

using CrystalDecisions.CrystalReports.Engine;using System.Data;using MySql.Data.MySqlClient;ReportDocument myReport = new ReportDocument();DataSet myData = new DataSet();MySql.Data.MySqlClient.MySqlConnection conn;MySql.Data.MySqlClient.MySqlCommand cmd;MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;conn = new MySql.Data.MySqlClient.MySqlConnection();cmd = new MySql.Data.MySqlClient.MySqlCommand();myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();conn.ConnectionString = "server=127.0.0.1;uid=root;" +    "pwd=12345;database=test";try{    cmd.CommandText = "SELECT name, population, countrycode FROM city ORDER " +        "BY countrycode, name; SELECT name, population, code, continent FROM " +        "country ORDER BY continent, name";    cmd.Connection = conn;    myAdapter.SelectCommand = cmd;    myAdapter.Fill(myData);    myReport.Load(@".\world_report.rpt");    myReport.Database.Tables(0).SetDataSource(myData.Tables(0));    myReport.Database.Tables(1).SetDataSource(myData.Tables(1));    myViewer.ReportSource = myReport;}catch (MySql.Data.MySqlClient.MySqlException ex){    MessageBox.Show(ex.Message, "Report could not be created",        MessageBoxButtons.OK, MessageBoxIcon.Error);}

Visual Basic Code Example

Imports CrystalDecisions.CrystalReports.EngineImports System.DataImports MySql.Data.MySqlClientDim myReport As New ReportDocumentDim myData As New DataSetDim conn As New MySqlConnectionDim cmd As New MySqlCommandDim myAdapter As New MySqlDataAdapterconn.ConnectionString = "server=127.0.0.1;" _    & "uid=root;" _    & "pwd=12345;" _    & "database=world"Try    conn.Open()    cmd.CommandText = "SELECT name, population, countrycode FROM city ORDER BY countrycode, name; " _        & "SELECT name, population, code, continent FROM country ORDER BY continent, name"    cmd.Connection = conn    myAdapter.SelectCommand = cmd    myAdapter.Fill(myData)    myReport.Load(".\world_report.rpt")    myReport.Database.Tables(0).SetDataSource(myData.Tables(0))    myReport.Database.Tables(1).SetDataSource(myData.Tables(1))    myViewer.ReportSource = myReportCatch ex As Exception    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)End Try

It is important to order theSELECT queries in alphabetic order, as this is the order the report will expect its source tables to be in. One SetDataSource statement is needed for each table in the report.

This approach can cause performance problems because Crystal Reports must bind the tables together on the client-side, which will be slower than using a pre-saved data set.