dotConnect Universal Documentation
In This Topic
    Using dotConnect Universal with dotConnect for MySQL
    In This Topic

    If you need to choose which data provider for MySQL to use with dotConnect Universal, we recommend using dotConnect for MySQL because of its obvious advantages. This article provides a brief overview of dotConnect for MySQL, describes some useful features and helps you get started quickly.

    Overview

    dotConnect for MySQL (formerly known as MySQLDirect .NET) is a full-featured 100% managed ADO.NET data provider for the .NET Framework and the .NET Compact Framework. dotConnect for MySQL is acknowledged as the leading ADO.NET data provider for MySQL, and it has many advantages over any rival product. The main features of dotConnect for MySQL are:

    The full list of dotConnect for MySQL features can be found on the product page.

    The Professional Edition of dotConnect Universal includes dotConnect for MySQL as a bundled provider. The Trial Edition includes trial dotConnect for MySQL Data Provider as well. For Standard Edition of dotConnect Universal, the dotConnect for MySQL Data Provider should be purchased and installed separately.

    Compatibility

    dotConnect Universal is compatible with dotConnect for MySQL version 5.00 and above.

    Deployment

    To deploy applications written with dotConnect Universal, copy the run-time assemblies Devart.Data.Universal.dll and Devart.Data.Universal.MySql.dll to target machine. If you are using the standalone (not bundled) dotConnect for MySQL Data Provider, you will also have to deploy its Devart.Data.MySql.dll assembly.

    These assemblies can be registered at the Global Assembly Cache (GAC) for the appropriate .NET Framework or placed in the folder of your application.

    For web projects, you may also need to deploy the Devart.Data.Universal.Web.dll, Devart.Data.MySql.Web.dll and App_Licenses.dll assemblies. The *.Web.dll assemblies are required for using the ASP.NET Provider Model and the App_Licenses.dll assembly is a part of licensing mechanism.

    Web projects are usually run as partially trusted code. The following permissions are required by dotConnect Universal and dotConnect for MySQL:

    Connection String

    Suppose you want to connect in Direct mode to a MySQL server running on the host server, at the port 3306, with the user name root and the password mypassword. Suppose also that you would like to choose myDB as the default database. In this case your connection string would look like this:

    Provider=MySQL;direct=true;host=server;port=3306;user=root;password=mypassword;database=myDB

    The direct parameter is true by default; the port parameter defaults to 3306 as well. The database parameter is not required to connect to the server. So basically you need only host name, user name and password:

    Provider=MySQL;host=server;user=root;password=mypassword

    The following table enumerates most important connection string parameters.

    Name Description
    Compress If true, enables compression for transferred data. The default value is false.
    Connection Lifetime When a connection is returned to the pool, its creation time is compared with the current time. The connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0 (connection always returns to pool).
    Connect Timeout -or- Connection Timeout The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.
    Data Source -or- Host -or- Server The name or IP address of a host of MySQL database to which to connect.
    Database The name of the database.
    Direct If true, by default, allows not to use the MySQL client library (libmysql.dll) to access MySQL server.
    Embedded If true, allows to use Embedded MySQL server. The default value is false
    Max Pool Size The maximum number of connections allowed in the pool. Setting the Max Pool Size value of the ConnectionString can affect performance. The default value is 100.
    Min Pool Size The minimum number of connections allowed in the pool. The default value is 0.
    Password The password for the account.
    Pooling If true, by default, the connection object is drawn from the appropriate pool or is created and added to the appropriate pool.
    Port The port of MySQL database to which to connect. The default value is 3306.
    Protocol The type of the network protocol which will be used to access the MySQL server. The default value is Tcp.
    Server Parameters Specifies the command-line arguments for launching MySQL embedded server. Usually used to provide path to files used by server and path to folder where data files reside. For instance: Server Parameters=\"--basedir=d:/servers/embedded/;--datadir=d:/servers/embedded/data/;\"
    SSH Authentication Type Client's SSH authentication methods.
    SSH Cipher List The list of ciphers that client agrees to use, separated by colons.
    SSH Host The name or IP address of SSH server.
    SSH Host Key The location of the public key on the client side to verify the server host key during establishing connection. OpenSSH or IETF SECSH key files are supported. You may use such utilities as 'ssh-keygen.exe' or 'puttygen.exe' for generation of the corresponding public/private key pair.
    SSH Passphrase The client key passphrase.
    SSH Password The user password on SSH server.
    SSH Port The number of port on SSH server to connect.
    SSH Private Key The location of the client private key.
    SSH Strict Host Key Check Indicates whether the host key is verified during establishing connection.
    SSH User The user id on SSH server.
    SSH Private Key Location of the private key, which is used by the client to sign data and by server to authenticate the user.
    SSL CA Cert The location of authority certificate.
    SSL Cert The location of client certificate.
    SSL Cipher List The list of ciphers that client agrees to use.
    SSL Key The location of user's private key.
    SSL TLS Protocol The preferred TLS protocol version, reported to the server when establishing a secure connection.
    SSLMode SSL connection priority. May be Disable, Allow, Prefer, and Require. The default value is Disable, which means that only an unencrypted SSL connection will be attempted.
    Unicode If true, sets the client charset to UTF8 and converts the client data according to this charset. The default value is false.
    User ID -or- User The MySQL login account.

    Quick Start Steps

    To get up and running quickly, you have to perform the following steps:

    1. Embed licensing information into your application. If you use bundled dotConnect for MySQL version, you need only dotConnect Universal licensing. If you use a standalone dotConnect for MySQL installation, embed the licensing information about both dotConnect Universal and dotConnect for MySQL data providers. For more details on licensing refer to the Licensing article. For bundled providers it may be enough to drop a UniConnection component onto a form designer to embed the licensing information.
    2. Create a UniConnection object.
    3. Set the ConnectionString property of the UniConnection object to a string containing the part Provider=MySQL.
    4. Create a UniCommand object and link it to the UniConnection object.
    5. Open the connection.

    That's all you need to start executing queries on the server. Below is a code fragment that illustrates steps 2-5. It also shows how to perform INSERT and SELECT operations.

    UniConnection connection = new UniConnection("Provider=MySQL;host=server;user=root;password=root;database=myDB");
    try {
      UniCommand cmd = connection.CreateCommand();
      cmd.CommandText = "INSERT INTO dept (DeptNo, Dname, Loc) VALUES (:DeptNo, :DName, :Loc)";
      UniParameter parameter = cmd.Parameters.Add("DeptNo", UniDbType.Int);
      parameter.Value = 150;
      parameter = cmd.Parameters.Add("DName", UniDbType.VarChar);
      parameter.Value = "DEPTNAME";
      cmd.Parameters.Add(parameter);
      parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar);
      parameter.Value = "LOCATION";
      cmd.Parameters.Add(parameter);
      connection.Open();
      cmd.ExecuteNonQuery();
    }
    finally {
      connection.Close();
    }
    
    ...
    
    DataSet dataSet = new DataSet();
    UniDataAdapter da = new UniDataAdapter("SELECT * FROM dept", "Provider=MySQL;host=server;user=root;password=root;database=myDB");
    da.Fill(dataSet, "Dept");
    
    
    Dim connection As Devart.Data.Universal.UniConnection = New _
      Devart.Data.Universal.UniConnection("Provider=MySQL;host=server;user=root;password=root;database=myDB")
    Try
      Dim cmd As UniCommand = connection.CreateCommand()
      cmd.CommandText = "INSERT INTO dept (DeptNo, DName, Loc) VALUES (:DeptNo, :DName, :Loc)"
      Dim parameter As Devart.Data.Universal.UniParameter = cmd.Parameters.Add("DEPTNO", UniDbType.Int)
      parameter.Value = 150
      parameter = cmd.Parameters.Add("DName", UniDbType.VarChar)
      parameter.Value = "DEPTNAME"
      parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar)
      parameter.Value = "LOCATION"
      connection.Open()
      cmd.ExecuteNonQuery()
    Finally
      connection.Close()
    End Try
    
    ...
    
    Dim dataSet As DataSet = New DataSet()
    Dim da As UniDataAdapter = New UniDataAdapter("Provider=MySQL;host=server;user=root;password=root;database=myDB")
    da.Fill(dataSet, "Dept")
    
    

    Special Features

    dotConnect Universal supports handling MySQL BLOBs. For detailed information on how to work with BLOB objects, refer to the Working with BLOB Data article.

    dotConnect Universal supports the MySQL DECIMAL type. Use the UniDecimal structure reference to find out how to manage big numbers on client side.

    It is also possible to take advantage of special network capabilities of dotConnect for MySQL by setting up the connection string parameters. This way you can enable:

    UniSQL Notes

    dotConnect for MySQL supports all features of UniSQL. Use MySQL and MyDirect predefined macros to build conditional UniSQL statements. For more information refer to the UniSQL topic.

    UniDbType Map

    The following table shows how the UniDbType enumeration maps to the MySQL database type.

    UniDbType MySQL Type
    BigInt BIGINT
    Binary BLOB
    Bit BIT
    Blob BLOB
    Boolean BIT
    Byte SMALLINT
    Char CHAR
    Clob TEXT
    Currency DOUBLE
    Date DATE
    DateTime DATETIME
    Decimal DECIMAL
    Double DOUBLE
    Guid VARCHAR
    Int INT
    NChar CHAR
    NClob TEXT
    NVarChar VARCHAR
    Single FLOAT
    SmallInt SMALLINT
    TinyInt TINYINT
    Time TIME
    TimeStamp TIMESTAMP
    VarChar VARCHAR
    Xml VARCHAR

    The national character encoding (NChar, NClob types) in the fields is not supported by MySQL server. However, you can achieve same effect with the Unicode=true connection string parameter.

    Additional Information

    For more information on dotConnect for MySQL Data Provider please visit the following locations:

    See Also

    Using dotConnect Universal with SQL Client  | Using dotConnect Universal with dotConnect for Oracle  | Using dotConnect Universal with dotConnect for PostgreSQL  | Using dotConnect Universal with dotConnect for SQLite  | Working with BLOB Data