C# SqlBulkCopy.Close方法代码示例

 /// <summary>
 /// 批量插入数据表
 /// </summary>
 /// <param name="dt">内容表</param>
 /// <param name="DestinationTableName">插入目标表名</param>
 public static void BulkToDataTable(DataTable FromDataTable, string DestinationTableName)
 {
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
         SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
         bulkCopy.DestinationTableName = DestinationTableName;
         bulkCopy.BatchSize = FromDataTable.Rows.Count;
         try
         {
             conn.Open();
             if (FromDataTable != null && FromDataTable.Rows.Count > 0)
                 bulkCopy.WriteToServer(FromDataTable);
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
             if (bulkCopy != null)
                 bulkCopy.Close();
         }
     }
 }

        public static bool BulkToDb(SqlConnection sqlConnection, SqlTransaction sqlTransaction, DataTable dt, string tb,
            out string sMsg)
        {
            bool ret = false;
            sMsg = null;
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction);
            bulkCopy.DestinationTableName = tb;
            bulkCopy.BatchSize = dt.Rows.Count;

            try
            {
                if (dt != null && dt.Rows.Count != 0)
                    bulkCopy.WriteToServer(dt);
                ret = true;
            }
            catch (Exception ex)
            {
                sMsg = ex.Message;
                throw ex;
            }
            finally
            {
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
            return ret;
        }

        protected void btnDownloadClasses_Click(object sender, EventArgs e)
        {
            com.maplewood.www.mwWebSrvStAc Integration = new com.maplewood.www.mwWebSrvStAc();
            Integration.Url = GetWebServiceURL();
            Integration.Timeout = 360000;

            //Loop thru Sudbury Schools
            string strSQL = "SELECT cpSchoolCode, SchoolId, InstitutionName FROM UserInfo WHERE cpSystemNameId = " + GetSystemNameId() + " order by institutionType, schoolid";
            DataTable dtb = DataAccess.GetDataTable(strSQL);

            foreach (DataRow dr in dtb.Rows)
            {
                Response.Write("--------------------------------------<br/>");
                Response.Write(dr["cpSchoolCode"].ToString() + ":" + dr["InstitutionName"].ToString() + "<br/>");
                Response.Write("--------------------------------------<br/>");

                //Student Courses in Progress
                //--------------------------
                com.maplewood.www.StAc_StuClass objMWProgress = new com.maplewood.www.StAc_StuClass();
                objMWProgress = Integration.GetStudentClasses(dr["cpSchoolCode"].ToString(), "");
                Response.Write("In Progress Courses: " + objMWProgress.Student_Class_Details.Count.ToString() + "<br/>");

                SqlBulkCopy oBulkInProgress = new SqlBulkCopy(strCareerdbImportLocal);
                oBulkInProgress.DestinationTableName = "MWStudentClasses";
                oBulkInProgress.WriteToServer(objMWProgress.Student_Class_Details);
                oBulkInProgress.Close();
                //--------------------------
            }
        }

        /// <summary>
        /// Insert data from datatable into database using SQL bulk Copy
        /// </summary>
        /// <param name="data"></param>
        /// <param name="DatabaseConnection"></param>
        /// <param name="TableOwner"></param>
        /// <param name="Table"></param>
        /// <returns></returns>
        public static bool SQLBulkInsert(DataTable data, string DatabaseConnection, string TableOwner, string Table)
        {
            try {
                using (SqlConnection dbConnection = new SqlConnection(DatabaseConnection)) {
                    dbConnection.Open();

                    // Copies all rows to the database from the data reader.
                    using (SqlBulkCopy bc = new SqlBulkCopy(dbConnection)) {
                        // Destination table with owner -
                        // this example doesn't
                        // check the owner and table names!
                        bc.DestinationTableName = "[" + TableOwner + "].[" + Table + "]";

                        // User notification with the SqlRowsCopied event
                        bc.NotifyAfter = 100;
                        bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                        // Starts the bulk copy.
                        bc.WriteToServer(data);

                        // Closes the SqlBulkCopy instance
                        bc.Close();
                    }
                    dbConnection.Close();
                    return true;
                }
            } catch (Exception Ex) {
                EventLog.WriteEntry(Application.ProductName, "BulkSQLInsert Error: " + Ex.Message, EventLogEntryType.Error);
                throw Ex;
            }
        }

        public void batch_save_2_db(string _tableName, ref DataTable dt, bool _clearDT = true)
        {
            try {
                if (dt.Rows.Count > 0) {
                    using(SqlConnection conn = get_conn()) {
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                        bulkCopy.DestinationTableName = _tableName;
                        string colNames = "";
                        for (int ccc = 0; ccc < dt.Columns.Count; ccc++) {
                            string colName = dt.Columns[ccc].ColumnName;
                            if (colName != "CELL")
                                 bulkCopy.ColumnMappings.Add(colName, colName);
                        }

                        bulkCopy.WriteToServer(dt);
                        bulkCopy.Close();
                        bulkCopy = null;
                        if (_clearDT) {
                            dt.Clear();
                        }
                    }
                }
            } catch (System.Exception ex) {
                LogHelper.instance().write(ex.Message);
            } finally { }
        }

 /// <summary>
 /// splbulkcopy函数
 /// </summary>
 public static void SqlBCP(string ConnString, DataTable dt, string destablename)
 {
     SqlBulkCopy sqlBulkCopyMobile = new SqlBulkCopy(ConnString);
     sqlBulkCopyMobile.DestinationTableName = destablename;
     sqlBulkCopyMobile.WriteToServer(dt);
     sqlBulkCopyMobile.Close();
 }

        public static void batch_save_2_db(string _tableName, ref DataTable dt, bool _clearDT = true)
        {
            try
            {
                if (dt.Rows.Count > 0)
                {
                    SqlBulkCopy bulkCopy = new SqlBulkCopy(DBHelper.get_conn());
                    bulkCopy.DestinationTableName = _tableName;

                    for (int ccc = 0; ccc < dt.Columns.Count; ccc++)
                    {
                        bulkCopy.ColumnMappings.Add(dt.Columns[ccc].ColumnName, dt.Columns[ccc].ColumnName);
                    }

                    bulkCopy.WriteToServer(dt);
                    bulkCopy.Close();
                    bulkCopy = null;
                    if (_clearDT)
                    {
                        dt.Clear();
                    }
                }
            }
            catch (System.Exception ex)
            {
                LogHelper.write(ex.Message);
            }
            finally
            {
            }
        }

        public void BulkLoadDestinationTables()
        {
            var sourceQueryRunner = new QueryRunner(tableSet.SourceConnectionStringName);

            foreach (var tableMap in tableSet.Mappings.Where(ts=>ts.TruncateDestinationAndBulkLoadFromSource).OrderBy(ts => ts.Ordinal))
            {
                var query = syncChangesData.BuildQueryToRemoveChangesForTable(tableMap.SourceSchema, tableMap.SourceTable);
                sourceQueryRunner.RunQuery(query);

                Logging.WriteMessageToApplicationLog("About to bulk load data from " + tableMap.FullyQualifiedSourceTable + " to " + tableMap.FullyQualifiedDestinationTable, EventLogEntryType.Information);
                using (var bulkCopy = new SqlBulkCopy(DestinationConnectionString, SqlBulkCopyOptions.KeepIdentity))
                {
                    bulkCopy.DestinationTableName = tableMap.FullyQualifiedDestinationTable;
                    bulkCopy.EnableStreaming = true;
                    bulkCopy.BulkCopyTimeout = BulkCopyTimeout;
                    using (var conn = openSourceConnection())
                    {
                        var sql = "select * from " + tableMap.FullyQualifiedSourceTable + ";";
                        if (!string.IsNullOrEmpty(tableMap.CustomSourceSQLForBulkLoadOnly))
                        {
                            sql = tableMap.CustomSourceSQLForBulkLoadOnly;
                        }
                        var command = createCommand(conn, sql);
                        command.CommandTimeout = BulkCopyTimeout;
                        using (var reader = command.ExecuteReader())
                        {
                            bulkCopy.WriteToServer(reader);
                        }
                    }
                    bulkCopy.Close();
                }
            }
        }

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="dt">要插入的数据（列名与数据库中的列名相同）</param>
        /// <param name="destinationTableName">数据库中的目标表的名称</param>
        /// <returns>如果插入成功则返回true，否则返回false</returns>
        /// <remarks>作成者：高奇，作成日：2013-9-6</remarks>
        public static bool BulkInsert(DataTable dt, string destinationTableName)
        {
            if (dt == null || dt.Rows.Count == 0) return false;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
                bulkCopy.DestinationTableName = destinationTableName;
                bulkCopy.BatchSize = dt.Rows.Count;
                try
                {
                    connection.Open();
                    bulkCopy.WriteToServer(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                    if (bulkCopy != null)
                        bulkCopy.Close();
                }
                return true;
            }
        }

        /// <summary>
        /// 往数据库中批量插入数据
        /// </summary>
        /// <param name="sourceDt">数据源表</param>
        /// <param name="targetTable">服务器上目标表</param>
        public static void BulkToDB(DataTable sourceDt, string targetTable, SqlBulkCopyColumnMapping[] mapping)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);   //用其它源的数据有效批量加载sql server表中
            bulkCopy.DestinationTableName = targetTable;    //服务器上目标表的名称
            bulkCopy.BatchSize = sourceDt.Rows.Count;   //每一批次中的行数

            try
            {
                conn.Open();
                if (sourceDt != null && sourceDt.Rows.Count != 0)
                {
                    for (int i = 0; i < mapping.Length; i++)
                        bulkCopy.ColumnMappings.Add(mapping[i]);
                    bulkCopy.WriteToServer(sourceDt);   //将提供的数据源中的所有行复制到目标表中
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }

        public void InsertTenThousandAlphabets1()
        {
            using (var entities = new Entities())
            {
                var insertTime = new Stopwatch();
                var alphabets  = Enumerable.Range(0, 10000).Select(i => new Alphabet { A = "A", B = "B", C = "C", D = "D", E = "E", F = "F", G = "G", H = "H", I = "I", J = "J", K = "K", L = "L", M = "M", N = "N", O = "O", P = "P", Q = "Q", R = "R", S = "S", T = "T", U = "U", V = "V", W = "W", X = "X", Y = "Y", Z = "Z" }).ToList();

                insertTime.Start();
                var sqlBulkCopy = new SqlBulkCopy(entities.SqlConnection)
                { 
                    DestinationTableName = "Alphabets",
                    ColumnMappings       = {{"A", "A"}, {"B", "B"}, {"C", "C"}, {"D", "D"}, {"E", "E"}, {"F", "F"}, {"G", "G"}, {"H", "H"}, {"I", "I"}, {"J", "J"}, {"K", "K"}, {"L", "L"}, {"M", "M"}, {"N", "N"}, {"O", "O"}, {"P", "P"}, {"Q", "Q"}, {"R", "R"}, {"S", "S"}, {"T", "T"}, {"U", "U"}, {"V", "V"}, {"W", "W"}, {"X", "X"}, {"Y", "Y"}, {"Z", "Z"}}
                };

                using (var dataReader = new ObjectDataReader<Alphabet>(alphabets))
                {
                    sqlBulkCopy.WriteToServer(dataReader);
                }
                
                sqlBulkCopy.Close();
                insertTime.Stop();

                double actualInsertTimePerAlphabet = (double)insertTime.ElapsedMilliseconds / alphabets.Count();

                Debug.WriteLine("Insert Time Milliseconds Per Row: " + actualInsertTimePerAlphabet);
            }
        }

        public void BatchSaveData(IList<Model.WeekStockModel> list, string stockCode, bool isChange)
        {
            string sql;
            if (isChange)
            {
                sql = "delete from " + GetTableName(stockCode) + " where stockcode='" + stockCode + "'";
                Data.DB.DbInstance.ExecuteNonQuery(CommandType.Text, sql);
            }

            if (list.Count > 0)
            {
                sql = "delete from " + GetTableName(stockCode) + " where stockcode='" + stockCode + "' and begindate="+list[list.Count-1].BeginDate.ToString();
                Data.DB.DbInstance.ExecuteNonQuery(CommandType.Text, sql);

                string connectionString = Data.DB.DbInstance.ConnectionString;

                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
                sqlBulkCopy.DestinationTableName = GetTableName(stockCode);
                sqlBulkCopy.BatchSize = list.Count;
                SqlBulkCopyColumnMappingCollection collection = sqlBulkCopy.ColumnMappings;

                SqlConnection sqlConnection = new SqlConnection(connectionString);
                sqlConnection.Open();
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("stockcode");
                collection.Add("stockcode", "stockcode");
                dataTable.Columns.Add("begindate");
                collection.Add("begindate", "begindate");
                dataTable.Columns.Add("enddate");
                collection.Add("enddate", "enddate");
                foreach (string key in list[0].Dic.Keys)
                {
                    dataTable.Columns.Add(key);
                    collection.Add(key, key);

                }

                for (int i = 0; i < list.Count; i++)
                {
                    if (list[i].Status == 1)
                    {
                        DataRow newRow = dataTable.NewRow();
                        newRow["stockcode"] = stockCode;
                        newRow["begindate"] = list[i].BeginDate;
                        newRow["enddate"] = list[i].EndDate;
                        foreach (string key in list[i].Dic.Keys)
                        {
                            newRow[key] = list[i].Dic[key];
                        }
                        dataTable.Rows.Add(newRow);
                    }
                }
                if (dataTable != null && dataTable.Rows.Count != 0)
                {
                    sqlBulkCopy.WriteToServer(dataTable);
                }
                sqlBulkCopy.Close();
                sqlConnection.Close();
            }
        }

        public ImportQueue ImportQueueBulkImport(ImportQueue importQueue)
        {
            using (var conn = DbHelper.GetDBConnection())
            {
                using (var bulk = new SqlBulkCopy((SqlConnection)conn)
                {
                    BulkCopyTimeout = 600
                })
                {
                    var columnIndex = 0;
                    bulk.DestinationTableName = "dbo.Fdp_ImportData";
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "FdpImportId"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "LineNumber"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Pipeline Code"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Model Year Desc"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "NSC or Importer Description (Vista Market)"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Country Description"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Derivative Code"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Derivative Description"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Trim Pack Description"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Bff Feature Code"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Feature Description"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex, "Count of Specific Order No"));

                    bulk.WriteToServer(importQueue.ImportData);
                    bulk.Close();
                }
            }
            return ImportQueueGet(importQueue.ImportQueueId.GetValueOrDefault());
        }

        /// <summary>
        /// 利用Net SqlBulkCopy 批量导入数据库,速度超快
        /// </summary>
        /// <param name="dataTable">源内存数据表</param>
        public bool MsSqlBulkCopyData(DataTable dt, string connectionString)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlTransaction trans = conn.BeginTransaction();

                    SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans);
                    // 设置源表名称
                    sqlbulkCopy.DestinationTableName = dt.TableName;
                    //分几次拷贝
                    //sqlbulkCopy.BatchSize = 10;
                    // 设置超时限制
                    sqlbulkCopy.BulkCopyTimeout = 1000;
                    foreach (DataColumn dtColumn in dt.Columns)
                    {
                        sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName);
                    }
                    try
                    {
                        // 写入
                        sqlbulkCopy.WriteToServer(dt);
                        // 提交事务
                        trans.Commit();
                        return true;
                    }
                    catch
                    {
                        trans.Rollback();
                        sqlbulkCopy.Close();
                        return false;
                    }
                    finally
                    {
                        sqlbulkCopy.Close();
                        conn.Close();
                    }
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------利用Net SqlBulkCopyData 批量导入数据库,速度超快-----------\r\n" + e.Message + "\r\n");
                return false;
            }
        }

        //private
        //**************************************
        //* Purpose: Accessing SQL database
        //*Methods:
        //*GetDataSet
        //*RunProc
        //*GetDataReader
        //*GetDataView
        //* *************************************
        public bool BulkCopyData(string strConnect, string bulkCopyTable, SqlDataReader sourcedata)
        {
            //********************************
            //* Purpose: Performs bulk copy from one data source to another
            //* Input parameters:
            //* bulkCopytable ---the target table to bulk data into
            //* sourcedata ---the SqlDataReader holding the data to bulk insert
            //* Returns :
            //* nothing
            //**************************************************
            bool err = false;
            //create source connection
            var sourceConnection = new SqlConnection(strConnect);
            sourceConnection.Open();
            // Create SqlBulkCopy
            var bulkData = new SqlBulkCopy(strConnect, SqlBulkCopyOptions.TableLock)
                               {
                                   BatchSize = 1000,
                                   BulkCopyTimeout = 360,
                                   DestinationTableName = bulkCopyTable
                               };
            //set number of records to process in one batch
            //set timeout for a single bulk process
            // Set destination table name

            try
            {
                bulkData.WriteToServer(sourcedata);
            }
            catch (Exception e)
            {
                err = true;
                PLOG.Write(e.Message, 1);
                bulkData.Close();
                sourceConnection.Close();
                sourceConnection.Dispose();
            }
            finally
            {
                bulkData.Close();
                sourceConnection.Close();
                sourceConnection.Dispose();
            }
            return err;
        }