Mysql connection

[rozyjaret]

How can i use mysql server with this runner.

[gerardo-lijs]

Hi, I have not tested it, but if Crystal Reports works with MySQL, it should also work fine using this runner for .NET Core since all the connection parameters are exposed and can be customized completely, in the same way as if you were using Crystal Reports directly.

[rozyjaret]

Thanks for reply.
My app is in .Net Core where I want to set parameters in my rpt file connected to mysql DB and then export that file.

I am using:
var report = new Report("rptname.rpt", "Sample Report")
{
Connection = CrystalReportsConnectionFactory.CreateSqlConnection("localhost", "DBname","username","password")
};

I am sending the mysql credentials but not able to connect.

When I am checking the LijsDev.CrystalReportsRunner.Core > CrystalReportsConnectionFactory, ,
private static Dictionary<string, string> SqlLogonProperties => new()
{
{ "Auto Translate", "-1" },
{ "Connect Timeout", "15" },
{ "General Timeout", "0" },
{ "Locale Identifier", "1033" },
{ "OLE DB Services", "-5" },
{ "Provider", "MSOLEDBSQL" },
{ "Tag with column collation when possible", "0" },
{ "Use DSN Default Properties", "False" },
{ "Use Encryption for Data", "0" },
};

It's private member of this class . So can you please guide me on how to setup mysql connection for a report.

Thanks in advance.

[gerardo-lijs]

Hi, instead of using the CrystalReportConnectionFactory.CreateSqlConnecion, you can directly create from scratch a CrystalReportsConnection class with the parameters you want. You can also use the factory and after you get the instance of the CrystalReportsConnection, you can add/remove entries in the LoginProperties property.

This is the class you can directly create https://github.com/gerardo-lijs/CrystalReportsRunner/blob/main/src/LijsDev.CrystalReportsRunner.Core/CrystalReportsConnection.cs

And this is the link to the static factory which is just a helper to get already configured instances of the class -> https://github.com/gerardo-lijs/CrystalReportsRunner/blob/main/src/LijsDev.CrystalReportsRunner.Core/CrystalReportsConnectionFactory.cs

Hope this help and you are able to test and use it. Let me know otherwise and in case you are not familiar with the Factory design pattern is also very interesting to read or watch some excellent videos/courses on that, that will be for sure very useful in your software developer career more than once in the future.

[rozyjaret]

Thank you so much for such a prompt reply. I will definitely try the above solution and let you know if I have any doubts .

…

On Sat, May 4, 2024 at 2:18 PM Gerardo Lijs ***@***.***> wrote: Hi, instead of using the CrystalReportConnectionFactory.CreateSqlConnecion, you can directly create from scratch a CrystalReportsConnection class with the parameters you want. You can also use the factory and after you get the instance of the CrystalReportsConnection, you can add/remove entries in the LoginProperties property. This is the class you can directly create https://github.com/gerardo-lijs/CrystalReportsRunner/blob/main/src/LijsDev.CrystalReportsRunner.Core/CrystalReportsConnection.cs And this is the link to the static factory which is just a helper to get already configured instances of the class -> https://github.com/gerardo-lijs/CrystalReportsRunner/blob/main/src/LijsDev.CrystalReportsRunner.Core/CrystalReportsConnectionFactory.cs Hope this help and you are able to test and use it. Let me know otherwise and in case you are not familiar with the Factory design pattern is also very interesting to read or watch some excellent videos/courses on that, that will be for sure very useful in your software developer career more than once in the future. — Reply to this email directly, view it on GitHub <#24 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEANZ2MTRHNIWSABIIPR2WLZASOG5AVCNFSM6AAAAABHFMUOG2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGMJSGQ4DC> . You are receiving this because you authored the thread.Message ID: <gerardo-lijs/CrystalReportsRunner/repo-discussions/24/comments/9312481@ github.com>

[rozyjaret]

