if you want to work with MySql database using EntityFramework project that uses
MySql.Data.Entity (latest version is 6.10.X) and MySql.Data (latest version is 8.0.X). Those
version numbers should match. You should use the MySql.Data.EntityFramework package
with MySql.Data version 8.0 and after, and the MySql.Data.Entity package with versions
6.10 and before.
Create a New Project Using Visual Studio =>
Web => Asp.Net Web Application => Select 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 connected to
internet)
2) Enter Command => Install MySql.Data.EntityFramework ( You have to connected to
internet)
After installing packages go to web config file and add connection string with name
DefaultConnection, be sure u have installed mysql server on your local computer or set
connection string for remote server.
<add name="DefaultConnection" connectionString="server=localhost;userid=root;password=root;data
base=testDb;persistsecurityinfo=true" providerName="MySql.Data.MySqlClient" />
replace user name password as per your environment.
Add New Class In Models Folder
[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class ApplicationDbContext : DbContext
{
public DbSet Products { get; set; }
public ApplicationDbContext()
:base("DefaultConnection") //Connection string name write here
{
}
}
Add New Class File In Models Folder
public class Product
{
public int Id { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
}
Go to Package Manager Console
Tools => NuGet Package Manager => Package Manager Console
Execute Following Commands step by step
1) Enable-migrations
2) add-migration InitialModel
3) update-database
Done We Successfully connected MySql Database with EntityFramework.
If you want to check open the mysql workbench and check there is a database created with
name testDb and it has products table also.
Add New API Controller and Add Following code to in.
Please add the following line to Global.asax.cs file
GlobalConfiguration.Configure(WebApiConfig.Register);
public class ProductsController : ApiController
{
private static ApplicationDbContext _context;
public ProductsController()
{
_context = new ApplicationDbContext();
}
protected override void Dispose(bool disposing)
{
_context.Dispose();
}
// GET /api/products
public IEnumerable<Product> GetProducts()
{
return _context.Products.ToList();
}
//GET /api/products/id
public Product GetProducts(int id)
{
return _context.Products.SingleOrDefault(p => p.Id == id);
}
Build your application and test your api going 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 restclient
Thank you
