ASP. NET-based data migration method dbf upload

1 Introduction

 

In recent years, many colleges and universities in China have gradually established network infrastructure and built some application systems. However, due to lack of understanding of the overall structure of the Digital Campus, there is a lack of unified planning for system building. The existing systems are separated from each other, and information and resources cannot be shared, however, some systems are highly integrated, difficult to maintain and upgrade, and the network basic service platform is out of touch with the application system.

In the construction of a digital campus, the concept of URP (University Resource Plan) has solved problems such as information sharing. By establishing a unified database and developing unified information standards, URP enables the integration of various information systems in a loosely coupled manner, ensuring data consistency, reliability, availability, and security. Historical data in the original system can be imported to the new system through data migration. But in the process of data migration, new problems also emerge.

2. Problems faced by data migration

Currently, most of the information systems are developed using large databases such as SQL Server, Oracle, and Sybase. Most database systems in the "digital campus" construction also use these databases. Dbase, FoxBASE, and FoxPro are widely used database development software in China in recent years. Many management systems in colleges and universities still use this DBF format. With the use of the new system, how to import a large amount of valuable data accumulated in the original system over the years into the new system becomes a problem to be solved, that is, we will face how to migrate the original data to the new system database.

3. Solution

The main purpose of a unified database is to achieve data sharing and ensure data consistency. However, whether it is a new information system or an original information system, the business of each system has not undergone any essential changes. Therefore, the structure and data of the database can be basically the same, but the database platform is different. In addition, the DTS (Data Transformation Services) tool provided by SQL Server also includes the function of importing old format database files such as Dbase, FoxBASE, and FoxPro to SQL database.

Therefore, ASP. the database operation functions provided by. NET can be programmed with SQL statements to replace all DTS functions, so that a large amount of historical data can be migrated to the new database system.

4. Design and Implementation

Taking the National Employment Management System for college graduates as an example, we will describe how to implement the data migration function.

The original system is a stand-alone software uniformly used by the Ministry of Education. It is written in FoxPro and stored in DBF format. The new system adopts the B/S structure, and uses ASP. NET to write and SQL Server database to store data.

You need to solve the following problems when writing a program for data migration:

(1) The original system data file is in the DBF format. in SQL Server, the folder where the DBF file is located is considered a database and the DBF file is considered a data table.

(2) During data migration, DBF Files may be stored on the client and SQL Server databases on the Server. Therefore, you must upload the DBF Files to the Server before performing operations.

(3) Based on your business needs, the data migration function should include the initial migration, data appending, and data overwriting functions.

4.1 DBF File Upload

Traditional data files in DBF format are generally small files. Therefore, you can use the Upload Component provided by ASP. NET. The file type to be uploaded is limited to. DBF format. The upload function code is as follows:

 

// Determine whether the file size is 0

If (File1.PostedFile. ContentLength> 0)

{

String fileExt = Path. GetExtension (File1.

PostedFile. FileName). ToLower ();

// Determine whether the file is in DBF format

If (fileExt! = ". Dbf ")

{

// Error message

Label1.Text = "only files in dbf format can be uploaded !! ";

}

// Valid non-empty dbf File

Else

{

// Specify the upload directory

String filepath = Server. MapPath ("Data /");

// Upload to the specified directory

File1.PostedFile. SaveAs (filepath +

"Student. dbf ");

// Perform related data migration operations

......

}

}

Else

{

// Error message

Label1.Text = "the number of uploaded file bytes is 0 !! ";

}

 

4.2 DBF File Import

If the target data table does not exist in the SQL Server database, use the command to import and generate a new table. The code for importing and creating a new data table is as follows:

 

// Open the SQL Server database connection

MyConnection. Open ();

 

// Filepath is the target folder defined when the DBF file is uploaded

// This statement will specify the student. dbf file in the folder

// Import the data to SQL Server's new data table student

String creattb = "SELECT * INTO student FROM

Openrowset ('msdasql ', 'driver = Microsoft

Visual FoxPro Driver; SourceDB = "+ filepath + ";

SourceType = dbf', 'select * from student ')";

 

// Define SQL commands

SqlCommand myComm = new SqlCommand (

Creattb, myConnection );

// Execute the import command

MyComm. ExecuteNonQuery ();

// Close the database connection

MyComm. Dispose ();

MyConnection. Close ();

// Operation success prompt

Label2.Text = "import and generate the student data table !! ";

 

4.3 DBF File appending

If the SQL Server database already has a student data table, use the APPEND Command to import the data. The code for appending data is as follows:

 

// Open the SQL Server database connection

MyConnection. Open ();

 

// Filepath is the target folder defined when the DBF file is uploaded

// This statement will specify the student. dbf file in the folder

// Append it to the SQL Server data table student

String creattb = "insert into student

SELECT * FROM Openrowset ('msdasql ',

'Driver = Microsoft Visual FoxPro Driver;

SourceDB = "+ filepath + ";

SourceType = dbf', 'select * from student ')";

 

// Define SQL commands

SqlCommand myComm = new SqlCommand (

Creattb, myConnection );

// Execute the import command

MyComm. ExecuteNonQuery ();

// Close the database connection

MyComm. Dispose ();

MyConnection. Close ();

// Operation success prompt

Label2.Text = "student data table imported !! ";

 

4.3 DBF File Overwrite

If the imported data needs to overwrite the original data in the SQL Server database, you must run the delete statement before executing the Import Statement. The data coverage code is as follows:

// Open the SQL Server database connection

MyConnection. Open ();

// Delete the student data table content in SQL Server

String creattb = "delete from student ";

// Define SQL commands

SqlCommand myComm = new SqlCommand

(Creattb, myConnection );

// Execute the DELETE command

MyComm. ExecuteNonQuery ();

 

// Filepath is the target folder defined when the DBF file is uploaded

// This statement uses the student. dbf file in the specified folder

// Overwrite the data in the SQL Server data table student

Creattb = "insert into student SELECT *

FROM Openrowset ('msdasql ',

'Driver = Microsoft Visual FoxPro Driver;

SourceDB = "+ filepath + ";

SourceType = dbf', 'select * from student ')";

// Redefine the SQL command

MyComm. CommandText = creattb;

// Execute the import command

MyComm. ExecuteNonQuery ();

// Close the database connection

MyComm. Dispose ();

MyConnection. Close ();

// Operation success prompt

Label2.Text = "overwriting student data table successful !! ";

 

From the code above, we can see that the overwriting operation is divided into two steps: delete and append.

Similarly, you can delete the student data table in SQL Server, and then use 4.1 to import and generate data tables to overwrite data. To delete a data TABLE, run the SQL command "DROP TABLE student.

5 knots

In terms of program reliability and accuracy, the migration test and verification of tens of thousands of student information, data import, append, coverage, and other functions fully meet the requirements, you can import the original DBF data files to the SQL Server database accurately and quickly.

The method for migrating DBF data to the SQL Server database provides powerful help for solving stability, security, and accuracy in data migration, and effectively solves the Inheritance Problem of a large amount of historical data. Similarly, this migration method only needs to replace the corresponding SQL statements for data migration of various database files, such as MDB files, EXCEL files, and text files in specific formats.