How C # Oracle mass data is instantly inserted into a database

How C # Massive data is inserted into a database instantaneously

When we do a large number of data append in the database, is not often because the data volume is too large and distressed?
The so-called massive data, generally also tens of thousands of data, such as we want to add 1 million of data, how should improve its efficiency?

Oracle Database:

Ordinary meat Cushion type

What is called BULK INSERT, is one-time insert a batch of data, we can interpret this batch of data as a large array, and these all only through a SQL to implement, and in the traditional way, need to call many times of SQL to complete, this is the famous "array binding" function. Let's take a look at how to insert multiple rows of records in the traditional way:

//set up a connection string for a database,stringConnectstr ="User Id=scott; Password=tiger;data source="; OracleConnection Conn=NewOracleConnection (connectstr); OracleCommand command=NewOracleCommand (); command. Connection=Conn;conn. Open ();//by looping through a large amount of data, this method is obviously a meat pad for(inti =0; i < RECC; i++){    stringsql ="INSERT INTO dept values ("+ i.tostring () +","+ i.tostring () +","+ i.tostring () +")"; Command.commandtext=SQL; Command. ExecuteNonQuery ();} 

Chestnut with a transaction:

stringresult;//Create a connectionvarconn =NewOracleConnection (_CONNECTSTR); Conn. Open ();varTran = conn. BeginTransaction ();//TransactionsTry{createtable (Jifenzhuisuguanxis.select (Info= = info. TableGroup). Distinct (). ToList ());//CREATE database tables with table requirements//Create a command and iterate through the data    varCommand =Conn.    CreateCommand (); foreach(varGuanXiinchJifenzhuisuguanxis) {        //Insert        varInsertstr =string. Format ("insert INTO {5} values (' {0} ', ' {1} ', ' {2} ', ' {3} ', ' {4} ')","","","","",""); Command.commandtext=Insertstr; Command.    ExecuteNonQuery ();    } tran.commit (); Result="Success";}Catch(OracleException ex) {tran.    Rollback (); Result="An error occurred. \ n"+Ex.    Message; Loghelper.writelog ("upload.oracleexception catch exception. \ n", ex);}Catch(Exception ex) {result="An error occurred. \ n"+Ex.    Message; Loghelper.writelog ("upload.exception catch exception. \ n", ex);}finally{    if(Conn. state = =ConnectionState.Open) Conn. Close ();}returnResult

Using the ODP feature

//set up a connection string for a databasestringConnectstr ="User Id=scott; Password=tiger;data source="; OracleConnection Conn=NewOracleConnection (connectstr); OracleCommand command=NewOracleCommand (); command. Connection=Conn;//so far, we're all familiar with the code, and we're going to start here .//This parameter specifies the number of records to be inserted per batchCommand. Arraybindcount =RECC;//In This command line, parameters are used, we are familiar with the parameters, but this parameter is passed to the value//using arrays instead of individual values, that's where it's unique.Command.commandtext ="INSERT INTO Dept values (:d Eptno,:d eptname,: Loc)"; Conn. Open ();//A few arrays are defined below, representing three fields, and the length of the array is given directly by the parameterint[] DeptNo =New int[RECC];string[] Dname =New string[RECC];string[] loc =New string[RECC];//in order to pass parameters, it is unavoidable to use parameters, the following will define three consecutive//The meaning of each parameter can be seen directly from the name, not in each explanation of theOracleParameter Deptnoparam =NewOracleParameter ("Deptno", Oracledbtype.int32);d eptnoparam.direction=Parameterdirection.input;deptnoparam.value=Deptno;command. Parameters.Add (Deptnoparam); OracleParameter Deptnameparam=NewOracleParameter ("Deptname", oracledbtype.varchar2);d eptnameparam.direction=Parameterdirection.input;deptnameparam.value=dname; command. Parameters.Add (Deptnameparam); OracleParameter Deptlocparam=NewOracleParameter ("Loc", oracledbtype.varchar2);d eptlocparam.direction=Parameterdirection.input;deptlocparam.value=Loc;command. Parameters.Add (Deptlocparam);//in the following loop, define the array first, instead of generating the SQL directly as above for(inti =0; i < RECC; i++) {Deptno[i]=i; Dname[i]=i.tostring (); Loc[i]=i.tostring ();}//This call will pass the parameter array into SQL and write to the databaseCommand. ExecuteNonQuery ();

If the data Command to insert multiple tables requires a new

stringresult;//Create a connectionvarconn =NewOracleConnection (_CONNECTSTR); Conn. Open ();varTran = conn. BeginTransaction ();//TransactionsTry{    //CREATE DATABASE table sub-table RequirementsCreateTable (Jifenzhuisuguanxis.select (info =info. TableGroup). Distinct ().      ToList ()); //grouping data According to the name of a table    varDatatablegroup = Jifenzhuisuguanxis.groupby (j =J.tablegroup); foreach(varGroupinchDatatablegroup) {        varCommand = conn. CreateCommand ();//Create commandCommand. Arraybindcount = group. Count ();//Number of Inserts//INSERT StatementCommand.commandtext =string. Format ("insert INTO {0} values (: Jiid,:p Roductcode,:p roductspec,:zhuisucode,:jifencode)", group.        Key.toupper ()); #regionDefining Pass ParametersvarIdparam =New string[group.          Count ()]; varProductcodeparam =New string[group.          Count ()]; varProductspecparam =New string[group.          Count ()]; varZhuisucodeparam =New string[group.        Count ()]; varJifencodeparam =New string[group.        Count ()]; //Defining Pass ParametersCommand. Parameters.addrange (New[]            {                NewOracleParameter ("jiid", ORACLEDBTYPE.NVARCHAR2) {Direction=ParameterDirection.Input, Value=Idparam},NewOracleParameter ("ProductCode", ORACLEDBTYPE.NVARCHAR2) {Direction=ParameterDirection.Input, Value=Productcodeparam},NewOracleParameter ("Productspec", ORACLEDBTYPE.NVARCHAR2) {Direction=ParameterDirection.Input, Value=Productspecparam},NewOracleParameter ("Zhuisucode", ORACLEDBTYPE.NVARCHAR2) {Direction=ParameterDirection.Input, Value=Zhuisucodeparam},NewOracleParameter ("Jifencode", ORACLEDBTYPE.NVARCHAR2) {Direction=ParameterDirection.Input, Value=Jifencodeparam}}        ); #endregion        #regionParameter assignmentvari =0; foreach(varXiinchGroup) {Idparam[i]= XI. Id;//ID parameterProductcodeparam[i] = XI. ProductCode;//ProductCode ParametersProductspecparam[i] = XI. ProductSpec;//Productspec ParametersZhuisucodeparam[i] = XI. Zhuisucode;//Zhuisucode ParametersJifencodeparam[i] = XI. Jifencode;//Jifencode Parametersi++; }        #endregioncommand.  ExecuteNonQuery (); //Execution} tran.commit (); Result="Success";}Catch(OracleException ex) {tran.    Rollback (); Result="An error occurred. \ n"+Ex.    Message; Loghelper.writelog ("uploadodp.oracleexception catch exception. \ n", ex);}Catch(Exception ex) {result="An error occurred. \ n"+Ex.    Message; Loghelper.writelog ("uploadodp.exception catch exception. \ n", ex);}finally{    if(Conn. state = =ConnectionState.Open) Conn. Close ();}returnResult