Empty Project for MySQL and Migrations

This project was generated in Visual Studio 2017.

This project was designed to allow the rapid creation of an API in Microsoft ASP.Net, using Entity Framework and Migrations with MySQL database. We also took advantage of Dependency Injection.

To the point:
Warming up the System
Cloning This Repository
About The Project and Some Remarks
Configuring the Database
Setting up The Dependency Injection
Creating and Mapping a New Table and Class
Adding a Path to the API Call

Warming up the System

To work, besides having VS installed, you will need a MySQL ADO connector, which can be downloaded from this link and a MySQL server running on your system, for test purposes, you can use the incredible USBWebServer, it is a combination of popular web server software: Apache, MySQL, PHP, and phpMyAdmin. With USBWebserver it is possible to develop and show your PHP websites everywhere and anytime. The main advantage of USBWebserver is that you can use it from USB.

Cloning This Repository

The easiest way is to create a local folder and clone this repo, type the following command in the windows prompt (CMD):
git clone https://github.com/rafaelpassarela/empty_project_mysql_migrations.git
This will generate a new local copy, but you can't commit there. If you want to control your own repo, create one at GitHub or another version control and clone it to a new folder. Go to the folder where you made the clone of empty_project_mysql_migrations and copy/cut all files and folders, with the exception of the ".git" folder.
Paste these files and folders into your new folder and you're done. Have fun!

About The Project and Some Remarks

When you load the Solution into VS, some NuGet packages will be downloaded, with additional attention to these ones:

• MySql.Data (v6.10.7.0): Beware, there are some reports that with versions higher than 8.0 the migration does not work, so I used this one.
• MySql.Data.Entity (v6.10.7.0)
• MySql.Data.Entities (v6.8.3.0)
• Unity.WebAPI (v5.3.0.0)

By default, the Migrations package does not support MySQL, so some changes need to be made before you start using, basically because the key size used in the __migrationhistory table, which is used by Migration to track database changes, is larger than the one supported by MySQL.
These three classes will solve the problem, I will not go into much detail, but just call the MySqlInitializer inside the DbContext constructor and everything is solved.

Configuring the Database

Before starting to load and write data to the database, some things need to be adjusted. We need to configure the server path, database name, user, and password. This information is configured in the Web.config file. There is the possibility of setting up a database for Debug and another for Release.
Basically, we need to provide only a valid connection string, you can consult various types at connectionstrings.com. In our case, we will provide a default MySQL connection string and define the provider name for this connection.

<connectionStrings>
	<add name="MyConnectionName" 
		 providerName="MySql.Data.MySqlClient" 
	     connectionString="Server=127.0.0.1;Port=3307;Database=sampleApi;Uid=root;Pwd=usbw;SslMode=none;"/>
</connectionStrings>

In the code above, we added a new connection string, the main attributes are:

• name = Define the name for our connection, we will reference it in the migration configuration
  
• providerName = Informs which class will be responsible for managing the connection
  
• connectionString = the connection string itself, this is the ** single ** that must be changed, according to its parameters.
  

Now imagine having to change the Web.config at all times, one hour pointing to the test DB and another time pointing to the real DB. To solve this, we have two extra configuration files: Web.Debug.config and Web.Release.config. They are pretty much the same web.config file, but only with the specific changes for each mode, the only detail is the use of the transform attribute xdt:Transform="SetAttributes" xdt:Locator="Match(name)", which indicates what and when to change.
This is my Release config:

<connectionStrings>
	<add name="MyConnectionName"
		 providerName="MySql.Data.MySqlClient"
		 connectionString="Server=MySQLServer;Database=sampleApi;Uid=MySQLUser;Pwd=MySQLPwd;"
		 xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
</connectionStrings>

And this is my Debug config:

<connectionStrings>
	<add name="MyConnectionName"
		 providerName="MySql.Data.MySqlClient"
		 connectionString="Server=127.0.0.1;Port=3307;Database=sampleApi;Uid=root;Pwd=usbw;SslMode=none;"
		 xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
</connectionStrings>

Did you notice the subtle difference? Only the connectionString attribute has changed. The name and providerName attributes must be the same. One last detail should be changed before actually starting to get your hands dirty. In the class responsible for the database context, we have to enter the same name assigned in the connection, inside the Web.config file. Locate the Core\MySQLDbContext.cs file and change the base name parameter, like the image below.

