C # insert tens of thousands of data

Insert tens of thousands of data records at a time

1.

[CSHARP]View plaincopy

1. /// <Summary>
3. /// Datatable batch add (with transactions)
5. /// </Summary>/
7. // <Param name = "table"> data source </param>
9. /// <Param name = "mapping"> define a set of relationships between data sources and target source columns </param>
11. /// <Param name = "destinationtablename"> target table </param>
13. Public static bool mysqlbulkcopy (datatable table, sqlbulkcopycolumnmapping [] mapping, string destinationtablename ){
15. Bool = true;
17. Using (sqlconnection con = new sqlconnection (connectionstring )){
19. Con. open ();
21. Using (sqltransaction TRAN = con. begintransaction ()){
23. Using (sqlbulkcopy copy = new sqlbulkcopy (con, sqlbulkcopyoptions. keepidentity, Tran )){
25. Copy. destinationtablename = destinationtablename; // specify the target table
27. If (mapping! = NULL) {// If data exists
29. Foreach (sqlbulkcopycolumnmapping map in mapping ){
31. Copy. columnmappings. Add (MAP );}}
33. Try {copy. writetoserver (table); // batch add
35. Tran. Commit (); // submit the transaction}
37. Catch {Tran. rollback ();
39. // Roll back the transaction bool = false ;}}}}
41. Return bool ;}

2. MSSQL uses sqlbulkcopy to upload the data source, table name, and column shadow. It takes 1.5 million seconds to import the data.

[CSHARP]View plaincopy

1. /// <Summary>
2. /// Use sqlbulkcopy for batch insert. Only sqlserver
3. /// Disadvantage: no number of returned rows
4. /// </Summary>
5. /// <Param name = "table"> the filled datatable supports other data sources. See reload. </param>
6. /// <Param name = "tablename"> name of the table corresponding to the database </param>
7. /// <Param name = "columns"> insert a column name set for the table. </param>
8. Public void sqlbulkcopyinsert (datatable table, string tablename, string [] columns)
9. {
10. Sqlbulkcopy SBC = new sqlbulkcopy ("successive strings ");
11. SBC. destinationtablename = tablename;
12. Foreach (string Col in columns)
13. {
14. SBC. columnmappings. Add (COL, col );
15. }
16. SBC. writetoserver (table );
17. }

3. For other databases, find a datatable, fill the data in the table, and then in adpt. Update (table)

[CSHARP]View plaincopy

1. /// <Summary>
2. /// Insert multiple rows. Check the connection/command/dataadapter for the database type you are connected.
3. /// Replace the value
4. /// </Summary>
5. /// <Param name = "ds"> dataset after data filling </param>
6. /// <Returns> Number of affected rows </returns>
7. Public int multyinsert (Dataset DS)
8. {
9. Int result = 0;
10. Idbconnection con = new oracleconnection ("connection string ");
11. Con. open ();
12. Idbcommand cmd = new oraclecommand ();
13. Cmd. commandtext = "insert into member (username, password) values (@ name, @ password )";
14. Idbdataparameter namepar = cmd. createparameter ();
15. Namepar. parametername = "@ name ";
16. Namepar. sourcecolumn = "username ";
17. Namepar. sourceversion = datarowversion. Original;
18. Namepar. dbtype = dbtype. String;
19. Cmd. Parameters. Add (namepar );
20. Idbdataparameter passpar = cmd. createparameter ();
21. Passpar. parametername = "@ pass ";
22. Passpar. dbtype = dbtype. String;
23. Passpar. sourcecolumn = "password ";
24. Passpar. sourceversion = datarowversion. Original;
25. Cmd. Parameters. Add (passpar );
26. Idbdataadapter adpt = new oracledataadapter ();
27. Adpt. insertcommand = cmd;
28. Try
29. {
30. Result = adpt. Update (DS );
31. }
32. Catch (exception)
33. {
35. Throw;
36. }
37. Finally
38. {
39. Con. Close ();
40. }
41. Return result;
42. }