Example #1
2
 /// <summary>
 ///大批量数据插入
 /// </summary>
 /// <param name="table">数据表</param>
 /// <param name="connectionString">数据库连接字符串</param>
 /// <returns></returns>
 public bool OracleBulkInsert(DataTable table, string connectionString)
 {
     try
     {
         using (OracleConnection connection = new OracleConnection(connectionString))
         {
             connection.Open();
             OracleTransaction trans = connection.BeginTransaction();
             using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
             {
                 //设置源表名称
                 bulkCopy.DestinationTableName = table.TableName;
                 //设置超时限制
                 bulkCopy.BulkCopyTimeout = CommandTimeOut;
                 //要写入列
                 foreach (DataColumn dtColumn in table.Columns)
                 {
                     bulkCopy.ColumnMappings.Add(dtColumn.ColumnName.ToUpper(), dtColumn.ColumnName.ToUpper());
                 }
                 try
                 {
                     // 写入
                     bulkCopy.WriteToServer(table);
                     // 提交事务
                     trans.Commit();
                     return true;
                 }
                 catch
                 {
                     trans.Rollback();
                     bulkCopy.Close();
                     return false;
                 }
                 finally
                 {
                     connection.Close();
                     connection.Dispose();
                     bulkCopy.Close();
                     bulkCopy.Dispose();
                 }
             }
         }
     }
     catch (Exception e)
     {
         DbLog.WriteException(e);
         return false;
     }
 }
		/// <summary>
		/// Bulk copies a set of objects to the server.
		/// </summary>
		/// <param name="connection">The connection to use.</param>
		/// <param name="tableName">The name of the table.</param>
		/// <param name="reader">The reader to read objects from.</param>
		/// <param name="configure">A callback method to configure the bulk copy object.</param>
		/// <param name="options">Options for initializing the bulk copy object.</param>
		/// <param name="transaction">An optional transaction to participate in.</param>
		public override void BulkCopy(IDbConnection connection, string tableName, IDataReader reader, Action<InsightBulkCopy> configure, InsightBulkCopyOptions options, IDbTransaction transaction)
		{
			if (reader == null) throw new ArgumentNullException("reader");
			if (transaction != null)
				throw new ArgumentException("OracleProvider does not support external transactions for bulk copy", "transaction");

			OracleBulkCopyOptions oracleOptions = OracleBulkCopyOptions.Default;
			if (options.HasFlag(InsightBulkCopyOptions.UseInternalTransaction))
				oracleOptions |= OracleBulkCopyOptions.UseInternalTransaction;

			using (var bulk = new OracleBulkCopy((OracleConnection)connection, oracleOptions))
			using (var insightBulk = new OracleInsightBulkCopy(bulk))
			{
				bulk.DestinationTableName = tableName;

				// map the columns by name, in case we skipped a readonly column
				foreach (DataRow row in reader.GetSchemaTable().Rows)
					bulk.ColumnMappings.Add((string)row["ColumnName"], (string)row["ColumnName"]);

				if (configure != null)
					configure(insightBulk);
				bulk.WriteToServer(reader);
			}
		}
