Connecting ComponentOne Studio Controls to MySQL Database

A very common issue reported by our customers is assigning MySQL database as ComponentOne Studio control’s DataSource. Simply assigning a data source is far more irritating than creating an awesome multi-featured application in such scenarios. On one hand, using Access and SQL databases is quick and easy due to the presence of OLEDB connectors. On the other, connecting to MySQL datasource seems a hard nut to crack, due to the absence of any such connector. This blog will help in solving such problems. The approach mentioned in this blog can be used to connect any ComponentOne Studio control to MySQL database. For the demonstration, we'll look at Report for WinForms.

What is a Database Connector/Interface?

Programming languages need connectors/interfaces like ODBC, OLEDB or .NET to connect with databases like MySQL. These are the database APIs, based on Call Level Interface (CLI) API definition published by various standard organizations. They're basically used to access data in different databases by using their corresponding drivers. The application calls the connector’s API functions using a standard SQL syntax. Then the driver makes any necessary translations to send the statement to DBMS and present the result back to the application.

Connecting using ODBC Connector in C1ReportDesigner

MySQL Connector/ODBC provides both driver-manager-based and native interfaces to the MySQL database, with full support for MySQL functionality, including stored procedures, transactions and, with Connector/ODBC 5.1 and higher, full Unicode compliance. For connecting MySQL database:

  1. You first need to install MySQL ODBC Connector (mysql-connector-odbc-5.3.4-win32.msi) which can be downloaded from the link below: http://dev.mysql.com/downloads/connector/odbc/
  2. After installing, create a new ODBC data source using C1ReportDesigner and select 'MySQL ODBC' (as shown in the screenshot).
  3. Finally, enter connection details to connect to MySQL database.

Connecting ComponentOne Studio Controls to MySQL

Connecting using OLEDB Connector in C1ReportDesigner

  1. Download and install MySQL OLE DB Provider. Some third party Vendors (like Cherry City) provides MySQL OLE DB Provider. You can download the same from the following link: http://cherrycitysoftware.com/ccs/Download/MySQLPV(x86).zip.zip)
  2. Once installed, you can now open C1ReportDesigner to connect the report with MySQL DataSource.
  3. Create New Report and set following details in DataSource form of C1Report Wizard: 
    Data Provider: Auto  
Connection String: Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID; Password=MyPassword;
    Please modify the connection string according to your server credentials.
  4. Select the desired table from populated list of tables.
  5. Now, you can create the report as mentioned in the documentation.

Connecting ComponentOne Studio Controls to MySQL: Select DataSource

Connecting using .NET Connector in Visual Studio

MySQL .Net connect provides secure, high-performance data connectivity with MySQL databases. It implements the required ADO.NET interfaces and integrates into ADO.NET aware tools. For using this:

  1. You first need to install MySQL Connector/Net on your machine, which provides classes like "MySqlDataAdapter" and "MySqlDataReader". You can download the installer (mysql-connector-net-6.9.6.msi) for the same from the following link: http://dev.mysql.com/downloads/connector/net/

  2. After installing, you need to add "MySql.Data" assembly in project's references which contains definition of classes like MySqlDataAdapter and MySqlDataReader. This assembly can be found at following location: 

    C:\\Program Files (x86)\\MySQL\\MySQL Connector Net 6.9.6\\Assemblies\\v4.0

  3. Now, you can use following code to fill the MySQL data in a DataTable which can be further assigned to "RecordSet" property of C1Report's DataSource.

    
MySqlConnection conn = null;  
MySqlCommand cmd = null;  
DataTable dataTable = new DataTable();  
conn = new MySqlConnection("SERVER=in-ggs-praveen;UID=root;PWD=root;DATABASE=test;");  
cmd = new MySqlCommand(sql, conn);  
string sql = "SELECT * FROM stuff_master";  
conn.Open();  
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))  
{  
da.Fill(dataTable);  
}  
c1Report1.DataSource.Recordset = dataTable;  
c1Report1.Render();

ComponentOne Studio Connecting to MySQL Report Populate Table Refer to the link below for more information on MySQL connection string with different connectors. http://www.connectionstrings.com/mysql/#p27

This approach can be used to connect any ComponentOne Studio Control, including FlexGrid, List, TrueDBGrid, etc., to MySQL database. You simply need to assign the DataSource property of respective control to the corresponding table. Please let me know (through the comments) if you face any problem while connecting to MySQL database with any other control.

Download Sample

Attached sample project shows connecting C1Report to MySQL database using .NET provider.
Net Connector CS Sample
Net Connector VB Sample

GrapeCity

GrapeCity Developer Tools
comments powered by Disqus