Hi, I tried connecting but I am getting error : [image: image.png] Steps: 1. I have created a mysql connected rpt file. 2. CrystalReportsConnection con = CrystalReportsConnectionFactory.CreateSqlConnection("localhost", "dbname", "username", "pwd"); con.LogonProperties.Clear(); con.LogonProperties = MySqlLogonProperties; var report = new Report("mysqlrpt.rpt", "Sample Report") { Connection = con }; 3. private Dictionary<string, string> MySqlLogonProperties => new() { { "Server Name", "localhost" }, // MySQL server address { "Port", "3306" }, // MySQL default port { "Database Name", "dbname" }, // Name of the MySQL database { "User ID", "user" }, // MySQL username { "Password", "pwd" }, // MySQL password { "Locale Identifier", "1033" }, // Locale Identifier { "Use DSN Default Properties", "False" }, // Use DSN Default Properties { "Use Encryption for Data", "0" }, // Use Encryption for Data // Additional properties can be added as needed }; Am I missing something? Please help . Thanks in advance.

…

On Sat, May 4, 2024 at 6:20 PM rozy jaret ***@***.***> wrote: Thank you so much for such a prompt reply. I will definitely try the above solution and let you know if I have any doubts . On Sat, May 4, 2024 at 2:18 PM Gerardo Lijs ***@***.***> wrote: > Hi, instead of using the > CrystalReportConnectionFactory.CreateSqlConnecion, you can directly create > from scratch a CrystalReportsConnection class with the parameters you want. > You can also use the factory and after you get the instance of the > CrystalReportsConnection, you can add/remove entries in the LoginProperties > property. > > This is the class you can directly create > https://github.com/gerardo-lijs/CrystalReportsRunner/blob/main/src/LijsDev.CrystalReportsRunner.Core/CrystalReportsConnection.cs > > And this is the link to the static factory which is just a helper to get > already configured instances of the class -> > https://github.com/gerardo-lijs/CrystalReportsRunner/blob/main/src/LijsDev.CrystalReportsRunner.Core/CrystalReportsConnectionFactory.cs > > Hope this help and you are able to test and use it. Let me know otherwise > and in case you are not familiar with the Factory design pattern is also > very interesting to read or watch some excellent videos/courses on that, > that will be for sure very useful in your software developer career more > than once in the future. > > — > Reply to this email directly, view it on GitHub > <#24 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AEANZ2MTRHNIWSABIIPR2WLZASOG5AVCNFSM6AAAAABHFMUOG2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGMJSGQ4DC> > . > You are receiving this because you authored the thread.Message ID: > <gerardo-lijs/CrystalReportsRunner/repo-discussions/24/comments/9312481@ > github.com> >

[gerardo-lijs]

Hi, the code looks fine. I did a small research and it seems that its not possible to connect directly Crystal Reports to MySQL. Every sample I found its using ODBC as a connection. See: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-crystalreports.html or https://dev.mysql.com/doc/connector-net/en/connector-net-programming-crystal-displaying.html

Last time I used MySQL was more than 20 years ago so do please make more research on your own and don't take my word for this.

I would recommend the following steps:

Step 1) Can you create a report in the Crystal Reports Designer that connects to MySQL and works fine in their App? If this works fine, then
Step 2) Use the connection settings that work inside Crystal Reports App and use them in C# code programatically to configure the report. For better debugging here, I recommend using Crystal Reports directly with .NET Framework 4.8 or similar. If this works,
Step 3) Use the working connection settings in the runner to be able to use the report from .NET Core. If the previous steps work fine, then its 99% sure that the runner will be able to work too, as its just a wrapper around Crystal Reports using named pipes for communication.

[rozyjaret]

