What is MySQL Connector NET: Installation, Connection & Usage Simplified

A growing business always finds it challenging to effectively process and handle the exponentially rising volumes of data. This requires a robust all-in-one solution that is scalable, effective, reliable, and secure. MySQL is such a powerful Open-Source Relational Database Management System that provides full Standard SQL support to quickly query, manipulate & add data to your MySQL Tables.

To develop a connection with a database, and start querying data you can use MySQL Connector NET. It is a completely managed Data Provider that allows you to create .NET applications requiring secure & high-performance Data Connectivity with MySQL.

In this article, you will learn how to effectively set up & use MySQL Connector NET.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating databases. It is widely used for web applications and as a component of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack.

Key Features of MySQL

• Open-source: Freely available and modifiable under the GNU General Public License.
• High Performance: Designed for high-speed, scalable applications with fast query processing.
• Cross-platform Support: Available for various operating systems like Linux, Windows, macOS.
• Scalability: Supports large databases, up to terabytes of data.
• Data Security: Offers strong data protection with authentication, encryption, and SSL support.

What is MySQL Connector NET?

MySQL Connector NET is a completely managed ADO.NET (Activex Data Object Network Enable Technologies) Data Provider. A Data Provider in general is used to establish a connection with a database, run commands and retrieve data. Similarly, MySQL Connector NET allows you to build .NET applications requiring secure & high-performance data connectivity with MySQL. Written fully in C#, MySQL Connector NET executes the necessary ADO.NET interfaces and integrates them into ADO.NET-aware tools.

It modifies the Entity Framework Core to operate with MySQL data and facilitates compression in the .NET Driver implementation. MySQL Connector NET also extends full cross-platform support to Linux and macOS. You can easily run MySQL Connector NET with Microsoft Visual Studio. Currently, MySQL Connector NET 8.0 is running which is a continuation of Connector NET 7.0. The older version i.e. MySQL Connector NET 6.10 has reached end-of-service. You can check out the MySQL Connector NET Release Notes to keep a tab on the modifications and features available in various versions.  

How do you Manage a Connection Pool in Connector/NET?

With database-intensive applications, the MySQL Connector/NET supports connection pooling for improved performance and scalability. By default, this is turned on. Using the connection string options, you can turn it off or change its performance characteristics. Pooling, Connection Reset, Connection Lifetime, Cache Server Properties, Max Pool Size, and Min Pool Size are all terms used to describe how a connection is set up.

Guidelines:

1. Avoid Manual Connection Management:

• Do not create and manage MySqlConnection objects directly.
• Rely on the connection pooling system for optimal performance.
• Manually managing connections disrupts pooling, leading to unpredictable behavior and potential exceptions.

2. Use Connection Strings:

• Utilize overloaded methods that accept connection strings as arguments.
• Connector/NET manages connection pooling automatically when using connection strings.
• This approach is used by Typed Datasets, MembershipProvider, RoleProvider, and many other classes.

3. Utilize MySqlHelper:

• MySqlHelper methods accept connection strings and fully support connection pooling.
• Employ the static methods of the MySqlHelper class for creating MySqlCommand objects.

How to use MySQL Connector NET?

To get started with effectively using the MySQL Connector NET, you can go through the following critical aspects:

1. Installing MySQL Connector NET
2. Creating a MySQL Connector NET Connection
3. Retrieving Data

1) Installing MySQL Connector NET

MySQL Connector NET can be easily installed on any platform that supports .NET Framework, .NET Core, and Mono. For example, Windows and Unix. You can select any of these methods to install MySQL Connector NET:

• Method 1: The recommended and effective method for Windows OS is by downloading & installing the MySQL Installer. While going through the installation wizard, choose and install the Connector NET product.

• Method 2: Another way is via the Windows Installer (.msi) Installation Package named as mysql-connector-net-version.msi, where version denotes the Connector/NET version. Follow the simple steps for the installation:
  • Step 1: Open the MSI installer and click on the Next button.
  • Step 2: You can now choose from Typical, Complete, or Custom Installation. A Typical Installation is a general-purpose approach whereas the Complete Installation installs all the files available. A Custom Installation allows you to select the components you like to install. Click on the Next button after choosing the desired installation.

• Step 3: After the successful installation, click on the Finish button to exit the installer.

• Step 4: Alternatively, you can directly install via the /quiet or /q command-line option with the msiexec tool. Though, it doesn’t provide any options, prompts, messages, or dialog boxes.

C:> msiexec /package connector-net.msi /quiet

