Introduction to LINQ-how to import data into Excel using LINQ

We have received a requirement today to implement a small function-Import ExcelThink about the guys who are active on the. NET platform. A considerable part of them should be engaged inInformation SystemDeveloped, so the younger brother is so arrogant here to import several Excel implementation methods. If there is any error, please correct me.

The first step is to import the data in Excel into the able. This step is very simple and paste the code. Then we will discuss how to put the data in the datatable into the database:

Code // Read Excel
Public   Static Datatable getexcelfiledata ( String Filepath)
{
Oledbdataadapter oleadp =   New Oledbdataadapter ();
Oledbconnection olecon =   New Oledbconnection ();
String Strcon =   " Provider = Microsoft. Jet. oledb.4.0; Data Source = "   + Filepath +   " ; Extended properties = 'excel 8.0; HDR = yes; IMEX = 1' " ;
Try
{
Datatable dt =   New Datatable ();
Olecon. connectionstring = Strcon;
Olecon. open ();
Datatable table = Olecon. getoledbschematable (oledbschemaguid. Tables, Null );
String Sheetname = Table. Rows [ 0 ] [ 2 ]. Tostring ();
String Sqlstr =   " Select * from [ "   + Sheetname +   " ] " ;
Oleadp =   New Oledbdataadapter (sqlstr, olecon );
Oleadp. Fill (DT );
Olecon. Close ();
Return DT;
}
Catch (Exception ex)
{
Throw Ex;
}
Finally
{
Oleadp =   Null ;
Olecon =   Null ;
}
}

 

There are several implementation methods to put the data in the datatable into the database:

1. Excel import-Execute insert operation cyclically

This method has many problems and is very inefficient. You need to execute the insert operation in the loop again and again. I used to define an insert statement first, then, the values of the parameters in the insert statement are constantly changed in the loop to execute the insert operation. Of course, the executenonquery of sqlhelper provided by Microsoft is used to execute the insert statement. The problem is that every insert statement needs to execute a database connection. The connection system maintains an object pool, and each connection is first retrieved from the object pool, if no new object is created in the object pool, it is added to the object pool. However, it takes some time for useless objects in the object pool to be released. Therefore, if you use a lot of connections in a short period of time, the object pool may be full. If you need to connect again, you need to wait for release, and the wait time is far shorter than the release time. Then the system will tell you that the connection times out and the system throws an exception, however, even if no exception is thrown, repeatedly opening and closing the data connection will greatly affect the system performance.

Of course, many friends didn't use sqlhelper to process such data import operations. Instead, they put the database connection in the loop. When the insert statement in the loop is executed, they release the connection, although this method is more efficient than the above method, the same problem is that every record inserted is executed once and every round-trip with the database server. If the data volume is large, the system performance is also a challenge.

 

2. import data in Excel-use the writetoserver method of sqlbulkcopy to import data in batches

The disadvantage of this approach is that the Excel format must be exactly the same as that of the table to be imported and the excess sheet must be deleted. However, it is superior to the above method in performance, it can load a large amount of data in dataset or datareader directly through data streams, and then add these records to the specified data table.

Usage:

Writetoserver (datatable) write data table

Writetoserver (datarow) Batch write data rows

Writetoserver (datatable, datarowstate) writes data to the database table by row

Writetoserver (datareader) writes a datareader object

At the same time, the sqlbulkcopy object has two attributes: batchsize, which is very important,ProgramPerformance depends on it. Batchsize indicates the number of rows in each batch. At the end of each batch, the rows in this batch are sent to the database. For example, setting the batchsize attribute to 500 means that each 500 rows of data are read and sent to the database for batch replication. The default value of batchsize is "1", which means that each row is sent to the database as a batch.

This is also a data migration method recommended by most online friends.

3. Excel import-useInsertallonsubmitMethod (this method is described in detail here)

The insertallonsubmit method is to add an object set to the datacontext object and execute the changes when submitchange (). Here I have not done performance testing yet. If I have time, make up it, first paste the Code:

Code Public   Static   Void Excelinsertdataforinsert (datatable dtexcel, String Username)
{
Using (Maucpodatacontext DB =   New Maucpodatacontext ())
{
Dictionary < String , String > Listpn_lavel = Getpn_lave ();
VaR Query = From Q In Dtexcel. asenumerable ()
Where   ! String . Isnullorempty (Q [ " Popgi_t " ]. Tostring (). Trim ()) &&
! String . Isnullorempty (Q [ " Pono " ]. Tostring (). Trim ()) &&
! String . Isnullorempty (Q [ " Pn " ]. Tostring (). Trim ()) &&
! String . Isnullorempty (Q [ " Qty " ]. Tostring (). Trim ()) &&
! String . Isnullorempty (Q [ " Price " ]. Tostring (). Trim ())
Select New
{
Popgi_t = Q [ " Popgi_t " ]. Tostring (). Trim (),
Pono = Q [ " Pono " ]. Tostring (). Trim (),
Pn = Q [ " Pn " ]. Tostring (). Trim (),
Qty = Q [ " Qty " ]. Tostring (). Trim (),
Price = Q [ " Price " ]. Tostring (). Trim ()
};
List < Maucpo > Listentity =   New List < Maucpo > ();
Foreach (VAR Q In Query)
{
Maucpo entity =   New Maucpo ();
Entity. create_t = Datetime. now;
Entity. createby = Username;
Entity. pn = Q. pn;
Entity. Pono = Q. Pono;
Entity. popgi_t = Datetime. parse (Q. popgi_t );
Entity. Price =   Decimal . Parse (Q. Price );
Entity. Priority =   Int . Parse (listpn_lavel [q. pn]);
Entity. processflag =   ' N ' ;
Entity. Qty =   Int . Parse (Q. qty );
Listentity. Add (entity );
}
DB. maucpo. insertallonsubmit (listentity );
DB. submitchanges ();
}
}

 

First, you need to convert the input able to the enumerable type to execute the LINQ query. Here, the where condition is to filter out empty rows in the datatable, the content in the SELECT statement is to retrieve the data of the corresponding column in the able, convert the queried data to a generic type listentity, and then call the insertallonsubmit (listentity) method to Perform Batch insert operations.

As for the efficiency of insertallonsubmit, I haven't found any details on the Internet. I have time to test the efficiency of the insertallonsubmit method with large data volumes.