Follow

How to connect to a MySQL data source ADO.Net

MySQL ADO.Net

This article is going to explain how you connect to a MySQL data source through the Any Source ADO connector.

Install the MySQL provider

Download a .Net mysql provider. I have used MySQL for NET. Just download the provider and install it. If you have our program opened when installing this, you need to restart it to see it.

Setting up the any source ado connection

Start by adding a new data source. Right click on Data Sources, go to Data Sources and click on Add Any Source ADO.Net.

01.PNG

You then need to choose that it is a MySQL connection. Press the Provider drop down menu and choose MySQL Data Provider (.Net Framework Data Provider for MySQL).

2.PNG

Find the Connection area. In the field Database add the database name. In the Server field you add the server name. It can be a ip address as well. I have added port 3306 in the Port field. It is not necessary in this case, as it is the expected default and my MySQL server is on localhost.

3.PNG

Find the Security area. In the User Id field add a user that has read rights on the database. I use root as it is the admin. In the Password field add that users password.

4.PNG

Then we press the Advanced Settings button.

5.PNG

Change the Prefix and Suffix to ` and press OK to close this menu and then OK again to add the data source.

6.PNG

That is it. Synchronize with the data source to see the fields.

If you want to remove some of the system fields from the list, you can set it up to only show a specific schema.

Start by going to the Advanced Data Source Properties menu. To see the existing schema's press the Table Schema button. In the Object filtering area set the Object Type to Schema Name, the Filter Type to String Equal and the Filter Value to the schema name you chose.

7.PNG

Press OK in both menus to save the settings and synchronize with the data source to remove all tables that does not exist in the schema you chose.

Common issues with MySQL data

Date fields smaller than 1753-01-01

In MySQL the smallest allowed date is 0001-01-01 in MSSQL that is not the case for datetime fields.

If that is the case you will get the following error message.

8.PNG

To fix this you can use the override data type feature: Using the Override Data Type feature.

In this case you would use the General Rule where DateTime fields are converted to DateTime2 fields. Remember that it differentiates between upper case and lower case.

9.PNG

Apply the override and synchronize with the data source. You should get a notification about which fields were affected by the change.

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.