• Method 3: You can also use the NuGet gallery to install or upgrade MySQL Connector NET. You can visit NuGet Gallery to check out the variety of client tools for a convenient installation.
• Method 4: For installing on Unix, you can follow the simple steps given below:
  • Step 1:  Firstly, ensure that you have Mono installed on your system by the following command.

$> mono --version

Also, make sure that the Mono C# compiler is installed for compiling the C# Source code. 

• Step 2: Now, you can download the mysql-connector-net-version-noinstall.zip and extract its components to a directory of your choice, for instance: ~/connector-net/. Navigate to the directory where you extracted the zip file and go to the bin subfolder. Check that MySql.Data.dll is present.
• Step 3: In the current directory, execute the following command to register the Connector/NET component, MySql.Data, in the Global Assembly Cache (GAC).

#> gacutil /i MySql.Data.dll

You are now ready to compile your applications, for example:

$> gmcs -r:System.dll -r:System.Data.dll -r:MySql.Data.dll NewApplication.cs 

• Method 5: For complete control, you can build and customize the MySQL Connector NET from the source code. You can Download the Source Code and build the Connector using Visual Studio or .NET Core CLI. For more information on the installation methods, you can visit the MySQL Connector NET Installation page.

2) Creating a MySQL Connector NET Connection

For any interaction to happen between the .NET application and the MySQL Server, you must start and configure the MySqlConnection object. Follow the easy steps given below to get started:

• Step 1: A Connection String consisting of several key-value pairs, separated by semicolons is essential for configuring the MySqlConnection object. An example string given below connects to a MySQL Server at “198.0.0.1”, with a user name of “main” and a password of “68375”. 

"server=198.0.0.1;uid=main;pwd=68375;database=sample" 

After creating the Connection String, you can use the following C# program to open a connection with a single server:

MySql.Data.MySqlClient.MySqlConnection conn;
string sampleConnectionString;

sampleConnectionString = "server=198.0.0.1;uid=main;" +
    "pwd=68375;database=sample";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection();
    conn.ConnectionString = sampleConnectionString;
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

A similar code for Visual Studio is:

Dim conn As New MySql.Data.MySqlClient.MySqlConnection
Dim myConnectionString as String

sampleConnectionString = "server=198.0.0.1;" _
            & "uid=main;" _
            & "pwd=68375;" _
            & "database=sample"

Try
  conn.ConnectionString = sampleConnectionString
  conn.Open()

Catch ex As MySql.Data.MySqlClient.MySqlException
  MessageBox.Show(ex.Message)
End Try

• Step 2: You can follow a similar approach to establish a connection for Multiple Hosts with Failover. A sample Connection String for both classic MySQL protocol and X Protocol connections is as follows:

// classic protocol example
"server=142.0.0.1:5508,198.1.10.10:5507,localhost:5508;uid=test;password=xxxx"

// X Protocol example
mysqlx://test:test@[198.1.10.10:5507,142.0.0.1:5508]

• Step 3: DNS Service Records can also be used for providing Failover, Load Balancing, and Replication services when multiple MySQL instances provide the same service in your installation. The following sample C# code shows how to easily open a connection using DNS SRV records by adding the DNS-SRV connection option to your Connection String.  

var conn = new MySqlConnection("server=_mysql._tcp.jkl.efg.com.;dns-srv=true;" +
             "user id=main;password=****;database=sample");

Note: Connection Pooling System automatically manages all connections. Regular background checks every three minutes are carried for removing connections from the pool that have been idle (unused) for more than three minutes. This frees up the essential resources on both the client-side and server-side. As a best practice, try creating MySqlCommand objects via static methods of the MySqlHelper Class rather than manually. This allows the Connection Pooling System of the MySQL Connector NET to automatically build, open, close, and end connections for best performance.

3) Retrieving Data

Several MySQL Connector NET Classes assist in executing queries and statements and managing query results. The most commonly used is the MySqlCommand class. This class’s methods allow you to query, add, insert or update a database. For faster execution, you can organize the MySqlCommand as a Prepared Statement or a stored procedure. The MySqCommandl consists of properties namely CommandText and CommandType Properties, Parameters Property, Attributes Property, and CommandTimeout Property. 

As an example, let’s discuss the Command Type Property. By Default, the CommandType type is set to “Text” for executing queries and other SQL commands. For Instance, if the CommandType is set to “TableDirect” then all the rows and columns of the table are returned once you have called the execute methods. This is similar to the SQL statement “SELECT * ” and the following code can be used to obtain the said output.