I tried the above steps with .net Framework 4.8 console app, *It's working fine.* Designed a file with mysql connection string connectionString = "server=localhost;database=dbname;user=username;password=password;"; string query = "SELECT * FROM persons"; // Create a MySqlConnection object using (MySqlConnection connection = new MySqlConnection(connectionString)) { // Create a MySqlCommand object with the query and connection MySqlCommand command = new MySqlCommand(query, connection); // Create a DataTable to hold the data DataTable dataTable = new DataTable(); try { // Open the database connection connection.Open(); // Execute the query and fill the DataTable with the results MySqlDataAdapter adapter = new MySqlDataAdapter(command); adapter.Fill(dataTable); } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); return; } finally { // Close the database connection connection.Close(); } // Create a ReportDocument object ReportDocument report = new ReportDocument(); // Load the Crystal Report file (.rpt) report.Load("mysqlrpt.rpt"); // Set the DataTable as the report's data source report.SetDataSource(dataTable); report.SetParameterValue("ReportFrom", new DateTime(2024, 04, 30)); report.SetParameterValue("Reporter", "xyz"); report.SetParameterValue("Details", "Testing mysql"); // For example, you can export it to PDF and then open the PDF file report.ExportToDisk(ExportFormatType.PortableDocFormat, "report.pdf"); // Then you can use Process.Start("report.pdf") to open the PDF file Process.Start("report.pdf"); } *BUT when using runner:* *Case 1: when I use it without connection , it shows a popup and after filling details i can see the report BUt getting error If I try to export the report* private static Report CreateReport() { var report = new Report("mysqlrpt.rpt", "Sample Report") { }; report.Parameters.Add("ReportFrom", new DateTime(2022, 01, 01)); report.Parameters.Add("Reporter", "RJ"); report.Parameters.Add("Details", "Msql Report"); return report; } [image: image.png] *Case 2: *If I pass the connection details*(which is the requirement of my

project to use different connection for the report) *I get error for viewing and export both

private static Report CreateReport() { var report = new Report("mysqlrpt.rpt", "Sample Report") { Connection = CrystalReportsConnectionFactory.CreateSqlConnection("localhost","database"."username","password") }; report.Parameters.Add("ReportFrom", new DateTime(2022, 01, 01)); report.Parameters.Add("Reporter", "RJ"); report.Parameters.Add("Details", "Msql Report"); return report; } [image: image.png] *What am I doing wrong? Please help. Thanks in advance.*

…

On Mon, May 6, 2024 at 10:09 PM Gerardo Lijs ***@***.***> wrote: Hi, the code looks fine. I did a small research and it seems that its not possible to connect directly Crystal Reports to MySQL. Every sample I found its using ODBC as a connection. See: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-crystalreports.html or https://dev.mysql.com/doc/connector-net/en/connector-net-programming-crystal-displaying.html Last time I used MySQL was more than 20 years ago so do please make more research on your own and don't take my word for this. I would recommend the following steps: Step 1) Can you create a report in the Crystal Reports Designer that connects to MySQL and works fine in their App? If this works fine, then Step 2) Use the connection settings that work inside Crystal Reports App and use them in C# code programatically to configure the report. For better debugging here, I recommend using Crystal Reports directly with .NET Framework 4.8 or similar. If this works, Step 3) Use the working connection settings in the runner to be able to use the report from .NET Core. If the previous steps work fine, then its 99% sure that the runner will be able to work too, as its just a wrapper around Crystal Reports using named pipes for communication. — Reply to this email directly, view it on GitHub <#24 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEANZ2JU4RVR3M3JFZ4W7ODZA6W37AVCNFSM6AAAAABHFMUOG2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGMZRG4YDK> . You are receiving this because you authored the thread.Message ID: <gerardo-lijs/CrystalReportsRunner/repo-discussions/24/comments/9331705@ github.com>

[gerardo-lijs]

Thats a lot of good progress. I will add my comments for each part.

1. The MySQL code working directly with Crystal Reports is not configuring a connection but directly sending the DataTable. This is the reason it works, Crystal Reports is not connecting to MySQL, but just displaying data from the DataTable.
2. For Case 1, it will not work because you can't use SqlConnection with Crystal Report for MySQL. This only works with Microsoft SQL Server. You can however do the same you did in the code in step 1, create a DataTable and send it directly to the .NET Core Runner. To do this, you should not set the connection and set the "DataSets" like you can see in the following picture or in the source code, in the samples/tests. It's the same approach as direct MySQL and Crystal, but adding the Runner in the Middle for .NET Core.

