C # uses the SqlBulkCopy class to bulk replicate data to a database

Recently, the company needs to optimize the import problem, because the way is to generate INSERT statements into the database, the amount of data is small, but as the development of data volume gradually, the previous method of performance is not up, so found SqlBulkCopy this class.

Use the SqlBulkCopy class to write data to a SQL Server table only. However, the data source is not limited to SQL Server; You can use any data source, as long as the data can be loaded into a DataTable instance or you can use a IDataReader instance to read the data.

 Public classConn {Private Static stringstrconn {Get            {                returnconfigurationmanager.connectionstrings["strconn"].                ToString (); //return configurationmanager.appsettings["strconn"]. ToString ();            }        }         Public StaticSqlConnection sqlconn {Get            {                return NewSqlConnection (strconn); }        }    }       Public classSqlHelper { PublicDataTable getdatatable (stringSQL) {DataTable dt=NewDataTable (); SqlConnection Conn=NULL; SqlDataAdapter SDA=NULL; Try{conn=Conn.sqlconn; SDA=NewSqlDataAdapter (SQL, conn); Conn.                Open (); Sda.            Fill (DT); }            Catch(Exception ex) {}finally            {                if(Conn! =NULL) {Conn.                    Close (); Conn.                Dispose (); }                if(SDA! =NULL) {SDA.                Dispose (); }            }            returnDT; }         PublicDataSet GetDataSet (stringSQL) {DataSet DS=NewDataSet (); SqlConnection Conn=NULL; SqlDataAdapter SDA=NULL; Try{conn=Conn.sqlconn; SDA=NewSqlDataAdapter (SQL, conn); Conn.                Open (); Sda.            Fill (DS); }            Catch(Exception ex) {}finally            {                if(Conn! =NULL) {Conn.                    Close (); Conn.                Dispose (); }                if(SDA! =NULL) {SDA.                Dispose (); }            }            returnds; }        /// <summary>        ///using the transaction insertion method/// </summary>        /// <param name= "DT" >Source Data</param>        /// <param name= "TableName" >Target table name</param>         Public voidInserto (DataTable DT,stringtableName) {            using(SqlConnection conn =conn.sqlconn) {using(SqlBulkCopy sqlbulecopy =NewSQLBULKCOPY (Conn. ConnectionString, Sqlbulkcopyoptions.checkconstraints | Sqlbulkcopyoptions.default |sqlbulkcopyoptions.useinternaltransaction)) {Try                    {                        //set the target table name, which is the database table nameSqlbulecopy.destinationtablename =TableName; //set the number of rows per batch, which is the number of rows you specify, to insert the database onceSqlbulecopy.batchsize =100000; //set time to complete before timing out (in seconds)Sqlbulecopy.bulkcopytimeout =3600;  for(inti =0; i < dt. Columns.count; i++)                        {                            //sets the mapping of the source data column to the column of the destination table, the first parameter is the source data column, and the second parameter is the target table columnsqlBuleCopy.ColumnMappings.Add (dt. Columns[i]. ColumnName, dt. Columns[i].                        ColumnName);                    } sqlbulecopy.writetoserver (DT); }                    Catch(Exception) {}}} }        /// <summary>        ///no transaction insertion method used/// </summary>        /// <param name= "DT" >Source Data</param>        /// <param name= "TableName" >Target table name</param>         Public voidInsertt (DataTable DT,stringtableName) {            using(SqlConnection conn =conn.sqlconn) {using(SqlBulkCopy sqlbulecopy =NewSqlBulkCopy (conn)) {                    Try{Conn.                        Open (); //set the target table name, which is the database table nameSqlbulecopy.destinationtablename =TableName; //set the number of rows per batch, which is the number of rows you specify, to insert the database onceSqlbulecopy.batchsize =100000; //set time to complete before timing out (in seconds)Sqlbulecopy.bulkcopytimeout =3600;  for(inti =0; i < dt. Columns.count; i++)                        {                            //sets the mapping of the source data column to the column of the destination table, the first parameter is the source data column, and the second parameter is the target table columnsqlBuleCopy.ColumnMappings.Add (dt. Columns[i]. ColumnName, dt. Columns[i].                        ColumnName);                    } sqlbulecopy.writetoserver (DT); }                    Catch(Exception) {conn.                        Close (); Conn.                    Dispose (); }                    finally{Conn.                        Close (); Conn.                    Dispose (); }                }            }        }    }

My source data is the data imported using Excel, the method of import does not say, not the focus here, then I will specifically summarize the Excel import method. Then query the target table for fields that need to insert data, modify the field name and type of the source data table, and then call the bulk Insert method.

protected voidBtnimport_click (Objectsender, EventArgs e) {            Try            {                //get the imported dataDataSet ds = BI. Execleds (Savepath,""); if(ds! =NULL&& ds. Tables.count >0) {DataTable dt= ds. tables[0]; //fields to be inserted for querying the target table                    stringsql ="Select u_no,u_name,u_pwd,p_id from UserInfo"; DataTable DT1=SqlHelper.                    getdatatable (SQL); if(DT1! =NULL)                    {                         for(inti =0; I < DT1. Columns.count; i++)                        {                            //Modify the field type and field name of the source data tableDt. Columns[i]. DataType =DT1. Columns[i].                            DataType; Dt. Columns[i]. ColumnMapping=DT1. Columns[i].                            ColumnMapping; Dt. Columns[i]. ColumnName=DT1. Columns[i].                        ColumnName; } sqlhelper. Inserto (DT,"UserInfo"); }                }            }            Catch(Exception ex) {Throw; }        }

If the columns of the source data table and the destination table have different order or column names, you must set the mapping relationship using the Columnmappings.add () method.

Reference:

Http://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html

C # uses the SqlBulkCopy class to bulk replicate data to a database