public SqlBulkCopy(string connectionString) : this(new SqlConnection(connectionString)) { if (connectionString == null) { throw ADP.ArgumentNull("connectionString"); } this._connection = new SqlConnection(connectionString); this._columnMappings = new SqlBulkCopyColumnMappingCollection(); this._ownConnection = true; }
public SqlBulkCopy(SqlConnection connection) { this._timeout = 30; this._objectID = Interlocked.Increment(ref _objectTypeCount); if (connection == null) { throw ADP.ArgumentNull("connection"); } this._connection = connection; this._columnMappings = new SqlBulkCopyColumnMappingCollection(); }
private void Dispose(bool disposing) { if (disposing) { this._columnMappings = null; this._parser = null; try { if (this._internalTransaction != null) { this._internalTransaction.Rollback(); this._internalTransaction.Dispose(); this._internalTransaction = null; } } catch (Exception exception) { if (!ADP.IsCatchableExceptionType(exception)) { throw; } ADP.TraceExceptionWithoutRethrow(exception); } finally { if (this._connection != null) { if (this._ownConnection) { this._connection.Dispose(); } this._connection = null; } } } }
/// <summary> /// 设置DataTable到数据库中表的映射 /// </summary> /// <param name="mappings"></param> private void SetMappings(SqlBulkCopyColumnMappingCollection mappings) { var cmd = this._db.CreateCommand(); cmd.CommandText = string.Format("select top 1 * from [{0}]", this._table.Name); var dbNames = new List<string>(); using (var reader = cmd.ExecuteReader()) { for (int i = 0, c = reader.FieldCount; i < c; i++) { var name = reader.GetName(i); dbNames.Add(name); } } foreach (var column in this._table.Columns) { var correspondingDbName = dbNames.First(c => string.Compare(c, column.Name, true) == 0); mappings.Add(column.Name, correspondingDbName); } }
private void WriteToServerInternal() { string tDSCommand = null; bool flag3 = false; bool flag2 = false; int[] useSqlValue = null; int num5 = this._batchSize; bool flag4 = false; if (this._batchSize > 0) { flag4 = true; } Exception inner = null; this._rowsCopied = 0; if (this._destinationTableName == null) { throw SQL.BulkLoadMissingDestinationTable(); } if (this.ReadFromRowSource()) { RuntimeHelpers.PrepareConstrainedRegions(); try { bool flag = true; this._parser = this._connection.Parser; this._stateObj = this._parser.GetSession(this); this._stateObj._bulkCopyOpperationInProgress = true; try { BulkCopySimpleResultSet set; this._stateObj.StartSession(this.ObjectID); try { set = this.CreateAndExecuteInitialQuery(); } catch (SqlException exception5) { throw SQL.BulkLoadInvalidDestinationTable(this._destinationTableName, exception5); } this._rowsUntilNotification = this._notifyAfter; tDSCommand = this.AnalyzeTargetAndCreateUpdateBulkCommand(set); if (this._sortedColumnMappings.Count == 0) { return; } this._stateObj.SniContext = SniContext.Snix_SendRows; Label_00DD: if (this.IsCopyOption(SqlBulkCopyOptions.UseInternalTransaction)) { this._internalTransaction = this._connection.BeginTransaction(); } this.SubmitUpdateBulkCommand(set, tDSCommand); try { this.WriteMetaData(set); object[] objArray = new object[this._sortedColumnMappings.Count]; if (useSqlValue == null) { useSqlValue = new int[objArray.Length]; } int num3 = num5; do { for (int i = 0; i < objArray.Length; i++) { _ColumnMapping mapping = (_ColumnMapping) this._sortedColumnMappings[i]; _SqlMetaData metadata = mapping._metadata; object obj2 = this.GetValueFromSourceRow(mapping._sourceColumnOrdinal, metadata, useSqlValue, i); objArray[i] = this.ConvertValue(obj2, metadata); } this._parser.WriteByte(0xd1, this._stateObj); for (int j = 0; j < objArray.Length; j++) { _ColumnMapping mapping2 = (_ColumnMapping) this._sortedColumnMappings[j]; _SqlMetaData data = mapping2._metadata; if (data.type != SqlDbType.Variant) { this._parser.WriteBulkCopyValue(objArray[j], data, this._stateObj); } else { this._parser.WriteSqlVariantDataRowValue(objArray[j], this._stateObj); } } this._rowsCopied++; if (((this._notifyAfter > 0) && (this._rowsUntilNotification > 0)) && (--this._rowsUntilNotification == 0)) { try { this._stateObj.BcpLock = true; flag2 = this.FireRowsCopiedEvent((long) this._rowsCopied); Bid.Trace("<sc.SqlBulkCopy.WriteToServerInternal|INFO> \n"); if (ConnectionState.Open != this._connection.State) { goto Label_02F7; } } catch (Exception exception2) { if (!ADP.IsCatchableExceptionType(exception2)) { throw; } inner = OperationAbortedException.Aborted(exception2); goto Label_02F7; } finally { this._stateObj.BcpLock = false; } if (flag2) { goto Label_02F7; } this._rowsUntilNotification = this._notifyAfter; } if (this._rowsUntilNotification > this._notifyAfter) { this._rowsUntilNotification = this._notifyAfter; } flag3 = this.ReadFromRowSource(); if (flag4) { num3--; if (num3 == 0) { goto Label_02F7; } } } while (flag3); } catch (NullReferenceException) { this._stateObj.CancelRequest(); throw; } catch (Exception exception4) { if (ADP.IsCatchableExceptionType(exception4)) { this._stateObj.CancelRequest(); } throw; } Label_02F7: if (ConnectionState.Open != this._connection.State) { throw ADP.OpenConnectionRequired("WriteToServer", this._connection.State); } this._parser.WriteBulkCopyDone(this._stateObj); this._parser.Run(RunBehavior.UntilDone, null, null, null, this._stateObj); if (flag2 || (inner != null)) { throw OperationAbortedException.Aborted(inner); } if (this._internalTransaction != null) { this._internalTransaction.Commit(); this._internalTransaction = null; } if (flag3) { goto Label_00DD; } this._localColumnMappings = null; } catch (Exception exception3) { flag = ADP.IsCatchableExceptionType(exception3); if (flag) { this._stateObj._internalTimeout = false; if (this._internalTransaction != null) { if (!this._internalTransaction.IsZombied) { this._internalTransaction.Rollback(); } this._internalTransaction = null; } } throw; } finally { if (flag && (this._stateObj != null)) { this._stateObj.CloseSession(); } } } finally { if (this._stateObj != null) { this._stateObj._bulkCopyOpperationInProgress = false; this._stateObj = null; } } } }
private void WriteRowSourceToServer(int columnCount) { this.CreateOrValidateConnection("WriteToServer"); bool flag = false; RuntimeHelpers.PrepareConstrainedRegions(); SNIHandle target = null; try { target = SqlInternalConnection.GetBestEffortCleanupTarget(this._connection); this._columnMappings.ReadOnly = true; this._localColumnMappings = this._columnMappings; if (this._localColumnMappings.Count > 0) { this._localColumnMappings.ValidateCollection(); foreach (SqlBulkCopyColumnMapping mapping2 in this._localColumnMappings) { if (mapping2._internalSourceColumnOrdinal == -1) { flag = true; break; } } } else { this._localColumnMappings = new SqlBulkCopyColumnMappingCollection(); this._localColumnMappings.CreateDefaultMapping(columnCount); } if (flag) { int ordinal = -1; flag = false; if (this._localColumnMappings.Count > 0) { foreach (SqlBulkCopyColumnMapping mapping in this._localColumnMappings) { if (mapping._internalSourceColumnOrdinal != -1) { continue; } string name = this.UnquotedName(mapping.SourceColumn); switch (this._rowSourceType) { case ValueSourceType.IDataReader: try { ordinal = ((IDataRecord) this._rowSource).GetOrdinal(name); } catch (IndexOutOfRangeException exception4) { throw SQL.BulkLoadNonMatchingColumnName(name, exception4); } break; case ValueSourceType.DataTable: ordinal = ((DataTable) this._rowSource).Columns.IndexOf(name); break; case ValueSourceType.RowArray: ordinal = ((DataRow[]) this._rowSource)[0].Table.Columns.IndexOf(name); break; } if (ordinal == -1) { throw SQL.BulkLoadNonMatchingColumnName(name); } mapping._internalSourceColumnOrdinal = ordinal; } } } this.WriteToServerInternal(); } catch (OutOfMemoryException exception3) { this._connection.Abort(exception3); throw; } catch (StackOverflowException exception2) { this._connection.Abort(exception2); throw; } catch (ThreadAbortException exception) { this._connection.Abort(exception); SqlInternalConnection.BestEffortCleanup(target); throw; } finally { this._columnMappings.ReadOnly = false; } }
// ctor // public SqlBulkCopy(SqlConnection connection) { if(connection == null) { throw ADP.ArgumentNull("connection"); } _connection = connection; _columnMappings = new SqlBulkCopyColumnMappingCollection(); }
/// <summary> /// 设置DataTable到数据库中表的映射 /// </summary> /// <param name="mappings">The mappings.</param> /// <param name="table">The table.</param> private void SetMappings(SqlBulkCopyColumnMappingCollection mappings, RdbTable table) { foreach (var column in table.Columns) { mappings.Add(column.Name, column.Name); } //暂留:通过查询的真实列名来实现 Mapping。 //var sql = string.Format("SELECT TOP 0 * FROM [{0}]", _table.Name); //var dbNames = new List<string>(); //using (var reader = _meta.DBA.QueryDataReader(sql)) //{ // for (int i = 0, c = reader.FieldCount; i < c; i++) // { // var name = reader.GetName(i); // dbNames.Add(name); // } //} //foreach (var column in _table.Columns) //{ // var correspondingDbName = dbNames.First(c => string.Compare(c, column.Name, true) == 0); // mappings.Add(column.Name, correspondingDbName); //} }
/// <summary> /// Maps columns by name. Required by SqlBulkCopy if schema changes or columns are out of order. /// </summary> private void MapColumns(SqlBulkCopyColumnMappingCollection columnMappings, IEnumerable<string> tableColumnNames, DataColumnCollection importFileColumns) { var importColumnNames = importFileColumns.Cast<DataColumn>().Select(c => c.ColumnName); var columnsInCommon = tableColumnNames.Intersect(importColumnNames); foreach (var column in columnsInCommon) { columnMappings.Add(column, column); } }
private void WriteToServerInternal() { string tDSCommand = null; bool flag3 = false; bool flag2 = false; int[] useSqlValue = null; int num5 = this._batchSize; bool flag4 = false; if (this._batchSize > 0) { flag4 = true; } Exception inner = null; this._rowsCopied = 0; if (this._destinationTableName == null) { throw SQL.BulkLoadMissingDestinationTable(); } if (this.ReadFromRowSource()) { RuntimeHelpers.PrepareConstrainedRegions(); try { bool flag = true; this._parser = this._connection.Parser; this._stateObj = this._parser.GetSession(this); this._stateObj._bulkCopyOpperationInProgress = true; try { BulkCopySimpleResultSet set; this._stateObj.StartSession(this.ObjectID); try { set = this.CreateAndExecuteInitialQuery(); } catch (SqlException exception5) { throw SQL.BulkLoadInvalidDestinationTable(this._destinationTableName, exception5); } this._rowsUntilNotification = this._notifyAfter; tDSCommand = this.AnalyzeTargetAndCreateUpdateBulkCommand(set); if (this._sortedColumnMappings.Count == 0) { return; } this._stateObj.SniContext = SniContext.Snix_SendRows; Label_00DD: if (this.IsCopyOption(SqlBulkCopyOptions.UseInternalTransaction)) { this._internalTransaction = this._connection.BeginTransaction(); } this.SubmitUpdateBulkCommand(set, tDSCommand); try { this.WriteMetaData(set); object[] objArray = new object[this._sortedColumnMappings.Count]; if (useSqlValue == null) { useSqlValue = new int[objArray.Length]; } int num3 = num5; do { for (int i = 0; i < objArray.Length; i++) { _ColumnMapping mapping = (_ColumnMapping)this._sortedColumnMappings[i]; _SqlMetaData metadata = mapping._metadata; object obj2 = this.GetValueFromSourceRow(mapping._sourceColumnOrdinal, metadata, useSqlValue, i); objArray[i] = this.ConvertValue(obj2, metadata); } this._parser.WriteByte(0xd1, this._stateObj); for (int j = 0; j < objArray.Length; j++) { _ColumnMapping mapping2 = (_ColumnMapping)this._sortedColumnMappings[j]; _SqlMetaData data = mapping2._metadata; if (data.type != SqlDbType.Variant) { this._parser.WriteBulkCopyValue(objArray[j], data, this._stateObj); } else { this._parser.WriteSqlVariantDataRowValue(objArray[j], this._stateObj); } } this._rowsCopied++; if (((this._notifyAfter > 0) && (this._rowsUntilNotification > 0)) && (--this._rowsUntilNotification == 0)) { try { this._stateObj.BcpLock = true; flag2 = this.FireRowsCopiedEvent((long)this._rowsCopied); Bid.Trace("<sc.SqlBulkCopy.WriteToServerInternal|INFO> \n"); if (ConnectionState.Open != this._connection.State) { goto Label_02F7; } } catch (Exception exception2) { if (!ADP.IsCatchableExceptionType(exception2)) { throw; } inner = OperationAbortedException.Aborted(exception2); goto Label_02F7; } finally { this._stateObj.BcpLock = false; } if (flag2) { goto Label_02F7; } this._rowsUntilNotification = this._notifyAfter; } if (this._rowsUntilNotification > this._notifyAfter) { this._rowsUntilNotification = this._notifyAfter; } flag3 = this.ReadFromRowSource(); if (flag4) { num3--; if (num3 == 0) { goto Label_02F7; } } }while (flag3); } catch (NullReferenceException) { this._stateObj.CancelRequest(); throw; } catch (Exception exception4) { if (ADP.IsCatchableExceptionType(exception4)) { this._stateObj.CancelRequest(); } throw; } Label_02F7: if (ConnectionState.Open != this._connection.State) { throw ADP.OpenConnectionRequired("WriteToServer", this._connection.State); } this._parser.WriteBulkCopyDone(this._stateObj); this._parser.Run(RunBehavior.UntilDone, null, null, null, this._stateObj); if (flag2 || (inner != null)) { throw OperationAbortedException.Aborted(inner); } if (this._internalTransaction != null) { this._internalTransaction.Commit(); this._internalTransaction = null; } if (flag3) { goto Label_00DD; } this._localColumnMappings = null; } catch (Exception exception3) { flag = ADP.IsCatchableExceptionType(exception3); if (flag) { this._stateObj._internalTimeout = false; if (this._internalTransaction != null) { if (!this._internalTransaction.IsZombied) { this._internalTransaction.Rollback(); } this._internalTransaction = null; } } throw; } finally { if (flag && (this._stateObj != null)) { this._stateObj.CloseSession(); } } } finally { if (this._stateObj != null) { this._stateObj._bulkCopyOpperationInProgress = false; this._stateObj = null; } } } }
private void WriteRowSourceToServer(int columnCount) { this.CreateOrValidateConnection("WriteToServer"); bool flag = false; RuntimeHelpers.PrepareConstrainedRegions(); SNIHandle target = null; try { target = SqlInternalConnection.GetBestEffortCleanupTarget(this._connection); this._columnMappings.ReadOnly = true; this._localColumnMappings = this._columnMappings; if (this._localColumnMappings.Count > 0) { this._localColumnMappings.ValidateCollection(); foreach (SqlBulkCopyColumnMapping mapping2 in this._localColumnMappings) { if (mapping2._internalSourceColumnOrdinal == -1) { flag = true; break; } } } else { this._localColumnMappings = new SqlBulkCopyColumnMappingCollection(); this._localColumnMappings.CreateDefaultMapping(columnCount); } if (flag) { int ordinal = -1; flag = false; if (this._localColumnMappings.Count > 0) { foreach (SqlBulkCopyColumnMapping mapping in this._localColumnMappings) { if (mapping._internalSourceColumnOrdinal != -1) { continue; } string name = this.UnquotedName(mapping.SourceColumn); switch (this._rowSourceType) { case ValueSourceType.IDataReader: try { ordinal = ((IDataRecord)this._rowSource).GetOrdinal(name); } catch (IndexOutOfRangeException exception4) { throw SQL.BulkLoadNonMatchingColumnName(name, exception4); } break; case ValueSourceType.DataTable: ordinal = ((DataTable)this._rowSource).Columns.IndexOf(name); break; case ValueSourceType.RowArray: ordinal = ((DataRow[])this._rowSource)[0].Table.Columns.IndexOf(name); break; } if (ordinal == -1) { throw SQL.BulkLoadNonMatchingColumnName(name); } mapping._internalSourceColumnOrdinal = ordinal; } } } this.WriteToServerInternal(); } catch (OutOfMemoryException exception3) { this._connection.Abort(exception3); throw; } catch (StackOverflowException exception2) { this._connection.Abort(exception2); throw; } catch (ThreadAbortException exception) { this._connection.Abort(exception); SqlInternalConnection.BestEffortCleanup(target); throw; } finally { this._columnMappings.ReadOnly = false; } }