Change datasource in Visual Studio 2012 from SQLSRV to MySQL

Question

I have a basic Application in Visual Studio 2012 which is connected to Sql Server 2008 R2 but now I want it to be connected only to a MySQL database (whose tables and columns are the same).

I downloaded MySQL for Visual Studio and I got a successfully connection in Data Sources to my database in MySQL.

Now I want to ask, what should I change from my app (apart of the connection string) if it only does simple CRUD with the db?

For example I've changed connection strings and when the app tried to search in db and fill a dataset I found an error in this code:

SqlDataAdapter dataAdapter = database.getPaquetes(date, _destino);

        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
        DataSet ds = new DataSet();
        dataAdapter.Fill(ds);
        dgv_paqdisp.ReadOnly = true;
        dgv_paqdisp.DataSource = ds.Tables[0];

ERROR:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

Are those types only for SQL Server? If yes, what is an equivalent when using MySQL?

Notice I don't want you to re-make my code or anything, I only want to know if there is something I'm missing or if its not that simple to change DataSource? Thanks in advance.

score 1 · Answer 1 · answered May 24 '14 at 23:58

You'll neet to add a reference to MySQL Connector/NET assemblies. They should come with MySQL for Visual Studio, otherwise that link has download instructions.

Then your code would become something like

MySqlDataAdapter dataAdapter = database.getPaquetes(date, _destino);

It shouldn't change much since both implemented using ADO.net

score 1 · Accepted Answer · answered May 25 '14 at 00:33

As WearWolf write you need a Mysql Connector/Net. You can download it from Nuget as packages. For the code that you have written for Sql Server will need to modified too much to get it worked on MySQL.

for example

SqlConnection will be MySqlConnection

SqlCommand will be MySqlCommand

You can do this changes Be Manual Find replace for every .cs file in solution carefully. At then end you will got working your app in MySQL.

You didn't mention that your CRUD is written using Entity framework and data-source tag make confusion here.

If you use Entity framework then simply generate the entity from database, because Mysql is a different database system you need to make the table in your mysql database first.

or if you use SQl queries execution for CRUD then just use given Find and replace and it will works.

I finally changed it, I can do an insert, but at the time of selecting data and filling a dataset to be displayed in a Data Grid View, I found with this error: `Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>='` exactly on this line `DataSet ds = new DataSet(); dataAdapter.Fill(ds);` — mdelafuq, May 25 '14 at 22:38

Change datasource in Visual Studio 2012 from SQLSRV to MySQL

2 Answers2