-
Sep 28th, 2018, 02:56 PM
#1
Thread Starter
Hyperactive Member
How to use mySql instead of Microsoft SQL server
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.
Nothing I post is Self Reliable. Use it at your own risk
-
Sep 28th, 2018, 10:28 PM
#2
Re: How to use mySql instead of Microsoft SQL server
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:
vb.net Code:
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:
vb.net Code:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|