How to Connect Crystal Reports to MySQL directly by C# code without DSN or a DataSet

Question

How can I connect a Crystal Report (VS 2008 basic) to a MySQL DB without using a DSN or a preload DataSet using C#?

I need install the program on several places, so I must change the connection parameters. I don't want to create a DSN on every place, nor do I want to preload a DataSet and pass it to the report engine. I use nhibernate to access the database, so to create and fill the additional DS would take twice the work and additional maintenance later. I think the best option would be to let the crystal reports engine to connect to MySQL server by itself using ODBC.

I managed to create the connection in the report designer (VS2008) using the Database Expert, creating an ODBC(RDO) connection and entering this connection string

"DRIVER={MySQL ODBC 5.1 Driver};SERVER=myserver.mydomain"

and in the "Next" page filling the "User ID", "Password" and "Database" parameters. I didn't fill the "Server" parameter. It worked. As a matter of fact, if you use the former connection string, it doesn't matter what you put on the "Server" parameter, it seems the parameter is unused. On the other hand, if you use "DRIVER={MySQL ODBC 5.1 Driver}" as a connection string and later fill the "Server" parameter with the FQDN of the server, the connection doesn't work.

How can I do that by code? All the examples I've seen till now, use a DSN or the DataSet method. I saw the same question posted but for PostgreSQL and tried to adapt it to mysql, but so far, no success. The first method:

Rp.Load();
Rp.DataSourceConnections[0].SetConnection("DRIVER={MySQL ODBC 5.1 Driver};SERVER=myserver.mydomain", "database", "user", "pass");
Rp.ExportToDisk(ExportFormatType.PortableDocFormat, "report.pdf");

raise an CrystalDecisions.CrystalReports.Engine.LogOnException during ExportToDisk

Message="Logon failed.\nDetails: IM002:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.\rError in File temporal file path.rpt:\nUnable to connect: incorrect log on parameters.

the InnerException is an System.Runtime.InteropServices.COMException with the same message and no InnerException

The "no default driver specified" makes me wonder if the server parameter is unused here too (see above). In that case: How can I specify the connection string?

I haven't tried the second method because it doesn't apply.

Does anybody know the solution?

Answer 1

I think it'll likely be quicker to generate the Dataset via nHibernate, or do a direct ADO.NET query, then trying to solve the issue.