Normally your development environment has no connection tot the live environment … well, it shouldn’t have. You develop in a development environment.

When using migrations in a code first approach, you will probably not be able to update the database on the production server (or staging server).

Luckily there is a simple way to get your database changes in a staging / production environment, simply by generating SQL scripts.

To generate a full script, in case you need to generate the database starting from zero, in Visual Studio’s Package Manager Console, enter:

update-database -script -SourceMigration:0

This will generate a SQL-script for all migrations.

Once the database is in production, after you have made changes, you can generate a change scripts of a single migration using:

update-database -script -SourceMigration:[name of the migration]

When entering the name of the migration, you do not need to enter the datestamp-portion of the name.

To generate change scripts of multiple migrations (in sequential order), enter:

update-database -script -SourceMigration:[name of the migration] -TargetMigration:[name of the migration]

Once you have the scripts you can test them and after that run them on your production database.

This post describes the very basic actions needed to setup a code first approach with Entity Framework (EF) and MySQL Database. For the sake of simplicity a console app is used. The purpose of this post is not to explain Entity Framework or Code First, it is just a getting started guide.

Open Visual Studio and create a new console application:

In Visual Studio, go to Tools > NuGet Package Manager > Manage NuGet Packages for Solution…

Select “browse” and enter “Entity Framework MySQL” in the search box.

Select “MySQL.Data.Entityframework”

At the right side, check “Project” and click install.

Next, open the App.config file. Add a section directly below the <startup> section:

<connectionStrings>    
<add name="DefaultConnection" providerName="MySql.Data.MySqlClient" connectionString="server=[your mySql server address];User Id=[db user]; Persist Security Info=True;database=[database name];password=[your password]" />
</connectionStrings>

In the entityFramework section delete all providers that do not have invariantname=”MySql.Data.MySqlClient”. There should be only one provider.

Your App.config file should resemble this (with your own server, db,name and credentials):

Next, you will need to write a table class and the DBContext class. To keep things simple we will place this class in program.cs.

Open program.cs, add references to system.Data.Entity and MySQL.Data.EntityFramework. On top of the code:

using System.Data.Entity;
using MySql.Data.EntityFramework;

Now add an Author Class , directly under the opening { of the namespace:

public class Author
{
public int Id { get; set; }
public string Name { get; set; }
}

This simple class will represent the Authors table in the database. As this is a code first approach, this table does not yet exist!

Following thing to do is to add a DBContext class. Write this directly below the Author class:

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class MyDbContext : DbContext
{
    public DbSet<Author> Authors { get; set; }
    // Constructor - set name of connection
    public MyDbContext() : base("name=DefaultConnection")
    {
    }
}

Three things to note here:

  • The attribute DbConfigurationType. Needed by Entity Framework to know it is a MySQL Database to talk to
  • The public DBSet. This tells Entity Framework there will be a table called Authors, of which the model is the class Author.
  • The (empty) constructor, needed to point to the right connection in App.config.

Next, open the Package Manager Console (Tools>NuGet Package Manager>Package Manager Console)

You will need to tell your project that you will use migrations. This is a one time action. In the Package Manager Console, type: enable-migrations.

If everything is ok, you will get the following message:

If you get errors, check the connection string.

Last step is to generate the Authors table in the database. To do this, first make a migration, after that update the database.

Again, in the package Manager Console, type add-migration [name for the migration]:

The result:

Finally, update the database:

Result:

Done! We have used the Code First approach in Entity Framework to generate a table in a MySQL database.