Example #3
0
        /// <summary>
        /// 将DataTable中的数据向数据库中的表里插入数据
        /// </summary>
        /// <param name="dt">需要插入数据库的DataTable</param>
        /// <param name="targetTable">数据库中已经存在的目标表</param>
        public void InsertDatabase(DataTable dt, string targetTable)
        {
            string           _connStr       = ConfigurationManager.ConnectionStrings["OracleConnection"].ToString();
            OracleConnection conn           = new OracleConnection(_connStr);
            OracleBulkCopy   oracleBulkCopy = new OracleBulkCopy(_connStr, OracleBulkCopyOptions.UseInternalTransaction);

            //oracleBulkCopy.BatchSize = dt.Rows.Count;   //每一批次中的行数
            try
            {
                conn.Open();
                if ((dt != null) && (dt.Rows.Count != 0))
                {
                    oracleBulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
                    oracleBulkCopy.BatchSize            = 100000;      //每一批次中的行数
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string col = dt.Columns[i].ColumnName;
                        oracleBulkCopy.ColumnMappings.Add(col, col);
                    }
                    oracleBulkCopy.WriteToServer(dt);   //将提供的数据源中的所有行复制到目标表中
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
Example #4
0
        public long LoadData(IDataReader reader, string fileName, ITableDefinition tableDefinition)
        {
            long           rowsCopied;
            OracleBulkCopy oracleBulkCopy = null;

            try
            {
                oracleBulkCopy = new OracleBulkCopy(_dbContext.ConnectionString)
                {
                    DestinationTableName = tableDefinition.Name,
                    BatchSize            = BatchSize,
                    BulkCopyTimeout      = 3 * 60 * 60
                };
                // 3 hours
                using (reader)
                {
                    oracleBulkCopy.WriteToServer(reader);
                    rowsCopied = reader.RecordsAffected;
                }
            }
            catch (Exception ex)
            {
                throw new ADatabaseException("OracleBulkCopy threw error", ex);
            }
            finally
            {
                if (oracleBulkCopy != null)
                {
                    oracleBulkCopy.Close();
                    oracleBulkCopy.Dispose();
                }
            }

            return(rowsCopied);
        }
        /// <summary>
        /// 使用OracleBulkCopy方式向表中批量添加数据。
        /// </summary>
        /// <param name="entities">记录集合</param>
        /// <param name="columnNames">目标表列名集合</param>
        /// <returns>0表示成功,其他失败</returns>
        protected virtual int InsertByBulkCopy(List <T> entities, params string[] columnNames)
        {
            if (entities == null || entities.Count == 0)
            {
                throw new ArgumentNullException("entites");
            }

            MetaDataTable metaDataTable = new MetaDataTable(typeof(T), this._tableName);
            DataTable     dataTable     = this.GetDataTable(entities, metaDataTable, columnNames);

            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(this._connectionString))
            {
                bulkCopy.DestinationTableName = this._tableName;
                bulkCopy.BatchSize            = dataTable.Rows.Count;
                this.SetColumnMappings(bulkCopy.ColumnMappings, metaDataTable, columnNames);

                try
                {
                    bulkCopy.WriteToServer(dataTable);
                }
                catch (Exception ex)
                {
                    string message = string.Format("[SQL]:{0},[Exception]:{1}", "BulkCopy", ex.ToString());
                    throw new ApplicationException(message);
                }
            }

            return(0);
        }
Example #6
0
        /// <summary>
        /// 导入到Oracle
        /// </summary>
        /// <param name="dtData">需要导入的datatable</param>
        /// <returns></returns>
        public static void BulkToOracle(DataTable dtData)
        {
            using (OracleConnection conn_bulkcopy = new OracleConnection(ConfigurationManager.AppSettings["OracleConnectionString"].ToString()))
            {
                //OracleTransaction myTransaction = conn_bulkcopy.BeginTransaction();

                try
                {
                    conn_bulkcopy.Open();
                    using (OracleBulkCopy bcp = new OracleBulkCopy(conn_bulkcopy))
                    {
                        bcp.DestinationTableName = dtData.TableName;
                        if (bcp.DestinationTableName != "WHwarehousearea1")
                        {
                            bcp.BatchSize = dtData.Rows.Count;
                            SetColumnMapping(dtData, bcp);
                            bcp.WriteToServer(dtData);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
Example #7
0
        public override int BulkInsert(string tableName, DbDataReader reader, int batchSize = 200000, int timeout = 60)
        {
            List <string>  lstAllColName = this.Info[tableName];
            OracleBulkCopy bulk          = null;

            bulk = new OracleBulkCopy(this.ConnectionString);
            using (bulk)
            {
                int colCount = reader.FieldCount;
                for (int j = 0; j < colCount; j++)
                {
                    string currName = reader.GetName(j);
                    if (lstAllColName.Contains(currName, StringComparer.OrdinalIgnoreCase))
                    {
                        bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping(currName, currName));
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout      = timeout;
                bulk.BatchSize            = batchSize;

                bulk.WriteToServer(reader);
                reader.Close();
            }
            return(reader.RecordsAffected);
        }
        public bool SqlBulkCopyImport(System.Collections.Generic.IList <string> maplist, string TableName, DataTable dt)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();

                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = TableName;

                    foreach (string a in maplist)
                    {
                        bulkCopy.ColumnMappings.Add(a, a);
                    }

                    try
                    {
                        bulkCopy.WriteToServer(dt);

                        return(true);
                    }

                    catch (Exception e)
                    {
                        throw e;
                    }
                }
            }
        }
Example #9
0
        /// <summary>
        /// 批量插入记录
        /// </summary>
        /// <param name="dt">要插入的数据</param>
        /// <param name="targetTable">数据库中的表</param>
        public void BulkToDB(DataTable dt, string targetTable)
        {
            string           connOrcleString = connectionDic[DbContextName];
            OracleConnection conn            = new OracleConnection(connOrcleString);
            OracleBulkCopy   bulkCopy        = new OracleBulkCopy(connOrcleString, OracleBulkCopyOptions.UseInternalTransaction); //用其它源的数据有效批量加载Oracle表中

            bulkCopy.BulkCopyTimeout      = 260;
            bulkCopy.DestinationTableName = targetTable;   //服务器上目标表的名称
            bulkCopy.BatchSize            = dt.Rows.Count; //每一批次中的行数
                                                           //自定义的datatable和数据库的字段进行对应
            foreach (DataColumn column in dt.Columns)
            {
                bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            }
            try
            {
                conn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    bulkCopy.WriteToServer(dt);  //将提供的数据源中的所有行复制到目标表中
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }
        }
Example #10
0
        public bool ImportaDados(DataTable dt)
        {
            bool ret = false;

            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(ConfigAplication.GetConnectString().Replace(";Unicode=True", "")))
            {
                bulkCopy.DestinationTableName = "OWN_FUNCESP.FUN_TBL_AD_USUARIO";
                bulkCopy.ColumnMappings.Add("Nome", "nome");
                bulkCopy.ColumnMappings.Add("Usuario", "login");
                bulkCopy.ColumnMappings.Add("Email", "email");
                bulkCopy.ColumnMappings.Add("Departamento", "departamento");
                bulkCopy.ColumnMappings.Add("Data", "dt_inclusao");

                try
                {
                    bulkCopy.WriteToServer(dt);
                    ret = true;
                }
                catch (Exception ex)
                {
                    throw new Exception("Problemas contate o administrador do sistema: \\n" + ex.Message);
                }
                finally
                {
                    bulkCopy.Close();
                }
                return(ret);
            }
        }
Example #11
0
        public void WriteToOracle(string qualifiedTableName, string conString, DataTable dataTable)
        {
            try
            {
                //Oracle connection
                OracleConnection con = new OracleConnection();
                con.ConnectionString = conString;
                con.Open();

                // First truncate table
                OracleCommand truncateTbl = con.CreateCommand();
                truncateTbl.CommandText = "TRUNCATE table " + qualifiedTableName;
                OracleDataReader dataReader = truncateTbl.ExecuteReader();

                // them bulk data
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(con))
                {
                    bulkCopy.DestinationTableName = qualifiedTableName;
                    bulkCopy.BulkCopyTimeout      = 600;
                    bulkCopy.WriteToServer(dataTable);
                }
                con.Close();
                con.Dispose();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error encountered during Bulk INSERT operation " + e.Message);
                throw;
            }
            finally
            {
                Console.WriteLine("Volcado de datos en Oracle table " + qualifiedTableName + " satisfactorio");
            }
        }
Example #12
0
        public void Oracle_UseSqlBulkCopy(DataTable dt)
        {
            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(DBConfig.CmsSqlConString))
            {
                try
                {
                    bulkCopy.DestinationTableName = dt.TableName;//Ҫ����ı�ı���

                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.WriteToServer(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    // Close the SqlDataReader. The SqlBulkCopy
                    // object is automatically closed at the end
                    // of the using block.
                }
            }
        }
        public Task <int> ExecuteBulkCopyAsync(DataTable dt)
        {
            var identityColumnInfo = this.entityInfo.Columns.FirstOrDefault(it => it.IsIdentity);

            if (identityColumnInfo != null)
            {
                throw new Exception("Oracle bulkcopy no support identity");
            }
            OracleBulkCopy copy = GetBulkCopyInstance();

            try
            {
                copy.DestinationTableName = dt.TableName;
                copy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDb();
            }
            return(Task.FromResult(dt.Rows.Count));
        }
        public override int BulkCopy(
            DbConnection connection,
            DataTable dataTable,
            string destinationTableName = null,
            int?bulkCopyTimeout         = null,
            int?batchSize = null)
        {
            if (!(connection is OracleConnection conn))
            {
                return(0);
            }

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }


            using (var bulkCopy = new OracleBulkCopy(conn))
            {
                bulkCopy.BatchSize            = dataTable.Rows.Count;
                bulkCopy.DestinationTableName = destinationTableName ?? dataTable.TableName;
                bulkCopy.BulkCopyTimeout      = conn.ConnectionTimeout;
                bulkCopy.WriteToServer(dataTable);
                return(dataTable.Rows.Count);
            }
        }
        /// <summary>
        /// 批量复制
        /// </summary>
        /// <param name="dataSet">数据集</param>
        /// <param name="dbConnection">数据库连接</param>
        public void BulkCopy(DataSet dataSet, DbConnection dbConnection)
        {
            #region # 验证

            if (dataSet == null || dataSet.Tables.Count == 0)
            {
                return;
            }

            #endregion

            OracleConnection oracleConnection = (OracleConnection)dbConnection;
            foreach (DataTable dataTable in dataSet.Tables)
            {
                if (dataTable.Rows.Count > 0)
                {
                    //开启批量复制
                    using (OracleBulkCopy bulkCopy = new OracleBulkCopy(oracleConnection, OracleBulkCopyOptions.UseInternalTransaction))
                    {
                        bulkCopy.BatchSize            = dataTable.Rows.Count;
                        bulkCopy.DestinationTableName = dataTable.TableName;

                        //列映射
                        foreach (DataColumn dataColumn in dataTable.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName.ToUpper());
                        }

                        //执行批量插入
                        bulkCopy.WriteToServer(dataTable);
                    }
                }
            }
        }
        /// <summary>
        /// 批量复制
        /// </summary>
        /// <param name="dataTable">数据表</param>
        public void BulkCopy(DataTable dataTable)
        {
            #region # 验证

            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                return;
            }

            #endregion

            using (OracleConnection connection = this.CreateConnection())
            {
                connection.Open();

                //开启批量复制
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction))
                {
                    bulkCopy.BatchSize            = dataTable.Rows.Count;
                    bulkCopy.DestinationTableName = dataTable.TableName;

                    //列映射
                    foreach (DataColumn dataColumn in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName.ToUpper());
                    }

                    //执行批量插入
                    bulkCopy.WriteToServer(dataTable);
                }
            }
        }
    static void Main()
    {
        string connectionString = GetConnectionString();

        // Open a connection to the DB.
        using (OracleConnection sourceConnection = new OracleConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the source/destination table.
            OracleCommand commandRowCount = new OracleCommand("SELECT COUNT(*) FROM BLOGS", sourceConnection);
            long          countStart      = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a OracleDataReader.
            OracleCommand    commandSourceData = new OracleCommand("SELECT ID, URL FROM BLOGS", sourceConnection);
            OracleDataReader reader            = commandSourceData.ExecuteReader();

            // Open the destination connection. In the real world you would
            // not use OracleBulkCopy to move data from one table to the other
            // in the same database. This is for demonstration purposes only.
            using (OracleConnection destinationConnection = new OracleConnection(connectionString))
            {
                destinationConnection.Open();

                // Set up the bulk copy object.
                // Note that the column positions in the source data reader match the column positions in
                // the destination table so there is no need to map columns.
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName = "BLOGS";

                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(reader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        // Close the OracleDataReader. The OracleBulkCopy object is automatically
                        // closed at the end of the using block.
                        reader.Close();
                    }
                }

                // Perform a final count on the destination table to see how many rows were added.
                long countEnd = System.Convert.ToInt32(
                    commandRowCount.ExecuteScalar());
                Console.WriteLine("Ending row count = {0}", countEnd);
                Console.WriteLine("{0} rows were added.", countEnd - countStart);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }
    }
