ABP framework uses MySQL database, and the schema and data of MySQL database based on SQL server

Time:2021-11-23

The default database of ABP is SQL server, but the bottom layer of ABP framework is EF framework, so it is easy to support other types of databases. This essay introduces the use of MySQL database in ABP framework and the schema and data processing of creating MySQL database based on SQL server.

If you need other database support, you need to introduce the corresponding support assembly. MySQL related packages are installed in nuget. The MySQL package provided by Oracle company has been tried many times, but there are still problems in using it. Therefore, the third-party MySQL package is used now. This package is also recommended in the official ABP document.

pomelo.entityframeworkcore.mysql

pomelo.entityframeworkcore.mysql.design

Currently, the official version of. Net core 5.0 is used, and the corresponding version of Microsoft. Entityframeworkcore. Relational is 5.0.0. Therefore, the drivers corresponding to these versions are shown in the figure below.

Find xxdbcontextconfigurator in the entityframeworkcore layer and modify the database configuration in dbcontext. The default sqlserver is mysql.

namespace MyProject.EntityFrameworkCore
{
    /// 
    ///Configure processing using a specific database
    /// 
    public static class MyProjectDbContextConfigurer
    {
        public static void Configure(DbContextOptionsBuilder builder, string connectionString)
        {
            //builder.UseSqlServer(connectionString);// Default sqlserver
            builder.UseMySql(connectionString, new MySqlServerVersion(new Version(5, 7)));//MySQL

            //builder.UseNpgsql(connectionString);//PostgreSQL
            //builder.UseOracle(connectionString);//Oracle
        }

        public static void Configure(DbContextOptionsBuilder builder, DbConnection connection)
        {
            //builder.UseSqlServer(connection);// Default sqlserver
            builder.UseMySql(connection, new MySqlServerVersion(new Version(5, 7)));//MySQL

            //builder.UseNpgsql(connection);//PostgreSQL
            //builder.UseOracle(connection);//Oracle
        }
    }
}

The second version parameter is required for usemysql. I use mysql5.7 for MySQL, so I use code to build the version parameter.

new MySqlServerVersion(new Version(5, 7)

We defined the connection information of different databases in appsettings.json in the host startup project, as shown below.

{
  "ConnectionStrings": {
    "Default": "Server=.\\SQL2014; Database=MyProjectDb; Trusted_Connection=True;",
    "Oracle": "User Id=orcl;Password=orcl;Data Source=orcl;Persist Security Info=True;",
    "MySql": "Server=localhost;Database=myprojectdb;Uid=root;Pwd=123456;",
    "PostgreSQL": "Server=localhost;Port=5432;Database=myprojectdb;User Id=postgres;Password=123456"
  },
  "RedisCache": {
    "ConnectionString": "localhost",
    "DatabaseId": "-1"
  },

...............

The different database connections in our system come from the key value objects in connectionstrings. In our ABP framework, we use reference constants to configure the corresponding database connections.

public class MyProjectDbContextFactory : IDesignTimeDbContextFactory
    {
        public MyProjectDbContext CreateDbContext(string[] args)
        {
            var builder = new DbContextOptionsBuilder();

            //Get profile information
            var configuration = AppConfigurations.Get(WebContentDirectoryFinder.CalculateContentRootFolder());

            //Switch the corresponding database through the connection string. The connectionstringname is default by default
            MyProjectDbContextConfigurer.Configure(builder, configuration.GetConnectionString(Constants.ConnectionStringName));

            return new MyProjectDbContext(builder.Options);
        }
    }

Then we can modify the corresponding constant definition and switch to the MySQL database we need, as shown below.

So far, the code modification has been completed. How can we migrate our existing running SQL Server database to MySQL environment?

 

2. Create the schema and data of MySQL database based on SQL Server

The above section describes the code adjustment and modification when switching to other databases MySQL database, but we also need to migrate the existing database running on SQL server to MySQL. How to deal with it.

Some people want to reconstruct the project database through the Migrator project with ABP, but I don’t recommend using this, because when we actually develop, we change a lot of databases and map them back to the code. It’s more troublesome. It’s better to migrate on the basis of the database.

I import the SQL Server database into the MySQL database through tools, and then adjust the export script to complete it.

The tool I’m talking about here is Navicat premium. Since my database uses sqlserver2014, it is recommended to use Navicat premium 15 or higher to handle database migration. I’ve tested Navicat premium · 11.

Connect mysql database and SQLSERVER database on Navicat premium tool, then create a database with the same name in mysql, and create the database in UTF-8 code, as shown below.

Then select database transfer on the tool

Select source database and target database in the pop-up interface, as shown below.

Then, step by step processing can complete the import of database structure and data, so that there will be corresponding database structure and data in MySQL database.

However, it is not perfect to complete this one, because when importing from SQL server to MySQL database, all the self growth columns of the database are cancelled. If we modify it in the management interface, we cannot modify the primary key with constraint relationship as self growth.

Therefore, we need to adjust again, that is, export the MySQL database as an SQL script, and then make some adjustments on the script.

Open the exported database script with Notepad + + and add a keyword Auto to the self growing table fields_ Increment, marked as self growth, as shown below.

Then delete the existing MySQL database, re create the database with the corresponding name, re import the MySQL script for operation, and generate new tables and data.

Run the ABP background project and start the swagger interface, as shown below.

Then start the Vue + element client to test the operation of the MySQL database. The front end is normal.

The above is to introduce the code adjustment of using MySQL database in ABP framework, and the operation process of creating the schema and data of MySQL database based on SQL server

Recommended Today

Seven solutions for distributed transactions

1、 What is distributed transaction Distributed transaction means that transaction participants, transaction supporting servers, resource servers and transaction managers are located on different nodes of different distributed systems. A large operation is completed by more than n small operations. These small operations are distributed on different services. For these operations, either all of them are […]