C # simple movie record management system: Development 9 [data recovery]

Overview

In the last issue, we learned and implemented the data backup function of the system. In this phase, we will implement the data recovery function of the system. data recovery means that when data is lost due to external conditions, we need to use the previous backup to restore the data to avoid unnecessary losses.

 

Principle

Here, the data recovery is mainly divided into two steps: ① reading Excel Data: After opening the Excel file, call System. data. the OleDb data access interface reads Excel file data and temporarily stores the data in the DataSet. ② store the read data to SQLServer: traverse all rows in the Record table of the DataSet, and execute the SQL database operation to insert the data in the Record table into the SQL Server; finally, call SQL Server to view the data and refresh the imported data and display it on the DataGridView.

 

Interface Design

On the FrmManager page, add the Excel import button Name: btnImportExcel, as shown in figure

 

Before writing code, we need to add a namespace System. Data. OleDb in the FrmManager. cs class for connecting and operating Excel Data files.

using System.Data.OleDb;

 

Then implement the Code. The Code consists of two parts: ① reading data in Excel ② importing the data to SQL Server

Public void ImportExcel (String filePath, DataGridView dgv) {/********************************* ① open the Excel file and read it ************************************** * /// open an Excel file based on the path and fill the data in the DataSet so that SQL Server calls the Excel connection string of // 03 when executing the data insert statement. If the Excel connection string of 03 is used "The External table is not in the expected format" // string connExcel = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ filePath +"; Extended Properties = 'excel 8.0 ;'"; // The Excel connection string of Version 07 is used here. compatible with version 03, string connExcel = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ filePath +"; Extended Properties = 'excel 8.0; '"; OleDbConnection OleDbConn; try {// create OleDbConn to connect OleDbConn = new OleDbConnection (connExcel ); oleDbConn. open (); // create an Excel Data Table // Microsoft. office. interop. excel. dataTable dtExcel = OleDbConn. getOleDbSchemaTable (OleDbSchemaGuid. tables, null); Sy Stem. data. dataTable dtExcel = OleDbConn. getOleDbSchemaTable (OleDbSchemaGuid. tables, null); // obtain the Excel table string tableName = dtExcel. rows [0] [2]. toString (). trim (); tableName = "[" + tableName. replace ("'", "") + "]"; string queryExcel = "select movie name, Director, release date from" + tableName; // create a DataSet ds for later loading the obtained result DataSet ds = new DataSet (); OleDbDataAdapter oleAdapter = new OleDbDataAdapter (queryEx Cel, connExcel); oleAdapter. fill (ds, "Record"); OleDbConn. close (); /*************************** ① Excel file data reading is completed ****** **************************************** * // *************************** ② after obtaining Excel data, import data to SQL Server ********************************/string sqlInsert = null; // initialize SQL int n = 0; // define an integer for obtaining ExecuteNonQuery () number of successfully executed rows // traverse all rows in the Record table in the ds dataset foreach (DataRow dr in ds. tables ["Recor D "]. rows) {// create the insert statement sqlInsert = "insert into Record (Movie_Name, movie_ctor ctor, Date_Released) values ('"; // each line has three Field movie names, director and release date sqlInsert + = dr. itemArray [0] + "','"; sqlInsert + = dr. itemArray [1] + "','"; sqlInsert + = dr. itemArray [2] + "')"; SqlConnection conn = new SqlConnection (connStr); SqlCommand cmd = new SqlCommand (sqlInsert, conn); conn. open (); n + = cmd. executeNonQuery (); // + 1 conn each time the query is successful. cl Ose () ;}// if n> 0, data is successfully inserted. if (n> 0) {MessageBox. Show ("added successfully! Insert "+ n. ToString () +" data entries ");} else // otherwise failed {MessageBox. Show (" failed to add! "); Return ;} /************************** ② data is imported to SQL Server ****** **************************************** /// call the refresh method, after the data is added, the new data Refresh ();} catch (Exception e) is automatically refreshed in the DataGridView table. {// if an error occurs during the process, the error message MessageBox is displayed. show (e. message, "error Message") ;}} private void btnImportExcel_Click (object sender, EventArgs e) {// select the Excel file OpenFileDialog ofDialog = new OpenFileDialog (); ofDialog. title = "select the Excel file to be imported"; ofDialog. filter = "Excel Files (Version 07 *. xlsx) | *. xlsx | (version 03 *. xls) | *. xls | all files (*. *) | *. * "; string fullPath = string. empty; if (ofDialog. showDialog () = DialogResult. OK) {fullPath = ofDialog. fileName;} if (fullPath = "") {MessageBox. show ("Excel file not selected"); return;} // call the import Excel file method ImportExcel (fullPath, dgvManager );}

 

Let's run it.

1. First, clear the database data.

 

2. Click the import Excel button to select an Excel file

 

3. It is displayed that the instance has been added successfully.

 

4. The final data is imported to SQL Server and displayed on the DataGridView.

 

Here we have finished data recovery. In the next phase, we will jointly learn how administrators assign function permissions to common users.

 

Add source code

MovieRecordManagementSystem09.zip

Related Recommendations [click here to view the Directory]

• C # simple movie record management system: developer 1 [user logon]
• C # simple movie record management system: Development 2 [add, delete, modify, and query]
• C # simple movie record management system: Development 3 [Password modification]
• C # simple movie record management system: Development 4 [log view]
• C # simple movie record management system: developer 5 [User Registration]
• C # simple movie record management system: Development 6 [data search]
• C # simple movie record management system: Development 7 [User Classification]
• C # simple movie record management system: Development 8 [data backup]
• C # simple movie record management system: Conclusion [permission Assignment]