Setting up The Dependency Injection

To control Dependency Injection we will use the Unity.WebAPI package. Unity.WebAPI allows the simple Integration of the Unity IoC container with ASP .NET Web API.
But, What is dependency injection? Dependency injection is a technique for achieving loose coupling between objects and their dependencies or collaborators. Rather than directly instantiating collaborators, or using static references, the objects that the class needs in order to perform its actions are provided to the class in some way.
Most often, classes will declare their dependencies via their constructor, allowing them to follow the Explicit Dependencies Principle. This approach is known as "constructor injection" and it is the one we will use here.
This registry is very simple, you only need a contract signature between an interface and a class. Let's assume that we have a "person" class and this class will use another class responsible for this person's "vehicles". We can not inject or refer directly to the Vehicle class in the Person class, we need to inject the signature, the interface that represents the Vehicle class, in this case, called IVehicle. First, we need an interface, like this one:

public interface IVehicle {
	bool DoSomething();
}

Then we create the class that implements this interface:

public class Vehicle : IVehicle {
	public bool DoSomething(){
		// it doesn't do anything, I know, this is just an example
		return true; 
	}
} 

Now, to make the Person class use the Vehicle class, let's inject the interface into the class constructor, with that nice code below:

public class Person{
	private readonly IVehicle _vehicle;

	public Person(IVehicle vehicle) {
		_vehicle = vehicle;
	}

	public bool NeedSomethingFromVehicle() {
		return _vehicle.DoSomething();
	}
}

You may be wondering, how does the program know that when we want an interface of type IVehicle it should instantiate the class Vehicle, right? Well, we need to register this association in the UnityConfig class, in this case, it looks like this:

public static class UnityConfig{
	public static void RegisterComponents(){
		...
		container.RegisterType<IVehicle, Vehicle>();
		...
	}
}

This is the code used in this example:

Creating and Mapping a New Table and Class

In this project there is a fully implemented class, its name is Values, as well as the table name in the database, you can use this class as a reference and create as many as you want.
But by coincidence, I need to create a new table to record some pets, so I'll enjoy explaining a little better how to create a new class.
First, go to the Models folder and create a new class, call it Pets and create two attributes, Id and Name.

And this is our new model class:

public class Pets {
	[Key]
	[Required]
	public int Id { get; set; }

	[Required]
	[MaxLength(20)]
	public string Name { get; set; }
}

For this new class to be recognized as a table in the database, we need to register it in the application's DbContext by adding a new reference in the Core\MySQLDbContext class.

Now, do you remember when I said "we need a contract between a class and an interface so we can use dependency injection"? To help with this process I have created a base Interface to perform the CRUD process, called ICrudPersistence and using generics, it can map the specific class.

public interface ICrudPersistence<T> {
	T Save(T item);
	T Load(params object[] keys);
	bool Delete(params object[] keys);
	IEnumerable<T> Query(params object[] keys);
}

Before continuing, it is worth remembering that we are about to provide a path in the API URL to load and save changes related to this new class and we'll need an interface that knows what to do with this object when a database operation is requested.
Fortunately, this part is relatively simple, we just need to extend the generic interface ICrudPersistence to one more specific interface to handle with the Pets class. This interface will be responsible for the persistence of information with the database, so I usually put it in the PersistenceIntf folder, and the code stays that way, only extending the methods of the base interface, this will be calledIPetsPersist.

public interface IPetsPersist : ICrudPersistence<Pets> { }

Now let's create the class that will implement this interface, which will be our first to use dependency injection. Following this pattern of persistence, I usually put this kind of class inside the Persistence folder. I will try to simplify as much as possible, for this I will use the Dapper ORM.

public class PetsPersist : IPetsPersist {
	private readonly IAppContext _context;
	private DbConnection Db => _context.Connection();

	public PetsPersist(IAppContext context){
		_context = context;
	}

	public bool Delete(params object[] keys){
		var id = Convert.ToInt32(keys[0]);
		var rowCount = Db.Execute($"delete from pets where id = {id}");

		return (rowCount > 0);
	}

