Using Devart dotConnect for MySQL

i've used devart's dotconnect for oracle and found it to be excellent. its support for entity framework 6 code-first has been very good, and it massively simplified the setup. so, i would like to try it out for mysql as well. for this sample, i'm using the devart dotconnect for mysql professional trial.

what you need

• visual studio 2013+ (i used visual studio 2015 community)
• devart dotconnect for mysql ( download here )
• any mysql server variant (i'm using an instance of mariadb on amazon rds)

using dotconnect

to start with, create an asp.net 4.5.2+ project):

in the templates section, choose the mvc template. also, set the authentication type to "no authentication", and click ok:

now that the solution has been created, in the solution explorer: right-click on references and go to add reference:

in the references menu, add "..\devart\dotconnect\mysql\entity\ef6" items:

and the general "..\devart\dotconnect\mysql" items:

now, we will need to install entity framework from nuget. in the nuget package manager console in visual studio, type in the following to install the latest version of entity framework 6:

install-package entityframework

now that entity framework is installed, we need to add the devart mysql provider. in the web.config file, find the section. in the section, add the following line:

<provider invariantname="devart.data.mysql" type="devart.data.mysql.entity.mysqlentityproviderservices, devart.data.mysql.entity.ef6" />

which should look like:

now we can create the dbcontext for the connection. right-click on the models folder in the solution explorer, and click on add > add new item... choose the "ado.net entity data model" template, and enter a name for it:

click add, and you will be taken to the entity data model wizard. if you're going code-first, you would likely just choose "empty code first model", however i like to use the "code first from database" option even if i'm not importing anything, since it creates the connection string for me:

click next, and you will move on to choosing the data connection. here, click on new connection:

choose "mysql server" for your data source (you will see "dotconnect for mysql" in the data provider window):

in the connection properties window, add in your connection information and click ok:

this will take you back to the "choose your data connection" screen. you will need to choose between storing sensitive data in the connection string or not. once you have made a selection, click next:

the next screen will ask if you want to import anything from the database. since we're just doing this to build the connection string, just click on finish here:

this will create our dbcontext in the models folder. next, we will need to enable migrations for the context. in the package manager console, enter the following command and hit enter:

enable-migrations

this will enable migrations for the context, and create a migrations folder in your solution. the migrations folder will have a file named configuration.cs, which is where you can specify any configuration items for your migrations connection:

to configure entity framework to generate sql correctly when migrating to the database, we will need to make some changes to configuration.cs. first, in the usings section, add:

using devart.data.mysql.entity.migrations;

next, in the constructor, add:

this.setsqlgenerator(mysqlconnectioninfo.invariantname, new mysqlentitymigrationsqlgenerator());

creating the entities

now that entity framework is set up to use the devart connector, it's time to create the entities and create a migration. first, add the following classes to your models folder in the solution explorer:

customer.cs

using system;
using system.componentmodel.dataannotations;
using system.componentmodel.dataannotations.schema;

namespace mysqlwithdevart.models
{
    [table("customers")]
    public partial class customer
    {
        public int customerid { get; set; }

        [required]
        [stringlength(200)]
        public string customername { get; set; }

        public datetime createddate { get; set; }
    }
}

product.cs

using system;
using system.componentmodel.dataannotations;
using system.componentmodel.dataannotations.schema;

namespace mysqlwithdevart.models
{
    [table("products")]
    public class product
    {
        public int productid { get; set; }

        [required]
        [stringlength(5)]
        public string productcode { get; set; }

        [required]
        [stringlength(150)]
        public string description { get; set; }

        public datetime createddate { get; set; }

        public datetime lastmodifieddate { get; set; }
    }
}

order.cs

using system;
using system.componentmodel.dataannotations;
using system.componentmodel.dataannotations.schema;

namespace mysqlwithdevart.models
{
    [table("orders")]
    public class order
    {
        public int orderid { get; set; }

        [required]
        public int customerid { get; set; }

        public virtual customer customer { get; set; }

        public datetime createddate { get; set; }
    }
}

orderproduct.cs

using system;
using system.componentmodel.dataannotations.schema;

namespace mysqlwithdevart.models
{
    [table("orderproducts")]
    public class orderproduct
    {
        public int orderproductid { get; set; }

        public int orderid { get; set; }
        public virtual order order { get; set; }

        public int productid { get; set; }
        public virtual product product { get; set; }

        public datetime createddate { get; set; }
    }
}

now that the entities for the data model are created, we'll need to add them to the dbcontext as virtual dbsets. add the following four lines to your context class:

public virtual dbset<customer> customers { get; set; }
public virtual dbset<order> orders { get; set; }
public virtual dbset<product> products { get; set; }
public virtual dbset<orderproduct> orderproducts { get; set; }

so that it looks like:

creating and applying a migration

now we can create and apply a code-first migration. open the package manager console, and enter:

add-migration 'initialmigration'

you will see a response like:

once you see the message, a .cs file will be created in the migrations folder that describes the migration:

the next step is to apply the migration to the database specified in the connection string. in the package manager console, enter the following command:

update-database

this will run all migrations against the database that the database hasn't received yet (as per what it finds in the __migrations table):

now you can connect to the database with any data tool compatible with mysql, and you will see your classes migrated as tables in the database:

conclusion

so far, i strongly recommend devart's various connectors for the different database engines. i haven't tried using any of devart's other functionality, but the basic entity framework connector support in dotconnect is excellent and removes many of the setup headaches with other options.

you can find the documentation for devart dotconnect for mysql here . you can find the sample code for this on github here .