How to connect to MySQL Database?
Solution 1
Install Oracle's MySql.Data
NuGet package.
using MySql.Data;
using MySql.Data.MySqlClient;
namespace Data
{
public class DBConnection
{
private DBConnection()
{
}
public string Server { get; set; }
public string DatabaseName { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
private MySqlConnection Connection { get; set;}
private static DBConnection _instance = null;
public static DBConnection Instance()
{
if (_instance == null)
_instance = new DBConnection();
return _instance;
}
public bool IsConnect()
{
if (Connection == null)
{
if (String.IsNullOrEmpty(databaseName))
return false;
string connstring = string.Format("Server={0}; database={1}; UID={2}; password={3}", Server, DatabaseName, UserName, Password);
Connection = new MySqlConnection(connstring);
Connection.Open();
}
return true;
}
public void Close()
{
Connection.Close();
}
}
}
Example:
var dbCon = DBConnection.Instance();
dbCon.Server = "YourServer";
dbCon.DatabaseName = "YourDatabase";
dbCon.UserName = "YourUsername";
dbCon.Password = "YourPassword";
if (dbCon.IsConnect())
{
//suppose col0 and col1 are defined as VARCHAR in the DB
string query = "SELECT col0,col1 FROM YourTable";
var cmd = new MySqlCommand(query, dbCon.Connection);
var reader = cmd.ExecuteReader();
while(reader.Read())
{
string someStringFromColumnZero = reader.GetString(0);
string someStringFromColumnOne = reader.GetString(1);
Console.WriteLine(someStringFromColumnZero + "," + someStringFromColumnOne);
}
dbCon.Close();
}
Solution 2
You can use Package Manager to add it as a package and it is the easiest way. You don't need anything else to work with a MySQL database.
Or you can run this command in Package Manager Console:
PM> Install-Package MySql.Data
Solution 3
You must to download MySQLConnection NET from here.
Then you need add MySql.Data.DLL
to MSVisualStudio like this:
- Open menu project
- Add
- Reference
- Browse to
C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.12\Assemblies\v4.5.2
- Add MySql.Data.dll
If you want to know more visit: enter link description here
To use in the code you must import the library:
using MySql.Data.MySqlClient;
An example with connectio to Mysql database (NO SSL MODE) by means of Click event:
using System;
using System.Windows;
using MySql.Data.MySqlClient;
namespace Deportes_WPF
{
public partial class Login : Window
{
private MySqlConnection connection;
private string server;
private string database;
private string user;
private string password;
private string port;
private string connectionString;
private string sslM;
public Login()
{
InitializeComponent();
server = "server_name";
database = "database_name";
user = "user_id";
password = "password";
port = "3306";
sslM = "none";
connectionString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
connection = new MySqlConnection(connectionString);
}
private void conexion()
{
try
{
connection.Open();
MessageBox.Show("successful connection");
connection.Close();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message + connectionString);
}
}
private void btn1_Click(object sender, RoutedEventArgs e)
{
conexion();
}
}
}
Solution 4
Looking at the code below, I tried it and found:
Instead of writing DBCon = DBConnection.Instance();
you should put DBConnection DBCon - new DBConnection();
(That worked for me)
and instead of MySqlComman cmd = new MySqlComman(query, DBCon.GetConnection());
you should put MySqlCommand cmd = new MySqlCommand(query, DBCon.GetConnection());
(it's missing the d)
Solution 5
Another library to consider is MySqlConnector, https://mysqlconnector.net/. Mysql.Data is under a GPL license, whereas MySqlConnector is MIT.
Admin
Updated on July 08, 2022Comments
-
Admin almost 2 years
New to C# programming, I'd like to be able to access
MySQL
Databases.I know
MySQL connector/NET
andMySQL for Visual Studio
are required for C# development.
Do I need to install them into my app?
Is it possible I can just release the connector DLL with the program?Update:
Are both of them required for the end-user or just the connector?
Is there anything else they would need?