	public Pets Load(params object[] keys){
		var id = Convert.ToInt32(keys[0]);
		var obj = Db.Query<Pets>($"select id, name from pets where id = {id}").FirstOrDefault();

		return obj;
	}

	public IEnumerable<Pets> Query(params object[] keys){
		var list = Db.Query<Pets>("select id, name from pets");
		return list;
	}

	public Pets Save(Pets item){
		_context.DataContext().Pets.AddOrUpdate(item);
		_context.DataContext().SaveChanges();

		return item;
	}
}

After this beautiful creation, we need to register this association in the UnityConfig class.

container.RegisterType<IPetsPersist, PetsPersist>();

Last but not least, we have to register this new class so that Migration can create it in the database. Since this project already has the Migration configuration, we just need to register the new table, and to do this, open the Package Manager Console, and run the command Add-Migration passing the name of the new migration as the parameter.

PM> Add-Migration PatsTable

And you can specify the database name to apply the migration

Update-Database -ConnectionProviderName MySql.Data.MySqlClient -ConnectionString "Server=127.0.0.1;Port=3307;Database=sampleApi;Uid=root;Pwd=usbw;SslMode=none;" -verbose -script
Add-migration PatsTable -ConnectionProviderName MySql.Data.MySqlClient -ConnectionString "Server=127.0.0.1;Port=3307;Database=sampleApi;Uid=root;Pwd=usbw;SslMode=none;"

This command will generate a new file with the DbMigration class containing the changes.

public partial class PatsTable : DbMigration {
	public override void Up() {
		CreateTable(
			"dbo.Pets",
			c => new
				{
					Id = c.Int(nullable: false, identity: true),
					Name = c.String(nullable: false, maxLength: 20, storeType: "nvarchar"),
				})
			.PrimaryKey(t => t.Id);           
	}
        
	public override void Down() {
		DropTable("dbo.Pets");
	}
}

You may receive the Access denied for user 'root@localhost' (using password:NO) error while adding the new migration, if this occurs, create a new user in MySQL, for example, user = migration; pwd = 1234. Then be sure to change the Web.config file to use the new user.

CREATE USER 'migration'@'127.0.0.1' IDENTIFIED BY '1234';
GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE, LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, SUPER  ON *.* TO 'migration'@'127.0.0.1' WITH GRANT OPTION;

Adding a Path to the API Call

Now that everything is ready between the class mapping and the database, we need to add a way to access this information, and to do this, we'll add a new controller, which will respond when the requested path to the API matches this new. To add a new controller, right-click the Controllers folder, Add -> Controller.
Then select Web API Controller 2 with read/write actions and call it PetsController.

Do not worry about Dependency Injection for the Controller, when passing a URL that looks for Pets, like this http://localhost:57431/api/Pets, the framework will automatically look for a controller that matches the name of the class and the word "controller" and will automatically load the class PetsController.
Now, add the constructor for this new Controller and pass the Interface responsible for the persistence of this object in the database as a parameter, as following code:

public class PetsController : ApiController{
	private readonly IPetsPersist _petsPersist;

	public PetsController(IPetsPersist petsPersist)	{
		_petsPersist = petsPersist;
	}

	// GET: api/Pets
	public IHttpActionResult Get(){
		return Ok(_petsPersist.Query());
	}

	// GET: api/Pets/5
	public IHttpActionResult Get(int id){
		var obj = _petsPersist.Load(id);
		if (obj == null)
			return NotFound();

		return Ok(obj);
	}

	// POST: api/Pets
	public IHttpActionResult Post([FromBody]Pets value){
		_petsPersist.Save(value);
		if (value.Id > 0)
			return Ok(value);
		return NotFound();
	}

	// PUT: api/Pets/5
	public void Put(int id, [FromBody]string value){ }

	// DELETE: api/Pets/5
	public IHttpActionResult Delete(int id){
		if (_petsPersist.Delete(id))
			return Ok();
		return NotFound();
	}
}

Now, try to get a list of all pets just calling the API URL http://localhost:57431/api/Pets or post some new pet with the same URL, but providing a JSON in the request body.

    {
        "Name": "Thor"
    }

Please ignore small differences between the code and the images in this post. This is an open source hosted on GitHub, any suggestions are welcome. Last Tag: MySQLData-v6.10.7.0