How to use mySql instead of Microsoft SQL server

[TATARPRO]

Please can someone help me out on how I can use mySql in my database application instead of using Microsoft SQL server? Due to some reasons, I don't want to use Microsoft sql server.

[jmcilhinney]

ADO.NET is basically the same regardless of the data source, so your application will change very little. In fact, you can even convert an application that currently uses SQL Server to use MySQL with very little effort.

Firstly, you need to reference Connector/Net in your project. You can add it as a NuGet package on a per project basis or you can install it on your dev machine and then reference it directly. You can find the download for Connector/Net and other MySQL tools here.

Once you've done that, you simply use types from the MySql.Data.MySqlClient namespace instead of the equivalent types from the System.Data.SqlClient namespace. For instance, where you would use a SqlConnection to connect to SQL Server, you use a MySqlConnection to connect to MySQL. If you install the MySQL tools for VS then you can also do things like create a typed DataSet that uses MySqlClient types under the hood.

The pattern of your code will be exactly the same. For example, here's some example code I wrote using SQL Server:

Private Sub ExecuteQuery()
    Dim connection As New SqlConnection(My.Settings.ConnectionString)
    Dim command As New SqlCommand("SELECT * FROM Data WHERE DateAndTime >= @StartDate AND DateAndTime < @EndDate", connection)
 
    command.Parameters.AddWithValue("@StartDate", fromDatePicker.Value.Date)
    command.Parameters.AddWithValue("@EndDate", toDatePicker.Value.Date.AddDays(1))
 
    Dim adapter As New SqlDataAdapter(command)
    Dim table As New DataTable
 
    adapter.Fill(table)
    DataGridView1.DataSource = table
End Sub

and here's that code converted for MySQL:

Private Sub ExecuteQuery()
    Dim connection As New MySqlConnection(My.Settings.ConnectionString)
    Dim command As New MySqlCommand("SELECT * FROM Data WHERE DateAndTime >= @StartDate AND DateAndTime < @EndDate", connection)
 
    command.Parameters.AddWithValue("@StartDate", fromDatePicker.Value.Date)
    command.Parameters.AddWithValue("@EndDate", toDatePicker.Value.Date.AddDays(1))
 
    Dim adapter As New MySqlDataAdapter(command)
    Dim table As New DataTable
 
    adapter.Fill(table)
    DataGridView1.DataSource = table
End Sub

The only changes are the 'My' on the type names. You'd have to change the connection string, of course. You can find details of what's needed for that here. You may need to some minor changes to the SQL code in some cases too, although in other cases you won't need any. MySQL even added support for "@" as a parameter prefix to Connector/Net for maximum compatibility.