C Excel import database in a hurry

In daily projects, it is common to import data in Excel, Word, TXT and other formats into the database.

Here will be divided into: .NET import SQL Server, Oracle database and WinForm import SQL Server, Oracle database.

The basic idea of the implementation:

1. Use the fileupload control fuload to upload the excel file to a certain folder on the server.

2. Use oledb to read out the excel file that has been uploaded to the server, and read the excel file as a database. In the contact database statement, the data source is the physical path of the file on the server.

3. Return the data read out in the second step as a datatable object.

4. Traverse the DataTable object, and then query the SQL Server database to see if the data exists. If it exists, it can be updated or not processed; If it doesn't exist, the data is inserted.

Note: I use it when traversing datatable"name"].tostring();name is the header of the name column, so the order of the columns in Excel doesn't matter.

Of course, the premise is that you know the names of the headers in the Excel columns. If the order of the columns in Excel is fixed, you can proceed as in the following **.

Added references:

The entire database?

It's just a table.

。Get a datatable or list collection to store the data. Then the loop is added to excel.

foreach (entity class item in collection) to add a new row.

row = ;

Account. row[0] = ;

Name. row[1] = ;

row[2] = ;

row[3] = ;

row[4] = ;;

I'm adding some :(1) bind the data to DataGridView and then export to Excel.

2) Use third-party DLL components.

You can save the table as an Excel file.

SQL Server blocked the component'ad hoc distributed queries'statement'openrowset/opendatasource'Because this component is closed as part of the security configuration of this server. System administrators can enable this by using SP Configure'ad hoc distributed queries'。About the opening of the brief for sale'ad hoc distributed queries'For more information, see SQL Server Books Online"Surface App Configurator"。

Because sql2005 is not open by default'ad hoc distributed queries'component, how to enable it is as follows.

exec sp_configure 'show advanced options', 1

go reconfigure

go exec sp_configure 'ad hoc distributed queries', 1

go reconfigure

Go can then import Excel.

1 I'm not a master at all, just talk about it, I hope it helps you:

1. If the data has thousands of rows, but it is estimated that there are not many updated rows, you can set a column in the excel row to identify whether it has been changed, so that only the changed rows can be updated or added, which can greatly reduce the amount of operation data.

2. You can use dataset, you don't need to open and close the database every time, just open and close it once in total.

3. You can also insert the excel sheet directly into the database at one time, and then process the duplicate rows. In this way, it should be faster to only operate Excel first, and then only operate the database. (Duplicate rows can be judged by time columns to determine which is new data).