How to Create a Crystal Report using C# and MySQL

Introduction

This article is about creating Crystal Reports using Visual C#.NET and MySQL. We all know that MySQL is an open source DBMS and .NET Framework is free. We have used MySQL and Visual Studio .NET 2003 in developing some of our projects with the help of MySQL Connector .NET 1.05. In this article, I don't discuss how to use My SQL Connector .NET 1.05 in details. However, I got some difficulties while creating reports with Crystal Report, since Crystal Report currently doesn't support connecting directly to MySQL. When I was doing this kind of development, I also looked for samples, but I couldn't find one. That's why I wrote this article on The Code Project.

Daniel Brown has to say... "after working with Crystal Reports and MySQL for almost 2 years, I can tell you that it is possible to natively connect to MySQL.

You need to install the MyODBC ODBC adaptor and just use the ODBC connector in Crystal and you can use all of the technology crystal offers".

But I have some problem with Unicode character when using MyODBC ODBC adaptor.

Create a DataSet, the Key of Designing a Report

As stated above, Crystal Report doesn't allow us to make a connection to datasource which is MySQL and we don't have constructing report dynamic on runtime with Crystal Report. So to design a report, the first step is create a DataSet describing the data you want to display in the report.

To create a Dataset, you click the "Add New Item" in the Project Menu. A picture of an added Dataset looks like the following window and can add elements to the Dataset by dragging and dropping elements from the toolbox. You will fill data from the database to this dataset by using a query. (Note: The names of the elements in this Dataset must be equal to the names of the columns specified in query.) The following code uses MySQL Connector .NET 1.0.5 objects to fill the DataSet.

MySqlConnection conn; 
MySqlCommand cmd; 
MySqlDataAdapter adap; 

// Code to get data from database 
conn = new MySqlConnection("Server=localhost; Database=saf; 
	User ID=root; Password=root; charset=utf8;"); conn.Open(); 
cmd = conn.CreateCommand(); 
cmd.CommandText = "SELECT customer_code, 
	customer_name, customer_address FROM Customers"; 
// Create a Dataset and using DataAdapter to fill it 
adap = new MySqlDataAdapter(); 
adap.SelectCommand = cmd; 
CustomerDS custDB = new CustomerDS(); 
custDB.Clear(); 
adap.Fill(custDB, "Customers");

Designing a Report with Crystal Report

Now that you have a created Datset, we can use it to fill the report with data, which will be obtained from the DB. As I said before, you can add a Crystal Report to the project by clicking "Add New Item" in the "Project" menu. Then the following window will appear, and you can select your choices and click OK.

In my simple project, I use Report Expert and choose Standard. In the next window, I choose my previous Dataset from ProjectData \ ADO.NET DataSets.

After selecting Table and Fields to display in Report, you will have a Report that looks like the following figure. And at this time, you can design your report interface as you want.

Display Report in the Form

Up to now, you have fully basic components. Then you'll set the report source attribute for the Crystal Report Viewer control to display the report. You can set the report source by using the following code:

// Create a CrystalReport1 object 
CrystalReport1 myReport = new CrystalReport1(); 
// Set the DataSource of the report 
myReport.SetDataSource(custDB); 
// Set the Report Source to ReportView 
crystalReportViewer1.ReportSource = myReport;

After that, you can start your application and view the report.

Additional Information

To use this example you need to download install MySQL Connector .NET 1.0.5 in http://dev.mysql.com/downloads/connector/net/1.0.html.
Regarding this article you can send any question to lequanganh.aiti@gmail.com or leadershipqa@yahoo.com. I will send you any information that you want for your development.

About Le Quang Anh

Le Quang Anh
AiTi-APTECH Computer Education
Shape Your Future
35/115 Dinh Cong str - Hoang Mai dis - Ha Noi cap
Website: www.aiti-aptech.edu.vn
Email: contact@aiti-aptech.edu.vn
Forum: forum.aiti-aptech.edu.vn

Can you hear the music…

Says it feels right this time <br />Turned it 'round and found the right line <br />"Good day to be alive, Sir <br />Good day to be alive" he said. <br /><br />"No Leaf Clover" --- Metallica