...
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "mytable";
cmd.Connection = someConnection;
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
   Console.WriteLn(reader[0], reader[1]...);
}
... 

You can check out the Official Documentation to know more about MySQLCommand and several other MySQL Connector NET classes and methods such as BulkLoader, GetSchema() method, etc. 

Connection Errors in C# and Visual Basic: How to Handle Them

Because connecting to an external server can be unpredictable, your.NET application should include error handling. The MySqlConnection class will return a MySqlException object if there is a connection error. When dealing with errors, this object has two properties that are useful:

• Message: A textual description of the current exception.
• The MySQL error number is a number.

You can adjust your application’s response based on the error number when dealing with errors. When connecting, the following are the two most common error codes:

• 0: Server cannot be reached.
• 1045: User name, password, or both are invalid.

The following code example demonstrates how to manage an application’s response based on the actual error:

C# Example:

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
    conn.Open();
}
    catch (MySql.Data.MySqlClient.MySqlException ex)
{
    switch (ex.Number)
    {
        case 0:
            MessageBox.Show("Cannot connect to server.  Contact administrator");
            break;
        case 1045:
            MessageBox.Show("Invalid username/password, please try again");
            break;
    }
}

Visual Basic Example:

Dim myConnectionString as String

myConnectionString = "server=127.0.0.1;" _
          & "uid=root;" _
          & "pwd=12345;" _
          & "database=test"

Try
    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
    conn.Open()
Catch ex As MySql.Data.MySqlClient.MySqlException
    Select Case ex.Number
        Case 0
            MessageBox.Show("Cannot connect to server. Contact administrator")
        Case 1045
            MessageBox.Show("Invalid username/password, please try again")
    End Select
End Try

What are the Connector/NET Authentication Methods and Plugins?

MySQL Connector/NET provides a number of authentication plugins that MySQL Server can use to verify a user’s identity. Based on the user name and host name that your application passes to the server when making a connection, pluggable authentication allows the server to determine which plugin applies. See Pluggable Authentication for a detailed description of the authentication process.

The following authentication plugins and methods are available in Connector/NET:

• authentication_kerberos_client: For MySQL Enterprise Edition user accounts and services, applications and MySQL servers can use the Kerberos authentication protocol. Both the user and the server can validate each other’s identity using the authentication kerberos client plugin. Kerberos protocol messages are protected from eavesdropping and replay attacks, and no passwords are ever sent over the network. Only Linux is supported for the server-side plugin.

By host type, the availability of Kerberos authentication and the requirements for enabling it vary. For.NET applications running on macOS, Connector/NET does not provide Kerberos authentication. Kerberos authentication is implemented using the following interfaces in Linux and Windows applications:

For Linux, the GSSAPI stands for Generic Security Service Application Program Interface.

For traditional MySQL protocol connections, Connector/NET 8.0.26 is required. Only Linux users will be able to use this feature.

To allow authentication of request tickets for Connector/NET by a MySQL server, MIT Kerberos must be installed on each client system.

For classic MySQL protocol connections, the minimum version is Connector/NET 8.0.27. Only Windows is supported.

SSPI/Kerberos is used for authentication in Connector/NET. GSSAPI is implemented by SSPI on Windows. The following are some of the behavioural differences between SSPI and GSSAPI:

• Configuration: External libraries or Kerberos configuration are not used by Windows clients. On Linux, for example, you can change the expiry time of a ticket-granting ticket (TGT), the port of a key distribution centre (KDC), and so on. None of these options are available on Windows.
• TGT Ticket caching: If you provide a user name and password for authentication, the obtained tickets are not stored in the cache. Every time, new tickets are obtained.
• Authentication without a user or a password: The credentials and user name of the currently logged-in user on Windows are used. For a successful login, the Windows client must be a member of the server’s Active Directory domain.

• authentication_ldap_sasl_client:

SASL-based LDAP authentication requires MySQL Enterprise Edition and can only be used to connect to MySQL servers using the classic protocol. This authentication protocol is compatible with Linux, Windows (partial support), and macOS applications.

Version 1 (minimal):

On Linux and Windows, use Connector/NET 8.0.22 (SCRAM-SHA-1).

On Linux and Windows, use Connector/NET 8.0.23 (SCRAM-SHA-256).

Only Connector/NET 8.0.24 (GSSAPI) is supported on Linux.

To enable authentication of Connector/NET request tickets by a MySQL server, MIT Kerberos must be installed on each client system. The GSSAPI mechanism must be configured in the authentication ldap sasl plugin, and the application user must be identified as follows:

