How can I connect to a MySQL database through .NET?

All our Windows shared servers have Connector/Net, MySQL’s fully managed ADO.Net provider.  You can find the documentation on Connector/Net on MySQL’s site: http://dev.mysql.com/doc/connector-net/en/index.html

Here are a few common examples of connection strings you can use to connect to your MySQL database through .NET.

Standard

Where we have values such as ‘myServerAddress’ and ‘myUsername’, please enter in the appropriate value for you.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;

Specifying TCP Port

The port 3306 is the default MySQL port, but you can use a different TCP port if you require it.  If the UNIX socket is used, this value will be ignored.

Server=myServerAddress;Port=1234;Database=myDatabase;Uid=myUsername;Pwd=myPassword;

Multiple Servers

Through this, you can connect to a server in a replicated server configuration without needing to worry about which server to use.

Server=serverAddress1, serverAddress2, serverAddress3;Database=myDatabase;Uid=myUsername;Pwd=myPassword;

Using Encryption

If you have an SSL certificate installed onto your server, you can use this to activate SSL encryption for all data sent between the client and the server.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;Encrypt=true;

Disallow Batches

Preventing batch mode or batch files from running can protect your database from major changes.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AllowBatch=False;

Allow User Variables

You can allow users to enter in their own data by allowing user variables.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AllowUserVariables=True;

Allow Invalid Date/Time

This will return a MySQL DateTime object for invalid values and a System.DataTime object for valid values.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AllowZeroDate=True;

Alternative String to Allow Invalid Date/Time

This returns System.DataTime.MinValue valued System.DataTime object for invalid values and a System.DataTime object for valid values.

Server=myServerAddress;Database=myDatabase;Uid=myUserName;Pwd=myPassowrd;ConvertZeroDateTime;True;

Disable Transaction Participation

The use of auto-enlist transactions can cause trouble in Medium Trust environments.  This removes it as the default behaviour.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;AutoEnlist=False;

Skip Parameter Checks for Stored Routines

Parameters for stored routines and procedures are checked against the server and can slow down the system. This removes it as the default behaviour.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;CheckParameters=False;

Skip Parameter Type and Order Matching for Stored Procedures

If you have errors related to permissions and value casting, these errors can be fixed by this string.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UserProcedureBodies=False;

Count Changed Rows instead of Found Rows

This uses the changed rows instead of found rows, providing different data as needed.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UseAffectedRows=True;

Compress Network Communication Between Client and Server

This can cut down on the amount of time it takes to implement a script.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UseCompression=True;

Log Inefficient Database Operations

Log operations that are inefficient, for tighter development later on.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UserUsageAdvisor=True;

Enable Performance Counters

Turn on the performance counters for your project, and review regularly for optimised programming.

Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;UserPerformanceMonitor=True;