Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Using Devart dotConnect for MySQL

DZone's Guide to

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. Let's see how it can help us with MySQL...

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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): 
Create MySqlWithDevart solution

In the templates section, choose the MVC template. Also, set the Authentication type to "No Authentication", and click OK: 

Choose MVC

Now that the solution has been created, in the Solution Explorer: right-click on References and go to Add Reference: 

Add reference

In the References menu, add "..\Devart\dotConnect\MySQL\Entity\EF6" items: 

Add EF references

And the general "..\Devart\DotConnect\MySQL" items: 

Add Devart references

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: 
EF provider

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: 
New data model

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: 

Code First from database

Click Next, and you will move on to choosing the data connection. Here, click on New Connection: 

New Connection

Choose "MySQL Server" for your data source (you will see "dotConnect for MySQL" in the Data provider window): 

Data source

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

Connection info

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: 

Data connection setup

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: 

Database objects

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: 
Configuration.cs

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: 
DbSets

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: 
Add Migration

Once you see the message, a .cs file will be created in the Migrations folder that describes the migration: 
Migration folder

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): 
Update database

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: 
Tables

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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
asp.net ,entity framework ,c# ,mysql ,.net

Published at DZone with permission of Chris Dusyk, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}