Importing Excel data into a database--thinking

Description

Background:

Import Excel data to the database become the current project is a very common technology, to organize the previous implementation of the discovery: in the original system of import, there is no implementation method reuse, if you want to implement a data import, the original code needs to be copied past, revision modification, the implementation process is more complex, Implementing similar functionality requires a re-grooming of the original code, combined with business modification code.

In order for each of the same functions to be duplicated, we need to implement a program function that can be reused.

Objective:

we need to Excel Insert the data record in the DB table, how do I implement it?

1. Conversion
   2. will be Excel converted to List
   4. will be Excel converted to DataTable
2. validation, parsing
   2. Change column name--Chinese-Property name (field name)
   4. Necessity--necessary columns, rows of data
   6. Go repeat-- excel Repetition of data, excel with datatable Repetition of data
   8. default column-- excel No, you need to add another column to the program, and give the value of the (Import Question bank: quiz paper id -foreign key - parameter passed"
   10. change data--Chinese characters for numbers (male / Female- 1/0 , is / no- 1/0 )
   12. Foreign key--replace the data of the foreign key column with the primary key of the table in which the foreign key is located
   14. Correlation table--third table: The primary key of the target table, the primary key of the associated table 、......

......

Implement

1:

2: Implementation mode

Excel joins the database with the XML mapping, corresponding to the Excel The relationship between the data and the database table design, XML Examples of configuration file writing are as follows:

1. Overall structure

<excel><sheet name= "Student" table= "Student" primarykey= "StudentID" pktype= "GUID" > < column>  </ Column> < column> </column>                 < column> </column> ... </Sheet></Excel>

2. Detailed description

<excel name= The name of sheet in "student" ><!-Name:excel, this property is not configured, the default is "Sheet1", table: the name of the data table in the corresponding DB (the corresponding entity name is used to generate the table using EF), PrimaryKey: Primary key field name, Pktype: Gradual type-><sheet name= "student" table= "tb_student" primarykey= "StudentID" pktype= "GUID" > <!--column node: name:excel (first row), field: The name of the isneccessary in the corresponding database, whether it is necessary (True/false) isverifyrepeat= whether to validate duplicates (true/ False)--><column Name= "study number" field= "SCode" isnecessary= "true" isverifyrepeat= "true" > <datatype>string </DataType> <availablefield isexistavailablefield= "false" enablevalue= "true" >enable</ Availablefield></column><column name= "name" field= "SName" isnecessary= "true" > <datatype>string </DataType> <foreignkey isexist= "false" ></foreignkey></column><column name= "gender" field= " Sex "conversionvalue=" male-0, female-1 "> <DataType>string</DataType> <foreignkey isexist=" false "></ Foreignkey></column><column name= "Ages" field= "Age" isverifyrepeat= "false" > <datatype>int32</datatype> <foreignkey isexist= "false" ></foreignkey></column><column name= "Birthday" field = "BirthDate" isverifyrepeat= "false" > <DataType>datetime</DataType> <foreignkey isexist= "false" ></ForeignKey></Column><!--Isforeignkey: Foreign Key (true/false)--><column name= "Grade" field= "  Gradegid "isverifyrepeat=" false "isforeignkey=" true "isnecessary=" false "> <DataType>GUID</DataType> <!--foreign key information configuration-<ForeignKey><!--foreign key table--><tablename>tb_grade</tablename><!-- The Foreign key table information field that is displayed in Excel--><fieldname>gradename</fieldname>< the primary key field name for the foreign-key table that corresponds to the!----><primarykey> gradeid</primarykey><!--The Foreign key table is valid field Description: isexistavailablefield-whether there is a valid field (True/false), enable-whether the field name is available, Enablevalue-the specific storage value--><availablefield isexistavailablefield= "false" enablevalue= "true" in the available state >enable</ Availablefield> </ForeignKey></Column><!--Whether the ism2n-corresponds to a many-to-many relationship with other tables, SEPARATOR-EXCEThe delimiter used between the specific data in L--><column name= "title" field= "title" ism2n= "true" separator= "," isnecessary= "false" isactive= "true" ><!--Third table name--><thirdtable>tbr_studentjob</thirdtable><!--Third table name-->< Thirdtableprimarykey datatype= "string" &GT;ID&LT;/THIRDTABLEPRIMARYKEY&GT;&LT;THIRDTABLEFIRSTPK dataType= "string" &GT;STUDENTID&LT;/THIRDTABLEFIRSTPK&GT;&LT;THIRDTABLESECONDPK datatype= "string" >jobid</thirdtablesecondpk ><!--Third table other columns need to pass the default value--><thirdcolumns><column field= "Defaulttest" isdefault= "true" datatype= " String "/></thirdcolumns><!--the configuration tb_job-table name for the associated table that corresponds to the target table, isdefault-is the default value (" True ", The caller needs to pass the default value of the corresponding thirdtablesecondpk in the dictionary type parameter)--><relatedtable isdefault= "true" >tb_job</ relatedtable><!--The primary key--><relatedkey>jobid</relatedkey>< of the target table that is stored in the third table!--the primary key of the Target association table that is stored in the third table-- ><RelatedName>JobName</RelatedName><!--Whether there is a valid field configured for the associated table that corresponds to the target table--><availablefield Isexistavailablefield= "false" enableValue= "true" >enable</availablefield></column><column field= "Default" isdefault= "true" ></ Column></sheet></excel>

With the configuration file, the rest of the work is through the program to combine Excel with the corresponding XML configuration, do some intermediate parsing, validation, conversion and other work. In the middle of the implementation of the program can also be free to play, of course, if you want to achieve more perfect, or relatively complex, but there is no major technical problems. One of my own implementations will be described in the next blog post.

Summarize

The configuration file should be used frequently in the project, and generally our use is in others have been packaged, has been developed under the premise of the requirements of the format, such as Web under the project Web. XML configuration, as well as some of the more commonly used frameworks, it is necessary for us to use under its provisions. In turn, why can't we use XML as a configuration on some common requirements functional applications, standardize our rules, make programs more flexible and generic, and this is how the framework is formed.

Importing Excel data into a database--thinking