Bulk insert operations for various databases _oracle

Recent work in the need to optimize the previous various types of Excel batch import functionality, currently will be able to optimize the aspects of a record.

Selection technology:

Currently. NET can access Oracle's common DLLs, there are three kinds:

1. Microsoft comes with the System.Data.OracleClient
2. Oracle.dataaccess provided by Oracle Corporation
3. System.Data.OleDb

After comparison, the second is used for the following reasons:

How to access the database	Advantages	Disadvantages
System.Data.OracleClient	Easy to use, no matter 32,64 direct reference	Microsoft will not update it after 4.0, and does not support bulk operations, bulk operation of the data, very slow
Oracledataaccess	Operation of the database is very fast, the volume operation of 1w data speed in 3 seconds	References need to be 32 64. And not easy to control, if not careful, often can not find the DLL's bug
System.Data.OleDb	Already pass does not apply	Already pass does not apply

Download the required DLL oracle.dataaccess

First on the official introduction: Official homepage

There is a need to differentiate between 32-bit and 64-bit versions before oracle.dataaccess. and has always been more error-prone.

Because oracle.dataaccess the unmanaged code, and managed code, unmanaged code is compiled into machine code instead of. NET bytes[]. So there is no way to implement a function similar to any CPU.

Subsequent Oracle published the managed code DLL, oracle.manageddataaccess. Referencing the DLL will not require a significant 32-bit and 64-bit judgment:

Ex: for example, when debugging on the client is a 32-bit machine, referring to the 32-bit oracle.dataaccess. When publishing to the server side requires a 64-bit environment, you need to manually replace the 64-bit DLL.

Where the difference between managed DLLs and unmanaged DLLs is very small, there are very few features that are less commonly used. So try to use oracle.manageddataaccess in the program.

Steps to use:

1: Download the corresponding version of Odp.net

Note The components of Oracle here are explained below:

　　　　Oracle Data Access Components (ODAC)

　　　　Oracle Developer Tools for Visual Studio (ODT)

　　　　Oracle Data Provider for. NET (odp.net)

Where ODAC contains odp.net, which is required for use with Oracle's Entityframwork, the ODT is used to set up an Oracle data source in a. Net environment.

Into Oracle. NET Download Center, select the corresponding version of the DLL with the manager is the managed class.

2: Bulk Call:

The key code is already posted. If you have a better opinion, you can write it in the comments.　　

                    String sql = string.                    Empty; OracleCommand com = con.                    CreateCommand ();                    Com.commandtext = SQL; Com.                    Transaction = tra; #region keyword var datacount = mediabuyidkeywords.selectmany (n = n.value).                    Count (); Com.                    Arraybindcount = Datacount; Com.                    Transaction = tra; var allkeywords = mediabuyidkeywords.selectmany (n = n.value).                    ToList ();                                sql = @ "INSERT into KEYWORD (keyword_id,site_id,keyword_name,status,source,create_time,last_changed)                                    VALUES (Keyword_seq.nextval,                                    : site_id,: NAME, 1, : Source, Sysdate, SYsdate+0.0007) returning keyword_id into:key_id ";                    Com.commandtext = SQL; Com. Parameters.Add (New OracleParameter (": ID", Oracledbtype.int64, allkeywords.select (n = n.keyword_id).                    ToArray (), ParameterDirection.Input)); Com. Parameters.Add (New OracleParameter (": site_id", Oracledbtype.int64, allkeywords.select (n = n.site_id).                    ToArray (), ParameterDirection.Input)); Com. Parameters.Add (New OracleParameter (": NAME", Oracledbtype.nvarchar2, allkeywords.select (n = n.keyword_name).                    ToArray (), ParameterDirection.Input)); Com. Parameters.Add (New OracleParameter (": Source", Oracledbtype.nvarchar2, allkeywords.select (n = n.source).                    ToArray (), ParameterDirection.Input));                    var outputidparm = new OracleParameter (": key_id", Oracledbtype.int32, ParameterDirection.Output); Com.                    Parameters.Add (Outputidparm); Com. ExecuteNonquery ();                    var allkeywordids = Outputidparm.value as oracledecimal[]; for (int i = 0; i < Allkeywords.count; i++) {allkeywords[i]. keyword_id = Allkeywordids[i].                    Value.tointeger (); }