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.
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";
adap = new MySqlDataAdapter();
adap.SelectCommand = cmd;
CustomerDS custDB = new CustomerDS();
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:
CrystalReport1 myReport = new CrystalReport1();
crystalReportViewer1.ReportSource = myReport;
After that, you can start your application and view the report.
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 firstname.lastname@example.org
. 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
Can you hear the music…
Says it feels right this time
Turned it 'round and found the right line
"Good day to be alive, Sir
Good day to be alive" he said.
"No Leaf Clover" --- Metallica