How C # Massive data is inserted into a database instantaneously

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:

1 //set up a connection string for a database,2 stringConnectstr ="User Id=scott; Password=tiger;data source="; 3OracleConnection conn =NewOracleConnection (CONNECTSTR); 4OracleCommand command =NewOracleCommand (); 5Command. Connection =Conn; 6 Conn.  Open (); 7 //by looping through a large amount of data, this method is obviously a meat pad8  for(inti =0; i < RECC; i++)  9 {  Ten     stringsql ="INSERT INTO dept values ("+ i.tostring () +","+ i.tostring () +","+ i.tostring () +")";  OneCommand.commandtext =SQL;  A command.  ExecuteNonQuery ();  -}

Using the ODP feature

1 //set up a connection string for a database2 stringConnectstr ="User Id=scott; Password=tiger;data source="; 3OracleConnection conn =NewOracleConnection (CONNECTSTR); 4OracleCommand command =NewOracleCommand (); 5Command. Connection =Conn; 6 //so far, we're all familiar with the code, and we're going to start here .7 //This parameter specifies the number of records to be inserted per batch8Command. Arraybindcount =RECC; 9 //In This command line, parameters are used, we are familiar with the parameters, but this parameter is passed to the valueTen //using arrays instead of individual values, that's where it's unique. OneCommand.commandtext ="INSERT INTO Dept values (:d Eptno,:d eptname,: Loc)";  A Conn.  Open ();  - //A few arrays are defined below, representing three fields, and the length of the array is given directly by the parameter - int[] DeptNo =New int[RECC];  the 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 the +OracleParameter Deptnoparam =NewOracleParameter ("Deptno", Oracledbtype.int32);  -Deptnoparam.direction =ParameterDirection.Input;  +Deptnoparam.value =DeptNo;  A command.  Parameters.Add (Deptnoparam);  atOracleParameter Deptnameparam =NewOracleParameter ("Deptname", ORACLEDBTYPE.VARCHAR2);  -Deptnameparam.direction =ParameterDirection.Input;  -Deptnameparam.value =dname; command.  Parameters.Add (Deptnameparam);  -OracleParameter Deptlocparam =NewOracleParameter ("Loc", ORACLEDBTYPE.VARCHAR2);  -Deptlocparam.direction =ParameterDirection.Input;  -Deptlocparam.value =Loc;  in command.  Parameters.Add (Deptlocparam);  - //in the following loop, define the array first, instead of generating the SQL directly as above to  for(inti =0; i < RECC; i++)   + {   -Deptno[i] =i;  theDname[i] =i.tostring ();  *Loc[i] =i.tostring ();  $ }  Panax Notoginseng //This call will pass the parameter array into SQL and write to the database -Command. ExecuteNonQuery ();

When the amount of data reached 1 million levels, the time spent is still satisfactory, the fastest time to reach 890 milliseconds, generally 1 seconds or so.

SQL Server Database :

Since Oracle can be so readily available, is it possible to do so in SQL Server?
However, in SQL Server, there are no operational properties such as Arraybindcount.
However, we can use the BULK INSERT to enter the huge amount of data added.
Code:

1 //set up a connection string for a database2             stringConnectstr ="..."; 3SqlConnection conn =NewSqlConnection (CONNECTSTR); 4SqlCommand Command =NewSqlCommand (); 5Command.commandtimeout =0; 6Command. Connection =Conn; 7             /*Description:8 * "C:\\sql.txt" is a pre-generated file containing 100 data9 * Each field information is separated by ","Ten * Each piece of data is "|" Symbol Separation One * One transaction per 100,000 data.  A              */   -Command.commandtext ="BULK INSERT TableB from ' C:\\sql.txt ' with (FieldTerminator = ', ', rowterminator = ' | ', batchsize = 100000)";  - Conn.  Open ();  the             //System.Data.SqlClient.SqlBulkCopy SQLBC - command.  ExecuteNonQuery ();  -    -Conn. Close ();

Although not as abnormal as Oracle, but the insertion of millions data is only a few seconds, compared to our usual speed of adding, no doubt has been very excited about it, hehe.