The MySQL Connector .NET DLL files - Available on the mysql.com site or in our project to download at the end of this article..
Let's get started
Launch Apache and MySQL services from XAMPP Control Panel. Now we can move on to the database creation and connection steps from a C# application.
Open the MySQL Admin page (From XAMPP Control Panel or type 127.0.0.1 in your browser URL) and create a new database. In our example we will call it "DBContacts".
After creating your database, add a table to it. In our example we create a "contact" table with the following fields:
id (INTEGER), name (VARCHAR), tel (VARCHAR).
--
--Structure of the `contact` table
--
CREATE TABLE IF NOT EXISTS `contact` (
`id` int(10) NOT NULL,
`name` varchar(250) NOT NULL,
`tel` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now is the time to create your project in Visual Studio. Open the IDE, and add a new project. For my part I call it MySQLConCCharp. Once the project has been created, we will add the "references", in other words the libraries which will allow us to work with MySQL. Small reminder, a library is nothing other than a DLL file, made up of classes. To do this, right-click on “Reference” to add a new reference. Import the previously downloaded MySql.Data.dll DLL file.
Add the MySql namespace to your project, this assembly will be necessary for the DBContacts class shown below.
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
// MySQL library added in references.
using MySql.Data.MySqlClient;
namespace MySQLConCCharp
{
}
Connection string overview
// 127.0.01 is the localhost address of the Apache XAMPP server
// We created a database named "Customers"
// The default user when installing XAMPP is "root" without a password.
string connectionString = "SERVER=127.0.0.1; DATABASE=Customers; UID=root; PASSWORD=";
The following code shows how to insert data into a MySQL table. To do this, let's create a simple Contact class and Customer as an example with an add and connection method (or function).
public class Contact
{
// Creation of 3 properties identifier, name and telephone
public int Id { get; set; }
public string Name { get; set; }
public string Tel { get; set; }
// Constructor
public Contact()
{
}
}
The DBContacts class will allow communication between the SQL database and the program. Initialization of the connection is done in the constructor. We can very well not do this and put the InitConnection method in public with the connection string as a parameter.
public class DBContacts
{
private MySqlConnection connection;
// Constructor
public DBContacts()
{
this.InitConnexion();
}
// Method to initialize the connection
private void InitConnexion()
{
//Creating the connection string
string connectionString = "SERVER=127.0.0.1; DATABASE=Customers; UID=root; PASSWORD=";
this.connection = new MySqlConnection(connectionString);
}
// Method to add a contact
public void AddContact(Contact contact)
{
try
{
// Open SQL connection
this.connection.Open();
// Creation of an SQL command based on the connection object
MySqlCommand cmd = this.connection.CreateCommand();
// SQL request
cmd.CommandText = "INSERT INTO contact (id, name, tel) VALUES (@id, @name, @tel)";
// use of the contact object passed as a parameter
cmd.Parameters.AddWithValue("@id", contact.Id);
cmd.Parameters.AddWithValue("@name", contact.Name);
cmd.Parameters.AddWithValue("@tel", contact.Tel);
// Executing the SQL command
cmd.ExecuteNonQuery();
// Closing the connection
this.connection.Close();
}
catch
{
// Error management :
// Possibility of creating a Logger for SQL exceptions received
// Possibility of creating a method with a boolean in return to know if the contact was added correctly.
}
}
}
Usage in code:
static class Program
{
static void Main(string[] args)
{
// Create a contact to add
Contact contact = new Contact();
contact.Id = 1;
contact.Name = "John";
contact.Tel = "2567407000";
// Creation of the DBContacts object for interaction with the MySQL database
DBContacts dbContacts = new DBContacts();
dbContacts.AddContact(contact);
}
}
To finish, we have just seen how to create a local connection to MySQL with C# as well as a brief use of data processing. Be aware that your application can become extremely interesting if you host this database on a remote server. This manipulation will allow you to create shared applications in the same way as a client/server.
EN 
Español - ES 
Français - FR 