3. For Case 2, if you want to send the connection details to work "directly" with MySQL, you will need to use ODBC DSN connection information and specify that in the Provider/Driver. It can be done but it requieres some steps. I'm currently working on some samples that use ODBC directly with Microsoft SQL that you will find interesting for MySQL as well. I'm currently using this to be able to use an Encrypted SQL Server with advanced options that are not available in Crystal Reports SQL Driver client.

[gerardo-lijs]

Please take a look at the changes in main branch. I think you will be able to adapt this for MySql creating whatever setting MySql ODBC connector requires. See 132e331

And the test here will help you in how to use it

[rozyjaret]

Hi, *Thanks a ton for your efforts and prompt response!!* Finally I have created a ODBC helper class to create Data Source for mysql. Then calling CreateODBCSqlConnection. Finally I can See the mysql data on my report. private static Report CreateReport() { var dsnName = "dsnname"; CrystalReportRunnerSample.*ODBCHelper*.*createMysqlDsn*(dsnName, "localhost", "dbname", "username", "password"); var report = new Report("mysqlrpt.rpt", "Sample Report") { Connection = CrystalReportsConnectionFactory.CreateODBCSqlConnection(dsnName, "dbname") }; report.Parameters.Add("ReportFrom", new DateTime(2024, 04, 13)); return report; } Thanks a lot again.

…

On Wed, May 8, 2024 at 12:29 AM Gerardo Lijs ***@***.***> wrote: Please take a look at the changes in main branch. I think you will be able to adapt this for MySql creating whatever setting MySql ODBC connector requires. See 132e331 <132e331> And the test here will help you in how to use it image.png (view on web) <https://github.com/gerardo-lijs/CrystalReportsRunner/assets/14139570/e7cdfeb2-c4d4-4288-b498-3c35707ba81b> — Reply to this email directly, view it on GitHub <#24 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEANZ2I5ESPQWTIGS6RCGU3ZBEQBLAVCNFSM6AAAAABHFMUOG2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGNBWGA4DQ> . You are receiving this because you authored the thread.Message ID: <gerardo-lijs/CrystalReportsRunner/repo-discussions/24/comments/9346088@ github.com>

[carlosxjose]

Could be great if you can publish of PR this example for future request... We can follow this example for other databases as well.

[rozyjaret]

Sure!

…

On Mon, May 13, 2024 at 6:08 PM Carlos Castaño ***@***.***> wrote: Could be great if you can publish of PR this example for future request... We can follow this example for other databases as well. — Reply to this email directly, view it on GitHub <#24 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEANZ2MSKBX5W7RRYZRWXILZCCX25AVCNFSM6AAAAABHFMUOG2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TIMJZGU2DG> . You are receiving this because you authored the thread.Message ID: <gerardo-lijs/CrystalReportsRunner/repo-discussions/24/comments/9419543@ github.com>

[rozyjaret]

I tried to make a pull request but it failed. PFA the example code for mysql connection using ODBC. I hope it will help someone with similar requirements. Thanks. CrystalReportsRunner.Samples-main.zip <https://drive.google.com/file/d/1t2f2D9b2rYVhsJYRvR_Wx_gcD2aW1qJz/view?usp=drive_web>

…

On Mon, May 13, 2024 at 6:56 PM rozy jaret ***@***.***> wrote: Sure! On Mon, May 13, 2024 at 6:08 PM Carlos Castaño ***@***.***> wrote: > Could be great if you can publish of PR this example for future > request... We can follow this example for other databases as well. > > — > Reply to this email directly, view it on GitHub > <#24 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AEANZ2MSKBX5W7RRYZRWXILZCCX25AVCNFSM6AAAAABHFMUOG2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TIMJZGU2DG> > . > You are receiving this because you authored the thread.Message ID: > <gerardo-lijs/CrystalReportsRunner/repo-discussions/24/comments/9419543@ > github.com> >

[gerardo-lijs]

Hi, this link might help you with how to fork and PR to a public open source repository -> https://docs.github.com/en/get-started/exploring-projects-on-github/contributing-to-a-project