.NET MySQL Connection Strings

This guide explains how to construct a connection string for your .NET application to connect to a MySQL or MariaDB database using dotConnect for MySQL. We'll cover the essential elements of the connection string and explore some advanced options for fine-tuning your database connection.

MySQL Connection Strings

To establish a connection to a server, you have to provide some connection parameters to dotConnect for MySQL. This information is used by the MySqlConnection component to find the server and log in with your account's credentials. The parameters are represented as connection strings. You can compose the connection string manually or have dotConnect for MySQL construct it for you.

using Devart.Data.MySql;
...
MySqlConnection connection = new MySqlConnection();
connection.Host = "127.0.0.1";
connection.Port = 3306;
connection.UserId = "root";
connection.Password = "mypassword";
connection.Database = "test";

You can do this all in a single assignment. It actually does not matter whether the connection string is assigned directly or composed with particular properties. After you assign a value to the ConnectionString property, all other properties will be populated with parsed values. So you can choose what is more convenient for you.

MySqlConnection.Connection = "Host=127.0.0.1;Port=3306;UserId=root;Password=mypassword;
Database=test;";

Direct Connection Properties

MySqlConnectionStringBuilder

The MySqlConnectionStringBuilder class offers a programmatic approach to constructing and managing a connection string. By utilizing this class, developers can set connection string parameters in a structured manner, thus reducing errors and improving code clarity.

using Devart.Data.MySql;

public void UseConnectionStringBuilder()
{
  MySqlConnectionStringBuilder connection = new MySqlConnectionStringBuilder();
  connection.Host = "127.0.0.1";
  connection.Port = 3306;
  connection.UserId = "root";
  connection.Database = "test";

  MySqlConnection myConnection = new MySqlConnection(connection.ConnectionString);
  myConnection.Open();
  Console.WriteLine(myConnection.ServerVersion);
  myConnection.Close();
}

MySqlConnection Constructor

The MySqlConnection constructor in dotConnect for MySQL is designed to accept a connection string as its parameter, which details the necessary credentials and parameters required to establish a connection with a MySQL database. Here's a basic example of using the MySqlConnection constructor:

using Devart.Data.MySql;

public void CreateMySqlConnection()
{
  string ConnectionString = "Host=127.0.0.1;Port=3306;UserId=root;Database=Test;";
  MySqlConnection connection = new MySqlConnection(ConnectionString);
  connection.Open();
  // Do something
  connection.Close();
}

SSL/TLS Connection Strings

Improving secure communication between your .NET application and MySQL or MariaDB database is critical if you want to safeguard your data in transit. SSL/TLS provides encryption at the transport layer, protecting data as it moves between the client and the server.

Here's a sample code that illustrates how to establish an SSL connection using dotConnect for MySQL or MariaDB:

MySqlConnection connection = new MySqlConnection("Host=127.0.0.1;protocol=SSL;UserId=root;Password=root;Database=test;");
connection.SslOptions.CACert = "file://D:\\Temp\\CA-cert.pem";
connection.SslOptions.Cert = "file://D:\\Temp\\SSL-client-cert.pem";
connection.SslOptions.Key = "file://D:\\Temp\\SSL-client-key.pem";
MySqlCommand myCommand = new MySqlCommand("select count(*) from dept",connection);
connection.Open();
Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
Console.WriteLine(count);
connection.Close();
Console.ReadLine();

SSL/TLS Connection Properties

SSH Connection Strings

A benefit of SSH tunneling is that it allows you to connect to a MySQL or MariaDB server from behind a firewall when the database server port is blocked. MySQL or MariaDB server does not need to be attuned for this type of connection and functions as usual. To connect to MySQL or MariaDB server a client must first be authorized on an SSH server.

The MySqlConnection.SshOptions property points to the object that holds all information necessary to connect to the SSH server. Alternatively, you may specify the corresponding parameters in the connection string.

To set up an SSH connection, set up the corresponding SSH options as described below:

Password Authentication

MySqlConnection connection = new MySqlConnection("Host=127.0.0.1;Port=3306;protocol=SSH;UserId=root;Password=root;Database=test;");
connection.SshOptions.AuthenticationType = SshAuthenticationType.Password;
connection.SshOptions.User = "sshUser";
connection.SshOptions.Host = "sshServer";
connection.SshOptions.Password = "sshPassword";
MySqlCommand myCommand = new MySqlCommand("select count(*) from dept", connection);
connection.Open();
Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
Console.WriteLine(count);
connection.Close();

Public Key Authentication

MySqlConnection connection = new MySqlConnection("Host=127.0.0.1;Port=3306;protocol=SSH;UserId=root;Password=root;Database=test;");
connection.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey;
connection.SshOptions.User = "sshUser";
connection.SshOptions.Host = "sshServer";
connection.SshOptions.PrivateKey = "E:\\WORK\\client.key";
MySqlCommand myCommand = new MySqlCommand("select count(*) from dept", connection);
connection.Open();
Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
Console.WriteLine(count);
connection.Close();

Keyboard-Interactive Authentication

