public void Add_HelperOverloads_ItemsAddedAsExpected()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
SqlBulkCopyColumnMapping item;
item = collection.Add(3, 4);
Assert.NotNull(item);
Assert.Equal(3, item.SourceOrdinal);
Assert.Equal(4, item.DestinationOrdinal);
item = collection.Add(5, "destination");
Assert.NotNull(item);
Assert.Equal(5, item.SourceOrdinal);
Assert.Equal("destination", item.DestinationColumn);
item = collection.Add("source", 6);
Assert.NotNull(item);
Assert.Equal("source", item.SourceColumn);
Assert.Equal(6, item.DestinationOrdinal);
item = collection.Add("src", "dest");
Assert.NotNull(item);
Assert.Equal("src", item.SourceColumn);
Assert.Equal("dest", item.DestinationColumn);
}
public void Remove_BehavesAsExpected()
{
var item1 = new SqlBulkCopyColumnMapping(0, 0);
var item2 = new SqlBulkCopyColumnMapping(1, 1);
SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2);
collection.Remove(item1);
Assert.Equal(1, collection.Count);
Assert.Same(item2, collection[0]);
collection.Remove(item2);
Assert.Equal(0, collection.Count);
// The explicit implementation of IList.Remove throws ArgumentException if
// the item isn't in the collection, but the public Remove method does not
// throw in the full framework.
collection.Remove(item2);
collection.Remove(new SqlBulkCopyColumnMapping(2, 2));
IList list = CreateCollection(item1, item2);
list.Remove(item1);
Assert.Equal(1, list.Count);
Assert.Same(item2, list[0]);
list.Remove(item2);
Assert.Equal(0, list.Count);
Assert.Throws <ArgumentException>(() => list.Remove(item2));
Assert.Throws <ArgumentException>(() => list.Remove(new SqlBulkCopyColumnMapping(2, 2)));
Assert.Throws <ArgumentException>(() => list.Remove("bogus"));
}
public ManualConfiguredStrictTypePropertyInfoProvider(SqlBulkCopyColumnMappingCollection mappingsCollection)
{
_mappingsCollection = mappingsCollection;
_itemProperties = new List <PropertyInfo>();
_propertiesIndicesByNames = new Dictionary <string, int>();
}
public int Load()
{
if (_dataTable == null || _dataTable.Rows.Count < 1) // nothing to load
{
return(0);
}
using (SqlBulkCopy bc = new SqlBulkCopy(_context.Connection, SqlBulkCopyOptions.Default, null))
{
bc.BatchSize = _dataTable.Rows.Count;
bc.DestinationTableName = _context.TableSchema.TableName;
bc.BulkCopyTimeout = 0;
SqlBulkCopyColumnMappingCollection colMappings = bc.ColumnMappings;
int columnCount = _context.TableSchema.ColumnNameList.Count;
for (int colIndex = 0; colIndex < columnCount; colIndex++)
{
if (_context.TableSchema.ReadOnlyColumnIndexes.BinarySearch(colIndex) > -1)
{
continue;
}
colMappings.Add(colIndex, colIndex);
}
bc.WriteToServer(_dataTable);
bc.Close();
}
return(_dataTable.Rows.Count);
}
/// <summary>
/// モデルのプロパティと宛先項目のマッピングを行います。
/// </summary>
/// <param name="columnMappings">マッピングコレクション。</param>
public void SetColumnMappings(SqlBulkCopyColumnMappingCollection columnMappings)
{
foreach (var property in this.Properties)
{
columnMappings.Add(property.Name, GetColumnName(property));
}
}
/// <summary>
/// 数据表映射
/// </summary>
/// <param name="columnMapping"></param>
/// <param name="bigDataTable"></param>
private static void Mapping(SqlBulkCopyColumnMappingCollection columnMapping, DataTable bigDataTable)
{
foreach (DataColumn propertyInfo in bigDataTable.Columns)
{
columnMapping.Add(propertyInfo.ColumnName, propertyInfo.ColumnName);
}
}
public void RemoveAt_BehavesAsExpected()
{
var item1 = new SqlBulkCopyColumnMapping(0, 0);
var item2 = new SqlBulkCopyColumnMapping(1, 1);
var item3 = new SqlBulkCopyColumnMapping(2, 2);
SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2, item3);
collection.RemoveAt(0);
Assert.Equal(2, collection.Count);
Assert.Same(item2, collection[0]);
Assert.Same(item3, collection[1]);
collection.RemoveAt(1);
Assert.Equal(1, collection.Count);
Assert.Same(item2, collection[0]);
collection.RemoveAt(0);
Assert.Equal(0, collection.Count);
IList list = CreateCollection(item1, item2, item3);
list.RemoveAt(0);
Assert.Equal(2, list.Count);
Assert.Same(item2, list[0]);
Assert.Same(item3, list[1]);
list.RemoveAt(1);
Assert.Equal(1, list.Count);
Assert.Same(item2, list[0]);
list.RemoveAt(0);
Assert.Equal(0, list.Count);
}
/// <summary>
/// Check that every column specified in the mappings collection exists
/// in the wrapped reader.
/// </summary>
void ValidateBulkCopySourceColumnMappings(SqlBulkCopyColumnMappingCollection mappings)
{
foreach (SqlBulkCopyColumnMapping mapping in mappings)
{
var sc = new SqlName(mapping.SourceColumn);
if (!String.IsNullOrEmpty(sc.Name))
{
if (wrappedReader.GetOrdinal(sc.Name) == -1)
{
string bestFit = wrappedReader.GetColumns().SingleOrDefault(c => c.Equals(sc.Name, StringComparison.OrdinalIgnoreCase));
if (bestFit == null)
{
throw new SchemaException("Source column " + mapping.SourceColumn + " does not exist in source.");
}
else
{
throw new SchemaException
(
"Source column " + mapping.SourceColumn + " does not exist in source." +
" Column name mappings are case specific and best found match is " + bestFit + "."
);
}
}
}
else
{
if (mapping.SourceOrdinal < 0 || mapping.SourceOrdinal >= wrappedReader.FieldCount)
{
throw new SchemaException("No column exists at index " + mapping.SourceOrdinal + " in source.");
}
}
}
}
public void Methods_NullParameterPassed_ThrowsArgumentNullException()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
collection.Add(new SqlBulkCopyColumnMapping());
Assert.Throws <ArgumentNullException>(() => collection.CopyTo(null, 0));
// Passing null to the public Add method should really throw ArgumentNullException
// (which would be consistent with the explicit implementation of IList.Add), but
// the full framework does not check for null in the public Add method. Instead it
// accesses the parameter without first checking for null, resulting in
// NullReferenceExpcetion being thrown.
Assert.Throws <NullReferenceException>(() => collection.Add(null));
// Passing null to the public Insert and Remove methods should really throw
// ArgumentNullException (which would be consistent with the explicit
// implementations of IList.Insert and IList.Remove), but the full framework
// does not check for null in these methods.
collection.Insert(0, null);
collection.Remove(null);
IList list = collection;
Assert.Throws <ArgumentNullException>(() => list[0] = null);
Assert.Throws <ArgumentNullException>(() => list.Add(null));
Assert.Throws <ArgumentNullException>(() => list.CopyTo(null, 0));
Assert.Throws <ArgumentNullException>(() => list.Insert(0, null));
Assert.Throws <ArgumentNullException>(() => list.Remove(null));
}
/// <summary>
/// Constructor for BulkCopySettings class
/// </summary>
/// <param name="DestinationTable">Table into which the bulk copy should be done</param>
/// <param name="FieldCount">Number of columns that will be copied into the destination table</param>
/// <param name="DataMap">
/// Delegate that maps fields of the generic type of the QueryParallelizer instance to ordinal positions
/// </param>
/// <param name="OutputRowsToExternalEnumerator">
/// If true, the external enumerator will output the same rows that are processed by the bulk copy component.
/// If false, the rows will only be output via the bulk component, and the external enumerator will output
/// an empty result.
/// </param>
/// <param name="ConnectionString">
/// Connection string for the destination table. If left null, the loopback connection provider will be used
/// </param>
/// <param name="CopyOptions">
/// SqlBulkCopyOptions enumeration, used to specify table locking, whether to keep NULLs, and other options
/// </param>
/// <param name="BatchSize">Number of rows to transfer per batch. Use 0 for unlimited.</param>
/// <param name="ColumnMap">
/// Mapping between the DataMap ordinals and the ordinals of columns in the actual destination table. To be
/// used when the column order in the base table is different from that specified by the DataMap, or when there
/// are fewer columns in the destination table than those that will be populated by the bulk copy operation.
/// Can be left null if not needed.
/// </param>
public BulkCopySettings(
string destinationTable,
int fieldCount,
BulkCopyMappingDelegate dataMap,
bool outputRowsToExternalEnumerator,
int numThreads,
string connectionString,
SqlBulkCopyOptions copyOptions,
int batchSize,
SqlBulkCopyColumnMapping[] columnMap)
: this
(destinationTable,
fieldCount,
dataMap,
outputRowsToExternalEnumerator,
numThreads,
connectionString)
{
this.CopyOptions = copyOptions;
this.BatchSize = batchSize;
//create an instance of SqlBulkCopy to grab the ColumnMappingCollection
SqlBulkCopy bc = new SqlBulkCopy("");
this.ColumnMap = bc.ColumnMappings;
if (columnMap != null)
{
foreach (SqlBulkCopyColumnMapping cm in columnMap)
{
this.ColumnMap.Add(cm);
}
}
}
private static async Task TestAsync(string srcConstr, string dstConstr, string dstTable, SemaphoreSlim outputSemaphore)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
datatable.Rows[0].BeginEdit();
datatable.Rows[0][0] = 333;
datatable.Rows[0].EndEdit();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 7;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "col1");
ColumnMappings.Add(1, "col2");
ColumnMappings.Add(2, "col3");
bulkcopy.WriteToServer(datatable, DataRowState.Unchanged);
datatable.Rows.GetEnumerator().Reset();
await bulkcopy.WriteToServerAsync(datatable, DataRowState.Modified);
datatable.Rows.GetEnumerator().Reset();
await bulkcopy.WriteToServerAsync(datatable, DataRowState.Deleted);
bulkcopy.Close();
}
await outputSemaphore.WaitAsync();
Helpers.VerifyResults(dstConn, dstTable, 4, 18);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
DataRow[] rows;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (orderid int, customerid nchar(5), rdate datetime, freight money, shipname nvarchar(40))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 100 * from orders", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
rows = new DataRow[datatable.Rows.Count];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = datatable.Rows[i];
}
}
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowCopied);
bulkcopy.DestinationTableName = dstTable;
bulkcopy.NotifyAfter = 50;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "orderid");
ColumnMappings.Add(1, "customerid");
ColumnMappings.Add(4, "rdate");
ColumnMappings.Add(7, "freight");
ColumnMappings.Add(8, "shipname");
bulkcopy.WriteToServer(rows);
bulkcopy.SqlRowsCopied -= new SqlRowsCopiedEventHandler(OnRowCopied);
}
Helpers.VerifyResults(dstConn, dstTable, 5, 100);
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void AddRange(this SqlBulkCopyColumnMappingCollection columnMappings,
IEnumerable <SqlBulkCopyColumnMapping> mappings)
{
foreach (var mapping in mappings)
{
columnMappings.Add(mapping);
}
}
private static async Task TestAsync(string srcConstr, string dstConstr, string dstTable, SemaphoreSlim outputSemaphore)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
DataRow[] rows;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
rows = new DataRow[datatable.Rows.Count];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = datatable.Rows[i];
}
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 4;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "col1");
ColumnMappings.Add(2, "col3");
await bulkcopy.WriteToServerAsync(rows);
bulkcopy.Close();
}
await outputSemaphore.WaitAsync();
Helpers.VerifyResults(dstConn, dstTable, 4, 9);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public void GetEnumerator_NoItems_EmptyEnumerator()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
IEnumerator e = collection.GetEnumerator();
Assert.Throws <InvalidOperationException>(() => e.Current);
Assert.False(e.MoveNext());
Assert.Throws <InvalidOperationException>(() => e.Current);
}
void ValidateBulkCopyDestinationColumnMappings
(
SqlBulkCopyColumnMappingCollection mappings,
Dictionary <string, int> columnLookup,
DataRow[] columns
)
{
foreach (SqlBulkCopyColumnMapping mapping in mappings)
{
var destColumn = new SqlName(mapping.DestinationColumn);
if (!String.IsNullOrEmpty(destColumn.Name))
{
if (!columnLookup.ContainsKey(destColumn.Name))
{
// If we can't find an exact match by case, try for a case-insensitive match.
string bestFit = columnLookup.Keys.SingleOrDefault(c => c.Equals(destColumn.Name, StringComparison.OrdinalIgnoreCase));
if (bestFit == null)
{
throw new SchemaException
(
"Destination column " + mapping.DestinationColumn +
" does not exist in destination table " + targetTableName +
" in database " + targetDatabaseName +
" on server " + targetServerName + "."
);
}
else
{
throw new SchemaException
(
"Destination column " + mapping.DestinationColumn +
" does not exist in destination table " + targetTableName +
" in database " + targetDatabaseName +
" on server " + targetServerName +
". Column name mappings are case specific and best found match is " + bestFit + "."
);
}
}
}
else
{
if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal >= columns.Length)
{
throw new SchemaException
(
"No column exists at index " + mapping.DestinationOrdinal +
" in destination table " + targetTableName +
" in database " + targetDatabaseName +
" on server " + targetServerName + "."
);
}
}
}
}
public static void Test(string constr, string dstTable)
{
string srctable = "[" + dstTable + " src]";
dstTable = "[" + dstTable + "]";
string[] epilogue =
{
"create table " + srctable + "([col 1] int primary key, [col 2] text)",
"insert into " + srctable + " values (33, 'Michael')",
"create table " + dstTable + "([col 1] int primary key, [col 2] text)",
};
string[] prologue =
{
"drop table " + srctable,
"drop table " + dstTable,
};
using (SqlConnection dstConn = new SqlConnection(constr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.ProcessCommandBatch(typeof(SqlConnection), constr, epilogue);
using (SqlConnection srcConn = new SqlConnection(constr))
using (SqlCommand srcCmd = new SqlCommand(string.Format("select * from {0} ", srctable), srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("[col 1]", "col 1");
ColumnMappings.Add("col 2", "[col 2]");
bulkcopy.WriteToServer(reader);
DataTestUtility.AssertEqualsWithDescription(bulkcopy.RowsCopied, 1, "Unexpected number of rows.");
}
Helpers.VerifyResults(dstConn, dstTable, 2, 1);
}
}
}
finally
{
Helpers.ProcessCommandBatch(typeof(SqlConnection), constr, prologue);
}
}
}
void CreateLookupFromColumnMappings
(
SqlBulkCopyColumnMappingCollection mappings,
DataTable schemaTableOfDestinationTable
)
{
// create lookup dest column definition by source index
foreach (SqlBulkCopyColumnMapping mapping in mappings)
{
int sourceIndex = -1;
var sourceColumn = new SqlName(mapping.SourceColumn);
if (!String.IsNullOrEmpty(sourceColumn.Name))
{
sourceIndex = wrappedReader.GetOrdinal(sourceColumn.Name);
}
else
{
sourceIndex = mapping.SourceOrdinal;
}
DataRow destColumnDef = null;
var destColumn = new SqlName(mapping.DestinationColumn);
if (!String.IsNullOrEmpty(destColumn.Name))
{
destColumnDef = schemaTableOfDestinationTable.Rows.Cast <DataRow>().
Single(c => (string)c["ColumnName"] == destColumn.Name);
//foreach (DataRow column in schemaTableOfDestinationTable.Rows)
//{
// if ((string)column["ColumnName"] == destColumn.Name)
// {
// destColumnDef = column;
// break;
// }
//}
}
else
{
destColumnDef = schemaTableOfDestinationTable.Rows.Cast <DataRow>().
Single(c => (int)c["ColumnOrdinal"] == mapping.DestinationOrdinal);
//foreach (DataRow column in schemaTableOfDestinationTable.Rows)
//{
// if ((int)column["ColumnOrdinal"] == mapping.DestinationOrdinal)
// {
// destColumnDef = column;
// break;
// }
//}
}
lookup[sourceIndex] = destColumnDef;
}
}
public void GetEnumerator_ModifiedCollectionDuringEnumeration_ThrowsInvalidOperationException()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
IEnumerator e = collection.GetEnumerator();
collection.Add(0, 0);
// Collection changed.
Assert.Throws <InvalidOperationException>(() => e.MoveNext());
Assert.Throws <InvalidOperationException>(() => e.Reset());
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
SqlTransaction myTrans = dstConn.BeginTransaction();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.Default, myTrans))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
try
{
bulkcopy.WriteToServer(reader);
SqlCommand myCmd = dstConn.CreateCommand();
myCmd.CommandText = "select * from " + dstTable;
myCmd.Transaction = myTrans;
using (DbDataReader reader1 = myCmd.ExecuteReader())
{
Assert.True(reader1.HasRows, "Expected reader to have rows.");
}
}
finally
{
myTrans.Rollback();
}
}
Helpers.CheckTableRows(dstConn, dstTable, false);
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string constr, string srctable, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(constr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
// create the source table
Helpers.TryExecute(dstCmd, "create table " + srctable + " (col1 int , col2 int, col3 text)");
Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (33, 498, 'Michael')");
Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (34, 499, 'Astrid')");
Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (65, 500, 'alles Käse')");
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int primary key, col2 int CONSTRAINT CK_" + dstTable + " CHECK (col2 < 500), col3 text)");
using (SqlConnection srcConn = new SqlConnection(constr))
using (SqlCommand srcCmd = new SqlCommand("select * from " + srctable, srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
try
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.CheckConstraints, null))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("col1", "col1");
ColumnMappings.Add("col2", "col2");
ColumnMappings.Add("col3", "col3");
bulkcopy.WriteToServer(reader);
}
}
catch (SqlException sqlEx)
{
// Error 547 == The %ls statement conflicted with the %ls constraint "%.*ls".
DataTestUtility.AssertEqualsWithDescription(547, sqlEx.Number, "Unexpected error number.");
}
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
Helpers.TryExecute(dstCmd, "drop table " + srctable);
}
}
}
private static SqlBulkCopyColumnMappingCollection CreateCollection(params SqlBulkCopyColumnMapping[] mappings)
{
Debug.Assert(mappings != null);
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
foreach (SqlBulkCopyColumnMapping mapping in mappings)
{
Debug.Assert(mapping != null);
collection.Add(mapping);
}
return(collection);
}
public StrictTypeMappingInfoProvider(Type type, SqlBulkCopyColumnMappingCollection mappingsCollection)
{
_itemProperties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
if (_itemProperties == null || _itemProperties.Any() == false)
{
throw new InvalidOperationException("Cannot collect properties from object");
}
MappingsCollection = mappingsCollection;
for (var i = 0; i < _itemProperties.Length; i++)
{
MappingsCollection.Add(new SqlBulkCopyColumnMapping(i, _itemProperties[i].Name));
}
}
internal static void InnerTest(SqlRowsCopiedEventHandler eventHandler)
{
bool hitException = false;
try
{
if (null == _tx || null == _tx.Connection)
{
_tx = _dstConn.BeginTransaction();
_dstcmd.Transaction = _tx;
_dstcmd.ExecuteNonQuery();
}
SqlBulkCopy bulkcopy;
using (bulkcopy = new SqlBulkCopy(_dstConn, SqlBulkCopyOptions.Default, _tx))
{
bulkcopy.SqlRowsCopied += eventHandler;
bulkcopy.DestinationTableName = _dstTable;
bulkcopy.NotifyAfter = 10;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "orderid");
ColumnMappings.Add(1, "customerid");
ColumnMappings.Add(4, "rdate");
ColumnMappings.Add(7, "freight");
ColumnMappings.Add(8, "shipname");
bulkcopy.WriteToServer(_dataTable);
bulkcopy.SqlRowsCopied -= eventHandler;
_tx.Commit();
}
}
catch (Exception e)
{
while (null != e.InnerException)
{
e = e.InnerException;
}
Assert.True(e is InvalidOperationException, "Unexpected exception type: " + e.GetType());
Assert.True(e.Message.Contains(expectedErrorMsg), string.Format("Incorrect error message. Expected: {0}. Actual: {1}.", expectedErrorMsg, e.Message));
_tx.Dispose();
hitException = true;
}
Assert.True(hitException, "Did not encounter expected exception.");
}
// public void Load(DataTable data) {
// var tableName = "TestTable";
// var databaseName = "TestDB";
// var serverName = ".\\sqlexpress";
// var connStr = $"Data Source={serverName}; Database={databaseName}; Integrated Security=SSPI;";
//
//
// using (var conn = new SqlConnection(connStr)) {
// conn.Open();
// using (var bulk = new SqlBulkCopy(conn)) {
// bulk.DestinationTableName = tableName;
// AddtMappings( bulk.ColumnMappings);
// bulk.WriteToServer(data);
// }
// }
// }
private void AddtMapping(SqlBulkCopyColumnMappingCollection map, dynamic mapPair)
{
var source = mapPair.source.ToString();
var dest = mapPair.destination.ToString();
if (string.IsNullOrEmpty(source))
{
throw new ArgumentNullException("loader mapping is invalid because source is missing ");
}
if (string.IsNullOrEmpty(dest))
{
throw new ArgumentNullException("loader mapping is invalid because destination is missing");
}
map.Add(source, dest);
}
/// <summary>
/// 实体字段映射数据库列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="mappings"></param>
private void GenerateColumnMappings <TBulk>(SqlBulkCopyColumnMappingCollection mappings) where TBulk : class
{
var properties = typeof(TBulk).GetProperties();
foreach (var property in properties)
{
if (property.GetCustomAttributes(typeof(KeyAttribute), true).Any())
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, typeof(T).Name + property.Name));
}
else
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, property.Name));
}
}
}
private void GenerateColumnMappings <T, TKey>(SqlBulkCopyColumnMappingCollection mappings)
where T : BaseModel <TKey>
{
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
if (property.GetCustomAttributes <KeyAttribute>().Any())
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, typeof(T).Name + property.Name));
}
else
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, property.Name));
}
}
}
public void Insert_BehavesAsExpected()
{
var item1 = new SqlBulkCopyColumnMapping(0, 0);
var item2 = new SqlBulkCopyColumnMapping(1, 1);
var item3 = new SqlBulkCopyColumnMapping(2, 2);
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
collection.Insert(0, item3);
collection.Insert(0, item2);
collection.Insert(0, item1);
Assert.Equal(3, collection.Count);
Assert.Same(item1, collection[0]);
Assert.Same(item2, collection[1]);
Assert.Same(item3, collection[2]);
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.Execute(dstCmd, "create table " + dstTable + " (orderid int, customerid nchar(5), rdate datetime, freight money, shipname nvarchar(40))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 100 * from orders", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkcopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowCopied);
bulkcopy.DestinationTableName = dstTable;
bulkcopy.NotifyAfter = 50;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("OrderID", "orderid");
ColumnMappings.Add("CustomerID", "customerid");
ColumnMappings.Add("RequiredDate", "rdate");
ColumnMappings.Add("Freight", "freight");
ColumnMappings.Add("ShipName", "shipname");
bulkcopy.NotifyAfter = 3;
DataTestUtility.AssertThrowsWrapper <OperationAbortedException>(() => bulkcopy.WriteToServer(reader));
bulkcopy.SqlRowsCopied -= new SqlRowsCopiedEventHandler(OnRowCopied);
bulkcopy.Close();
}
}
}
finally
{
Helpers.Execute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (SqlConnection conn3 = new SqlConnection(srcConstr))
{
conn3.Open();
// Start a local transaction on the wrong connection.
SqlTransaction myTrans = conn3.BeginTransaction();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.Default, myTrans))
{
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
bulkcopy.DestinationTableName = dstTable;
string exceptionMsg = SystemDataResourceManager.Instance.ADP_TransactionConnectionMismatch;
DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => bulkcopy.WriteToServer(reader), exceptionMessage: exceptionMsg);
SqlCommand myCmd = dstConn.CreateCommand();
myCmd.CommandText = "select * from " + dstTable;
myCmd.Transaction = myTrans;
DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => myCmd.ExecuteReader(), exceptionMessage: exceptionMsg);
}
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
