Open Menu dzosoft
Close Menu dzosoft

   ALL ABOUT COMPUTER SCIENCE AND TECHNOLOGY


                             




Dazzle with your smile!

Publish perfectly-optimized content in 1-click









 
 
 

How to create a local MySQL connection in C#

 
 
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;


 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.


You could download the whole project here   Download this project

It might also interest you


C# Get Started

Using ChatGPT in C#

Quick overview of the differences between C# and Visual Basic .NET

How to execute a .NET Assembly inside a C# Program


Leave comment
          

Save nickname and email in this browser for the next time.



Loading...     
close publicity
Nord VPN