Tuesday, April 7, 2015

Entity Framework Code First and MySql on Azure

We have used Entity framework with SQL Servers mostly. In this blog post we are going to see how we can use Entity framework with MySql on Azure.

How to create MySql database on Azure:

On azure MySql is not maintained by Microsoft itself. It is provided their partner cleardb. So You need to search at Azure Market Place and then add it like below.

market-place-azure-my-sql-database


Once you click on and provide name of database it will start creating like below.

creating-my-sql-database

After sometime your database will ready to use.

How to use Entity Framework with MySql:

We are going to create a console application for the same like below.

console-application-for-entityframework-mysql

Once you are done with creating console application. It is time to add Nuget Package for Entity framework like below.

install-package-entity-framework

Once you are done with that It’s time to add MySql specific Nuget package to use MySql provider for entity framework. Following is a Nuget package for the same which will add all the required providers and files for Entity Framework to use it with MySql.

nuget-pacakge-for-mysql-entity

and Here’s how I have installed it.

mysql-entity-framework-nuget-package
Now it’s time to write code. Following is a Model class for Customer.
namespace EFWithMySQL
{
    public class Customer
    {
        public int CustomerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string City { get; set; }
    }
}
And here is the my data context class.
using System.Data.Entity;

namespace EFWithMySQL
{
    [DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))] 
    public class CustomerDataConext : DbContext
    {
        public CustomerDataConext(): base("DefaultConnectionString")
        {
            
        }
        public DbSet<Customer> Customers { get; set; }
    }
}
Here we need to add DbConfiguration Type attribute to tell Entity Framework that it is MySql EFConfiguration. So it will create a table based on MySql database.

Note: When you write connection string in app.config file then do not forgot to add providername =MySql.Data.MySqlClient. So connection string will look like following.
<connectionStrings>
    <add name="DefaultConnectionString" connectionString="your connectionstring" 
             providerName="MySql.Data.MySqlClient"/> 
</connectionStrings>
Following is a code for main console application.
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFWithMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            Customer customer = new Customer {FirstName = "Jalpesh", 
                                              LastName = "Vadgama",
                                              City = "Ahmedabad"};


            using (CustomerDataConext customerDataConext = new CustomerDataConext())
            {
                customerDataConext.Customers.Add(customer);
                customerDataConext.SaveChanges();
                Console.WriteLine("Customer Added Sucesscully");

                //Code to fetch data from MySQL
                foreach (var anotherCustomer in customerDataConext.Customers)
                {
                    Console.WriteLine("Customer Details");
                    Console.WriteLine(anotherCustomer.FirstName);
                    Console.WriteLine(anotherCustomer.LastName);
                    Console.WriteLine(anotherCustomer.City);
                }
            }

        }
    }
}
Here I have created a customer  object and saved it to database and then I have written code to fetch customer from database and display it on console application. 

Now It’s time to run application and following is output as expected.

output-as-expected

That’s it. Hope you like it. Stay tuned for more!!
You can find complete source code for this application on github at - https://github.com/dotnetjalps/EFWithMySql
Share:

1 comment:

Your feedback is very important to me. Please provide your feedback via putting comments.

About Me
My Photo

Full Stack Developer,Blogger,Microsoft Mvp, Mentor, Life long learner, 10+ Years of Experience for JavaScript, web and Microsoft technologies. ASP.NET MVC,Web Forms,C#,AngularJs,Golang
Mvp profile
Subscribe to my blog

Enter your email address:

Follow us on facebook
Blog Archive
Total Pageviews