Post
Ask Question

Create ASP.NET MVC Web API Using Entity Framework With MySQL Database

If you want to work with MySql database using an Entity Framework project that uses MySql.Data.Entity (the latest version is 6.10.X) and MySql.Data (latest version is 8.0.X), those version numbers should match. You should use MySql.Data.EntityFramework package with MySql.Data version 8.0 and later versions, and the MySql.Data.Entity package with versions 6.10 and earlier versions.

Create a new project using Visual Studio. Go to Web => ASP.NET Web Application => MVC Project Template.

Authentication => No Authentication

Open NuGet Package Manager Console

Tools => NuGet Package Manager => Package Manager Console

  1. Enter Command => Install MySql.Data => Press Enter (You have to connect to internet)
  2. Enter Command => Install MySql.Data.EntityFramework (You have to connect to internet)

After installing packages, go to the web config file and add a connection string with the name DefaultConnection. Make sure you have installed MySql server on your local computer or set connection string for remote server.

  1. <addname="DefaultConnection"connectionString="server=localhost;userid=root;password=root;database=testDb;persistsecurityinfo=true"providerName="MySql.Data.MySqlClient" /> 

Replace the username password as per your environment.

Add a new class in Models folder.

  1. [DbConfigurationType(typeof(MySqlEFConfiguration))]  
  2. public class ApplicationDbContext: DbContext {  
  3.     public DbSet Products {  
  4.         get;  
  5.         set;  
  6.     }  
  7.     public ApplicationDbContext(): base("DefaultConnection") //Connection string name write here  
  8.     {}  
  9. } 

Add a new class file in Models folder.

  1. public class Product {  
  2.     public int Id {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     [Required]  
  7.     [StringLength(50)]  
  8.     public string Name {  
  9.         get;  
  10.         set;  
  11.     }  
  12. } 

Go to Package Manager Console.

Tools => NuGet Package Manager => Package Manager Console

Execute the following commands step by step,

  1. Enable-migrations
  2. add-migration InitialModel
  3. update-database

Done!  We have successfully connected the MySql database with Entity Framework.

If you want to check, open the MySQL workbench and check there is a database created with name testDb which has products table also.

Add a new API Controller and add the following code to it. Please add the following lines to the Global.asax.cs file.

  1. GlobalConfiguration.Configure(WebApiConfig.Register);  
  2. public class ProductsController: ApiController {  
  3.     private static ApplicationDbContext _context;  
  4.     public ProductsController() {  
  5.         _context = new ApplicationDbContext();  
  6.     }  
  7.     protected override void Dispose(bool disposing) {  
  8.         _context.Dispose();  
  9.     }  
  10.     // GET /api/products  
  11.     public IEnumerable < Product > GetProducts() {  
  12.         return _context.Products.ToList();  
  13.     }  
  14.     //GET /api/products/id  
  15.     public Product GetProducts(int id) {  
  16.         return _context.Products.SingleOrDefault(p => p.Id == id);  
  17.     }  
  18. }  

Build your application and test your API by going through the following URL.

http:localhost:port/api/products (replace port no with your application port)

You can also test your API using postman or any other test client.
Next Recommended Reading Generic Repository Pattern With Web-API And Entity Framework