MySqlConnection connection = new MySqlConnection("Host=127.0.0.1;Port=3306;UserId=root;Password=root;Database=test;");
connection.Protocol = MySqlProtocol.Ssh;
// sets ssh options
connection.SshOptions.Host = "ssh_host";
connection.SshOptions.Port = 22;
connection.SshOptions.User = "ssh_user";
connection.SshOptions.AuthenticationType = SshAuthenticationType.KeyboardInteractive;
// Associate the AuthenticationPrompt event with your event handle
connection.AuthenticationPrompt += new MySqlAuthenticationPromptHandler(connection_AuthenticationPrompt);

connection.Open();
Console.WriteLine(connection.State);

connection.Close();
Console.ReadKey();

...

static void connection_AuthenticationPrompt(object sender, MySqlAuthenticationPrompEventArgs e) {

  foreach (string prompt in e.Prompts) {
	if (prompt.Contains("Password"))
  	e.Responses[0] = "testPassword";
	else
  	if (prompt.Contains("UserId"))
  	e.Responses[1] = "testUserId";
  }
}

SSH Connection Properties

Locations of private keys can be specified in three ways:

• As a file in the system: file://C:\Temp\client.key.
• As an item of certificate store: storage://Name.Id. For examples: RSA.Client.key or DSA.Client.key.
• As a compiled resource: resource://client.key.
• Loaded to memory, using MemCryptStorage class: memory://key_id

The MySQL server address that you specify in the connection string is the address to which the SSH server can refer. For instance, if both servers are running on the same machine you need to specify "host=localhost" in the connection string.

The property SshOptions.CipherList contains the list of the ciphers that client agrees to use, separated by colons. By default, it is empty, meaning that the client agrees to use any of the available ciphers. The appropriate values for the CipherList property are listed below and highlighted in bold.

dotConnect for MySQL supports two modes of block ciphering: Cipher-block chaining (CBC) and Counter (CTR). The following ciphers are available for SSH connections in the CBC mode:

• 3DES or 3DES(168) - Triple Data Encryption Algorithm. Key size 168 bits.
• Blowfish - Symmetric-key block cipher, designed in 1993 by Bruce Schneier. Key size 128 bits.
• AES(128) - Advanced Encryption Standard. Key size 128 bits.
• AES(192) - Advanced Encryption Standard. Key size 192 bits.
• AES or AES(256) - Advanced Encryption Standard. Key size 256 bits.

In the CTR mode, the AES ciphers are used:

• AES(128)-CTR - Advanced Encryption Standard. Key size 128 bits.
• AES(192)-CTR - Advanced Encryption Standard. Key size 192 bits.
• AES(128) - Advanced Encryption Standard. Key size 256 bits.

Proxy Connection Strings

Proxy connections are used to route database traffic through a proxy server, providing benefits such as load balancing, logging, and additional security controls.

MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "Host=127.0.0.1;Port=3307;UserId=root;Password=root;Database=test;";
connection.ProxyOptions.Host = "10.0.0.1";
connection.ProxyOptions.Port = 808;
connection.ProxyOptions.User = "ProxyUser";
connection.ProxyOptions.Password = "ProxyPassword";
connection.Open();

Proxy Connection String Properties

HTTP Tunnel Connection Strings

Sometimes, client machines are shielded by a firewall that does not allow you to connect to the server directly at a specified port. If the firewall allows HTTP connections, you can use dotConnect for MySQL together with HTTP tunneling software to connect to MySQL server.

dotConnect for MySQL supports two kinds of HTTP tunneling: new, based on the PHP script, and old, working with GNU HTTP tunnel. Old HTTP tunneling will not be supported in future versions. dotConnect chooses the kind of HTTP tunneling depending on your options. If the URL property is set, the PHP script will be used for tunneling. If the HTTP Host and HTTP Port properties are set, the old HTTP tunneling method is used.

MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "UserId=root;Password=root;Database=test;";
connection.Protocol = MySqlProtocol.Http;
connection.HttpOptions.Host = "192.168.0.1";
connection.HttpOptions.Port = 8080;
connection.Open();

HTTP Tunnel Properties

HTTP Tunnel and SSL Connection Strings

You may use dotConnect for MySQL to establish secure SSL connections through the HTTP tunnel to the database. Such connections are only supported in the Direct mode (enabled by default). To use such a connection, set the Protocol property of the connection to MySqlProtocol.HttpSsl, then set connection HTTP and SSL options.

MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "UserId=root;Password=root;";
connection.ProxyOptions.Host = "10.0.0.1";
connection.ProxyOptions.Port = 808;
connection.ProxyOptions.User = "ProxyUser";
connection.ProxyOptions.Password = "ProxyPassword";
connection.Protocol = MySqlProtocol.Http;
connection.HttpOptions.Host = "192.168.0.1";
connection.HttpOptions.Port = 8080;
connection.Open();

The connection string does not include the host information because MySQL server host and port are known to HTTP tunneling server.

Pooling Connection String Options

Advanced Connection String Options