Logging Onto The Server

This tutorial describes how to connect to a MySQL server with dotConnect for MySQL - dotConnect for MySQL a high-performance ADO.NET provider from Devart with ORM support. This article consists of the following sections:

• Requirements
• General information
• Creating MySqlConnection
• Opening connection
• Closing connection
• Modifying connection
• Conclusion

Requirements

In order to connect to MySQL server you need the server itself running, dotConnect for MySQL installed and IDE running. You should know server name or IP address, account login and password. Usually this information is enough.

Note that if you do not use design-time (specifically, if you do not place the MySqlConnection component from the toolbox on a form designer), you have to embed licensing information manually. This is described in dotConnect for MySQL documentation.

General information

To establish a connection to 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 login with credentials of your account. The parameters are represented as connection string. You can compose the connection string manually or have dotConnect for MySQL construct it for you.

Creating MySqlConnection

Design-time Creation

Note that design-time connection creation, described here, is applicable only to Windows Forms applications for Full .NET Framework.

The following assumes that you have IDE running, and you are currently focused on a form designer.

1. Open Toolbox and find MySqlConnection component in dotConnect for MySQL category.
2. Double-click the component. Note that new object appears on the designer underneath the form. If this is first time you create MySqlConnection in this application, it is named mySqlConnection1.
3. Click on the mySqlConnection1 object and press F4 to focus on object's properties.
4. In the Host property provide DNS name or IP address of the computer where MySQL server resides. For example, if MySQL server is running on same machine where you launch the application, this property can be set to localhost or 127.0.0.1.
5. In the Port property specify the port that the server listens to. Usually this is 3306.
6. In the UserId property specify your login. For example, root.
7. In the Password property specify your password. For example, mypassword.
8. Note that as you assign values to these properties the ConnectionString property is automatically updated to reflect your settings. Now it contains something like User Id=root;Password=mypassword;Host=127.0.0.1;Port=3306;.

Run-time Creation

Same operations performed in runtime look as follows (note that you have to add references to Devart.Data.MySql.dll and Devart.Data.dll assemblies for Full .NET Framework or install the Devart.Data.MySql NuGet package for .NET Core):

[C#]

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

[Visual Basic]

Imports Devart.Data.MySql
...
Dim mySqlConnection1 As MySqlConnection = New MySqlConnection()
mySqlConnection1.Host = "127.0.0.1"
mySqlConnection1.Port = 3306
mySqlConnection1.UserId = "root"
mySqlConnection1.Password = "mypassword"

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

[C#]

mySqlConnection1.ConnectionString = "User Id=root;Password=mypassword;Host=127.0.0.1;";

[Visual Basic]

mySqlConnection1.ConnectionString = "User Id=root;Password=mypassword;Host=127.0.0.1;"

Using connection string builder

If you decide to setup a connection by assigning values to several properties, consider using the the MySqlConnectionStringBuilder class. It has all of the possible connection settings exposed as properties, thus allowing you to customize the connection at full extent. The following example demonstrates how to compose a more complex connection string:

[C#]

MySqlConnectionStringBuilder myCSB = new MySqlConnectionStringBuilder();
myCSB.Port = 3307;
myCSB.Host = "localhost";
myCSB.UserId = "root";
myCSB.Password = "mypassword";
myCSB.Direct = true;
myCSB.Compress = true;
myCSB.Database = "demobase";
myCSB.MaxPoolSize = 150;
myCSB.ConnectionTimeout = 30;
MySqlConnection myConnection = new MySqlConnection(myCSB.ConnectionString);

[Visual Basic]

Dim myCSB As MySqlConnectionStringBuilder = New MySqlConnectionStringBuilder
myCSB.Port = 3307
myCSB.Host = "localhost"
myCSB.UserId = "root"
myCSB.Password = "mypassword"
myCSB.Direct = True
myCSB.Compress = True
myCSB.Database = "demobase"
myCSB.MaxPoolSize = 150
myCSB.ConnectionTimeout = 30
Dim myConnection As MySqlConnection = New MySqlConnection(myCSB.ConnectionString)

Note that in this example we used MySqlConnection constructor that accepts connection string as argument.

Besides Host, UserId and Password there is another very important property. This is Direct property. It indicates whether the MySQL client library (libmysql.dll) will be used for connecting to server. By default Direct mode is enabled, and generally it is recommended to work in Direct mode. Switch to Client mode only when you really know what do you do it for. Some features require Direct=false mode, for example, data compression.

For the information on arguments are allowed in the connection string, refer to the description of the MySqlConnection.ConnectionString property in dotConnect for MySQL documentation.

Opening connection

Opening a connection is as simple as that:

[C#]

mySqlConnection1.Open();

[Visual Basic]

mySqlConnection1.Open()

Of course, mySqlConnection1 must have a valid connection string assigned earlier. When you call Open, dotConnect for MySQL tries to find the host and connect to server. If any problem occurs it raises an exception with brief explanation on what is wrong. If no problem is encountered dotConnect for MySQL tries to establish the connection during ConnectionTimeout interval. Finally, when connection is established, the Open method returns and State property is changed to Open.

In design-time you can connect to server in few steps:

1. Right-click on mySqlConnection1 object in the designer.
2. Select Connect from the shortcut menu.
3. In the dialog window provide necessary login information.
4. Click the Connect button to establish connection.

Or you can simply change the State property to Open in the Properties window to establish a connection using the current connection string.

Closing connection

To close a connection call its Close method, or set its State property to Closed.

The following example summarizes aforementioned information and shows how to create, setup, open, use and then close the connection.

[C#]

MySqlConnection myConn = new MySqlConnection();
myConn.ConnectionString = "User Id=root;Password=mypassword;Host=127.0.0.1;";
myConn.Open();
MessageBox.Show(myConn.ServerVersion);
myConn.Close();

[Visual Basic]

Dim myConn As MySqlConnection = New MySqlConnection()
myConn.ConnectionString = "User Id=root;Password=mypassword;Host=127.0.0.1;"
myConn.Open()
MessageBox.Show(myConn.ServerVersion)
myConn.Close()

The sample code connects to a server, shows its version and then closes the connection. This actually is rare usage, because in real applications connections are used by other objects like MySqlCommand, MySqlDataTable and others. For more information on this, please see the corresponding tutorials or dotConnect for MySQL documentation.

Modifying connection

You can modify connection by changing properties of MySqlConnection object. Keep in mind that while some of the properties can be altered freely, most of them close connection when new value is assigned. For example, if you change Server property, it gets closed immediately, and you have to reopen it manually.

Conclusion

This tutorial describes how you can connect to an MySQL server with dotConnect for MySQL - an ADO.NET provider from Devart with ORM support. dotConnect for MySQL has wide set of features you can take advantage of. You may take a look at other dotConnect for MySQL tutorials to learn more about them or download dotConnect for MySQL and try it yourself.

More dotConnect for MySQL tutorials

• Creating Database Objects
• Inserting Data Into Tables
• Entity Framework Tutorial
• Entity Framework Core Database-First Tutorial for Full .NET Framework
• Tutorial: How to Connect Visual Studio LightSwitch to MySQL
• LINQ to MySQL Tutorial
• SimpleMembership and SimpleRole Providers for MySQL in ASP.NET MVC 4 Application Tutorial