22.2.5.17. Using Connector/Net with Crystal Reports

When creating a report in Crystal Reports there are two options for accessing the MySQL data while designing your report.

The first option is to use Connector/ODBC as an ADO data source when designing your report. You will be able to browse your database and choose tables and fields using drag and drop to build your report. The disadvantage of this approach is that additional work must be performed within your application to produce a data set that matches the one expected by your report.

The second option is to create a data set in VB.NET and save it as XML. This XML file can then be used to design a report. This works quite well when displaying the report in your application, but is less versatile at design time because you must choose all relevant columns when creating the data set. If you forget a column you must re-create the data set before the column can be added to the report.

The following code can be used to create a data set from a query and write it to disk:

Visual Basic Example

Dim 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 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)    myData.WriteXml("C:\dataset.xml", XmlWriteMode.WriteSchema)Catch ex As Exception    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)End Try

C# Example

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);  myData.WriteXml(@"C:\dataset.xml", XmlWriteMode.WriteSchema);}catch (MySql.Data.MySqlClient.MySqlException ex){  MessageBox.Show(ex.Message, "Report could not be created",  MessageBoxButtons.OK, MessageBoxIcon.Error);}

The resulting XML file can be used as an ADO.NET XML datasource when designing your report.

If you choose to design your reports using Connector/ODBC, it can be downloaded from dev.mysql.com.

For most purposes, the Standard Report wizard helps with the initial creation of a report. To start the wizard, open Crystal Reports and choose the New > Standard Report option from the File menu.

The wizard first prompts you for a data source. If you use Connector/ODBC as your data source, use the OLEDB provider for ODBC option from the OLE DB (ADO) tree instead of the ODBC (RDO) tree when choosing a data source. If using a saved data set, choose the ADO.NET (XML) option and browse to your saved data set.

The remainder of the report creation process is done automatically by the wizard.

After the report is created, choose the Report Options... entry of the File menu. Un-check the Save Data With Report option. This prevents saved data from interfering with the loading of data within our application.

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 in Section 22.2.5.17.1, "Creating a Data Source", and have a crViewer control on your form named myViewer.

Visual Basic 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

C# 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);}

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 multiple SELECT statements in our MySqlCommand object. These SELECT 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 two SELECT queries and displayed with the following code:

Visual Basic 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

C# 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);}

It is important to order the SELECT 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.