IDENTIFIED WITH 'authentication_ldap_sasl'

For Linux, you’ll need the libgssapi krb5.so.2 library.

• authentication_oci_client:

Connector/NET 8.0.27 is the minimum version required for classic MySQL protocol connections.

Oracle Cloud Infrastructure pluggable authentication is supported by Connector/NET, allowing.NET applications to securely access MySQL Database Services without the use of passwords. For.NET Framework 4.5.x implementations, this pluggable authentication is not supported.

Access to a tenancy, a Compute instance, a MySQL DB System connected to a private network, and properly configured groups, compartments, and policies are all required for this type of connection. The basic setup for MySQL user accounts can be done by an Oracle Cloud Infrastructure administrator.

Before attempting a connection, the MySQL DB System must also have the server-side authentication plugin installed and loaded. The client-side authentication plugin is implemented by Connector/NET.

The client-side plugin locates the user’s Oracle Cloud Infrastructure configuration file during authentication and obtains a signing key file from it. The ociConfigFile connection option can be used to specify the location of the configuration file; otherwise, the default location is used. Connector/NET then signs a token it receives from the server, uses the token to generate the SHA256 RSA signature it sends back to the server, and waits for the authentication process to succeed or fail.

The following are examples of possible error conditions:

• The private key specified in the OCI configuration entry ‘key file’ could not be found.
• The private key could not be found at the specified location by Connector/NET. The ‘key file’ OCI configuration entry does not refer to a valid key file.
• The specified private key could not be loaded or used by Connector/NET. There is no ‘fingerprint’ or ‘key file’ entry in the OCI configuration file.
• The fingerprint entry, the key file entry, or both are missing from the configuration file. The OCI configuration file was unable to be read.
• The configuration file could not be found or loaded by Connector/NET. Make sure the ociConfigFile value corresponds to the file’s location.
• The OCI SDK isn’t installed or can’t be found. At runtime, Connector/NET was unable to load the Oracle Cloud Infrastructure SDK library.

The OCI.DotNetSDK is used by Connector/NET. To read configuration-file entry values, a common NuGet package in the Oracle Cloud Infrastructure SDK library must be available.

• authentication_windows_client:

All versions of Connector/NET are supported.

Using the Windows Native Authentication Plugin, MySQL Connector/NET applications can authenticate to a MySQL server. Users who have logged in to Windows can connect to the MySQL server using the information in their environment without having to enter another password. The MySql.Data.MySqlClient object has the same interface as the MySql.Data.MySqlClient object.

To enable, use the value yes or sspi in the connection string for Integrated Security. It is not required to enter a user ID. A MySQL user is created and configured to be used by Windows authentication when Windows authentication is set up.

This user ID is called auth windows by default, but it can be given a different name. Passing the user ID to the Connector/NET connection string is optional if the default name is used, because the auth windows user will be used. Otherwise, the user ID element must be used to pass the name to the connection string.

• caching_sha2_password: Connector/NET 8.0.11 is the minimum version required for classic MySQL protocol connections.
• mysql_clear_password:

Only for classic MySQL protocol connections, Connector/NET 8.0.22 is required.
A secure connection to the server is required by mysql clear password, which can be met by either of the following client conditions:

None is not the value for the SslMode connection option. By default, Preferred is set to this value.

For Unix domain sockets, the ConnectionProtocol connection option is unix.

• mysql_native_password: To establish classic MySQL protocol and X Protocol connections, all versions of Connector/NET are supported.
• sha256_password: Connector/NET 8.0.11 is required for traditional MySQL protocol connections and X Protocol connections using the MYSQL41 mechanism (see the Auth connection option).

Conclusion

In this article, you have learned how to effectively work with MySQL Connector NET. You can easily install MySQL Connector NET on Windows and Unix. After you have created a Connection String you can establish a MySQL Connector NET connection with a Single Server as well as with Multiple hosts. You can also add, insert, modify or query your Database using MySQL Connector NET Classes such MySqlCommand, BulkLoader, etc.

To get a complete picture of your business health & performance, you need to consolidate data from MySQL and all the other applications used across your business for Marketing, Customer Relationship Management, Accounting, Sales, etc. To achieve this you need to assign a portion of your Engineering Bandwidth to Integrate Data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-Based ETL tool such as Hevo Data.  

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 150+ sources such as MySQL to a Data Warehouse or a Destination of your choice to be visualized in a BI Tool. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using MySQL as your Database Management System and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 150+ sources & BI tools(Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Sign up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

FAQs