Creating Reports with DevExpress XtraReport from a MySQL Database

 

When working with applications that need to display structured data in a report format, DevExpress XtraReport is a powerful tool. In this tutorial, we’ll walk through creating a simple report from a MySQL database using XtraReport in C#.

Prerequisites

To follow along with this tutorial, you’ll need:

• DevExpress: Installed in your Visual Studio project (includes XtraReport).
• MySQL Database: A database with sample data (for example, a products table with columns like ProductID, ProductName, and Price).
• MySQL.Data Connector: The MySQL ADO.NET connector to connect C# to the MySQL database.

Step 1: Set Up the MySQL Database Connection

First, set up a connection to your MySQL database.

    1. Install the MySQL.Data NuGet package in Visual Studio.

Install-Package MySql.Data

    2. Add your connection details (server, database name, user, and password) in your code.

Here’s an example of how to set up the connection in C#:

using MySql.Data.MySqlClient;
using System.Data;

string connectionString = "Server=localhost;Database=your_database;User ID=root;Password=your_password;";
MySqlConnection connection = new MySqlConnection(connectionString);

Step 2: Retrieve Data from MySQL

Once the connection is set up, query the database to get data for the report.

public DataTable GetProductData()
{
    string query = "SELECT ProductID, ProductName, Price FROM products";
    DataTable dataTable = new DataTable();

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
        MySqlCommand command = new MySqlCommand(query, connection);
        connection.Open();
        MySqlDataAdapter adapter = new MySqlDataAdapter(command);
        adapter.Fill(dataTable);
    }

    return dataTable;
}

Step 3: Create an XtraReport

Now, create a report in Visual Studio using DevExpress's XtraReport Designer.

    1. Add a new XtraReport to your project:

• Right-click on your project in Solution Explorer.
• Select Add > New Item.
• Choose DevExpress Report and name it ProductReport.

    2. Bind the Data:

• Open ProductReport.cs in the XtraReport Designer.
• Go to Data Source and bind the data from GetProductData().

    3. Design the Report:

• Drag a Detail Band onto the design surface.
• Add text labels or fields for each column (e.g., ProductID, ProductName, Price).
• Drag data fields from the data source onto the Detail Band to display them in the report.

Here’s an example of the ProductReport code:

using DevExpress.XtraReports.UI;
using System.Data;

public partial class ProductReport : XtraReport
{
    public ProductReport(DataTable dataTable)
    {
        InitializeComponent();
        this.DataSource = dataTable;
        this.DataMember = dataTable.TableName;

        // Bind fields to report labels or cells
        xrLabelProductID.DataBindings.Add("Text", null, "ProductID");
        xrLabelProductName.DataBindings.Add("Text", null, "ProductName");
        xrLabelPrice.DataBindings.Add("Text", null, "Price");
    }
}

Step 4: Display the Report

Now, let’s display the report in your application.

In your form, use the following code to generate the report from the data retrieved from MySQL:

using DevExpress.XtraReports.UI;

// Inside a form or a method
DataTable productData = GetProductData();
ProductReport report = new ProductReport(productData);

// Show the report in the XtraReport viewer
ReportPrintTool printTool = new ReportPrintTool(report);
printTool.ShowPreview();

Running the Application

Now, run your application. When you execute the code, the ProductReport will fetch data from the MySQL database and display it in a report format using DevExpress XtraReport.

Summary

In this article, we demonstrated how to create a simple report from MySQL data using DevExpress XtraReport. We:

1. Set up a MySQL connection.
2. Queried data from the products table.
3. Designed the report using XtraReport Designer.
4. Displayed the report using the ReportPrintTool.

Using XtraReport with MySQL enables you to generate flexible and visually appealing reports in your applications quickly. With more advanced customization, you can make your reports even more powerful by adding parameters, grouping, and styling options.