Example #18
0
        protected void InsertDataToDbBulkMethod(DataTable table)
        {
            OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection);

            bcopy.DestinationTableName = table.TableName;
            bcopy.WriteToServer(table);
            bcopy.Close();
        }
Example #19
0
        public void WriteToServer(DataTable dataTable)
        {
            //dynamic bulkCopy = Activator.CreateInstance(BulkCopyType, new object[] { Connection });
            OracleBulkCopy bulkCopy = new OracleBulkCopy((OracleConnection)Connection);

            bulkCopy.DestinationTableName = TableName;
            bulkCopy.WriteToServer(dataTable);
        }
 /// <summary>
 /// Bulk Copy a Data Reader to an Oracle Database.
 /// </summary>
 /// <param name="oracleConnection">Oracle Database Connection.</param>
 /// <param name="dataReader">Data Reader to consumer.</param>
 public static void ConsumeDataReader(
     OracleConnection oracleConnection,
     IDataReader dataReader)
 {
     using (var bulkCopy = new OracleBulkCopy(oracleConnection))
     {
         bulkCopy.WriteToServer(dataReader);
     }
 }
Example #21
0
        /// <summary>
        ///  导入数据到目标表
        /// </summary>
        /// <param name="session">数据库连接Session</param>
        /// <param name="tableName">表名</param>
        /// <param name="filePath">数据文件流路径,支持xls、xlsx、csv文件</param>
        /// <param name="columnCont">字段数量</param>
        /// <returns>成功行数</returns>
        public static int Import(IDbSession session, string tableName, string filePath, int columnCont = 0)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                throw new ArgumentNullException("tableName");
            }
            if (string.IsNullOrWhiteSpace(filePath))
            {
                throw new ArgumentNullException("filePath");
            }

            int rows;

            using (var tran = session.Connection.BeginTransaction())
            {
                var oracleBulkCopy = new OracleBulkCopy(session.Connection)
                {
                    Timeout = 30 * 60, DestinationTableName = tableName
                };
                try
                {
                    var       extension = Path.GetExtension(filePath).ToLower();
                    DataTable dt;
                    if (extension.StartsWith(".xls") || extension.StartsWith(".xlsx"))
                    {
                        dt = ExcelHelper.ExportExcelDataTable(filePath);
                    }
                    else if (extension == ".csv" || extension == ".txt")
                    {
                        dt = ExcelHelper.ExportCsvDataReader(filePath);
                    }
                    else
                    {
                        throw new Exception("暂不支持:{0} 文件".FormatWith(extension));
                    }

                    if (columnCont > 0 && dt.Columns.Count != columnCont)
                    {
                        throw new Exception("字段数量不一致({0} / {1})".FormatWith(columnCont, dt.Columns.Count));
                    }

                    rows = oracleBulkCopy.WriteToServer(dt);
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw new UserFriendlyException("导入失败: " + ex.GetTrueMessage(), ex.GetBaseException());
                }
                finally { oracleBulkCopy.Dispose(); }
            }
            return(rows);
        }
        public void BulkCopyDPTS(DataTable Dt, string UserID)
        {
            OracleConnection Oracleconn = new OracleConnection(WebConfigurationManager.ConnectionStrings["OracleConString"].ConnectionString);

            Oracleconn.Open();

            using (OracleBulkCopy bulkcopy = new OracleBulkCopy(Oracleconn))
            {
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_RECEIPT_NO", "DPTS_RECEIPT_NO"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_POLICY_NO", "DPTS_POLICY_NO"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_PREM_RECEIPT_NO", "DPTS_PREM_RECEIPT_NO"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_RECEIPT_AMT", "DPTS_RECEIPT_AMT"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TIME_SLAB", "DPTS_TIME_SLAB"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_AGENT", "DPTS_AGENT"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TABLE", "DPTS_TABLE"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TERM", "DPTS_TERM"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_MODE", "DPTS_MODE"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TRANSFER_AMT", "DPTS_TRANSFER_AMT"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_VARIANCE", "DPTS_VARIANCE"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_COMM_MONTH", "DPTS_COMM_MONTH"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_COMM_YEAR", "DPTS_COMM_YEAR"));
                //bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TIME_SLAB_INDEX", "TFR_AMT1"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_PROCESS_IND", "DPTS_PROCESS_IND"));

                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_UPLOAD_BY", "DPTS_UPLOAD_BY"));
                ////bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_UPLOAD_DATE", "DPTS_UPLOAD_DATE"));
                bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_UPLOAD_TYPE", "DPTS_UPLOAD_TYPE"));


                bulkcopy.DestinationTableName = "HCI_TBL_DPTS_TEMP_BEFORE";
                try
                {
                    bulkcopy.WriteToServer(Dt);
                    Oracleconn.Close();


                    //Transfer Data to HCI_TBL_DPTS_TEMP_BEFORE to HCI_TBL_DPTS_TEMP with auto ID
                    OracleConnection connection = new OracleConnection(ConnectionString);
                    connection.Open();
                    OracleCommand cmd = null;
                    cmd             = new OracleCommand("HCI_SP_DPTS_DATA_MOVE");
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection  = connection;
                    cmd.Parameters.Add("V_USER_ID", OracleDbType.Char).Value = UserID;
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
Example #23
0
        /// <summary>
        /// 快速导入dataTable映射的列至数据库表(目标列名区分大小写)
        /// </summary>
        /// <param name="dataTable">要导入的dataTable</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="columnMapping">列映射关系</param>
        /// <returns>成功返回success,否则为返回错误消息</returns>
        public string FastImportDatatableToDB(DataTable dataTable, string tableName, DataRow[] columnMapping)
        {
            OracleBulkCopy bulkCopy = new OracleBulkCopy(this._strConnString, OracleBulkCopyOptions.UseInternalTransaction);

            bulkCopy.DestinationTableName = tableName;
            foreach (DataRow row in columnMapping)
            {
                bulkCopy.ColumnMappings.Add(row[0].ToString(), row[1].ToString());
            }
            OpenConn();
            bulkCopy.WriteToServer(dataTable);
            return("success");
        }
Example #24
0
        /// <summary>
        /// 将整表数据写入数据库,表名及栏位名需与数据库一致。
        /// </summary>
        /// <param name="table">数据表。</param>
        public void WriteTable(DataTable table)
        {
            var bulkCopy = new OracleBulkCopy(ConnectionString, OracleBulkCopyOptions.UseInternalTransaction)
            {
                BulkCopyTimeout      = 300,
                DestinationTableName = table.TableName
            };

            foreach (DataColumn item in table.Columns)
            {
                bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
            }
            bulkCopy.WriteToServer(table);
        }
Example #25
0
 /// <summary>
 ///大批量数据插入
 /// </summary>
 /// <param name="table">数据表</param>
 /// <param name="connectionString">数据库连接字符串</param>
 /// <returns></returns>
 public bool OracleBulkInsert(DataTable table, string connectionString)
 {
     try
     {
         using (OracleConnection connection = new OracleConnection(connectionString))
         {
             connection.Open();
             OracleTransaction trans = connection.BeginTransaction();
             using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
             {
                 //设置源表名称
                 bulkCopy.DestinationTableName = table.TableName;
                 //设置超时限制
                 bulkCopy.BulkCopyTimeout = CommandTimeOut;
                 //要写入列
                 foreach (DataColumn dtColumn in table.Columns)
                 {
                     bulkCopy.ColumnMappings.Add(dtColumn.ColumnName.ToUpper(), dtColumn.ColumnName.ToUpper());
                 }
                 try
                 {
                     // 写入
                     bulkCopy.WriteToServer(table);
                     // 提交事务
                     trans.Commit();
                     return(true);
                 }
                 catch
                 {
                     trans.Rollback();
                     bulkCopy.Close();
                     return(false);
                 }
                 finally
                 {
                     connection.Close();
                     connection.Dispose();
                     bulkCopy.Close();
                     bulkCopy.Dispose();
                 }
             }
         }
     }
     catch (Exception e)
     {
         DbLog.WriteException(e);
         return(false);
     }
 }
Example #26
0
 /// <summary>
 /// Bulk Insert
 /// </summary>
 /// <param name="Ps_TableName">Destination Target Table</param>
 /// <param name="P_Data">Data Table</param>
 public void BulkInsert(string Ps_TableName, DataTable P_Data)
 {
     using (OracleBulkCopy BulkCopy = new OracleBulkCopy(Conn))
     {
         BulkCopy.DestinationTableName = Ps_TableName;
         try
         {
             BulkCopy.WriteToServer(P_Data);
         }
         catch (Exception ex)
         {
             throw ex;
         }
     }
 }
Example #27
0
        /// <summary>
        /// 批量执行
        /// </summary>
        /// <param name="batchSize"></param>
        /// <param name="timeout"></param>
        public override void Execute(int batchSize = 10000, int timeout = 10 * 1000)
        {
            OracleConnection newConnection = (OracleConnection)_database.CreateConnection();

            try
            {
                _dataTable = ToDataTable(_list);

                if (_dataTable == null || _dataTable.Rows.Count == 0)
                {
                    return;
                }

                var sbc = new OracleBulkCopy(newConnection);

                using (sbc)
                {
                    sbc.BatchSize = batchSize;

                    sbc.DestinationTableName = _dataTable.TableName;

                    sbc.BulkCopyTimeout = timeout;

                    if (newConnection.State != ConnectionState.Open)
                    {
                        newConnection.Open();
                    }

                    sbc.WriteToServer(_dataTable);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (newConnection.State == ConnectionState.Open)
                {
                    newConnection.Close();
                }
                _list.Clear();
            }
        }
Example #28
0
 /// <summary>
 /// Function to bulk copy to Oracle
 /// </summary>
 /// <param name="dr"></param>
 private void OracleBulkCopy(DbDataReader dr)
 {
     //We create the SqlBulkCopy object with the Destination Connection String arguments and with the SqlBulkCopy options selected.
     using (OracleBulkCopy oSbc = new OracleBulkCopy(sDest, (OracleBulkCopyOptions)iBcOptions))
     {
         //We configure the SqlBulkCopy object.
         oSbc.DestinationTableName = sDestTable;
         oSbc.BatchSize            = iSize;
         oSbc.BulkCopyTimeout      = 0;
         if (iNotifyAfter > 0)
         {
             oSbc.NotifyAfter = iNotifyAfter;
             //Each time the number of rows specified in NotifyAfter property has been copied it will call to the OnSqlRowsCopied function.
             oSbc.OracleRowsCopied += new OracleRowsCopiedEventHandler(OnOracleRowsCopied);
         }
         //We start to copy the data to the dest server.
         oSbc.WriteToServer(dr);
         oSbc.Close();
     }
 }
Example #29
0
        protected internal void InsertDataOrcl <T>(List <T> list, string TableName, string ConnectionString)
        {
            using (OracleBulkCopy bulkcopy = new OracleBulkCopy(ConnectionString))
            {
                foreach (PropertyDescriptor prop in GetListroperties <T>())
                {
                    bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping(prop.Name, prop.Name));
                }

                bulkcopy.BulkCopyTimeout      = 660;
                bulkcopy.DestinationTableName = TableName;
                try
                {
                    bulkcopy.WriteToServer(ConvertToDataTable(list));
                }
                catch
                {
                }
            }
        }
Example #30
0
        public override void ExecuteBulkCopy(IDataReader reader, string destinationTable, IList <KeyValuePair <string, string> > mappings)
        {
            if (reader == null)
            {
                Logger.Log(LevelEnum.Error, Resources.ERR_DBREADER_ISNULL);
                throw new ArgumentNullException(nameof(reader), Resources.ERR_DBREADER_ISNULL);
            }

            if (string.IsNullOrWhiteSpace(destinationTable))
            {
                Logger.Log(LevelEnum.Error, Resources.ERR_TABLENAME_ISNULL);
                throw new ArgumentNullException(nameof(destinationTable), Resources.ERR_TABLENAME_ISNULL);
            }

            Logger.LogFormat(LevelEnum.Verbose, Resources.VERBOSE_BULK_INSERT, destinationTable);
            IConfigurationProvider config = Bootstrapper.GetContainer().Resolve <IConfigurationProvider>();

            using (OracleConnection destinationConnection = (OracleConnection)CreateConnection())
            {
                destinationConnection.Open();
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName = destinationTable;
                    bulkCopy.BatchSize            = Convert.ToInt32(config.GetAppSettings(DrakeQuest.Configuration.Constants.ConfigBulkCopyBatchSize));
                    bulkCopy.BulkCopyTimeout      = Convert.ToInt32(config.GetAppSettings(DrakeQuest.Configuration.Constants.ConfigBulkCopyTimeout));
                    if (mappings != null)
                    {
                        foreach (var mapping in mappings)
                        {
                            bulkCopy.ColumnMappings.Add(mapping.Key, mapping.Value);
                        }
                    }
                    bulkCopy.WriteToServer(reader);
                    //bulkCopy.OracleRowsCopied += new OracleRowsCopiedEventHandler(bulkCopy_OracleRowsCopied);
                    bulkCopy.Close();
                    bulkCopy.Dispose();
                    destinationConnection.Dispose();
                }
            }
            Logger.LogFormat(LevelEnum.Verbose, Resources.VERBOSE_BULK_INSERT_END, destinationTable);
        }
Example #31
0
        public void InsertUsingOracleBulk(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
        {
            try
            {
                using (var bulkCopy = new OracleBulkCopy(ConnectionString, OracleBulkCopyOptions.Default))
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }

                    bulkCopy.BulkCopyTimeout      = 600;
                    bulkCopy.DestinationTableName = "[" + schmapper.SchemaName + "].[" + schmapper.TableName + "]";
                    bulkCopy.WriteToServer(dt);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #32
0
        public async Task <int> ExecuteBulkCopyAsync()

        {
            if (DbColumnInfoList == null || DbColumnInfoList.Count == 0)
            {
                return(0);
            }



            if (Inserts.First().GetType() == typeof(DataTable))

            {
                return(WriteToServer());
            }

            DataTable dt = GetCopyData();

            OracleBulkCopy bulkCopy = GetBulkCopyInstance();

            bulkCopy.DestinationTableName = InsertBuilder.GetTableNameString;

            try

            {
                await Task.Run(() => bulkCopy.WriteToServer(dt));
            }

            catch (Exception ex)

            {
                CloseDb();

                throw ex;
            }

            CloseDb();

            return(DbColumnInfoList.Count);
        }
Example #33
0
        public override void Load(string table, DataTable records, int bachSize, int timeout)
        {
            using (var oraBulkCopy = new OracleBulkCopy(ConnectionString))
            {
                // set bulk copy options
                oraBulkCopy.BulkCopyOptions = OracleBulkCopyOptions.Default;

                // mapping between data source and target database table
                oraBulkCopy.DestinationTableName = table;

                // batch and time options
                oraBulkCopy.BatchSize = bachSize;
                oraBulkCopy.BulkCopyTimeout = timeout;  // in seconds; i've set my time out to 10 minutes

                // do the actual copy
                oraBulkCopy.WriteToServer(records);
            }
        }
 /// <summary>
 /// Does the actual bulk inserts.
 /// </summary>
 /// <param name="table"></param>
 /// <param name="table_name"></param>
 /// <param name="batch_size"></param>
 private void BulkCopy(DataTable table, string table_name, int batch_size)
 {
     if (table != null && table.Rows.Count > 0)
     {
         OracleBulkCopy bulk = new OracleBulkCopy(_connection, OracleBulkCopyOptions.Default);
         bulk.BatchSize = batch_size;
         bulk.DestinationTableName = table_name;
         bulk.WriteToServer(table);
         bulk.Dispose();
         bulk = null;
         Console.WriteLine("Inserted {0} records into {1}!", table.Rows.Count, table_name);
     }
 }
 //Interface para o w(DataTable) do ODP.NET
 //http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm#BIGCDJDD
 public bool WriteToServer(DataTable dados, string tabela)
 {
     ClearMessages();
     OracleBulkCopy bulkCopy = new OracleBulkCopy(myConnection);
     bulkCopy.DestinationTableName = tabela;
     bulkCopy.BulkCopyTimeout = 3600;
     try
     {
         bulkCopy.WriteToServer(dados);
     }
     catch (Exception e)
     {
         ERROR = e.Message;
         if (e.InnerException != null)
         {
             ERROR += e.InnerException.Message;
         }
         return false;
     }
     bulkCopy.Dispose();
     return true;
 }
		/// <summary>
		/// Bulk copies a set of objects to the server.
		/// </summary>
		/// <param name="connection">The connection to use.</param>
		/// <param name="tableName">The name of the table.</param>
		/// <param name="reader">The reader to read objects from.</param>
		/// <param name="configure">A callback method to configure the bulk copy object.</param>
		/// <param name="options">Options for initializing the bulk copy object.</param>
		/// <param name="transaction">An optional transaction to participate in.</param>
		public override void BulkCopy(IDbConnection connection, string tableName, IDataReader reader, Action<InsightBulkCopy> configure, InsightBulkCopyOptions options, IDbTransaction transaction)
		{
			if (transaction != null)
				throw new ArgumentException("OracleProvider does not support external transactions for bulk copy", "transaction");

			OracleBulkCopyOptions oracleOptions = OracleBulkCopyOptions.Default;
			if (options.HasFlag(InsightBulkCopyOptions.UseInternalTransaction))
				oracleOptions |= OracleBulkCopyOptions.UseInternalTransaction;

			using (var bulk = new OracleBulkCopy((OracleConnection)connection, oracleOptions))
			using (var oracleBulk = new OracleInsightBulkCopy(bulk))
			{
				bulk.DestinationTableName = tableName;
				if (configure != null)
					configure(oracleBulk);
				bulk.WriteToServer(reader);
			}
		}
Example #37
0
        public bool SqlBulkCopyImport(System.Collections.Generic.IList<string> maplist, string TableName, DataTable dt)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {

                connection.Open();

                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
                {

                    bulkCopy.DestinationTableName = TableName;

                    foreach (string a in maplist)
                    {

                        bulkCopy.ColumnMappings.Add(a, a);

                    }

                    try
                    {

                        bulkCopy.WriteToServer(dt);

                        return true;

                    }

                    catch (Exception e)
                    {

                        throw e;

                    }
                }
            }
        }
Example #38
0
 /// <summary>
 /// 快速导入dataTable映射的列至数据库表(目标列名区分大小写)
 /// </summary>
 /// <param name="dataTable">要导入的dataTable</param>
 /// <param name="tableName">目标表名</param>
 /// <param name="columnMapping">列映射关系</param>
 /// <returns>成功返回success,否则为返回错误消息</returns>
 public string FastImportDatatableToDB(DataTable dataTable, string tableName, DataRow[] columnMapping)
 {
     OracleBulkCopy bulkCopy = new OracleBulkCopy(this._strConnString, OracleBulkCopyOptions.UseInternalTransaction);
     bulkCopy.DestinationTableName = tableName;
     foreach (DataRow row in columnMapping)
     {
         bulkCopy.ColumnMappings.Add(row[0].ToString(), row[1].ToString());
     }
     OpenConn();
     bulkCopy.WriteToServer(dataTable);
     return "success";
 }
Example #39
-1
        public void InsertBulkEmail(List<Email> emailList)
        {
            string connectionString = OracleConnectionString;
            try
            {
                DataTable smsTable = GetEmailTable(emailList);
                OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
                bulkCopy.ColumnMappings.Add("Id", "Id");
                bulkCopy.ColumnMappings.Add("ToAddress", "ToAddress");
                bulkCopy.ColumnMappings.Add("Subject", "Subject");
                bulkCopy.ColumnMappings.Add("StatusId", "StatusId");
                bulkCopy.ColumnMappings.Add("StatusDate", "StatusDate");
                bulkCopy.ColumnMappings.Add("CampaignId", "CampaignId");
                bulkCopy.ColumnMappings.Add("Body", "Body");
                bulkCopy.DestinationTableName = "EMAIL";
                bulkCopy.WriteToServer(smsTable);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #40
-1
        public void InsertBulkSMS(List<Sms> smsList)
        {
            string connectionString = OracleConnectionString;
            try
            {
                DataTable smsTable = GetSmsTable(smsList);
                OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
                bulkCopy.ColumnMappings.Add("Id", "Id");
                bulkCopy.ColumnMappings.Add("ToNumber", "ToNumber");
                bulkCopy.ColumnMappings.Add("Content", "Content");
                bulkCopy.ColumnMappings.Add("StatusId", "StatusId");
                bulkCopy.ColumnMappings.Add("StatusDate", "StatusDate");
                bulkCopy.ColumnMappings.Add("CampaignId", "CampaignId");
                bulkCopy.DestinationTableName = "SMS";
                bulkCopy.WriteToServer(smsTable);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #41
-1
 protected void InsertDataToDbBulkMethod(DataTable table)
 {
     OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection);
     bcopy.DestinationTableName = table.TableName;
     bcopy.WriteToServer(table);
     bcopy.Close();
 }