TOUT SUR L'INFORMATIQUE ET LA TECHNOLOGIE

BUILD THE MASS IN A FEW MONTHS THAT OTHERS BUILD IN YEARS!

Publish perfectly-optimized content in 1-click




How to create a local MySQL connection in C#





In this article I'll show you how to connect to a MySQL database using MySQL Connector .NET, we will use a local Apache server created with XAMPP.


Prerequisites:

Microsoft Visual Studio Community 2022 - version available on the microsoft.com website
XAMPP installed - Go to installer Apache Friends to download XAMPP and See How to install it (with Apache, MySQL & phpMyAdmin) on Windows
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;

 --
 --Structure of the `contact` table
 --
</br>
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
{
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
</br>
// MySQL library added in references.
</br>
using MySql.Data.MySqlClient;
</br>
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=";

// 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()
{
}
}

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; }
</br>
// 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.
        }
    }
}

public class DBContacts
{
</br>
    private MySqlConnection connection;
</br>
    // Constructor
    public DBContacts()
    {
        this.InitConnexion();
    }
</br>
    // 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);
    }
</br>
    // Method to add a contact
    public void AddContact(Contact contact)
    {
        try
        {
            // Open SQL connection
            this.connection.Open();
</br>
            // Creation of an SQL command based on the connection object
            MySqlCommand cmd = this.connection.CreateCommand();
</br>
            // SQL request
            cmd.CommandText = "INSERT INTO contact (id, name, tel) VALUES (@id, @name, @tel)";
</br>
            // 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);
</br>
            // Executing the SQL command
            cmd.ExecuteNonQuery();
</br>
            // 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);
    }
}

static class Program
{
</br>
    static void Main(string[] args)
    {
        // Create a contact to add
        Contact contact = new Contact();
        contact.Id = 1;
        contact.Name = "John";
        contact.Tel = "2567407000";
</br>
        // 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.


You could download the whole project here Download this project

Télécharger des logiciels

How to create a local MySQL connection in C#

Prerequisites:

Let's get started