Migrate MS SQL Server to MySQL database in the project for fast integration of MySQL database

In the development of the project, often encountered different requirements, compatible with different types of database is one of our project status quo, the level of compatibility with a variety of databases will enable us to develop different types of projects handy, if coupled with rapid framework support, it is icing on the cake. I have developed a project or framework that uses the Microsoft Enterprise Library module, which tends to support multiple databases, and provides a very convenient, fast, unified way for us to develop different types of projects. General general database including Ms Server, Oracle, MySQL, PostgreSQL, SQLite, DB2, domestic Dameng and other databases, this essay mainly describes how to implement from MS SQL Server to MySQL database, and to add different database types to implement the bottom-up approach.

1. SQL Server database exported to MySQL

If we have developed based on SQL Server and have a lot of basic database data, then we can use SQL Server to export to MySQL database, this is our common development method, After all, it's faster to develop based on SQL Server databases.

The management tool for SQL Server databases is SQL Server Management Studio, while the MySQL database management tool recommends the use of Navicat, a very powerful and useful management tool.

First we use Navicat to build a blank MySQL database to host the data export needs of SQL Server.

For example, we create a mvcwebmistest database with the same name as the SQL Server database, and set its character set to the default or UTF8 encoding, as shown below.

Then, using SQL Server Management Studio for direct export of data, select "Task" "Export data" as shown below.

Then select the imported data source and destination in the pop-up database, and select the imported data source interface as shown below.

Then select the MySQL driver in the target, fill in the corresponding information, as shown in the following interface.

Then select the table information you want to export in the next step.

Unfortunately, this way of operating the database export operation has no way to succeed.

2. Import MS SQL Server database data from Navicat

Now that you can't import data into the MySQL database through SQL Server Management Studio, we try MySQL's database management tool Navicat, which also has the ability to transfer data (import and export).

We right-click on the database we just built to bring up the corresponding "data transfer" function, then set the source and destination of the data as shown below.

Perform data transfer operations, complete successfully, very powerful and convenient, and without any errors. Shown in the following screen.

Check the database for imported data tables and data, no problem.

3, the transfer between MySQL database

So if we need to deploy to the server, we need to transfer the current MySQL database (or restore) to the server's MySQL database, generally speaking, we use the MySQL navicat management tool can be implemented in the data export SQL file inside, You can then restore the database successfully by using the reverse operation in the server.

The dump SQL file succeeds as shown in the following interface.

On top of the server, we can restore the MySQL database using Navicat's running SQL file.

The results of the run are as follows.

Of course, sometimes it will be found that there are some tables rely on the case of reference, do not know is not navicat version out of the problem, but if there is this problem, as long as it is not the problem of coding, to open the SQL file to create the table in order to adjust it, I have encountered this extreme situation, Adjust some of the dependency tables to the front so you can import the data smoothly.

4, the bottom of the MySQL database support

The bottom support of a variety of databases, we develop different types of projects have a very important support, we do not know the customer specific environment will choose that kind of database, if we expand the different databases very convenient and fast, will undoubtedly give us a good control and confidence.

Using the Microsoft Enterprise Library as our bottom database access module, for a variety of database access operations, will be unified adoption of this Enterprise library database Access objects, the operation is very consistent, For the purpose of simplifying the code, we can define a data access operation base class for each different database in order to implement some different database processing, but they still have a common data access base class for the further encapsulation of some operations such as the normal addition and deletion of different databases.

With different databases, we need to generate processing for the access layers of different databases, such as generating the relevant data access layer for the tables of SQL Server data, dalsql the contents of each Table object, but because of the related inheritance class processing and database-based code generation, There is little code to adjust.

We prepare a different database for different database implementation, but also is only a special interface call, General general additions and deletions and paging and other common interface, all by the base class interface to achieve.

As for the Rights management system, the general framework of the basic module, multi-database support is very necessary, its database access layer is as follows.

The implementation of different database access layer is also very similar, can be easily generated by the code generation tool, add a custom interface implementation.

and for different database support, directly copy the past, change the database implementation layer of the namespace and access layer of the base class can be, if there is a custom SQL script differences, you can modify the appropriate. Generally from one kind of database support extension to another kind of database support, just copy the past, make adjustments can be, very convenient and fast, this is the overall framework support charm and efficiency.

With this consolidation of the underlying database support, the entire data access architecture is designed as follows.

With these underlying support, we selectively retain one of the database configuration information when the configuration file chooses a different database. The following is a variety of database support configuration information reference, generally reserved one.

Migrate MS SQL Server to MySQL database in the project for fast integration of MySQL database